Nisarg Upadhyay
Query SQL03 to verify the data

Configuring SQL Server Express edition as SQL witness server in Database Mirroring

January 20, 2020 by

In this article, I am going to explain how we can use the SQL Server Express edition for the SQL witness server of the database mirroring setup. Database mirroring is a cost-effective and easy to configure high availability solution. Unfortunately, this feature has been deprecated, but still, some organizations use database mirroring as a high availability solution.

Database mirroring can be configured in SQL Server Standard edition. It supports manual failover and automatic failover using the witness server (High Safety with automatic failover). The following conditions must be fulfilled to perform a successful failover from the principal database to the mirrored database:

  1. The witness must be configured and the mirror operating mode of the database mirror must be High Safety with automatic failover
  2. The mirrored database must be fully synchronized with the principal database. All logs should be sent from the principal database to the mirror database and those should be written to the disks
  3. The primary database lost the communication with the mirroring configuration and but the mirror and witness must be online to retain the quorum

To configure an automatic failover, we must set up a SQL witness server that keeps an eye on principle database, and in case of database outage, it failover to the mirrored database without manual intervention. Now, to save the license cost, we can use the SQL Server Express edition as a witness server. I have explained the step by step process for adding a witness server in the existing database mirror.

Configure the witness server in the existing mirroring server

To demonstrate the process, I have created three virtual machines. The following are the details:

Host name

SQL Server version

Role

SQL02

SQL Server Developer edition

SQL principle server

SQL03

SQL Server Developer edition

SQL mirrored server

SQL04

SQL Server Express edition

SQL witness server

I have created a demo database named DBA, which we are going to use to set up the mirror. The article: What is SQL Server database mirroring? explains the step by step process of deployment of SQL Server Database Mirroring.

To add the SQL Server Express edition as a SQL witness server first, we must configure its security of the mirror. To do that, connect to SQL02 (principal server), open SQL Server Management Studio, connect to Database Engine, right-click on the DBA database, and select Properties. See the following image:

Connect to database engine and open database properties

On the properties dialog box, from the left pan, select Mirroring. On the right pan, click on Configure Security. See the following image:

Configure security option in Database properties window

The configuration wizard called Configure Database Mirroring Security Wizard opens. The first screen provides basic information about the wizard and the tasks that are performed by the wizard. Click on Next:

Configure database mirroring wizard

On Include Witness Server screen, you can choose to configure the witness server or not. We are adding SQL Server Express edition as a witness server; hence select Yes. Click on Next:

Include the SQL witness server screen

On the Choose Server to Configure screen, we can choose the server on which you want to configure the security. We are configuring the witness server hence tick the Witness server instance checkbox. See the below image and click on Next:

Choose servers to configure screen

On Principle Server Instance screen, you can select the SQL Server instance, which you want to use as the principal server. We have already configured the mirror, hence the options Principle server instance drop-down box, Listener port, and Endpoint name text boxes are greyed out. See the following image and click on Next:

Principal server instance screen

Now, to configure the SQL Server Express edition as the SQL witness server, we must connect it with appropriate permission. We have installed SQL Server Express edition on SQL04; hence on the Witness server instance screen, select the SQL04 from Witness server instance drop-down box and click on Connect:

SQL Witness server instance

On the Connect to Server window, provide appropriate credentials, and click on Connect:

connect to SQL witness server

If the connection is established successfully, the Connect to Server dialog box would close. Back to the Witness Server Instance screen, you can provide the desired port number and the endpoint name. We can also encrypt the data which is going to be transported through the defined endpoint. To do that, tick the Encrypt data sent through this endpoint checkbox. We will keep the options unchanged. See the following image and click on Next:

SQL Witness server instance screen

On the Service Accounts screen, provide the credentials of the SQL Server service account. These credentials will be used to connect the principle, witness, and mirror server. We have configured the service account named “dclocal\administrator,” which is used as a SQL Server service account across all the servers hence enter the “dclocal\administrator” in “Principal”, “Witness”, and “Mirror” text box:

Service accounts of Witness , Primary and mirror.

On the Complete the Wizard screen, you can review the list of the task that is going to be performed by the wizard. It is advisable to review them once and click on Finish. See the below image:

Complete the wizard screen

If the endpoints are configured correctly, then you can see the “Success” on configuring endpoints screen. Click on Close:

Configuring endpoints successful

Verify configuration

To verify that the witness server is configured successfully, open the database properties of the DBA database, and select the mirroring from the database properties dialog box. See the following image:

Database properties of SQL witness server after setup

As you can see in the above image, the endpoint of the witness server has been created. You can see its network name in the “Witness” text box. Also, notice that the operation mode of the mirror has been changed. Before we configured the mirror, the operational mode was “High safety without an automatic failover (Synchronous),” and now it is “High safety with automatic failover (Synchronous).

Test automatic failover using SQL witness server

As explained above, in the case of database outage, the SQL witness server automatically transfers all the connections to the mirrored instance. To test the failover, before I set up the mirror, I had created a table named employee and added a few records in the table by executing the following script:

Now, let’s test the process. To do that, perform following steps:

Connect to principal database

First, we must connect to the principal SQL Server. To do that, launch SSMS and connect to the SQL02 node. In the SQL Server Management Studio, you can see on the principal server, the DBA database is in the synchronized state. See the following image:

Simulate database outage

Before we simulate the database failover, first execute the following queries to insert the data in the employee table:

Once data is inserted, stop the SQL Server services to simulate the database outage. To do that, open SQL Server Configuration Manager, expand SQL Server Services, right-click on SQL Server (MSSQLSERVER) and click on Stop:

Stop the SQL Services on SQL02

Verify automatic failover

When we configure the database mirroring, the state of the mirror database is set to <Database Name> (Mirror, Synchronized / Restoring..). See the following image of mirrored instance SQL03:

Database state on mirrored database.

Once services are shut down, let us verify that the principal instance is failed over to the mirrored instance; to do that, connect to the SQL03, open SQL Server Management Studio, connect to the Database Engine and expand Databases:

After failover, SQL03 server is primary server

As you can see that the state of DBA database on the mirrored instance SQL03 has been changed from <Database Name> (Mirror, Synchronized / Restoring..) to <Database Name> (Principal, Synchronized). Let us try to run the following query to verify that the data has been copied to the mirror database:

The following is the output:

Query SQL03 to verify the data

Summary

In this article, I have explained step by step process of utilizing the SQL Server Express edition to configure the SQL witness server in the existing Database Mirroring.

Nisarg Upadhyay
168 Views