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 solutions. Show all posts
Showing posts with label solutions. 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 11, 2012
Best Practice for Report Projects Related To Application Solutions
Hello Reporting Services Gurus!
I'm about to start on my first reporting services project, but before I mess it up, I'm looking for some guidance on how best to achieve my mission. Here's what I'm looking to achieve:
I have a datacentric application (SQL Server 2005 Express w/ Advanced Services backend) in which I want to build about 50 "canned" reports for the end users. I want to build the reports utilizing server mode so I can take advantage of some of Reporting Services advanced features. I'm not sure what the best practice would be to build the reporting services project. Is it better to include the report project as another project within the application solution? Or, should I build the report project independent of the application solution? What are the pros and cons of doing it either way? How does including the report project build if it's included in the application solution? How would a ClickOnce deployment deploy the report project to the report server?
My ultimate goal would be to have an "off-the-shelf" software solution that includes an installation package consisting of the application project and report project. Is it even possible due to the Reporting Services architecture to achieve an install in this manner with ClickOnce, Windows Installer, or Installshield? Or, is building the report project indepedent of the application project and deploying the reports to the report server "manually" (i.e. deploy within the report server project) the only solution?
Any help would be greatly appreciated!
Tony
I haven't received any feedback yet, so I thought I'd try bumping it back to the top of the thread.
Thanks for any help!
Subscribe to:
Posts (Atom)