Note: Please go to docs.rightscale.com to access the current RightScale documentation set. Also, feel free to Chat with us!
Home > ServerTemplates > Infinity > ST > Microsoft SQL Server Witness - Beta (v14 Infinity) > Microsoft SQL Server Witness - Beta (v14 Infinity) - Tutorial

Microsoft SQL Server Witness - Beta (v14 Infinity) - Tutorial

 icon-Beta-v1.png  Service-level response times are the same as for general-release features. Although this new feature/technology has undergone significant testing and is not expected to change significantly prior to general release, the use of this feature/technology is not recommended for production environments. You are encouraged to use this feature/technology for development and testing purposes only.  


Table of Contents    

Infinity

Leading edge features

   ►  Tutorial

Objective

To set up a mirroring witness to a Microsoft SQL Server mirroring pair.

Prerequisites

  • This tutorial assumes that you are working with a SQL Server database (or databases) using either a backup (.bak) or database (.mdf) file stored in a Remote Object Storage (ROS) container such as Amazon S3 or Rackspace Cloud Files. For more information, see the Database Manager for Microsoft SQL Server (v14 Infinity) - Tutorial.

Overview

This tutorial describes the steps for launching a mirroring witness to a Microsoft SQL Server principal and mirror server (mirroring pair). The purpose of adding a witness is to serve as a monitor to make sure a mirroring pair stays connected. As an example, if a synchronized mirror server loses a connection to the principal server but remains connected to the witness, the mirror server contacts the witness to determine whether the witness has lost its connection to the principal. 

This tutorial assumes you have operational principal and mirror servers which were set up with the Database Manager for Microsoft SQL Server (v14 Infinity) ServerTemplate. For information on how to configure a mirrored pair of principal and mirror servers, see the Database Manager for Microsoft SQL Server Template - Tutorial.

Note: If you are just setting up a standalone SQL server, a witness would not be necessary since the purpose is to make sure a mirroring pair stays connected. 

Steps

Create a Witness Server

  1. Go to the MultiCloud Marketplace (Design > MultiCloud Marketplace > ServerTemplates) and import the most recently published revision of the Database Manager for Microsoft SQL Server Witness - Beta v14 ServerTemplate into your RightScale account.
  1. From the imported ServerTemplate's show page, click the Add Server button.
  2. Select the cloud for which you will configure a server. 
  3. Select the deployment where your principal and mirror server exists, then click Continue.
  4. Next, the Add Server Assistant wizard will walk you through the remaining steps that are required to create a server based on the selected cloud.
    • Server Name - Provide a nickname for your new database server (e.g., Witness1). 
    • Select the appropriate cloud-specific resources (e.g., SSH Key, Security Group, etc.) that are required in order to launch a server into the chosen cloud. The required cloud resources may differ depending on the type of cloud infrastructure. If the cloud supports multiple datacenters/zones, select a specific zone.
  5. Click Confirm, review the server's configuration and click Finish to create the server.

Create a Witness Certificate Credential

You will need to create a certificate for the witness server.  

  1. Go to Design > Credentials > New and create a new credential for the private key password that will be paired with the SQL certificate for the witness server.
    • SQL_WITNESS_PRIVATE_KEY_PASSWORD
  2. Go to the Scripts tab of the SQL database server where you generated the certificates for the principal and mirror database servers. 
  3. Run the 'DB SQLS Generate and Save a Certificate' operational script to create a unique certificate for the witness server. Be sure to select the credential (that you just created e.g., SQL_WITNESS_PRIVATE_KEY_PASSWORD) for the PRIVATE_KEY_PASSWORD input.
     
Input Name Description Example Value
PRIVATE_KEY_PASSWORD

This is the password used to encrypt the certificate's private key.

Although you can input this value as a text, it's recommended that you create a credential for each certificate password. Although you can use the same password for each certificate, it's more secure if each certificate has its own unique password.

NoteThe password must meet the Microsoft SQL Server Strong Password requirements:

  • Does not contain all or part of the user's account name. 
  • Is more than eight characters in length.
  • Contains characters from at least three of the following categories:
    • English uppercase characters (A through Z)
    • English lowercase characters (a through z)
    • Base 10 digits (0 through 9)
    • Nonalphabetic characters (for example: !, $, #, %)
  • For more information, please see Microsoft SQL Server Strong Password Guidelines

cred: SQL_WITNESS_PRIVATE_KEY_PASSWORD

 

MASTER_KEY_PASSWORD

This password is used for encryption of the master database key, which is a key that is used to protect other certificate keys and other various keys in the database. This input allows you to set a master key password so that you are later able to decrypt and use the master key if needed. The same value used on your Principal and Mirror servers should be used here.

 

Note: It's strongly recommended that you use a credential to hide the actual key value from non-admin users while still allowing them to pass the appropriate value as an input.

cred: MY_MASTER_KEY_PASSWORD

 

  1. RDP into the database server where you ran the script to create the certificate.
  2. Go to Start > Computer > Local Disk (C:) and open up the last (highest numbered) credential text file. If you created the principal and mirror certificates on this server, 'credential2.txt' will contain the mirror certificate. 

screen_WitnessCertificate.png

  1. Open the file and copy all of its contents.
  2. Go to the RightScale Dashboard and paste in the two lines into a new credential (Design > Credential > New) and name it appropriately. (e.g., SQL_WITNESS_CERTIFICATE) 

File:ServerTemplates/Infinity/ST/Database_Manager_for_Microsoft_SQL_Server_Witness_Beta_(v13_Infinity)/Database_Manager_for_Microsoft_SQL_Server_Witness_-_Beta_ST_-_Tutorial/screen_witnessCred.png

  1. Click Save.

Modify Deployment Inputs

Go to your Deployment's Inputs tab. Click Edit and modify the following inputs:

DATABASE

Input Name Description Example Value
SQL_EXPRESS_VERSION

This is the version of SQL Server Express that will be used for the witness. Currently SQL Server Express 2008R2 and SQL Server Express 2012 are supported. Please select appropriate version from dropdown.

  • 2008R2 (default)
  • 2012

Important! The version of SQL Server Express on the witness server needs to match versions of SQL Server on the principal and mirror servers. Go to the principal database server's Info tab, under the MultiCloud Image or Image sections to verify which SQL version is being used.

text: 2008R2
USE_PUBLIC_IP_WITNESS

Defines whether or not the witness will use the public network to connect to the principal and mirror database servers on TCP port 5022. If you launch the witness server in a cloud/region where it cannot communicate with the principal and mirror database servers over the private network, you must set this input to 'True' and set appropriate firewall permissions on both the pricipal and mirror servers to allow ingress communication from the witness server.

  • True (default) - Use the public network for the witness connection
  • False - Use private network for the witness connection
     

Important: If set to 'True,' and there are operational principal and mirror database servers, you must first set up any necessary firewall permissions so that it will accept incoming requests over the public network on TCP port 5022.

text: False
WITNESS_CERTIFICATE

This is the certificate to be used for authentication on the mirroring endpoint of the witness server. This input is required for a witness server.

Cred: SQL_WITNESS_CERTIFICATE
WITNESS_PRIVATE_KEY_PASSWORD

Password to decrypt private key contained in WITNESS_CERTIFICATE input. This input is required if you are going to launch a mirror server. This should be the same password which was used to generate and encode certificate and private key by the 'DB SQLS Generate and Save a Certificate' RightScript.

Cred: SQL_WITNESS_PRIVATE_KEY_PASSWORD

Launch the Witness Server

Next, launch the new server that will become a witness for the mirror and principal server.

If there are any required inputs that are missing values (highlighted in red), cancel the launch and add the missing values at the deployment level before launching the server again. Refer to the instructions in Launch a Server if you are not familiar with this process.

Add the Witness Server to the Mirroring Session

Once the server becomes operational, you will need to add the witness to the mirroring session by running the 'DB SQLS Init Witness' operational script on each individual server (principal, mirror, and witness).

If the witness server is going to communicate with the principal and mirror database servers over the public network (USE_PUBLIC_IP_WITNESS = True), you must update the firewall permissions on the running principal and mirror database servers to allow ingress requests from the witness server. If the cloud uses security groups (e.g. AWS), you will need to update one of the security groups associated with the principal and mirror database servers appropriately. If the cloud does not use security groups (e.g., Rackspace or Azure) the port will be opened to the IP of the witness server by the 'DB SQLS Init witness' script, when it's executed on the principal and mirror servers. (See steps below.)

  1. Verify that the same value for the USE_PUBLIC_IP_WITNESS input is used for all servers. (principal, mirror, and witness) You may need to update the input under the "current" server's Inputs tab on the principal and mirror database servers. The default value for this input is 'False' which means that the servers can communicate with each other over the private network. The input will be applied when you run the operational script in the following step.
  2. First run the 'DB SQLS Init witness' operational script on the witness server. 
  3. Next, run the same script on the principal and mirror servers. Most likely you created your mirror certificate after you launched the principal and mirror servers. Therefore, when you run the operational script, you may be prompted to select the appropriate credentials for the certificate and private key inputs related to the mirror and witness servers. When you run the script on the principal server you may also have to select the appropriate password and credential for the inputs specific to the mirror server because it was launched before those credentials existed.
Input Name Description Example Value
WITNESS_PRIVATE_KEY_PASSWORD

Password to decrypt the private key of the generated witness certificate. (SQL_WITNESS_CERTIFICATE)

cred: SQL_WITNESS_PRIVATE_KEY_PASSWORD
WITNESS_CERTIFICATE

Certificate to be used for authentication on mirrroring endpoint of Witness server. 

cred: SQL_WITNESS_CERTIFICATE
  1. Once all scripts are completed across all three servers go to the deployment's Servers tab. Each server should now have the appropriate "witness" tags (highlighted below). 

screen-MS_SQL_4tier_Servers-v1.png

Next Steps

Terminating Witness Server

If you want to stop and terminate a witness server:

  1. Go to the witness server's Scripts tab.
  2. Run 'DB SQLS Switch Mirroring Witness Off'. This will detach the witness from the principal and mirror. Note: Similar to associating the witness to the mirroring pair, you will need to run this script on all three servers to detach the witness. 
  3. Once the witness is detached you can terminate the SQL witness server from your deployment.
You must to post a comment.
Last modified
13:57, 23 Jan 2015

Tags

This page has no custom tags.

Classifications

This page has no classifications.

Announcements

None


© 2006-2014 RightScale, Inc. All rights reserved.
RightScale is a registered trademark of RightScale, Inc. All other products and services may be trademarks or servicemarks of their respective owners.