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 tasks. Show all posts
Showing posts with label tasks. 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 maintenance plans
Hi,
with SQL 2k the maintenance plan wizard proposed defaults for all
maintenance tasks. With SQL 2005 this is gone.
What are the best practices to setup a "normal" SQL 2005 maintenance?
Thanks for any advice
Greg
--
Greg JimsonHi Greg,
You should be installed SSIS (Integration Service) to be able to use it, or
just to install Service Packets would be enough as well.
Maintenance still stands in the Management node in SQL Server 2005. Go to
Maintenance Plans and right click on it.
Click Maintenance Plan Wizard.
--
Ekrem Önsoy
"Greg Jimson" <nospam@.hotmail.com> wrote in message
news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> with SQL 2k the maintenance plan wizard proposed defaults for all
> maintenance tasks. With SQL 2005 this is gone.
> What are the best practices to setup a "normal" SQL 2005 maintenance?
> Thanks for any advice
> Greg
> --
> Greg Jimson
>|||Hi Ekrem,
thanks for you answer. May I specify my question. What Task need to be
setup? Shrink, History Clean up, etc.?
Thanks in advance
Greg
--
Greg Jimson
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:147C4862-4C46-48F8-8440-4A2AB441AFDE@.microsoft.com...
> Hi Greg,
>
> You should be installed SSIS (Integration Service) to be able to use it,
> or just to install Service Packets would be enough as well.
> Maintenance still stands in the Management node in SQL Server 2005. Go to
> Maintenance Plans and right click on it.
> Click Maintenance Plan Wizard.
> --
> Ekrem Önsoy
>
>
> "Greg Jimson" <nospam@.hotmail.com> wrote in message
> news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> with SQL 2k the maintenance plan wizard proposed defaults for all
>> maintenance tasks. With SQL 2005 this is gone.
>> What are the best practices to setup a "normal" SQL 2005 maintenance?
>> Thanks for any advice
>> Greg
>> --
>> Greg Jimson
>|||It depends on what you want to do and your needs.
In the "Select Maintenance Tasks" window at the bottom side you will see the
description box which describes each option when you click on them.
For instance, you schedule "Maintenance Cleanup Task" to remove files left
over from executing a maintenance plan.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT
"Greg Jimson" <nospam@.hotmail.com> wrote in message
news:eqHkn4V7HHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Ekrem,
> thanks for you answer. May I specify my question. What Task need to be
> setup? Shrink, History Clean up, etc.?
> Thanks in advance
> Greg
> --
> Greg Jimson
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:147C4862-4C46-48F8-8440-4A2AB441AFDE@.microsoft.com...
>> Hi Greg,
>>
>> You should be installed SSIS (Integration Service) to be able to use it,
>> or just to install Service Packets would be enough as well.
>> Maintenance still stands in the Management node in SQL Server 2005. Go to
>> Maintenance Plans and right click on it.
>> Click Maintenance Plan Wizard.
>> --
>> Ekrem Önsoy
>>
>>
>> "Greg Jimson" <nospam@.hotmail.com> wrote in message
>> news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> with SQL 2k the maintenance plan wizard proposed defaults for all
>> maintenance tasks. With SQL 2005 this is gone.
>> What are the best practices to setup a "normal" SQL 2005 maintenance?
>> Thanks for any advice
>> Greg
>> --
>> Greg Jimson
>>
>
with SQL 2k the maintenance plan wizard proposed defaults for all
maintenance tasks. With SQL 2005 this is gone.
What are the best practices to setup a "normal" SQL 2005 maintenance?
Thanks for any advice
Greg
--
Greg JimsonHi Greg,
You should be installed SSIS (Integration Service) to be able to use it, or
just to install Service Packets would be enough as well.
Maintenance still stands in the Management node in SQL Server 2005. Go to
Maintenance Plans and right click on it.
Click Maintenance Plan Wizard.
--
Ekrem Önsoy
"Greg Jimson" <nospam@.hotmail.com> wrote in message
news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> with SQL 2k the maintenance plan wizard proposed defaults for all
> maintenance tasks. With SQL 2005 this is gone.
> What are the best practices to setup a "normal" SQL 2005 maintenance?
> Thanks for any advice
> Greg
> --
> Greg Jimson
>|||Hi Ekrem,
thanks for you answer. May I specify my question. What Task need to be
setup? Shrink, History Clean up, etc.?
Thanks in advance
Greg
--
Greg Jimson
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:147C4862-4C46-48F8-8440-4A2AB441AFDE@.microsoft.com...
> Hi Greg,
>
> You should be installed SSIS (Integration Service) to be able to use it,
> or just to install Service Packets would be enough as well.
> Maintenance still stands in the Management node in SQL Server 2005. Go to
> Maintenance Plans and right click on it.
> Click Maintenance Plan Wizard.
> --
> Ekrem Önsoy
>
>
> "Greg Jimson" <nospam@.hotmail.com> wrote in message
> news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> with SQL 2k the maintenance plan wizard proposed defaults for all
>> maintenance tasks. With SQL 2005 this is gone.
>> What are the best practices to setup a "normal" SQL 2005 maintenance?
>> Thanks for any advice
>> Greg
>> --
>> Greg Jimson
>|||It depends on what you want to do and your needs.
In the "Select Maintenance Tasks" window at the bottom side you will see the
description box which describes each option when you click on them.
For instance, you schedule "Maintenance Cleanup Task" to remove files left
over from executing a maintenance plan.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT
"Greg Jimson" <nospam@.hotmail.com> wrote in message
news:eqHkn4V7HHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Ekrem,
> thanks for you answer. May I specify my question. What Task need to be
> setup? Shrink, History Clean up, etc.?
> Thanks in advance
> Greg
> --
> Greg Jimson
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:147C4862-4C46-48F8-8440-4A2AB441AFDE@.microsoft.com...
>> Hi Greg,
>>
>> You should be installed SSIS (Integration Service) to be able to use it,
>> or just to install Service Packets would be enough as well.
>> Maintenance still stands in the Management node in SQL Server 2005. Go to
>> Maintenance Plans and right click on it.
>> Click Maintenance Plan Wizard.
>> --
>> Ekrem Önsoy
>>
>>
>> "Greg Jimson" <nospam@.hotmail.com> wrote in message
>> news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> with SQL 2k the maintenance plan wizard proposed defaults for all
>> maintenance tasks. With SQL 2005 this is gone.
>> What are the best practices to setup a "normal" SQL 2005 maintenance?
>> Thanks for any advice
>> Greg
>> --
>> Greg Jimson
>>
>
Monday, February 13, 2012
Beginner T-SQL Question
I'd like to query my Tasks and TaskActivity Tables to return the following results:
Case Id
Estimated Hours
Total Hours
Remaining Hours
Because I'm an SQL novice, I've had to resort to a hack of creating three different views to get the results I want: I'm sure there's a better way (i.e. a single, well-formed query) but when I try that my numbers are all wrong.
Here's my hack to get the correct results:
First I have a view to get my Estimated Hours and CaseId from my TASKS table:
SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId
Then I have a view to get my Total Hours and CaseId from my TASKACTIVITES table:
SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
Finally I have a third view (that I actually use in my program) to put it all together:
SELECT vTaskActual.CaseId, vTaskEstimates.EstHrs, vTaskActual.TotalHrs,
vTaskActual.TotalHrs - vTaskEstimates.EstHrs AS RemHrs
FROM vTaskActual INNER JOIN
vTaskEstimates ON vTaskActual.CaseId = vTaskEstimates.CaseId
Any help would be greatly appreciated, and, if this is not the correct forum for newbie questions like this, please let me know.
TIA,
Rob
You can turn a query containing a single select statement into a derived table by surround it in parethesises and adding an alias. eg
select managers.name, managers.SSN, dept.departmentCode, dept.description
from department as dept
join (select name, SSN, deptid from employee where salary > 50.000) as managers
on dept.deptid = managers.deptid
So with the two views above, turn both into derived tables, and join:
select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,
TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs
from
(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId
) as TaskEstimates
INNER JOIN
(SELECT Tasks.CaseId, SUM(TaskActivity.Minutes / 60.00) AS TotalHrs
FROM TaskActivity RIGHT OUTER JOIN
Tasks ON TaskActivity.TaskId = Tasks.TaskId
GROUP BY Tasks.CaseId) as TaskActual
ON TaskActual.CaseId = TaskEstimates.CaseId
That should do it. As both derived table queries use the tasks table, it is likely that you can do the query in one query without using derived tables, but without having a better understanding of your schema and data I don't want to attempt that!
HTH
For more SQl tips, check out my blog:
|||Please indicate if this answered your question or not.|||Thanks for the reply it was very helpful.
I did have to make a few changes to your response (see below) because my results are gathered from two different tables: Tasks contains the estimate, and TaskActivity contains the records of actual time. The info on derived tables will really help clean-up my DB.
Thanks again!
P.S. I use "COALESCE" to assign 0.00 to any NULL values in EstHrs. It is working so I assume that is correct)
Here's the final query that's working:
select TaskActual.CaseId, TaskEstimates.EstHrs, TaskActual.TotalHrs,
TaskActual.TotalHrs - TaskEstimates.EstHrs AS RemHrs
from
(SELECT COALESCE (SUM(EstHrs), 0.00) AS EstHrs, CaseId
FROM dbo.Tasks
GROUP BY CaseId
) as TaskEstimates
INNER JOIN
(SELECT dbo.Tasks.CaseId, SUM(dbo.TaskActivity.Minutes / 60.00) AS TotalHrs
FROM dbo.TaskActivity RIGHT OUTER JOIN
dbo.Tasks ON dbo.TaskActivity.TaskId = dbo.Tasks.TaskId
GROUP BY dbo.Tasks.CaseId) as TaskActual
ON TaskActual.CaseId = TaskEstimates.CaseId
Friday, February 10, 2012
before delete triggers in MSSQL
Hi
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.
> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.
> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?
> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.
> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.
> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.
> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?
> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.
> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)