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 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