Database mirroring is a high-availability feature in SQL Server that provides redundancy and failover capabilities for your databases. By configuring a witness server, you can enhance the reliability of your database mirroring setup. In this article, we will walk through the steps to configure database mirroring with a witness server in SQL Server.
Step 1: Connect to the Principal Server Instance
After connecting to the principal server instance, right-click the database and go to properties. Select “Mirroring” from the left side options.
Step 2: Configure Security
Click “Configure Security” and the “Configure Database Mirroring Security Wizard” welcome screen will appear. Click “Next” to proceed.
Step 3: Include Witness Server
In the “Include Witness Server” dialog box, click “Yes” and then click “Next”.
Step 4: Choose Servers to Configure
In the “Choose Servers to Configure” dialog box, the witness server instance check box is automatically checked. Click “Next” to continue.
Step 5: Configure Principal Server Instance
On the “Principal Server Instance” dialog box, keep the existing port and endpoint. Click “Next” to proceed.
Step 6: Configure Witness Server Instance
Click “Connect” on the “Witness Server Instance” dialog box. In the “Connect to Server” dialog box, specify the witness server instance in the “Server name” field and use Windows Authentication (the default). Once a connection is established, the listener port and database mirroring endpoint of the witness server instance will be displayed. Click “Next” to continue.
Step 7: Configure Service Accounts
The “Service Accounts” dialog box contains fields for the domain service accounts of the principal, mirror, and witness server instances. Fill in the necessary information and proceed.
Step 8: Complete the Wizard
On the “Complete the Wizard” summary screen, you can optionally verify the witness configuration. Click “Finish” to complete the configuration.
Step 9: Enable the Witness
On finishing, the wizard will return you to the “Database Properties” dialog box where the server network address of the witness will now appear in the “Witness” field. Also, “High-safety mode with automatic failover (synchronous)”, which is required with a witness, will be automatically selected. To enable the witness and change the session to high-safety mode with automatic failover, click “OK”.
By following these steps, you can successfully configure database mirroring with a witness server in SQL Server. This setup will provide enhanced reliability and failover capabilities for your databases.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN on:
Other Linked Profiles: