For an instructor lead, in-depth look at learning SQL click below.
Running a smooth and efficient database is key for the success of any business. However, just as important as everyday maintenance is having a plan for those times when things go wrong. From power outages to natural disasters, a multitude of factors potentially compromise your database. This blog post explores the concept of Disaster Recovery and how it applies to SQL Server Databases.
What is Disaster Recovery?
Disaster Recovery (DR) forms part of a company’s Business Continuity plan. It focuses on restoring data and operational IT systems following a disaster. A well-structured DR plan can save your business from significant data loss and consequent financial disaster.
SQL Server’s Built-In Tools for DR
SQL Server itself comes with built-in features designed to assist in implementing a DR plan. These tools involve replication, log shipping, and AlwaysOn Availability groups.
Replication
Replication is the process of distributing data across multiple databases for backup or analytical purposes. To exemplify, below is how we set up transactional replication:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- configure the distributor at the Publisher sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'h2_publisher', 1; GO RECONFIGURE; GO -- add the distributor on the Publisher EXEC sp_adddistributor @distributor = N'PublisherServer', @password = N'distributor_admin_password'; |
Log Shipping
Log shipping allows you to automatically send log backups from a primary database on a primary server instance to a secondary database on a secondary server instance. Here’s the SQL statement to set this up:
|
1 2 3 4 5 6 7 8 9 10 11 |
BACKUP LOG [Your_Database] TO DISK = 'D:\Backup\Your_Database.TRN' WITH NOINIT, NOFORMAT, NAME = 'Your_Database-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10; |
AlwaysOn Availability Groups
AlwaysOn Availability Groups provide a high-availability and disaster-recovery solution. This feature allows us to failover a group of databases as a single entity. Below is an example of how to setup an AlwaysOn Availability Group:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [master] GO CREATE AVAILABILITY GROUP [AG1] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY) FOR REPLICA ON N'INSTANCE01' WITH (ENDPOINT_URL = N'TCP://INSTANCE01:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)), N'INSTANCE02' WITH (ENDPOINT_URL = N'TCP://INSTANCE02:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)); GO |
In conclusion, a well-crafted, efficient disaster recovery plan can save your business from irreversible damage in event of a disaster. Ensuring your data is secure and recoverable gives your business a necessary safety net.