Q:

Disaster Recovery Options for Sql Server

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:

Transactional Replication: Too Much Data Not viable
AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
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.

A:

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: 

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. 
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: 

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.
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.

