MS SQL Server - HA Technologies

Replication

The source data will be copied to destination through replication agents (jobs). Object level technology.

Terminology

Publisher is source server.
Distributor is optional and stores replicated data for the subscriber.
Subscriber is the destination server.

Log Shipping

The source data will be copied to destination through Transaction Log backup jobs. Database level technology.

Terminology

Primary server is source server.
Secondary server is destination server.
Monitor server is optional and will be monitored by log shipping status.

Mirroring

The primary data will be copied to secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology.

Terminology

Principal server is source server.
Mirror server is destination server.
Witness server is optional and used to make automatic failover.

Clustering

The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level technology. Windows Clustering setup is required with shared storage.

Terminology

Active node is where SQL Services are running.
Passive node is where SQL Services are not running.

AlwaysON Availability Groups

The primary data will be copied to secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without shared storage.

Terminology

Primary replica is source server.
Secondary replica is destination server.

Following are the steps to configure HA technology (Mirroring and Log shipping) except Clustering, AlwaysON Availability groups and Replication.

Step 1 − Take one full and one T-log backup of source database.

Example

To configure mirroring\log shipping for the database 'TestDB' in 'TESTINSTANCE' as primary and 'DEVINSTANCE' as secondary SQL Servers, write the following query to take full and T-log backups on Source (TESTINSTANCE) server.

Connect to 'TESTINSTANCE' SQL Server and open new query and write the following code and execute as shown in the following screenshot.

Backup database TestDB to disk = 'D:\testdb_full.bak'
GO
Backup log TestDB to disk = 'D:\testdb_log.trn'

Step 2 − Copy the backup files to destination server.

In this case, we have only one physical server and two SQL Servers Instances installed, hence there is no need to copy, but if two SQL Server instances are in different physical server, we need to copy the following two files to any location of the secondary server where 'DEVINSTANCE' instance is installed.

Step 3 − Restore the database with backup files in destination server with 'norecovery' option.

Example

Connect to 'DEVINSTANCE' SQL Server and open New Query. Write the following code to restore the database with the name 'TestDB' which is the same name of primary database ('TestDB') for database mirroring. However, we can provide different name for log shipping configuration. In this case, let’s use 'TestDB' database name. Use 'norecovery' option for two (full and t-log backup files) restores.

Restore database TestDB from disk = 'D:\TestDB_full.bak'
with move 'TestDB' to 'D:\DATA\TestDB_DR.mdf',
move 'TestDB_log' to 'D:\DATA\TestDB_log_DR.ldf',
norecovery
GO
Restore database TestDB from disk = 'D:\TestDB_log.trn' with norecovery

Refresh the databases folder in 'DEVINSTANCE' server to see restored database 'TestDB' with restoring status as shown in the following snapshot.

Step 4 − Configure the HA (Log shipping, Mirroring) as per your requirement as shown in the following snapshot.

Example

Right-click on 'TestDB' database of 'TESTINSTANCE' SQL Server which is primary and click Properties. The following screen will appear.

Step 5 − Select the option called either 'Mirroring' or 'Transaction Log Shipping' which are in red color box as shown in the above screen as per your requirement and follow the wizard steps guided by system itself to complete configuration.