SQL Server Disaster Recovery tips
MSSQL Tips by Burleson Consulting
SQL Server Disaster
BC provides a full line of SQL Server
SQL Server Consulting and disaster
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.
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
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.