we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
>> we are frequently running the maintenance tasks on production during that
>> databases are not accessible.
>> to avoid this we want to switch over to the standby server during
>> maintenace and after maintenance has been completed
>> switch back to primary server.During maintenance all data changes need to
>> be replicated back to production server.
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>> To accomplish this I am planning to setup database replication from
>> producation database server to Standby server.
>> Please advise best solutions for this like microsoft trnsaction
>> replication,Goldengate replication, wansync replication.
>> I think microsoft trnsaction replication,Goldengate replication doesn't
>> support complex shema changes to get replicated.
>> Requirement :
>> 1. 10 databases need to get replicated (with 30GB - 70GB each)
>> 2. these 10 databases will have schema changes monthly.
>> 3. data changes need to moved from standby to production server after
>> maintenance has been completed.
>> Please advise
>> Thanks
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Tuesday, March 27, 2012
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
I think bi-directional transactional replication is ideal for this. Point
the feed to the standby when you want to do maintenance on the primary. Then
stop the distribution agents, do your work, and restart the agents, and then
repoint to the primary.
When you do your schema changes you will have to tear down replication on
both sides and recreate it after you have done the schema changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ObcjXaJMHHA.5000@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
I think bi-directional transactional replication is ideal for this. Point
the feed to the standby when you want to do maintenance on the primary. Then
stop the distribution agents, do your work, and restart the agents, and then
repoint to the primary.
When you do your schema changes you will have to tear down replication on
both sides and recreate it after you have done the schema changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ObcjXaJMHHA.5000@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
best solutions : sql server replication (Maintenance)
we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com
Sunday, March 25, 2012
Best Replication Type please
Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> Hello,
>> We are implementing a high availablity solution to our
SQL
>> Servers, so if our production server goes down we have a
>> standby server ready to go.
>> So we would like to know the best type of replication,
>> bascially we want to update the stand-by server every 5
>> minutes. We cannot use the log shipping as we backup the
>> transaction log every 10 minutes.
>> Personally I think we should be using snapshot
>> replication, does anyone have any major disagreements
with
>> this ?
>> Thanks
>> Peter
>>
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> >--Original Message--
> >Snapshot replication would give you a complete copy of
> the publication ....
> >Probably some sort of incremental thing might be
> better... perhaps
> >transactional...
> >
> >But be aware-- replication does NOT replicate system
> tables, so
> >permissions, etc will not be replicated..
> >
> >Also, SQL Replication is not very friendly about Schema
> changes ,
> >adding/dropping columns, or changing data types...
> >
> >For these reasons, may people use log shipping.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are implementing a high availablity solution to our
> SQL
> >> Servers, so if our production server goes down we have a
> >> standby server ready to go.
> >>
> >> So we would like to know the best type of replication,
> >> bascially we want to update the stand-by server every 5
> >> minutes. We cannot use the log shipping as we backup the
> >> transaction log every 10 minutes.
> >>
> >> Personally I think we should be using snapshot
> >> replication, does anyone have any major disagreements
> with
> >> this ?
> >>
> >> Thanks
> >> Peter
> >>
> >>
> >
> >
> >.
> >|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
>> Thanks Wayne,
>> I would love to use Log Shipping, but I'm assuming that
it
>> produces the same internal result to the log file as a
log
>> file backup, if that the case then the backup we have in
>> place will not work. This is somewhat of a 'legacy'
>> backup, and one I have been specifically told not to
touch.
>> Again thanks for help and I will look into what you have
>> said.
>> Peter
>> >--Original Message--
>> >Snapshot replication would give you a complete copy of
>> the publication ....
>> >Probably some sort of incremental thing might be
>> better... perhaps
>> >transactional...
>> >
>> >But be aware-- replication does NOT replicate system
>> tables, so
>> >permissions, etc will not be replicated..
>> >
>> >Also, SQL Replication is not very friendly about Schema
>> changes ,
>> >adding/dropping columns, or changing data types...
>> >
>> >For these reasons, may people use log shipping.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are implementing a high availablity solution to
our
>> SQL
>> >> Servers, so if our production server goes down we
have a
>> >> standby server ready to go.
>> >>
>> >> So we would like to know the best type of
replication,
>> >> bascially we want to update the stand-by server
every 5
>> >> minutes. We cannot use the log shipping as we backup
the
>> >> transaction log every 10 minutes.
>> >>
>> >> Personally I think we should be using snapshot
>> >> replication, does anyone have any major disagreements
>> with
>> >> this ?
>> >>
>> >> Thanks
>> >> Peter
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bcp files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option which also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future
Regards
Paul Ibison
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> Hello,
>> We are implementing a high availablity solution to our
SQL
>> Servers, so if our production server goes down we have a
>> standby server ready to go.
>> So we would like to know the best type of replication,
>> bascially we want to update the stand-by server every 5
>> minutes. We cannot use the log shipping as we backup the
>> transaction log every 10 minutes.
>> Personally I think we should be using snapshot
>> replication, does anyone have any major disagreements
with
>> this ?
>> Thanks
>> Peter
>>
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> >--Original Message--
> >Snapshot replication would give you a complete copy of
> the publication ....
> >Probably some sort of incremental thing might be
> better... perhaps
> >transactional...
> >
> >But be aware-- replication does NOT replicate system
> tables, so
> >permissions, etc will not be replicated..
> >
> >Also, SQL Replication is not very friendly about Schema
> changes ,
> >adding/dropping columns, or changing data types...
> >
> >For these reasons, may people use log shipping.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are implementing a high availablity solution to our
> SQL
> >> Servers, so if our production server goes down we have a
> >> standby server ready to go.
> >>
> >> So we would like to know the best type of replication,
> >> bascially we want to update the stand-by server every 5
> >> minutes. We cannot use the log shipping as we backup the
> >> transaction log every 10 minutes.
> >>
> >> Personally I think we should be using snapshot
> >> replication, does anyone have any major disagreements
> with
> >> this ?
> >>
> >> Thanks
> >> Peter
> >>
> >>
> >
> >
> >.
> >|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
>> Thanks Wayne,
>> I would love to use Log Shipping, but I'm assuming that
it
>> produces the same internal result to the log file as a
log
>> file backup, if that the case then the backup we have in
>> place will not work. This is somewhat of a 'legacy'
>> backup, and one I have been specifically told not to
touch.
>> Again thanks for help and I will look into what you have
>> said.
>> Peter
>> >--Original Message--
>> >Snapshot replication would give you a complete copy of
>> the publication ....
>> >Probably some sort of incremental thing might be
>> better... perhaps
>> >transactional...
>> >
>> >But be aware-- replication does NOT replicate system
>> tables, so
>> >permissions, etc will not be replicated..
>> >
>> >Also, SQL Replication is not very friendly about Schema
>> changes ,
>> >adding/dropping columns, or changing data types...
>> >
>> >For these reasons, may people use log shipping.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are implementing a high availablity solution to
our
>> SQL
>> >> Servers, so if our production server goes down we
have a
>> >> standby server ready to go.
>> >>
>> >> So we would like to know the best type of
replication,
>> >> bascially we want to update the stand-by server
every 5
>> >> minutes. We cannot use the log shipping as we backup
the
>> >> transaction log every 10 minutes.
>> >>
>> >> Personally I think we should be using snapshot
>> >> replication, does anyone have any major disagreements
>> with
>> >> this ?
>> >>
>> >> Thanks
>> >> Peter
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bcp files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option which also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future
Regards
Paul Ibison
Best Replication Type please
Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
Peter
Snapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>
|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with
>
>.
>
|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with
|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the
>
>.
>
|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option whic
h also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibison
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
Peter
Snapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>
|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with
>
>.
>
|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with
|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the
>
>.
>
|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option whic
h also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibison
Best Replication Type please
Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with[vbcol=seagreen]
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and
Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a6
01280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
>
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, t
hen transactional with queued updating subscribers may be used in this situa
tion. Snapshot could be used but this depends on the size of your system - c
reating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By d
efault, standard transactional will not take defaults and identity columns (
although the schema can be scripted), so it's easier to use the queued updat
ing subscribers option whic
h also gives the ability to send back to the publisher any changes subsequen
tly made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibisonsql
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with[vbcol=seagreen]
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and
Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a6
01280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
>
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, t
hen transactional with queued updating subscribers may be used in this situa
tion. Snapshot could be used but this depends on the size of your system - c
reating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By d
efault, standard transactional will not take defaults and identity columns (
although the schema can be scripted), so it's easier to use the queued updat
ing subscribers option whic
h also gives the ability to send back to the publisher any changes subsequen
tly made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibisonsql
Thursday, March 22, 2012
Best Practices, Create a Test DB Subset
Hello, we have a production DB that is 125 GB. Lots of tables. Is there an
automated way to extract say 10% of all those records? Thanks.Hi,
You have to write your own query to extract 10% data from each table.
Thanks
Hari
SQL Server MVP
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:7F1A5FC8-6431-49E0-812B-14ECEC8998CB@.microsoft.com...
> Hello, we have a production DB that is 125 GB. Lots of tables. Is there
> an
> automated way to extract say 10% of all those records? Thanks.|||That's going to be very time consuming as there are 100+ user tables. I was
hoping to perhaps create something more automated.
"Hari Pra
" wrote:
> Hi,
> You have to write your own query to extract 10% data from each table.
> Thanks
> Hari
> SQL Server MVP
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:7F1A5FC8-6431-49E0-812B-14ECEC8998CB@.microsoft.com...
>
>|||Try something like this..
select
name, id
into
#TempTables
from
sysobjects
where
type = 'u'
declare @.CurrentID int
select @.CurrentID = min(id) from #TempTables
while (@.currentid is not null)
begin
-- Currently just selecting, but you could dynamically add new tables into
the DB from here.
declare @.sqltext nvarchar(2000)
set @.sqltext = N'select top 10 percent * from ' + (select name from
#TempTables where id = @.currentid)
exec sp_executesql @.sqltext
select @.CurrentID = min(id) from #TempTables where id > @.CurrentID
end
"Pancho" wrote:
> Hello, we have a production DB that is 125 GB. Lots of tables. Is there
an
> automated way to extract say 10% of all those records? Thanks.|||Pancho
See if this helps
EXEC sp_msforeachtable 'select top 10 percent * from ?'
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D790FDC0-4F2D-452F-8E47-B73FAC4A9D95@.microsoft.com...
> That's going to be very time consuming as there are 100+ user tables. I
> was
> hoping to perhaps create something more automated.
> "Hari Pra" wrote:
>sql
automated way to extract say 10% of all those records? Thanks.Hi,
You have to write your own query to extract 10% data from each table.
Thanks
Hari
SQL Server MVP
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:7F1A5FC8-6431-49E0-812B-14ECEC8998CB@.microsoft.com...
> Hello, we have a production DB that is 125 GB. Lots of tables. Is there
> an
> automated way to extract say 10% of all those records? Thanks.|||That's going to be very time consuming as there are 100+ user tables. I was
hoping to perhaps create something more automated.
"Hari Pra
" wrote:> Hi,
> You have to write your own query to extract 10% data from each table.
> Thanks
> Hari
> SQL Server MVP
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:7F1A5FC8-6431-49E0-812B-14ECEC8998CB@.microsoft.com...
>
>|||Try something like this..
select
name, id
into
#TempTables
from
sysobjects
where
type = 'u'
declare @.CurrentID int
select @.CurrentID = min(id) from #TempTables
while (@.currentid is not null)
begin
-- Currently just selecting, but you could dynamically add new tables into
the DB from here.
declare @.sqltext nvarchar(2000)
set @.sqltext = N'select top 10 percent * from ' + (select name from
#TempTables where id = @.currentid)
exec sp_executesql @.sqltext
select @.CurrentID = min(id) from #TempTables where id > @.CurrentID
end
"Pancho" wrote:
> Hello, we have a production DB that is 125 GB. Lots of tables. Is there
an
> automated way to extract say 10% of all those records? Thanks.|||Pancho
See if this helps
EXEC sp_msforeachtable 'select top 10 percent * from ?'
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D790FDC0-4F2D-452F-8E47-B73FAC4A9D95@.microsoft.com...
> That's going to be very time consuming as there are 100+ user tables. I
> was
> hoping to perhaps create something more automated.
> "Hari Pra
" wrote:>sql
Best Practices of cube release process
Have you guys come across best way to move/release cube changes from test to production server. Any way to automate this process.
- Ashok
You're probably going to want to give the Deployment Wizard a try:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1485584&SiteID=1
|||The Deployment Wizard is probably a good option. It will handle things like not overwriting datasources, roles and partitions which can be handy if these vary between your environments (which they often do). This tool can also be run from the command line which allows for a degree of automation.Tuesday, March 20, 2012
Best practices for copying databases...
I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.
Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.
|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<data base_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
what the best process is for copying multiple databases from the production
server to the test server.
Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.
|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<data base_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
Best practices for copying databases...
I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the productio
n
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.b
ak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the productio
n
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.b
ak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com
Best practices for copying databases...
I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.comsql
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.comsql
Monday, March 19, 2012
Best Practice to deploy reports
I'm a DBA and need to deploy the reports our developers made in a test
environment on the production servers.
What is the best way of doing this.
Are there some how to's, white papers or best practices?
Regards"Zekske" <Zekske@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2291B63E-7BC4-432B-A871-B0903C7E5179@.microsoft.com
> I'm a DBA and need to deploy the reports our developers made in a test
> environment on the production servers.
> What is the best way of doing this.
> Are there some how to's, white papers or best practices?
perhaps the tool could help:
Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Gruß Olaf
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||If you are allowed to use Visual Source Safe, I would:..
Create Configurations for each of your environments in BIDS.
Save all in Source Safe.
Then use BIDS to deploy using the configurations...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Zekske" wrote:
> I'm a DBA and need to deploy the reports our developers made in a test
> environment on the production servers.
> What is the best way of doing this.
> Are there some how to's, white papers or best practices?
> Regards
environment on the production servers.
What is the best way of doing this.
Are there some how to's, white papers or best practices?
Regards"Zekske" <Zekske@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2291B63E-7BC4-432B-A871-B0903C7E5179@.microsoft.com
> I'm a DBA and need to deploy the reports our developers made in a test
> environment on the production servers.
> What is the best way of doing this.
> Are there some how to's, white papers or best practices?
perhaps the tool could help:
Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Gruß Olaf
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||If you are allowed to use Visual Source Safe, I would:..
Create Configurations for each of your environments in BIDS.
Save all in Source Safe.
Then use BIDS to deploy using the configurations...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Zekske" wrote:
> I'm a DBA and need to deploy the reports our developers made in a test
> environment on the production servers.
> What is the best way of doing this.
> Are there some how to's, white papers or best practices?
> Regards
Friday, February 24, 2012
Best backup plan for 24*7 running production database
Hi:
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John
Best approach to updating remote server?
I have a local production server, and a web server at a remote ISP connected
via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
of data on the production server and update it to the web server daily. The
web server only gets a subset of the information on the production server.
I know I can't be the first to need this so what I am searching for is the
best way to accomplish this. Do I refresh the web server daily (about 500MB
of data)? Should I send each table with an indicator as to
Add/Change/Delete, and write a procedure to import? or is there just another
solution I am overlooking.
Any suggestions would be appreciated.
TIA, MarkHi Mark.
A common approach to achieving this is to create a DTS package that
encapsulates the process, applies business rules to the data etc & then
schedule it to run from the local production server. I've done this with
various ISP hosted SQL databases with fairly good success.
Is your ISP hosted SQL Server dedicated or shared? If dedicated, many
options may open up to you that a shared server might have available.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:enU%238d8DEHA.2932@.tk2msftngp13.phx.gbl...
> I have a local production server, and a web server at a remote ISP
connected
> via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
> of data on the production server and update it to the web server daily.
The
> web server only gets a subset of the information on the production server.
> I know I can't be the first to need this so what I am searching for is the
> best way to accomplish this. Do I refresh the web server daily (about
500MB
> of data)? Should I send each table with an indicator as to
> Add/Change/Delete, and write a procedure to import? or is there just
another
> solution I am overlooking.
> Any suggestions would be appreciated.
> TIA, Mark
>
via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
of data on the production server and update it to the web server daily. The
web server only gets a subset of the information on the production server.
I know I can't be the first to need this so what I am searching for is the
best way to accomplish this. Do I refresh the web server daily (about 500MB
of data)? Should I send each table with an indicator as to
Add/Change/Delete, and write a procedure to import? or is there just another
solution I am overlooking.
Any suggestions would be appreciated.
TIA, MarkHi Mark.
A common approach to achieving this is to create a DTS package that
encapsulates the process, applies business rules to the data etc & then
schedule it to run from the local production server. I've done this with
various ISP hosted SQL databases with fairly good success.
Is your ISP hosted SQL Server dedicated or shared? If dedicated, many
options may open up to you that a shared server might have available.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:enU%238d8DEHA.2932@.tk2msftngp13.phx.gbl...
> I have a local production server, and a web server at a remote ISP
connected
> via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
> of data on the production server and update it to the web server daily.
The
> web server only gets a subset of the information on the production server.
> I know I can't be the first to need this so what I am searching for is the
> best way to accomplish this. Do I refresh the web server daily (about
500MB
> of data)? Should I send each table with an indicator as to
> Add/Change/Delete, and write a procedure to import? or is there just
another
> solution I am overlooking.
> Any suggestions would be appreciated.
> TIA, Mark
>
Best (and simplest) practice for refreshing Test data
What is the best and simplest way to script a straightforward refresh of Test data from Production data in MS SQL 2005/Transact-SQL? (Just the data, not the objects.) Thanks.
If you have not yet had an opportunity to explore the Data Generator included with Visual Studio Team System for Database Professionals, I highly recommend that you download the 180 day eval and let the Data Generator 'blow your socks off'.
http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx
http://msdn2.microsoft.com/en-us/teamsystem/aa718764.aspx
http://msdn2.microsoft.com/en-us/teamsystem/bb507197.aspx
Here you can download a virtualized version:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9D60655E-814C-40A8-9762-53A40D8E7B37&displaylang=en
After you see the potentials for Test and Development data generation, you will no long try writing your own scripts.
|||A series o scripts with "bcp out" from Production then another series with "bcp in" to Development (after a series of "truncate table ..").
Subscribe to:
Posts (Atom)