MS SQL Server - HA Technologies

High Availabipty (HA) is the solutionprocess echnology to make the apppcationdatabase available 24x7 under either planned or un-planned outages.

Mainly, there are five options in MS SQL Server to achievesetup high availabipty solution for the databases.

Reppcation

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

Terminology

Pubpsher is source server.

Distributor is optional and stores reppcated 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 availabipty 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 Availabipty 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 reppca is source server.

Secondary reppca is destination server.

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

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

Example

To configure mirroringlog 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:	estdb_full.bak 
GO
Backup log TestDB to disk =  D:	estdb_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: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

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-cpck on TestDB database of TESTINSTANCE SQL Server which is primary and cpck 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.