Introduction
High Availability (HA) is the solution\process\technology to make the Application\database available 24x7 under either planned or un-planned outages.
Mainly, there are five options in MS SQL Server to achieve\setup High Availability solution for the databases.
Replication
The source data will be copied to the destination through replication agents (jobs). Object level technology.
Terminology
- Publisher is a source Server.
- Distributor is optional and stores the replicated data for the subscriber.
- Subscriber is the destination Server.
Log Shipping
The source data will be copied to the destination through Transaction Log backup jobs. Database level technology.
Terminology
- Primary Server is a source Server.
- Secondary Server is destination Server.
- Monitor Server is optional and will be monitored by logging shipping status.
Mirroring
The primary data will be copied to the secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology.
Terminology
- Principal server is a source Server.
- Mirror Server is destination Server.
- Witness Server is optional and is used to make automatic failover.
Clustering
The data will be stored in the 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 the 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 the secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without the shared storage.
Terminology
- Primary replica is a source Server.
- Secondary replica is a destination Server.
The steps are given below 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 the source database.
Example
To configure mirroring\log shipping for the database 'TestDB' in 'TESTINSTANCE' as primary and 'DEVINSTANCE' as secondary SQL Servers, write the query given below to take full and T-log backups on the Source (TESTINSTANCE) Server.
Connect to TESTINSTANCE SQL Server and open new query. Write the code given below and execute, as shown below.
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 the destination Server.
In this case, we have only one physical Server and two SQL Servers Instances are installed. Hence, there is no need to copy, but if two SQL Server instances are in different physical Servers, then we need to copy the two files given below to any location of the secondary Server, where 'DEVINSTANCE' instance is installed.
![]()
Step 3
Restore the database with the backup files in the destination Server with 'norecovery' option.
Example
Connect to 'DEVINSTANCE' SQL Server and open New Query. Write the code given below to restore the database with the name 'TestDB' , which is the same name of the primary database ('TestDB') for the database mirroring. However, we can provide different names 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 the restored database TestDB with restoring the status, as shown below.
![]()
Step 4
Configure the HA (Log shipping, Mirroring) as per your requirement, as shown below.
Example
Right-click on TestDB database of TESTINSTANCE SQL Server, which is primary and click Properties. The screen given below will appear.
![]()
Step 5
Select the option called either Mirroring or Transaction Log Shipping, which is in Red color box, as shown above, as per your requirement and follow the wizard steps guided by the system itself to complete the configuration.