|
|
You're in the
sql server dts
group:What's the best way to keep a live db and an archive db?
sql server dts:
I have a live, production db that I'd like to keep lean and mean for
performance reasons. Every so often, I'll run a cleanup script to scrub out some older data in order to keep the tables from growing indefinitely and slowing my querying speed. But some employees would like to query ALL the data, the old and the new. What's a good way of doing something like this? Is there a tool that will allow me to copy new, production data from some checkpoint over to the archive db, and then I can run my cleanup script on the production db?
Not that I am aware of, you will need to write your own. You could use
partitioning to simplify the process, if not SSIS may be good start -- Simon Sabin SQL Server MVP http://sqljunkies.com/weblog/simons [quoted text, click to view] "0to60" <holeshot60_nospam_@yahoo.com> wrote in message news:O5bjMx%23YGHA.1220@TK2MSFTNGP02.phx.gbl... >I have a live, production db that I'd like to keep lean and mean for >performance reasons. Every so often, I'll run a cleanup script to scrub >out some older data in order to keep the tables from growing indefinitely >and slowing my querying speed. But some employees would like to query ALL >the data, the old and the new. What's a good way of doing something like >this? Is there a tool that will allow me to copy new, production data from >some checkpoint over to the archive db, and then I can run my cleanup >script on the production db? >
Create a transactional publication with bogus filters which look like this
1=1. Edit the FLTR_XXXX procs to return false when you wish to run your clean up scripts in production. After you have completed your scrub, modify the FLTR procs back to returning true. You could also drop your subscriber, do your scrub, and then do a no sync subscription. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 [quoted text, click to view] "0to60" <holeshot60_nospam_@yahoo.com> wrote in message news:O5bjMx%23YGHA.1220@TK2MSFTNGP02.phx.gbl... >I have a live, production db that I'd like to keep lean and mean for >performance reasons. Every so often, I'll run a cleanup script to scrub >out some older data in order to keep the tables from growing indefinitely >and slowing my querying speed. But some employees would like to query ALL >the data, the old and the new. What's a good way of doing something like >this? Is there a tool that will allow me to copy new, production data from >some checkpoint over to the archive db, and then I can run my cleanup >script on the production db? >
0to60 (holeshot60_nospam_@yahoo.com) writes:
[quoted text, click to view] > I have a live, production db that I'd like to keep lean and mean for > performance reasons. Every so often, I'll run a cleanup script to scrub > out some older data in order to keep the tables from growing > indefinitely and slowing my querying speed. But some employees would > like to query ALL the data, the old and the new. What's a good way of > doing something like this? Is there a tool that will allow me to copy > new, production data from some checkpoint over to the archive db, and > then I can run my cleanup script on the production db? What DB sizes are we talking about? It might be an idea to review indexing and such, so that you can handle the larger data volumes. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
On the back of Hilary's suggestions, we have an application in which there
are no deletes apart from DBA deletes of archived data. If this is the case for yourself, you can use transactional replication and disable the delete propagation (@del_cmd = NONE). Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
That's a truly great suggestion Paul, but you can't toggle this with active
subscribers. Trying to do so gives the following: sp_changearticle 'pubs','authors','del_cmd','none' Server: Msg 20608, Level 16, State 1, Procedure sp_MSreinit_article, Line 122 Cannot make the change because there are active subscriptions. Set @force_reinit_subscription to 1 to force the change and reinitialize the active subscriptions. So if you set the @force_reinit_subscription parameter to 1 as illustrated below - it works sp_changearticle 'pubs','authors','del_cmd','none',@force_reinit_subscription=1 Reinitialized subscription(s). Article update successful. The problem is the snapshot for this article is regenerated the next time the snapshot is run, or you kick it off, and if its big it will cause significant locking on the base tables and take time to send to the subscriber(s), and then you have to go through the entire process again to re-enable the deletes. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:u2s29rFZGHA.4144@TK2MSFTNGP04.phx.gbl... > On the back of Hilary's suggestions, we have an application in which there > are no deletes apart from DBA deletes of archived data. If this is the > case for yourself, you can use transactional replication and disable the > delete propagation (@del_cmd = NONE). > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > >
I follow your point, but I wasn't suggesting toggling dynamically - rather
setting up the publication initially this way. In our app we could disable all deletes from replicating, as the only deletes ever done are archiving DBA ones on the publisher, and app deletes are purely logical. This may or may not be relevant to 0to60 - it depends on his app. Cheers, Paul [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:ud1mzdGZGHA.3448@TK2MSFTNGP04.phx.gbl... > That's a truly great suggestion Paul, but you can't toggle this with > active subscribers. > > Trying to do so gives the following: > > sp_changearticle 'pubs','authors','del_cmd','none' > Server: Msg 20608, Level 16, State 1, Procedure sp_MSreinit_article, Line > 122 > Cannot make the change because there are active subscriptions. Set > @force_reinit_subscription to 1 to force the change and reinitialize the > active subscriptions. > > So if you set the @force_reinit_subscription parameter to 1 as illustrated > below - it works > > sp_changearticle > 'pubs','authors','del_cmd','none',@force_reinit_subscription=1 > > Reinitialized subscription(s). > Article update successful. > > The problem is the snapshot for this article is regenerated the next time > the snapshot is run, or you kick it off, and if its big it will cause > significant locking on the base tables and take time to send to the > subscriber(s), and then you have to go through the entire process again to > re-enable the deletes. > > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > 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 > > > > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > news:u2s29rFZGHA.4144@TK2MSFTNGP04.phx.gbl... >> On the back of Hilary's suggestions, we have an application in which >> there are no deletes apart from DBA deletes of archived data. If this is >> the case for yourself, you can use transactional replication and disable >> the delete propagation (@del_cmd = NONE). >> Cheers, >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> (recommended sql server 2000 replication book: >> http://www.nwsu.com/0974973602p.html) >> >> > >
Another good point. Many of my customers need to keep a rolling month, but
want some deletes to flow, hence my filter modification option where you can dynamically toggle it via a proc or job step. My option does have the draw back of not only ignoring deletes, but also inserts or updates while I set the filter condition to false. Clearly there is no perfect solution to this problem - or none that has been suggested or coded yet. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:Ob5T0hGZGHA.1200@TK2MSFTNGP03.phx.gbl... >I follow your point, but I wasn't suggesting toggling dynamically - rather >setting up the publication initially this way. > In our app we could disable all deletes from replicating, as the only > deletes ever done are archiving DBA ones on the publisher, and app deletes > are purely logical. > This may or may not be relevant to 0to60 - it depends on his app. > Cheers, > Paul > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:ud1mzdGZGHA.3448@TK2MSFTNGP04.phx.gbl... >> That's a truly great suggestion Paul, but you can't toggle this with >> active subscribers. >> >> Trying to do so gives the following: >> >> sp_changearticle 'pubs','authors','del_cmd','none' >> Server: Msg 20608, Level 16, State 1, Procedure sp_MSreinit_article, Line >> 122 >> Cannot make the change because there are active subscriptions. Set >> @force_reinit_subscription to 1 to force the change and reinitialize the >> active subscriptions. >> >> So if you set the @force_reinit_subscription parameter to 1 as >> illustrated below - it works >> >> sp_changearticle >> 'pubs','authors','del_cmd','none',@force_reinit_subscription=1 >> >> Reinitialized subscription(s). >> Article update successful. >> >> The problem is the snapshot for this article is regenerated the next time >> the snapshot is run, or you kick it off, and if its big it will cause >> significant locking on the base tables and take time to send to the >> subscriber(s), and then you have to go through the entire process again >> to re-enable the deletes. >> >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> 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 >> >> >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> news:u2s29rFZGHA.4144@TK2MSFTNGP04.phx.gbl... >>> On the back of Hilary's suggestions, we have an application in which >>> there are no deletes apart from DBA deletes of archived data. If this is >>> the case for yourself, you can use transactional replication and disable >>> the delete propagation (@del_cmd = NONE). >>> Cheers, >>> Paul Ibison SQL Server MVP, www.replicationanswers.com >>> (recommended sql server 2000 replication book: >>> http://www.nwsu.com/0974973602p.html) >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |