High Availability - Availability of MS SQL Server

High Availability (HA) is a solution / process / technology to ensure applications / databases are accessible 24/7 in any condition, whether intended or unexpected.

Basically, there are 5 options to set up database availability in MS SQL Server.

1. Replication
2. Log Shipping
3. Mirroring
4. Clustering
5. AlwaysON Availability Groups

Replication

The original data will be copied to the destination via the replication task (agent / job), using technology at the object level. Some terms need to know:

The publisher (Publisher) is the source server.

1. Distributor (Distributor) is optional, storing the copied data to the subscriber (Subscriber).
2. Subscriber is the destination server.

Log Shipping

The source data is copied to the destination via the Transaction Log backup task, using the database-level technology. Some terms:

1. The primary server (Primary Server) is the source server.
2. Secondary server (Secondary Server) is the destination server.
3. The monitoring server is optional, monitored by Log Shipping status.

Mirroring

Primary data is copied to secondary through network transactions with the help of projection and port number connections, using database-level technology. Some terms:

1. The original server (Principal Server) is the source server.
2. The Mirror Server is the destination server.
3. Witness Server is optional, used for automatic fault tolerance solutions.

Clustering

Data is stored in a common location, used by both primary and secondary servers, using technology at the instance level. You need to set up Windows Clustering on this shared storage. Some terms:

1. Active Node is where SQL Services runs.
2. Passive Node (Passive Node) is where SQL Services does not run.

AlwaysON Availability Groups

Primary data will be transferred to secondary through transactions, using technology at the database group level. Windows Clustering settings do not need common storage. Some terms:

1. Primary Replica is the source server.
2. Secondary Replica is the destination server.

Steps to configure High Availability for Mirroring and Log Shipping

Below are the steps to configure HA (Mirroring and Log Shipping) except Clustering, AlwaysON Availability Groups and Replication.

Step 1 - Get a full copy and a T-log copy of the original database.

For example:

To configure Mirroring / Log Shipping for the TestDB database on TESTINSTANCE as the primary SQL Server server and DEVINSTANCE as the secondary server, use the following query to get the full backup and T-log on the source server (TESTINTANCE).

Connect to SQL Server TESTINSTANCE and open a new query, write the code below and execute as shown in the figure.

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

Query retrieves backup

Step 2 - Copy the backup file to the destination server.

In this case we only have one physical server and two versions of SQL Server Instance, so we don't have to copy. But if two copies of SQL Server Instance are located in two different physical servers, then copy the following two files to the location on the secondary server, where DEVINSTANCE is installed .

Copy the backup file to the destination server

Step 3 - Restore the database with a backup file on the destination server by selecting norecovery.

For example.

Connect to SQL Server DEVINSTANCE and open a new query New Query. Write the following code to restore the database with the name TestDB, identical to the name of the original database. You can use a different name for Log Shipping configuration. Use the norecovery option to restore.

 Restore database TestDB from disk = 'D:TestDB_full.bak' 
 with move 'TestDB' to 'D:DATATestDB_DR.mdf', 
 move 'TestDB_log' to 'D:DATATestDB_log_DR.ldf', 
 norecovery 
 GO 
 Restore database TestDB from disk = 'D:TestDB_log.trn' with norecovery 

Restore databases with file saving

Refresh the database folder on the DEVINSTANCE server to see that the database has been restored TestDB with recovery status as shown below.

Database recovery status

Step 4 - Configure HA (Log Shipping / Mirroring) as shown below.

For example:

Right-click on SQL Server TestDB database TESTINTANCE (root server) and click Properties. The screen below will appear.

Configuring Log Shipping / Mirroring

Step 5 - Choose Mirroring or Transaction Log Shipping according to your needs and follow the steps provided by the system to complete the configuration.

Previous article: How to start and stop services in MS SQL Server

Next article: Report creation service in MS SQL Server