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 |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2008-04-16 : 07:06:07
|
| Our website is held in a content management system (Microsoft CMS2003) which sits on a SQL Server 2000. For DR purposes we would like to setup a "warm" standby SQL Server. The website gets a fair number of hits (a few thousand per day) but the changes in content are infrequent - perhaps a handful of times per day.I am looking at the options I have. One I have looked at is snapshot replication but I have a slight concern that if I say schedule this for daily replication that it will lock the tables and make the website seem slow or timeout during that period. I am also a little concerned that the replication will also have to add a field to the tables for its own purposesI have also considered using DTS to transfer the data on a scheduled basis, though this may have a similar (or worse) impact as I understand that replication uses BCP which is more efficient than DTS.Does anyone have any experience of this sort of scenario or any other suggestions, advice or comments pleasethankssteve-----------ASCII and ye shall receive. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-16 : 07:48:35
|
| Have you looked into log shipping? |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2008-04-16 : 08:57:45
|
| I did look at it briefly but am not sure (yet) that we will be using Enterprise Edition. If we are then it may well be an ideal solution for us. Having said that, red gate are showing how to use their backup tool to perform log shipping http://www.red-gate.com/products/SQL_Backup/technical_papers/log_shipping.htm which makes me wonder if it may be simpler to roll my own - transaction log backup to a shared folder and then a restore on the warm standby server. Anyone tried this?thankssteve-----------ASCII and ye shall receive. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-04-16 : 10:01:00
|
| Creating the basics of a custom log shipping routine is not that hard actually, I've created one which sent the log files via ftp to a remote site and then it was restored to the standby server. The problem you'll face is when for some reason one of the logfiles doesn't get restored on the standby. You'll have to create some sort of verification mechanism that all log-files are moved and restored. And do stay away from DTS, they are depricated in 2005. Create something that will work on both.--Lumbago |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-16 : 17:05:26
|
| I would do transaction replication if table has primary key. If your tables are huge,try creating snapshot with 'concurrent'(which doesn't lock table ). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-16 : 17:51:31
|
| In my opinion, replication is not a disaster recovery solution.Replication does not add extra columns to the tables if you are just using one-way transactional replication.I'd go with custom log shipping. It is relatively easy to put in place.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2008-04-21 : 08:31:34
|
| Thanks all for your replies. I think that custom log shipping will be a good (and relatively simple) solution for what is required here.steve-----------ASCII and ye shall receive. |
|
|
|
|
|
|
Is replication too much?