by High Availability(HA) describe methods used to ensure that the database is available close to 24/7 365 days under every circumstance. This is a very important aspect of a DBAs work. In this tutorials, we would examine 5 different technologies to achieve high availability.
1. Database Replication
In this approach, we copy object from a database to another database. These objects can be tables, view, stored procedures etc. It can be scheduled to run as SQL Server Agent jobs. There are three components which includes
- Publisher – this is the primary server holding the data to be replicated.
- Distributor – this is an optional database that stores the replicated data from the publisher
- Subscriber – this is the destination server where data is copied to
You can find replication in SQL Server Management studio a shown in the figure below:
2. Database Mirroring
In database mirroring, a database is copied from a primary instance called the principal to a secondary instance. Here, there are three components involved:
- Principal – this is the primary instance containing the original data
- Mirror – this is the instance where data would be mirrored to
- Witness – optional instance that contains additional information
Database mirroring may be removed in subsequent versions of SQL Server. Would be replaced with Always On Availability.
However, to configure database mirroring, you can right-click on the database, Tasks > Mirror. This is shown below:
3. Log Shipping
This is a high availability and disaster recovery(DR) approach for SQL Server database. As the name shows, this method requires backup of database transaction logs. This backup is configured to run at intervals using SQL Server job. The following three components are involved:
- Primary Server – this is source server holding the original database
- Secondary Server – this is the destination server where the transaction logs are backed up to
- Monitor Server – an optional server used for monitoring the log shipping status
You can find Log Shipping in SQL server management studio when you right-click on the database > Tasks > Ship Transaction Logs. See figure below:
4. MS SQL Database Clustering
Here we are talking about Failover Clustering. In this approach, we create two or more servers(cluster) with similar access to shared storage disks that stored the database files. Here the servers are described as nodes and communicate via a network.
- Different nodes in a network(Virtual machines are used the the demo)
5. Always On Availability
This is a new feature in SQL Server 2016 and provides a high availability feature for mission critical databases. This relates to the Database Clustering. However, in this demo for this part we would set up a network with three nodes:
- DC Server – this a Windows 2019 Server configured as a domain controller
- Server 2 – this is the primary node of the SQL Always On Availability group
- Server 3 – the secondary node of the SQL Server Always On Availability group
You can find Always On Availability in Management studio as shown in the figure below
Now we have given an overview of the 5 High Availability approaches to SQL Server. You can now look at the related sections to learn how to perform the step by step configuration for each approach.
