Disaster Recovery Options for Sql Server

Question

I wanted to see what suggestions you might have for Disaster Recovery Options based on the following criteria.

• Currently running SQL 2012 standard edition
• We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
• Recovery needs to happen within 1 hour (Not sure that this is realistic)
• We are building a new data center and building dr from the ground up.

What I have looked into is:

1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options will be a nightmare.

Is there anything else that you can think of that I should look into?

Thanks in advance for any help provided.

Answer 1

During Hurricane Sandy (natural disaster) and recently when moving our data center (managed failover), I was in your situation.

I have implemented Logshipping and was able to efficiently failover during the above scenarios.

The technique that I used was reverse logshipping (swapping roles between primary and secondary) - which allows you to swap the log backups.

Below steps can be scripted with Powershell or TSQL script (I used sqlcmd and xp_cmdshell)

FAILOVER Process:

1. Kill all user connections on primary server and take a final log backup (WITH NO RECOVERY) putting the primary into NO RECOVERY state.

   a.This means that no users can now connect to the database and the database is ready for failover.

2. Ship the transaction log to the secondary server and restore the log WITH RECOVERY bringing the secondary up.

   b.This means that the users can now connect to the secondary server as the database is successful failed-over.

FAILBACK Process:

3. Kill all user connections on the primary server (original secondary) and take a final log backup (WITH NO RECOVERY) putting the primary (original primary) into NO RECOVERY state.

4. Ship the transaction log to the primary server (original secondary) and restore the log WITH RECOVERY bringing the primary (original primary) up.

This will utilize less network bandwidth as it just has to ship small T-log backups.

I do have a question about how log shipping handles Differential backups. Do I have to stop doing differentials for this to work?

A differential or FULL backup does not break LSN chain. ONLY an adhoc log backup will break the LSN chain.

Answer 2

When reviewing Microsoft's 'High Availability Solutions' (http://msdn.microsoft.com/en-us/library/ms190202.aspx) there's an interesting note toward the bottom:

'If you are running an edition of SQL Server that does not support AlwaysOn Availability Groups, we recommend log shipping.'

Note this is even with 'SQL Server 2014' chosen as the version at the top (versus choosing an older version to get its technical document). This should help give you a high level of confidence that Log Shipping is a very viable solution for your situation and isn't going anywhere.

No need to stop doing differentials either. Here's a great article on using differentials to help you refresh the Secondary: http://www.brentozar.com/archive/2014/09/log-shipping-magic-using-differential-backup-delayed-secondary/?utm_content=buffer69cb0&utm_medium=social&utm_source=linkedin.com&utm_campaign=buffer

When looking at Log Shipping, having a prepared solution for failing over (and possibly back) will be critical when having such a high number of databases. We use a SQL script I developed that helps generate the steps/scripts you need for such a Failover/Failback. If you want to get a running start on building a solution, consider starting with it and taking things from there (http://streamlinelogshippingfailovers.codeplex.com)

With Log Shipping in general, periodically perform the steps needed to help keep the Secondary a viable Failover option: Copy logins, scheduled jobs (disabled on Secondary), SSIS packages, etc.

It's also best practice to perform a DR test on a regular basis, which could also include copying the items mentioned above. For example, fail over to the Secondary, recreate logins based on scripting them out from the Primary, copy jobs (disabled), point your application at the Secondary to ensure it works and then fail back. This will help you know (and demonstrate) that you are truly prepared for a disaster.