Transactional Replication VS. Log Shipping | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transactional Replication VS. Log Shipping

I have a client that would like a Disaster Recovery site (East coast and West Coast facilities with good connectivity). They would like to have less than 5 minutes of data loss and a minimal failover time upon manually declaration of a disaster. The one DB is ~5G and with a data change rate of ~10M per hour. I was thinking the short failover timeframes and data loss constraints would push me into transaction replication. This is proposal state, so I can’t even look at their data to know any potential pitfalls they may be up against. I know they will have to manually sync SQL logins/passwords and system stored procedures. It also appears they need to manually do roles, rules, defaults, user defined functions/data types, full text catalogs, etc. Other than that, I have the following from various points on the web
All replicated tables must contain primary keys
The developer must evaluate SQL triggers. Some triggers have the potential to be marked ‘Not for Replication#%92 since the change caused by the trigger may already be replicated
You cannot use the WITH NO_LOG option in any statement for any field to be replicated (WRITETEXT and UPDATETEXT statements)
uniqueidentifier and timestamp data types have specific replication uses and behaviors in replicated environment. It is important that the SQL developer understand what the effect of replication is on these data types.
IDENTITY columns require the NOT FOR REPLICATION option. If a published IDENTITY column doesn’t use this option, INSERT commands may not replicate properly.
— I am missing anything? Log shipping looks much easier, but the data timeframe requirements seem to fit trans. repl. better. No?
Log shipping is the only viable option. I have tried to do what you have described using replication, While possible, I highly don’t recommend it. It is too fraught with potential pitfalls. When SQL Server 2005 comes out, you will also have the option of disk mirroring. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
Yeah, I have looked into database mirroring (I assume you meant this instead of disk mirroring). The new log shipping also look much improved. Thanks Brad. Matt
]]>