Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change SQL Server Database Mirroring from Manual to Automatic Failover


By:   |   Read Comments   |   Related Tips: More > Database Mirroring

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Suppose you have a new requirement to change the failover mode from manual to automatic for a SQL Server Database Mirroring configuration. As we know the witness server is mandatory for automatic failover, so we need to add a witness server to the database mirroring configuration to achieve automatic failover. Follow this step by step process of this tip if you need to add witness server to an existing SQL Server database mirroring configuration.

Solution

Database mirroring is a solution for increasing the availability of SQL Server databases. The operating modes for database mirroring are high-safety mode and high-performance mode. High-safety mode supports synchronous operation whereas high-performance mode runs asynchronously. Manual failover can be used in both operating modes. But if you need automatic failover then high-safety mode will be required by adding a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.

Microsoft recommends running the same version of SQL Server on both partners and the witness server which are going to be added to an existing mirroring configuration. This is also a recommendation that the witness should reside on a separate computer from either of the partners. The service account used by the witness must be in the same domain as the service accounts used by the principal and mirror server instances.

Adding a Witness to an existing SQL Server Database Mirroring Configuration

Step 1: First we should check whether database mirroring is working correctly and if the databases are in-sync. We can also see whether the witness server is added to this mirroring configuration. Run the below command to get the status of the witness server as well as the status of the database mirroring configuration.

SELECT databases.name AS DatabaseName,
       database_mirroring.mirroring_state_desc,
       database_mirroring.mirroring_role_desc,
       database_mirroring.mirroring_safety_level,
       database_mirroring.mirroring_safety_level_desc,
       database_mirroring.mirroring_safety_sequence,
    database_mirroring.mirroring_witness_name,
    database_mirroring.mirroring_witness_state,
    database_mirroring.mirroring_witness_state_desc   
FROM sys.database_mirroring    
INNER JOIN sys.databases
ON databases.database_id=database_mirroring.database_id
where databases.database_id>4

You can see the mirroring state is good, but the witness is not configured for this configuration because the "mirroring_witness_name" has no value and its state description shows "UNKNOWN". Here is a link to learn more about sys.database_mirroring.

Status of the witness server and the status of database mirroring configuration

Step 2: Now we have to add a witness server to this configuration to allow for automatic failover.  To do so, right click on the mirrored database where you want to add a witness and choose "Tasks" and click on "Mirror..." as shown in the below screenshot.

Launch the SQL Server Database Mirroring Configuration Page

Step 3: The Database Properties window for your target database (Manvendra) will open with the "Mirroring" tab selected. You can see the operating mode is "High safety without automatic failover" in the below screenshot, because the witness is not configured.

SQL Server Database Mirroring Configuration Page

Step 4: Click on the "Configure Security..." button as shown in the above screenshot. Once you will click this option, the welcome page of the "Configure Database Mirroring Security Wizard" will appear on the screen as per the below screenshot.

Configure Database Mirroring Security Wizard

Step 5: Click on the "Next" button to proceed with the wizard. The next window will ask you whether to include the witness server in the security configuration or not. Choose "Yes" to include a witness server to the security configuration as per the below screenshot and then click on the "Next" button.

Include witness server in the Configure Data Mirroring Security Wizard

The next page of the Wizard will determine the "Witness server instance" option. Select this option and click on the "Next" button.

Select witness server instance in SQL Server Database Mirroring

Step 6: Once you click on the "Next" button the "Principal Server Instance" wizard will load with all the options grayed out, because Database Mirroring is already configured.  The Principal server instance and listener port will load in their respective section. Click on the "Next" button to proceed.

Specify the Principal Server Instance, Port and End Point Name for SQL Server Database Mirroring

Step 7: The next page will configure the witness server instance. This is the interface where you need to enter the details about your witness server instance. The wizard informs you that the principal and witness server cannot be on the same server, so note this point.

SQL Server witness server instance configuration

Click on the "Connect..." button to enter the witness server instance details to make a connection to the witness server instance. See the below screenshot of this step.

Connect to the witness server instance for SQL Server Database Mirroring

Once you enter the details, click on the "Connect" button as highlighted in the above screenshot. Once a connection is established, the listener port and database mirroring endpoint of the witness server instance are displayed on the Witness Server Instance dialog box. Click "Next" to proceed.

Listener port and database mirroring endpoint of the witness server instance are displayed on the Witness Server Instance dialog box

Step 8: In the next screen, it is time to configure the service accounts. The service accounts dialog box contains fields for the domain service accounts of the principal, mirror, and witness server instances. If the server instances all use the same service account, leave the fields blank. If the witness server instance uses a different service account from either of the partners, fill in the Principal, Mirror, and Witness fields with the account name: DOMAINNAME\username. Make sure to add the SQL Server service account as a login with connect permission to the SQL Server instance. I have used the same service accounts for all instances, so I will leave this section blank.

Specify the SQL Server Database Mirroring Service Accounts

Step 9: The next step in the wizard is to verify all the details which you want to change or keep for this configuration. Verify the details and click on the "Finish" button as shown in the below screenshot.

Final dialog box to validate the SQL Server Database Mirroring Configuration

Once you click on the "Finish" button, the "Configuring Endpoints" window will load with the status of the witness configuration for both the principal and witness server instances as shown in the below screenshot. Now click on the "Close" button to complete the process.

Configuring Endpoints dialog box validates the changes to the SQL Server Database Mirroring Configuration

Validate SQL Server Database Mirroring Witness Configuration

Once you click on the "Close" button of the above screenshot, the wizard returns you to the Database Properties dialog box where the server network address of the witness now appears in the Witness field as we can see in the below screenshot.

Validate SQL Server Database Mirroring Witness Configuration

We can see one more thing here by comparing the above screenshot with the one shown in step 3. Here you can see the operating mode of this configuration has been changed from "High safety without automatic failover" to "High safety with automatic failover". Therefore we can validate that the witness server has been added to the existing SQL Server Database Mirroring configuration.

We can also validate the witness configuration by running the same command in step 1. We can see the "mirroring_witness_name" along with some other witness related columns that now have valid values which we just entered during witness configuration. Now the mirroring witness state should have a value of "Connected" as shown below.

Query the system views to validate the SQL Server Datase Mirroring Configuration
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools