|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What's the recommended way for me to keep two databases in sync?I have a database that captures real time production data that I'm trying to
keep lean 'n mean. We don't want our analysts querying this db all day. So, we've decided to have a second db that we will keep in sync with the lean 'n mean production db, and the analysts can query this until the cows come home and have been properly quantified and analyzed. What would be the best way to keep this second db in sync with the first? Prolly the easiest thing to do would be a backup/restore operation once a day. But I'm wondering if there's a better solution, like maybe DTS or publish/subscribe. I'll need the most recent data, but also any structural changes to the tables and stuff too, and it can be run in some sort of a batch that we do once a day. Any recommendations? For SQL Server 2005 you would use database mirroring (syncronous mode
probably) and use database snapshots to allow the analysts to query on the other end. Replication would be more maintanence because of the schema change requirement. DB dump each night could do it but it will be out of sync. Hope that helps. Show quote "0to60" <holeshot60_nospam_@yahoo.com> wrote in message news:euwB%23ScIGHA.2012@TK2MSFTNGP14.phx.gbl... >I have a database that captures real time production data that I'm trying >to keep lean 'n mean. We don't want our analysts querying this db all day. >So, we've decided to have a second db that we will keep in sync with the >lean 'n mean production db, and the analysts can query this until the cows >come home and have been properly quantified and analyzed. What would be >the best way to keep this second db in sync with the first? Prolly the >easiest thing to do would be a backup/restore operation once a day. But >I'm wondering if there's a better solution, like maybe DTS or >publish/subscribe. I'll need the most recent data, but also any structural >changes to the tables and stuff too, and it can be run in some sort of a >batch that we do once a day. > > Any recommendations? > You can also do this with log shipping in standby mode.
-- Show quoteMike http://www.solidqualitylearning.com Disclaimer: This communication is an original work and represents my sole views on the subject. It does not represent the views of any other person or entity either by inference or direct reference. "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:erUs$VcIGHA.344@TK2MSFTNGP11.phx.gbl... > For SQL Server 2005 you would use database mirroring (syncronous mode > probably) and use database snapshots to allow the analysts to query on the > other end. > > Replication would be more maintanence because of the schema change > requirement. > > DB dump each night could do it but it will be out of sync. > > Hope that helps. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "0to60" <holeshot60_nospam_@yahoo.com> wrote in message > news:euwB%23ScIGHA.2012@TK2MSFTNGP14.phx.gbl... >>I have a database that captures real time production data that I'm trying >>to keep lean 'n mean. We don't want our analysts querying this db all >>day. So, we've decided to have a second db that we will keep in sync with >>the lean 'n mean production db, and the analysts can query this until the >>cows come home and have been properly quantified and analyzed. What would >>be the best way to keep this second db in sync with the first? Prolly the >>easiest thing to do would be a backup/restore operation once a day. But >>I'm wondering if there's a better solution, like maybe DTS or >>publish/subscribe. I'll need the most recent data, but also any structural >>changes to the tables and stuff too, and it can be run in some sort of a >>batch that we do once a day. >> >> Any recommendations? >> > > You can't apply the transaction log when somebody is in the database which
is the draw back for the standby and a reporting type system which is the good bit about the snapshot stuff in 2005. Show quote "Michael Hotek" <m***@solidqualitylearning.com> wrote in message news:%23o2sZ1dIGHA.1676@TK2MSFTNGP09.phx.gbl... > You can also do this with log shipping in standby mode. > > -- > Mike > http://www.solidqualitylearning.com > Disclaimer: This communication is an original work and represents my sole > views on the subject. It does not represent the views of any other person > or entity either by inference or direct reference. > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:erUs$VcIGHA.344@TK2MSFTNGP11.phx.gbl... >> For SQL Server 2005 you would use database mirroring (syncronous mode >> probably) and use database snapshots to allow the analysts to query on >> the other end. >> >> Replication would be more maintanence because of the schema change >> requirement. >> >> DB dump each night could do it but it will be out of sync. >> >> Hope that helps. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "0to60" <holeshot60_nospam_@yahoo.com> wrote in message >> news:euwB%23ScIGHA.2012@TK2MSFTNGP14.phx.gbl... >>>I have a database that captures real time production data that I'm trying >>>to keep lean 'n mean. We don't want our analysts querying this db all >>>day. So, we've decided to have a second db that we will keep in sync with >>>the lean 'n mean production db, and the analysts can query this until the >>>cows come home and have been properly quantified and analyzed. What >>>would be the best way to keep this second db in sync with the first? >>>Prolly the easiest thing to do would be a backup/restore operation once a >>>day. But I'm wondering if there's a better solution, like maybe DTS or >>>publish/subscribe. I'll need the most recent data, but also any >>>structural changes to the tables and stuff too, and it can be run in some >>>sort of a batch that we do once a day. >>> >>> Any recommendations? >>> >> >> > > The following article describes various methods for implementing a standby
server, but some of them would also apply in your case: http://vyaskn.tripod.com/maintaining_standby_sql_server.htm One option the above article doesn't mention is detach / copy / reattaching the database files: http://support.microsoft.com/kb/224071 Show quote "0to60" <holeshot60_nospam_@yahoo.com> wrote in message news:euwB%23ScIGHA.2012@TK2MSFTNGP14.phx.gbl... >I have a database that captures real time production data that I'm trying >to keep lean 'n mean. We don't want our analysts querying this db all day. >So, we've decided to have a second db that we will keep in sync with the >lean 'n mean production db, and the analysts can query this until the cows >come home and have been properly quantified and analyzed. What would be >the best way to keep this second db in sync with the first? Prolly the >easiest thing to do would be a backup/restore operation once a day. But >I'm wondering if there's a better solution, like maybe DTS or >publish/subscribe. I'll need the most recent data, but also any structural >changes to the tables and stuff too, and it can be run in some sort of a >batch that we do once a day. > > Any recommendations? > If keeping the analytic database as up to date as possible is a priority,
then transactional replication is probably your best move because it will keep the database open to use while it is being populated but using replication for a whole database can involve a lot of setup and maintenance. Actually, backup and restore every night is a simple, reliable fast way to do it so it your analysts can live with day old data this is probably your simplest solution, -- Show quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "0to60" <holeshot60_nospam_@yahoo.com> wrote in message news:euwB%23ScIGHA.2012@TK2MSFTNGP14.phx.gbl... >I have a database that captures real time production data that I'm trying >to keep lean 'n mean. We don't want our analysts querying this db all day. >So, we've decided to have a second db that we will keep in sync with the >lean 'n mean production db, and the analysts can query this until the cows >come home and have been properly quantified and analyzed. What would be >the best way to keep this second db in sync with the first? Prolly the >easiest thing to do would be a backup/restore operation once a day. But >I'm wondering if there's a better solution, like maybe DTS or >publish/subscribe. I'll need the most recent data, but also any structural >changes to the tables and stuff too, and it can be run in some sort of a >batch that we do once a day. > > Any recommendations? >  |
|||||||||||||||||||||||
Other interesting topics