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:
- The witness must be configured and the mirror operating mode of the database mirror must be High Safety with automatic failover
- 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
- 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:
On the properties dialog box, from the left pan, select Mirroring. On the right pan, click on Configure Security. See the following image:
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:
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:
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:
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:
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:
On the Connect to Server window, provide appropriate credentials, and click on Connect:
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:
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:
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:
If the endpoints are configured correctly, then you can see the “Success” on configuring endpoints screen. Click on Close:
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE EMPLOYEE ( ID INT IDENTITY(1, 1), EMPLOYEENAME VARCHAR(50), DEPARTMENT VARCHAR(150) ) GO INSERT INTO EMPLOYEE (EMPLOYEENAME, DEPARTMENT) VALUES ('NISARG UPADHYAY', 'IT'), ('NIRALI UPADHYAY', 'HR'), ('SONALI BHATT', 'HR'), ('SHAILESH UPADHYAY', 'IT') GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE DBA GO INSERT INTO EMPLOYEE (EMPLOYEENAME, DEPARTMENT) VALUES ('BHARTI UPADHYAY', 'IT'), ('DIXIT UPADHYAY', 'IT') GO |
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:
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:
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:
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:
|
1 2 3 4 5 |
USE DBA GO SELECT * FROM DBA..EMPLOYEE |
The following is the output:
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022