|
Home > Archive > MS SQL Server DTS > January 2006 > What's the recommended way for me to keep two databases in sync?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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?
| |
| Tony Rogerson 2006-01-26, 4:58 pm |
| 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_@y
ahoo.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 schema changes aren't too frequent, transactional (or another type based
on the actual load) replication will keep you well in sync.
On SQL 2005 Mirroring (with database snapshot) is an option but currently
not supported by Microsoft.
BTW: yours is the most amusing post all day. :)
ML
---
http://milambda.blogspot.com/
| |
| Mark Williams 2006-01-26, 4:58 pm |
| As an add-on to Tony and ML's replies, your situation is a good candidate for
using the new READ COMMITTED SNAPSHOT isolation level in SQL 2005. You can
set up transactional replication to a reporting db, and set the default
isolation on the duplicate db to read committed snapshot. Then the analysts
could happily report away without being blocked by replication transactions.
--
"0to60" wrote:
> 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?
>
>
>
| |
| Tony Rogerson 2006-01-26, 4:58 pm |
| Interesting use - I'll have to remember that one!
I'm giving a webcast tomorrow here in the UK on optimistic concurrency and
i'll try and remember to mention it...
http://msevents-eu.microsoft.com/cu...&CountryCode=GB
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mark Williams" < MarkWilliams@discuss
ions.microsoft.com> wrote in message
news:6B63E116-4E7E-4CE5-A5DD- 9215560B2F99@microso
ft.com...[color=darkred]
> As an add-on to Tony and ML's replies, your situation is a good candidate
> for
> using the new READ COMMITTED SNAPSHOT isolation level in SQL 2005. You can
> set up transactional replication to a reporting db, and set the default
> isolation on the duplicate db to read committed snapshot. Then the
> analysts
> could happily report away without being blocked by replication
> transactions.
>
> --
>
> "0to60" wrote:
>
| |
|
| 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/ maintaini...
sql_server.htm
One option the above article doesn't mention is detach / copy / reattaching
the database files:
http://support.microsoft.com/kb/224071
"0to60" < holeshot60_nospam_@y
ahoo.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?
>
| |
| Michael Hotek 2006-01-26, 4:58 pm |
| 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" < tonyrogerson@sqlserv
erfaq.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_@y
ahoo.com> wrote in message
> news:euwB%23ScIGHA.2012@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Tony Rogerson 2006-01-26, 4:58 pm |
| 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.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Michael Hotek" < mike@solidqualitylea
rning.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" < tonyrogerson@sqlserv
erfaq.com> wrote in message
> news:erUs$VcIGHA.344@TK2MSFTNGP11.phx.gbl...
>
>
| |
| Roger Wolter[MSFT] 2006-01-28, 8:23 pm |
| 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,
--
This 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_@y
ahoo.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?
>
|
|
|
|
|