Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Alright, I feel like this is a terrible idea, but I need some help understanding why this is bad:

I'm still working on implementing a disaster recovery/business continuity solution for our datacenter. We're running MSSQL 2008 Enterprise and we plan on running a passive instance in the cloud. Currently I'm suggesting Log Shipping, but I've been asked to explore using RSync (or something similar) to push MDF/LDF file deltas to the cloud instead of using the internal tools.

The goal would be to reduce our footprint and not run the cloud SQL instance most of the time due to licensing issues (our Passive/DR license is already in use in our current datacenter, but if that center goes down, the license becomes available).

I've found a solution that uses VSS to create and push deltas even if the files are locked, but I'm wondering what sorts of issues could show up. Can I get some insight? We would be pushing deltas every 15 minutes, the database in question is roughly 2GB with maybe 10 MB of T-Logs generated in a 15 minute window.

share|improve this question

4 Answers 4

up vote 7 down vote accepted

I would not trust rsync to copy the ldf and mdf files for user or system databases, nor would I trust anything I hacked together (VSS or otherwise) in a production environment. SQL Server is very fussy about when (and if) things get written to the ldf and the mdf files. Software (rsync) that isn't designed with that in mind might not get it right, especially if it doesn't understand that the ldf files and mdf files need to be treated as an interrelated system of files. If that software doesn't get things right, nothing might be noticed until you failover, try to go live and have your databases flagged as suspect due to what SQL Server sees as data corruption. Even worse, "getting it right" might be dependant on how much load is on the system and you might not find the problem in a lightly-loaded test environment.

I have seen enough examples of people who thought that they were replicating their files but were not. They were left with corrupt files on the recovery site and with inaccessible backup files at the primary site. So, that meant no database for them.

In short, you are making an appointment for trouble.

If you had some sort of block-level replication technology like EMC's SRDF or were looking at shared-nothing clusters, that might be different. Those technologies interface with SQL Server and the clustering services that Windows provides in a way that your writes will be safe and your files will/should be consistent.

If my only disaster recovery option was a remote site that was normally down, I'd use log shipping and make sure that I had all of the pieces to restore the database(s) on the remote site. If you can't make the built-in log shipping do that, writing your own isn't that hard. I've probably written three or four (simplistic) log shipping systems from scratch in the last 14 years.

At a minimum, the key things you need are: A full backup needs to be taken and copied over to the remote site. Your tlog backups need to be taken and copied over to the remote site. You need an automated method to restore that full backup and any relevant tlog backups. Ideally, this should be simple enough for someone else to do and/or simple enough for you to figure out at 3AM when your primary server fails and you are half asleep.

When you have an event, bringing up the other server will take longer because you would have so much manual stuff to do. That means that this isn't as good as simply implementing regular log shipping. You will need to test this periodically, as well.

(Of course, you need to worry about other things too, like jobs, packages, login and user synchronization, changing DSNs on the web servers during a failure, etc. If you have a large environment and a serious disaster, like the loss of your primary data center, you will be trying to do this when IIS guys, file server guys, network guys and whatever else guys are trying to bring their stuff up too.)

If it were me, I would be agitating for a warm standby server at the remote site. That would make (standard, out-of-the box) log shipping easier and database mirroring possible. It sounds like you have tried that.

share|improve this answer
    
Thank you very much - this will help immensely in explaining the situation to my bosses. –  Gyrfalcon2138 Apr 12 '12 at 17:24
    
+1 Something like EMC Avamar would be the only possible way I'd consider non-native tools. We had a recent meeting with EMC and even w Avamar, not all of the T-SQL backup command set is supported. –  Eric Higgins Apr 12 '12 at 17:55

The minimum deltas you have to ship over is ... the log shipping delta! RSync will not reduce the delta, it will increase it. Log shipping does not need to ship over any MDF delta, because the the standby knows how to interpret the LDFs delta into actions to be applied to the local MDF (ie. by running recovery with standby on the log it received).

101% of the times one attempts to circumvent the logic of the WAL protocol by doing file level/block level replication outside of SQL Server control it ends up in disaster because file/block level operations are not aware of the WAL protocol. And that is even when the file level replication does manage to handle correctly the LDF rollover on the primary...

Log shipping is supported, is optimal, is exposed in the SQL management toolset, licensing is Standard (and one can always roll his own log shipping on Express using few scripts) and, last but not least, is correct.

share|improve this answer
    
Very excellent points, thank you. –  Gyrfalcon2138 Apr 12 '12 at 17:25

Your gut instinct is correct.

Windows and SQL Server are designed around objects and APIs, whereas Unix is designed around text and files.

At a philosophical level, using rsync – a Unix tool built around files – to patch up SQL Server databases – well-defined objects with set APIs – is wrong.

At a technical level, using rsync to apply MDF/NDF/LDF differentials to your standby database does not guarantee that your standby database will be usable in the event that your primary goes down.

Since rsync will bypass SQL Server's interface to the physical files, it will miss potentially critical information stored by SQL Server in memory or in other locations, build deltas off of source files that are actively being modified, and then likely leave the patched database in a corrupt or inconsistent state.

You could schedule some time every x hours, where x describes your maximum exposure to data loss, to quiesce or shut down the source database. Then you know that the source files describe a database in a consistent and clean state, and you can trust rsync to apply the file differences to your standby database and leave it in a good state. This approach will cut into your availability; it is also unsupported by Microsoft.

I'd just stick with log shipping.

share|improve this answer
    
Yeah, rsync seemed like a good idea for a split second, until I remembered that SQL server is very picky about how its files operate... –  Gyrfalcon2138 Apr 12 '12 at 17:25

rsync will work on database files. It is tested. There is only one prerequisite - offline your database before your final rsync sync over network. So you'll have consistent snapshot on a remote target. It's no different than copying files of a deattached database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.