Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Raketyboom
Starting Member
6 Posts |
 Posted - 2011-10-21 : 15:07:22
|
| I am in need of replicating couple of production SQL databases to our hot site. The databases need to be exact copies up to the second. The hot site also needs to be able to be queried for reporting and other searches as the production SQL is hammered fairly hard.Database 1 is in Simple mode database 2 is in full recovery mode. Currently I am using log shipping on this but if there is a better way I would be apt to investigate. If anyone has any suggestions whether it is using third party or native I would greatly appreciate it.Best regards,Cris |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-10-21 : 15:30:05
|
Your log shipping solution doesn't meet your requirement as it's always behind the primary server. Log shipping wouldn't work if db1 was using simple recovery model.  So you are either using bulk_logged (not good) or full recovery models.You could use transactional replication, which is what we use for our reporting solution. SQL Server 2012, when it's available next year, will provide even better options. But sigh, we have to wait.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Raketyboom
Starting Member
6 Posts |
Posted - 2011-10-21 : 16:25:44
|
| tkizer - You are correct. Only db2 utilizes the log shipping. Db1 is only backed up using schedule backups, and we do not have a live backup of this db currently. |
|
|
|
Raketyboom
Starting Member
6 Posts |
Posted - 2011-11-28 : 10:19:38
|
| So it appears that replication is my option. Are there different types of replication and is there one that closer matches what I am in need of. |
|
|
|
Raketyboom
Starting Member
6 Posts |
Posted - 2011-11-28 : 11:28:52
|
So I believe its Transactional replication to do what I need. This works with both full and simple restore databases?Thanks for your help.Crisquote:
Originally posted by Raketyboom
So it appears that replication is my option. Are there different types of replication and is there one that closer matches what I am in need of.
|
|
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-11-28 : 11:29:39
|
| Like Tara suggested, Transactional Replication will meet your requirements. There are a couple of other types of Replication that you can Google but I'd stick with Transactional.----------------------------Junior DBA learning the ropes |
|
|
|
|
|
|
Query-able replicated server