SQL Server Disaster Recovery

BC provides a full line of SQL Server SQL Server Consulting and disaster recovery tips.

A SQL Server disaster recovery architecture centers on the end-users tolerance for unplanned downtime.  The less tolerance for downtime, the higher the costs, especially the cost of a dark fiber network lines between the two cities.

Disaster Recovery for SQL Server involves creating a “safe site” in a remote, geographically distributed location, called a “hot site”.  The MSSQL DBA will then use one of these approaches to keep the got site database synchronized with the master database:

• Mirroring:  Database mirroring in SQL Server 2008 mirrors the transfer of transaction log updates directly from one server to another and can quickly failover to the standby server.
  
• Windows clusters:  Windows Failover clustering uses the Windows OS and MS-SQL to provide hardware redundancy in case of a server failure.  If a Windows server becomes unavailable (blue screen of death, Windows hanging), clustering to resurrect the SQL Server database on a new server with has the footprint for the SQL server software, automatically mounting the MSSQL database files.
  
• Log Shipping:  Backup log shipping copies the transaction logs to a remote server where they are applied to a copy of the production database that was created from a cold backup.  In the highly likely event of a Windows server failure, MS-SQL can redirect transactions to the standby server, which swaps out as the new production server.
   
• Third party replication:  Tools such as Goldengate can write updates simultaneously to two SQL Server databases.

Again, your choice of MS-SQL disaster recovery options depends upon your tolerance for unplanned downtime.

SQL Server disaster recovery planning

The choice of disaster recovery plans depends upon the end-users tolerance for unplanned downtime. 

• High speed:  If the end user must be up 24x7, a super-fast disaster recovery mechanism must be created, usually involving a high-speed dark-fibre communications line between the primary and the disaster recovery database.

• Medium recovery speed:  If 99% uptime is required (99% is 3 days each year!), then you can come-up with a slower, cheaper way to transfer the data to the hot site and start-up the database in less than 24 hours.

• Acceptable Data Loss: for data warehouse and decision support systems where the data can be reloaded, a cheap cold backup can be transported to the hot site, ready to fire-up yesterday’s copy of your SQL Server database.  This cheap approach to SQL Server disaster recovery is perfect when the end-user can suffer “acceptable data loss”, cases where today’s data is not critical and yesterday’s data can be re-loaded from batch sources at a later time.

The last component of SQL Server disaster recovery the automatic failover of the incoming users to the hot site database.  There are several ways to accomplish transparent application failover in MS-SQL.

Copying the database files to the hot site is easy using either full backups or differential SQL Server backups.  The differential SQL Server backup starts with the primary database and then periodically applies the transaction log backups with are spat from the primary SQL database and transferred over the geographical network to the hot site database.

In either case, the backups should be disk-to-disk and you never want to risk copying a SQL Server database files to tape because of the possibility of a parity check and the long times required to write the data from tertiary media onto disk platters.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.