SQL Server Transaction Log And High Availability Solutions

Introduction

SQL database administrators are responsible for ensuring that the SQL Server instances and databases are available when needed by businesses or users. The goal is to keep the business up and running smoothly and when corruption occurs, fix it with minimal possible downtime or no interruption. However, events such as SQL server crash, I/O Errors, etc. can bring the database down and possibly result in the loss of business-critical data.

To maintain business continuity, Microsoft provides different High Availability (HA) and Disaster Recovery (DR) solutions. These solutions ensure that SQL Server instances or databases remain available and reachable, with the shortest possible downtime, in case of any disaster situation.

Different Types of SQL Server High Availability and Disaster Recovery Methods

In this section, we will discuss the four major high availability solutions:

Log Shipping
Failover Clustering
Database Mirroring
AlwaysOn Availability Groups

Log Shipping

The SQL Server Log Shipping HA technique is suited for less mission-critical databases. It involves one primary (production) database server and one or more secondary servers.

Log shipping technique involves these steps:

Backing up the entire database and transaction log (T-Log) file from the primary server
Copying the T-Log backup file to one or more secondary servers
Restoring the transactional log backup on the secondary server

So, this way we will have a standby database, but this database will always be in restoring mode and couldn't respond to any user request. But, you will have a backup of your data.

Configuring Log Shipping

Follow these steps to configure log shipping:

Open SSMS and connect to your primary SQL server instance.
In the Object Explorer window, right-click the database that you want to use as the primary database, and then choose Properties.
In the Database Properties window, click Transaction Log Shipping under Select a page. Next, make sure that the “Enable this as a primary database in a log shipping configuration” check box is checked.

SQL Server Transaction Log And High Availability Solutions

Figure 1 - Transaction Log Shipping Page

Next, you need to set up and schedule a transaction log backup by clicking Backup Settings under Transaction log backups.

Figure 2 – Schedule a Transaction Log Backup

In this step, you need to create a backup job on the primary server, in the Transaction Log Backup Settings box, do the following:

If you’re creating a backup on a network share, then type the network path to the share you have created for the T-Log backup folder. However, if the backup is stored on the primary server, then enter the local folder path.
Set up the parameters for ‘Delete files older than’ and ‘alert if no backup occurs within’.
While setting up log shipping, you can control the compression behavior of the log backups by setting backup compression.
To apply the changes, click OK.

Figure 3 - Set up Transaction Log Backup Settings

Now, configure the secondary server instance and database by clicking the Add button under Secondary server instances and databases.

Figure 4 - Configure Secondary Server Instances and Databases

A Secondary Database Settings box will open. In this step, initialize the secondary database for log shipping by performing these steps:

Click Connect for connecting to the instance of SQL Server that you want to use as your secondary server.
Select a database from the Secondary database drop-down list or enter the name of a new database you want to create.
From the Initialize Secondary Database tab, select any of these options: ‘generate a full backup and restore it’, ‘restore to an existing backup’, or ‘do nothing’.

Figure 5 –Select the Initialize Secondary Database Tab

From the Secondary Database Settings window, click on the Copy Files tab. In the Destination folder for copied files: textbox, type the path of the Destination Shared Folder where the T-Log backup files will be copied on the secondary server.

Figure 6 - Select the Copy Files tab

Now click on the RestoreTransaction Log tab, choose any of the following restore options under Database states when restoring backups:

No recovery mode - The database is not accessible in this mode.
Standby mode - The database is read-only in Standby mode.

Figure 7 - Select the Restore Transaction Log Tab

Set the ‘Delay restoring backups’ interval as per your requirement, and then click OK to close the Secondary Database Settings window.

You will see the following screen when the log shipping will be ready for use.

Figure 8 - Successfully Restored T-Log Backup to Secondary Database

Note

A log shipping configuration does not automatically fall over from the primary server to any secondary server. So, if the primary database becomes unavailable, you will need to bring a secondary database online manually.

Pros and Cons of Log Shipping

(+) It is easy to set up and maintain.

(+) It can be combined with other HA/DR options such as replication, database mirroring, and AlwaysOn Availability Groups.

(-) All DBs need to be managed separately.

(-) Automatic failover isn't supported.

(-) Standby (secondary) database cannot be read during the restore process.

Database Mirroring

Database Mirroring is another disaster recovery solution that increases the availability of a SQL Server database that uses the full recovery model. It's comprised of two servers: a principal server (i.e. the primary server) that includes the primary database and a mirror server (i.e. the standby server) that includes the mirrored database. Database mirroring also consists of an optional third server instance, called a witness server.

Once a mirroring session starts, all log records from the principal server are copied to the mirror server. The optional instance of SQL Server (i.e. the witness server) is used only with a high-safety mirroring operating mode with automatic failover; this helps bring the standby (mirrored) copy of the database online without any data loss.

Database mirroring works in one of the following operating modes depending on the transaction safety setting:

High-safety mode (with automatic failover)
When the transaction safety setting is set to FULL, the database mirroring session operates synchronously in high-safety mode. To run the session in high-safety mode with automatic failover, add a witness server instance as well as a principal and mirror server. This mode helps switch from the principal server to the mirror server safely without any data loss.
High-performance mode
When the transaction safety setting is set to OFF, a database mirroring session operates asynchronously and uses only a principal server and mirror server. Configure a mirroring session for high-performance mode when “you do not need automatic failover and prefer performance over availability.” This mode is faster to switch from the principal server to the mirror server, but it can result in some data loss.

Configuring Database Mirroring

The steps to set up a database mirroring solution are as follows:

Note

Before proceeding with the process to set up mirroring, make sure to create a full backup of the database you want to mirror in the principal server:

Open SSMS and connect to the primary SQL Server instance.
From the Object Explorer window, expand Databases, and then right-click on the principal database and choose Tasks > Mirror.

Figure 9 - Select Mirror from Tasks

When the Database Properties dialog box is displayed, click on the Configure Security button.

Figure 10 - Select the Configure Security Option

When the Configuration Database Mirroring Security Wizard appears, click Next.

Figure 11 - Configure Database Mirroring Security Wizard Main Screen

From the Include witness Server screen, choose the ‘Yes’ radio button if you want to include the witness server to run mirroring session in high-safety (synchronous) mode with automatic failover. Hit the Next button.

Figure 12 - Include Witness Server

From the Choose Servers to Configure screen that opens, choose where you want to save the security configurations on Principal server instance, Mirror server instance, or Witness server instance. Click Next.

Figure 13 - Choose the SQL Server Instances to Configure

In this step, you need to select the ports used from the Principal Server Instance screen. Next, enter your principal server’s hostname, Listener port, and Endpoint name. Also, choose if you want to encrypt the data sent through the endpoints (i.e. from one server to another) by checking the ‘Encrypt data send through this endpoint’ checkbox. Press the Next button.

Figure 14 - Specify Information about Principal Server Instance

In this step, you need to connect to the mirror server by clicking on Connect in the Mirror Server Instance screen. Enter the connection properties and specify the Listener port and Endpoint name. Hit Next.

Figure 15 - Connect to the Mirror Server Instance

From the Witness Server Instance screen that appears, select the witness server instance and click Connect for connecting to the server instance. Also, specify the Listener port and the Endpoint name. Press Next.

Note

This is an optional step and needs to be performed if you have included the Witness server.

Figure 16 - Specify Properties to Allow Witness Server to Access Connections from Principal & Mirror Server Instances

The Service Accounts screen is displayed. Specify the SQL service accounts in the Principal, Mirror, and Witness server instances textboxes, and then click Next.

Figure 17 - Specify the Service Accounts of the Principal, Witness & Mirror Instances

The Complete the Wizard screen displays the list of actions that will be performed on Principal, Mirror, and Witness server instances. Once you’ve verified the list of actions, click Finish.

Note

If you want any changes in the actions to be performed, adjust the settings by clicking the ‘Back’ button to go back to the previous screens.

Figure 18 - Verify Actions to be Performed in the Complete the Wizard Screen

On successful completion of the Endpoint configuration, a Success message will be displayed. Click the Close button.

Figure 19 - Database Mirroring Configuration Completed Success Message Window

A message box to begin mirroring will appear. Click the Start Mirroring button to initiate mirroring between the principal server and mirror database server.

Figure 20 - Initiate Database Mirroring

The database mirroring will begin once the entire setup is configured correctly. Once you have configured database mirroring, stop the SQL Server database engine in the principal server instance.

Figure 21 - Stop the SQL Server Database Engine

After performing these steps, the mirror database will be active.

Note

To make the principal server active, restart the SQL Server service and stop the mirror server service.

Pros and Cons of Database Mirroring

(+) Configuring database mirroring is simpler than log shipping and replication

(+) Increases data protection and availability of the database

(+) Supports automatic server failover mechanism (with witness server)

(+) Configuring database mirroring solution does not require special hardware, resulting in lower infrastructure cost

(-) Data loss is possible when a database mirroring session operates asynchronously

(-) Supports only one failover partner.

AlwaysOn Failover Clustering (FCIs)

SQL Server AlwaysOn Failover Clustering (FCIs) HA solution ensures the availability of an entire SQL Server instance. It consists of at least two servers, allowing one server to take over the responsibilities of the failed server. Clustering is an ideal choice for setting up a high availability environment with 100% uptime. That is because when the component of one physical server fails, the other server automatically gets to work from where the failed server left off, leading to little or no database downtime.

Configuring AlwaysOn Failover Clustering

First thing, you need to add two separate servers and then make sure that both the servers have .NET Framework 3.5 features and Windows Failover Clustering (WSFC) installed. For this, perform these steps,

Click Start and open Server Manager.
In the Server Manager window, click Manage > Add Roles and Features.

Figure 22 - Select the Add Roles and Features Option

From the Add Roles and Features Wizard, click Features > Failover Clustering > Next.

Figure 23 - Choose Failover Clustering in Add Roles and features Wizard

When the “Add features that are required for Failover Clustering” window appears, click Add Features, and then click Next.
Install the Failover Clustering feature by clicking the Install button.
After installing the Windows Server Failover Clustering feature, proceed with configuring Windows Failover Clustering by performing these steps,

Click Failover Cluster Manager from within the Server Manager console.

Figure 24 - Select Failover Cluster Manager

In the Failover Cluster Management box, click the Validate a Configuration option.

Figure 25 - Validate a Failover Cluster Management Configuration

In the Validate a Configuration Wizard box, click the Select Servers or a Cluster option from the left side panel. On the Select Servers or a Cluster screen, enter the names of the SQL Servers that you want to configure as replicas in the AlwaysOn availability group.

Figure 26 - Validate Servers or a Cluster

In the Testing Options screen, select the Run all tests (recommended) radio button, and then click Next.

Figure 27 - Select to Run all tests (recommended)

Some validation tests will run. Click View Report if you receive any errors or else click Finish.

Figure 28 - Check the Failover Cluster Validation Report

The Create Cluster Wizard is displayed after completing the validation process. In the Access Point for Administering the Cluster page, specify the virtual cluster name (do not enter the server or instance name here). Also, specify the virtual IP address of your cluster. Then, press the Next button to create a cluster.

Figure 29 - Specify Details to Administer the Cluster

Once the cluster is created, you will see an Install Report such as the one shown below:

Figure 30 - View Report Created by Create Cluster Wizard

Once the cluster is created successfully, it will automatically get connected afterward.

Notes

After creating the cluster, you need to configure SQL Server 2012 on both the servers.
Installing a Failover Clustered Instance in SQL Server 2012 requires you to have appropriate Active Directory domain permissions. Also, you will need local Administrator rights for the machines that will serve as nodes in the failover cluster.

Pros and Cons of Failover Clustering

(+) Provides data protection at server instance level via redundancy

(+) Provides automatic failover in events such as OS failure, hardware failure, application, or service failure.

(-) It is super expensive

(-) Does not support multiple geographic locations

AlwaysOn Availability Group (AG)

Introduced in SQL Server 2012, The AlwaysOn AG combines the power of clustering as well as database mirroring high availability features. The best aspect about this HA/DR solution is that allows accessing secondary databases without them being in read-only or restoring mode – that clustering and mirroring did not allow. Also, the AlwaysOn feature allows creating multiple failover partners whereas in the past database mirroring allowed only one failover partner.

Configuring AlwaysOn Availability Group

To set up AlwaysOn AG, you need to execute these steps:

First of all, create a Service Account for connecting to SQL Server instances. For this, open Server Manager, click Tools > Active Directory Users and Computers.

Figure 31- Create a Service Account to Connect to SQL Server Instances

In the Active Directory Users and Computers dialog box, right-click on the Users folder, and then click New > User.

Figure 32 - Select a New user in Active Directory Users and Computers Window

In this step, you need to create a Service account username by specifying the service account login name, then hit Next.

Figure 33 - Create a Service Account UserName

In the window that opens, enter a password and check the ‘User cannot change password’ and ‘Password never expires’ checkboxes. Click Next.

Figure 34 - Specify Details to Complete Creation of Service Account

Click the Finish button to complete the service account creation process.

Figure 35 - Finish Service Account Creation Process

In this step, add the service account as a member of the administrative account on both servers. For this, do the following:

Open Server manager again, and then navigate to Tools and click Computer Management.

Figure 36 - Select Computer Management Option from Tools

When the Computer Management window opens, expand the Local Users and Groups folder and select the Groups folder. Next, right-click on Administrators, and then click Properties.

Figure 37 - Select Administrators as Groups

In the dialog box that pops-up, specify the Service Account Login names or check names, and then hit OK.

Figure 38 - Enter Service Account Details

In this step, create the Service account as a windows user by clicking on Manage User Accounts in Control Panel.

Figure 39 - Create Service Account as Windows User

When prompted, specify a Service account name and domain name. Press Next, choose Administrator and then click Finish.

On the SQL Server Configuration Manager window, right-click on the SQL Server Services tab. This will open the SQL Server (MSSQLSERVER) Properties dialog box. In the Properties box, click on the AlwaysOn High Availability tab, and then check the ‘Enable AlwaysOn Availability Groups’ checkbox.

Figure 40 - Enable AlwaysOn Availability Groups (AGs)

Start the SQL Server Service again after making all the above changes.
Next, you need to configure SQL Server Login accounts and Replicas. To do this, follow these steps:

If you haven’t already added a SQL Service account, add a SQL login via SQL Server Management Studio (SSMS).

Note
Make sure that the SQL Service account is a domain account and is not a service account installed on a local machine.

Next, add your SQL Service login account to the Administrators group on each server replica.
Connect to the SQL Service account using SSMS. For this, right-click on the SQL Service log in to open the Login Properties window. From the Login Properties window, click the Securables option under ‘Select a page’. On the Securables page, check the Connect SQL Grant box checkbox. Do this for all the server replicas.

Figure 41 - Connect to SQL Serivce Account through SSMS

Ensure that all the server replicas allow remote connections.

Now start with installing the AlwaysOn Availability Group on your primary replica by following these steps,

Open SSMS, expand the AlwaysOn High Availability folder, right-click on Availability Groups and then choose the New Availability Group Wizard option.

Figure 42 - Select the New Availability Group Wizard Option

In the New Availability Group dialog box, on the Specify Name page, enter your AlwaysOn group name.

Figure 43 - Enter the AlwaysOn AG Name

On the Select Databases page, choose the databases that you wish to include in your AlwaysOn group.

Figure 44 - Select User Databases for AlwaysOn AG

Note

You can see a blue link next to each database. The link helps check if the database can be included in the AlwaysOn availability group or not. If the link does not display the ‘Meets prerequisites’ message, click on that link to know what you need to do to include the database into the group.

Next, click on the Specify Replicas option to open Specify Replicas page. On this page, you need to perform these actions,

Add all the replicas you want to be included in your AlwaysOn group by clicking the Add Replica button. Then, for each replica, you need to state whether you want Automatic or Manual Failover, Synchronous or Asynchronous Data Replication, and the Connections you want the end users to connect.

Figure 45 - Specify SQL Server Instance to Host a Secondary Replica

Click on the Endpoints tab on the Specify Replicas page to verify that the port number is 5022.

Figure 46 - Specify Endpoints to Host a Secondary Replica

Select the Backup Preferences tab to select where you want the backups to occur, and set the priority based on which replica will want to run your backups.

Figure 47 - Select Backup Preferences to Run the Secondary Replica

In the Listener screen, choose the ‘Create an availability group listener’ option and specify all the listener preferences including the DNS name, port number, and the Network mode (i.e. an unused IP address) for your listener.

Figure 48 - Enter the Listener Preferences for the Secondary Replica

Now open the Select Data Synchronization page to choose your data synchronization preference. Select the Full option (i.e. the default option) to include the databases on a network share in the availability group. Select the Join or Skip option if you have large databases. With these options, you can restore the databases to each secondary database.

Figure 49 - Choose Data Synchronization Preference

Next, click on the Validation tab to run Validation checks and ensure that they return successful results.

Figure 50 - Run Validation Tests on Availability Group

· On the Summary page, click Finish after verifying that all the configuration settings are correct.

Figure Verify Actions to Complete AlwaysOn AG Configuration

· FFinallyopen the Results page to monitor the AlwaysOn availability group installation progress and verify that all the tasks have completed successfully.

Figure 52 - Monitor AlwaysOn AG Installation Progress

After you have completed performing all the above steps successfully, the AlwaysOn Availability Group will get created in SSMS.

Pros and Cons of AlwaysOn Availability Group

(+) Supports automatic fault tolerance

(+) Makes database available in the least possible downtime

(+) Allows accessing database without it being in the read-only or restoring mode

(-) New databases cannot be added automatically to AlwaysOn AGs

Here’s a comparison table to help you determine which SQL Server high availability option is best suited for you.

Table 1 - Comparing SQL Server High Availability Options

High Availability Solution	Ease of setup & maintenance	Relative cost	Automatic Failover Support	Hardware redundancy	Database redundancy
Log shipping	Easy	Low	No	Yes	Yes
Database mirroring	Easy	Low to moderate	Yes	Yes	Yes
Failover Clustering	Complex	High	Yes	Yes	No
Always On Availability Groups	Moderate	Moderate to High	Yes	Yes	Yes

Carefully, read about each high availability option in SQL Server discussed in this article and choose the one that best fits your server/database availability needs.

I have done another review of Stellar Repair for MS SQL which can also be used for the same purpose.