|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What's the best way to keep a live db and an archive db?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 Show quote "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:
> I have a live, production db that I'd like to keep lean and mean for What DB sizes are we talking about?> 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? It might be an idea to review indexing and such, so that you can handle the larger data volumes. -- Erland Sommarskog, SQL Server MVP, esq***@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 http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 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. -- Show quoteHilary 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 "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? > 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. -- Show quoteHilary 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.Ibi***@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 Show quote "Hilary Cotter" <hilary.cot***@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.Ibi***@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. -- Show quoteHilary 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.Ibi***@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.cot***@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.Ibi***@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) >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics