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 > Database Manager for Microsoft SQL Server (v14 Infinity) > Database Manager for Microsoft SQL Server (v14 Infinity) - Tutorial

Database Manager for Microsoft SQL Server (v14 Infinity) - Tutorial

Table of Contents    

Infinity

Leading edge features

   ►  Tutorial

Objective

To set up one of the following Microsoft SQL database configurations in a deployment using cloud resources of a public or private cloud. 

  • Single database server running in stand-alone mode. 
  • Two database servers (primary and mirror) using synchronous mirroring (high-safety mode) for high availability (HA) configurations. 

Prerequisites

  • 'actor', 'designer', 'security_manager', and 'library' user roles in order to complete the tutorial.
  • For clouds that support security groups (e.g. Amazon EC2, CloudStack-based clouds), you must have a security group defined with TCP port 3389 open to allow Remote Desktop Connections (RDC), and any other ports required by the server. For example, the default SQL Server port (TCP port 1433), and the mirroring listener port (TCP port 5022). Also remember that for clouds other than Amazon EC2, Windows Firewall is turned on by default.
  • In synchronous-mirroring environments, it's strongly recommended that your principal and mirror servers are located in the same cloud/region, but in different availability zones (if supported by the cloud) for high availability purposes.
  • We strongly recommend that you set up credentials for password values and any other sensitive data included as inputs. See "Create Credentials" section below.
  • This tutorial assumes that you are attaching a SQL Server database (or databases) using either a backup (.bak) or database (.mdf) file stored in a supported ROS container. Once you set up your SQL database on a cloud server and take a backup of the database, you can restore the SQL Server databases from a previous backup that contains the appropriate lineage tags for proper retrieval.
  • Go to Settings > Account Settings > Preferences tab, and make sure the "Verify Inputs" option is enabled so that you can view and change an input's value before running operational scripts, which will be useful if you run operational scripts to change the mode of the server. (e.g., Standalone to Principal)

Overview

This tutorial describes the steps for launching either a standalone Microsoft SQL server in a cloud or a mirrored setup consisting of a Principal and Mirror database server.

Note: Rackspace Performance Cloud Servers are not supported.

Create DNS Records

In a typical 3-tier architecture setup, DNS A records are used to create fully qualified domain names (FQDNs) that map to a particular server or tier of servers. The digram below shows a typical example of a 3-tier website architecture.

For example, the application servers locate the standalone or principal SQL database server by using the database server's FQDN (e.g., db-principal.example.com), which points to the server's private IP address. Similarly, front-end web traffic can be routed to a FQDN (e.g. www.example.com) where each load balancer server has a DNS record for that FQDN so that incoming requests are routed to one of the load balancer servers. Since the IP address of an instance in the cloud is often dynamically assigned at launch time, you are required to use a DNS provider that supports dynamic DNS (i.e. the ability to dynamically update the IP address of an A record) for the standalone/principal server (at a minimum). You can also use the same DNS provider for creating FQDNs for the load balancer servers. However, since they do not require the use of dynamic DNS, any DNS provider can be used.

TTLs

When you create the DNS records, it's important to set appropriate TTLs to ensure that servers will not stay connected to an old IP address that is no longer assigned to a functional server. For example, the DNS record that points to the standalone/principal database server should have a low TTL to ensure that the application servers will connect to the correct server within a reasonable amount of time. It's strongly recommended that you use a TTL of 60 seconds for the DNS record that points to the standalone/principal database server. If you are also creating DNS records for the front-end load balancer servers, you can use a more conservative TTL than the database tier. (e.g. 1800 seconds)

Note: If you are using Rackspace's Cloud DNS service, you must use a TTL of 300 seconds for the database servers because it's the lowest allowable TTL. Be sure to change the 'DNS_TTL' input from 60 (default) to 300.

diag-3tier_ga_Win-v1.png

 

You will need to create DNS records for the following servers:

  • Each Load Balancer Server or Load Balancing service (e.g. Amazon Elastic Load Balancer, Rackspace Cloud Load Balancer, etc.)
  • Standalone/Principal Database Server
  • Mirror Database Server (Optional)


RightScale's ServerTemplates contain scripts that support one of the following DNS providers. Create an account with one of the DNS providers below and set up the A records accordingly.

Important! The tutorials below assume that you are creating records for a Linux-based architecture. However, you can follow the general steps to create the DNS records that are required for Windows.

Create Credentials

Prerequisite: Requires 'actor' user role privileges in the RightScale account.

In order to securely pass sensitive information to a script at runtime, you can use Credentials as a means of variable substitution. Later in this tutorial you will select these credentials when you define your inputs.

General

  • WINDOWS_ADMIN_PASSWORD - Password for the Windows 'Administrator' user (default) or specified user with administrative privileges. You must specify a value that satisfies the minimum password requirements, otherwise the initial Windows password will be used instead. For example, a valid password should contain at least 7 characters and include at least one upper case letter, one lower case letter, and one digit. See Password Policy for details.
  • SQL_APPLICATION_USER - A SQL database user with login privileges for the specified database. The IIS application will use this credential to establish an authorized connection with the SQL database.
  • SQL_APPLICATION_PASSWORD - The password for the SQL database user with login privileges for the specified database. The IIS application will use this credential to establish an authorized connection with the SQL database.
  • MASTER_KEY_PASSWORD - The password for encrypting the master database key, which is used to protect other certificate keys in the database. 

Database Backup Retrieval 

Create the appropriate credentials depending where the initial SQL backup file (.bak) or existing database file (.mdf) will be retrieved.

Steps

Upload the Database Dump File

The ServerTemplate contains scripts that can retrieve a Microsoft SQL database backup file from a Remote Object Storage (ROS) location such as an Amazon S3 bucket or a Rackspace Cloud Files container. Create a new bucket/container and upload your database backup file. You can either restore the database using a SQL backup file (.bak) or attach an existing database file (.mdf). The file can remain a 'private' object because your cloud credentials can be used (as inputs) for authentication purposes to retrieve the file.

Warning! The filename of the backup file cannot contain a dash (-) in its prefix name. For example, if your dump file is named, 'my-app-201205030022.gz', you must manually rename it to be 'my_app-201205030022.gz' where you use an underscore (_) to replace the dash, otherwise the script (do::do_dump_import) that imports the database dump file into the instance will fail.


If you are setting up a database server for testing purposes, you may use the following sample SQL backup file to complete the tutorial.

Create a Database Server

Follow these steps to add a database server to the deployment.

  1. Go to the MultiCloud Marketplace (Design > MultiCloud Marketplace > ServerTemplates) and import the most recently published revision of the following ServerTemplate into the RightScale account.
  2. From the imported ServerTemplate's show page, click the Add Server button.
  3. Select the cloud for which you will configure a server. 
  4. Select the deployment for the new server, then click Continue.
  5. 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., sql-db1). Do not include "primary" or "mirror" in the name, because a database server's role can change in the future.
    • 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. Later, when you create the other database server you will use a different datacenter/zone to ensure high-availability. For more information, see Add Server Assistant.
    • Important! If the cloud does not support the use of volumes and snapshots for database backups, you must select an instance type with disk space that's at least twice as large as your database because data is stored locally on the instance before it is zipped and saved to the specified ROS location. Also, although these ServerTemplates may work with a variety of instance sizes, you may experience degraded performance with small instance sizes (such as EC2 micro, Rackspace 256MB etc) due to lack of system resources. We do not recommend smaller instance types for production use.
  6. Click Confirm, review the server's configuration and click Finish to create the server.

Configure Inputs

The next step is to define the properties of your database server or servers by entering values for inputs. It is simplest and best to do this at the deployment level. For a detailed explanation of how inputs are defined and used in Chef recipes and RightScripts, see Understanding Inputs.

The inputs that you need to provide values for will depend on which options you're going to use. The ServerTemplate is very flexible and supports a variety of different configurations. You will need to provide the necessary values as inputs based on which options you want to use.

Set Inputs at the Deployment Level

Go to the deployment's Inputs tab (Manage > Deployments > your deployment) and click Edit.

Although you can enter text values for all missing inputs, it's strongly recommended that you set up credentials for passing sensitive information to scripts such as passwords or any other sensitive data.

BACKUP

By default, the server will be configured to take continuous backups of the SQL database. Backups will either be saved as snapshots (if volumes and snapshots are supported by the cloud) or as backup files (.bak) to an ROS location such as an Amazon S3 bucket or Rackspace Cloud Files container. If you are setting up the SQL database with this ServerTemplate for the first time, you can either use an existing database (.mdf) or backup (.bak) file stored in an ROS container. If you are using the provided example, provide the appropriate inputs to use the backup file (DotNetNuke.bak).

Input Name Description Example Value
BACKUP_FILE_NAME

The name of the database backup file that you previously uploaded to an ROS container.  (e.g., sql-database.bak)

If you are going to use a database file (.mdf), leave this field empty and use the MDF_FILE_NAME input instead.

For the provided sample file use:

text:  DotNetNuke.bak

BACKUP_METHOD

Select the type of backup storage that will be used for taking backups of the database. If the cloud supports volumes and snapshots, you should use 'Snapshots' for this option.

  • Snapshots - Volume snapshots such as Amazon's Elastic Block Store (EBS), Microsoft Azure Volume Snapshots, etc.
  • Remote Storage - Remote Object Store (ROS) location such as an Amazon S3 bucket, Azure Blob Storage container, Rackspace Cloud Files container, etc.
text:  Snapshots
DB_BACKUP_KEEP_DAILY, _LAST, _MONTHLY, _WEEKLY, _YEARLY
 

If you are using snapshots (BACKUP_METHOD=Snapshots) to create database backups, you can use these inputs to specify the preferred backup policy. 

Inputs are ignored if you are using 'Remote Storage' for BACKUP_METHOD.

See Archiving of EBS Snapshots for more details.

 
MDF_FILE_NAME

The name of the database file that you previously uploaded to the ROS container.  (e.g., sql-database.mdf)

If you are going to use a database backup file (.bak), leave this field empty and use the BACKUP_FILE_NAME input instead.

text:  sql-database.mdf

CLOUD

AWS EC2 only
If you are launching your servers in AWS EC2 and using EBS volumes for local data storage, you must provide your AWS credentials for authentication purposes. If you are not using AWS services, set these inputs to 'ignore'.

Input Name Description Example Value

AWS_ACCESS_KEY_ID

AWS_SECRET_ACCESS_KEY

Specify valid AWS cloud credentials.

cred:  AWS_ACCESS_KEY_ID

cred:  AWS_SECRET_ACCESS_KEY

DATABASE

Input Name Description Example Value
DATA_VOLUME_SIZE

The size of each volume that will be used to store the SQL database.  For example, if the NUMBER_STRIPES input is '2' and you specify a DATA_VOLUME_SIZE of '5', two 5-GB volumes are created. It's recommended that you specify a large enough value that provides enough space for the database to grow over time and provide optimal read/write performance.

Warning! If your are deploying on a CloudStack-based cloud that does not allow custom volume sizes, enter a predefined CloudStack volume size (e.g., 'Small' or 'Large') rather than a numeric value here.

For the provided sample file use:
text:  10

 

NOTE: For Rackspace Open Cloud, the minimum volume size is 100 GB

DB_LINEAGE_NAME The lineage of the database backups. This is a string that is used to track all backups in a certain 'set', usually deployment wide. Ex: mymssqlserver text:  LineageName
DB_NAME

Enter the name of the default database to assign to the created SQL Server login. This value is not an arbitrary value. The name of the database is already set. If there are multiple databases, you can define a comma-separated list of all the database names. Ex: DatabaseName

text:  mydb

 

For the provided sample file use:

text:  DotNetNuke

INIT_MIRRORING_METHOD

The method for setting up a SQL database mirroring session between two servers. A backup (.bak) of the database and a self-signed certificate are used to authenticate a mirror session between a principal and mirror database server. It's recommended that you launch your principal and mirror servers in different availability zones for high availability and disaster recovery reasons. You can either use a snapshot or an ROS container depending on the cloud volume support. 

  • Snapshots (default) - A snapshot is used to transport the database backup file between the principal and mirror servers. The snapshot is used to create a volume in the same availability zone as the mirror server. Once the created volume (from the snapshot) has delivered the database file, it's unmounted, detached, and deleted.
  • Remote Storage - An ROS container is used to store and transfer the database backup file between the principal and mirror servers. You must use this option if the cloud in which the SQL servers are running does not support volumes and snapshots.
text:  Snapshots
MASTER_KEY_PASSWORD

This password is used for the encryption of the master database key, which is used to protect other certificate 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 necessary.

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
LOGS_VOLUME_SIZE

Size of the volume (in GB) for storing log files. Ex: 1

To optimize your Tempdb, this should be 1GB or greater than your instance core count.

For the provided sample file use:
text:  1
NUMBER_STRIPES

If the cloud supports the use of volumes, you can create a drive (e.g., D:\) that consists of a stripe of volumes. This input applies to both the data and log drives. If you do not want to use a volume stripe, keep the default setting which uses a single volume with no striping. (Default: 1)

Warning! You can set this to a numeric value between 1 and 5 when launching your server in the Amazon EC2 cloud; however, do not set it to values greater than 2 for CloudStack-based servers. This restriction is due to a difference in the quantity of block devices supported for CloudStack-based clouds.

text: 1

DNS

Input Name Description Example Value
DNS_DOMAIN_NAME

If you are using a DNS service provider that references records by a FQDN (DynDNSRackspace Cloud DNS, and Amazon Route 53) instead of a string ID, use this input to specify the fully qualified domain name that points to the standalone or principal database server. 

  • Example:  my-principal.example.com  (FQDN)

For other DNS services, you can leave this field blank/unset.

text:  my-principal.example.com
DNS_ID

Use this input to provide the appropriate identification number of the standalone or principal database server's DNS record. See below for details.

Examples:

  • DNS Made Easy - Dynamic DNS ID
    (e.g. 1234567)
  • DynDNSn/a; ignore
  • Rackspace Cloud DNS: ID of the Rackspace DNS zone of the DNS record to be updated  (e.g. a9300699)
  • Amazon Route 53 - Hosted Zone ID
    (e.g. Z3DSDFSDFX)
text: 1234567
DNS_IP_ADDRESS IP address to update the DNS record. Type a specific IP address or select whether to use the instance's public or private IP address. Set to "text:" and leave blank to bypass DNS registration on boot. Ex: 1.2.3.4 text: private
DNS_PASSWORD

The account or record level password used to log into your DNS provider. For security reasons you may want to create and use a credential to pass this value to the script.

  • DNS Made Easy - User Password
  • DynDNS - User Password
  • Rackspace Cloud DNS - User Password
  • Amazon Route 53 - AWS Secret Access Key
cred:  DNS_PASSWORD
DNS_SERVICE

Select the DNS provider that will be used to update the DNS record of the principal database server.

  • DNS Made Easy
  • DynDNS
  • Rackspace Cloud DNS (US region)
  • Rackspace Cloud DNS (UK region)
  • Route53
text:  DNS Made Easy

DNS_USER

The username used to log into your DNS provider. For security reasons you may want to create and use a credential to pass this value to the script.

  • DNS Made Easy - Username
  • DynDNS - Username
  • Rackspace CloudDNS - Username
  • Amazon Route 53 - AWS Access Key ID

cred:  DNS_USER

OPT_USE_PUBLIC_IP Defines whether or not a mirror database server will use the public (not private) IP address to connect to the principal database server. 
  • True - Use public IP address for mirroring connection
  • False - Use private IP address for mirroring connection (default)
text:  False
USE_PUBLIC_IP_WITNESS

If you are going to launch a MSSQL Witness server, use this input to specify 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

Note: Later when you launch the witness server, make sure the same value is used for this input.

text: False

REMOTE STORAGE

Input Name Description Example Value
REMOTE_STORAGE_ACCOUNT_ID

The Account ID or Name of the Remote Storage account which is used to authenticate your requests to Remote Storage services. 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.

  • Amazon S3 - Amazon Access Key ID (e.g. cred: AWS_ACCESS_KEY_ID)
  • Rackspace Cloud Files - Rackspace login username (e.g. cred: RACKSPACE_USERNAME)
  • Microsoft Azure Blob Storage - Azure Storage Account Name (e.g. cred: AZURE_ACCOUNT_NAME)
  • SoftLayer Object Storage - Username of a SoftLayer user with API privileges (e.g. cred: SOFTLAYER_USER_ID)
  • Swift - OpenStack Object Storage (Swift) Account ID (tenantID:username)  (e.g., SWIFT_ACCOUNT_ID)
cred: AWS_ACCESS_KEY_ID
REMOTE_STORAGE_ACCOUNT_PROVIDER

The remote object storage provider where your database file is stored, select appropriate provider from the dropdown list.

  • Amazon_S3 - Amazon S3
  • Rackspace_Cloud_Files_US - Rackspace Cloud Files (United States)
  • Rackspace_Cloud_Files_UK - Rackspace Cloud Files (United Kingdom)
  • Windows_Azure_Storage - Microsoft Azure Blob Storage
  • SoftLayer_Object_Storage_Dallas - SoftLayer's Dallas (USA) cloud
  • SoftLayer_Object_Storage_Singapore - SoftLayer's Singapore cloud
  • SoftLayer_Object_Storage_Amsterdam - SoftLayer's Amsterdam cloud
  • Openstack_Swift - Openstack Swift Object Storage
text: Amazon_S3
REMOTE_STORAGE_ACCOUNT_SECRET

The Secret Key or Password of the Remote Storage account which is used to authenticate your requests to Remote Storage services. 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.

  • Amazon S3 - AWS Secret Access Key (e.g. cred: AWS_SECRET_ACCESS_KEY)
  • Rackspace Cloud Files - Rackspace Account API Key (e.g. cred: RACKSPACE_AUTH_KEY)
  • Microsoft Azure Blob Storage - Microsoft Primary Access Key (e.g. cred: AZURE_PRIMARY_ACCESS_KEY)
  • SoftLayer Object Storage - SoftLayer API Access Key (e.g. cred: SOFTLAYER_API_KEY)
  • Swift - OpenStack Object Storage (Swift) Account Password (e.g. SWIFT_ACCOUNT_PASSWORD)
cred: AWS_SECRET_ACCESS_KEY
REMOTE_STORAGE_CONTAINER

If retrieving from a Remote Storage container, specify its name here. If retrieving from an ROS container from another cloud account, the specified file must be publicly accessible (e.g. set to "public-read" at a minimum).

Warning! If BACKUP_METHOD is set to 'Remote Storage' and the ROS container used for backups differs from the one where your database file is stored, ensure that you change this value back to your backup container, if needed, after running this script.
text:  my-bucket

SYSTEM

Input Name Description Example Value
ADMIN_PASSWORD

Set the new password for the local Administrator account.

NoteThe password must meet the Microsoft SQL Server Strong Password requirements, otherwise the random password that is generated for you at boot time (located under the Server's Info tab,'Initial Admin Password' field) will be used instead. The password must satisfy the following 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.

When you RDP into the server, you will use this password to log in as the Windows 'Administrator' user.

Note: It's strongly recommended that you use a credential to hide this value. However, anyone who needs to log into the server will need to know the actual value.

cred:  WINDOWS_ADMIN_PASSWORD  
SYS_WINDOWS_TZINFO

Sets the system timezone to the timezone specified, which must be a valid Windows timezone entry. You can find a list of valid examples in "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones". Some examples have been provided in the dropdown, which you may override if you do not see your timezone listed.

It's strongly recommended that you use "UTC"

text: UTC

Launch a Standalone Database Server

After configuring your inputs, launch the database server that will serve as either the standalone or principal server.

  1. Go to the deployment's Servers tab and launch the database server.
  2. When you view the input confirmation page, make sure the database server's mode is set to 'Standalone' mode. If you are setting up a mirrored Principal-Mirror setup, you will later run an operational script on this server that will change its mode to become a Principal.

DATABASE

Input Name Description Example Value
SERVER_MODE
Select the database server's role/mode. 
  • Standalone (default)  
  • Principal
  • Mirror
text:  Standalone
  1. 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.
  2. Click the Launch (not Save and Launch) button at the bottom of the input confirmation page. 
  3. (Optional)  If you are launching a server in a cloud that uses volumes (e.g. AWS) you can go to the server's Volumes tab once it's operational to view the volumes that were created for the database server.  

screen-volumes-v1.png

  1. Once the server becomes operational, the DNS Register IP script is automatically executed. Check the DNS record to make sure it was updated with the expected IP address. If you set the DNS_IP_ADDRESS input to 'Private IP' (default) the DNS record should match the instance's Private IP Address under the "current" server's Info tab because you typically want the IIS application servers to connect to the database over the private network.
  2. If you are using the default setting, the server's private IP address will be used. Check the server's audit entry and/or the DNS record with your DNS provider to view the results.
    screen-DNS_Update-v1.png

Load the Database

The next step is to load the database onto the server by either using a backup (.bak) or database file (.mdf) from an ROS container (e.g., Amazon S3 bucket).

  1. Go to the "current" server's Scripts tab.
  2. Run the appropriate operational script depending on the type of file.
    • For a backup file (.bak), run the 'DB SQLS Restore database from local disk / Remote Storage' script.
    • For a database file (.mdf), run the 'DB SQLS Attach database from local disk / Remote Storage' script.
  3. Provide values for the appropriate inputs depending on whether you're going to load the database from either a backup or database file.
  4. (Optional) Check the server to verify that the database was properly loaded. Click the server's RDP button to open up a Remote Desktop Connection to the insdstance.
    • For SQL Server 2008R2, navigate to Start and in "Search programs and files" enter "SQL Server Management Studio" and open the program. When asked to connect to the database, click Connect. You should see the imported database listed under the "Databases" section. (See screenshot below.)
    • For SQL Server 2012, move your cursor to the lower-left corner of the window to make the Start menu visible. Click the Start menu. Move your cursor and click in the lower-right corner of the screen to view the Search tool. Perform a keyword search to find the "Microsoft SQL Server Management Studio" utility.

screen-ShowDotNetNuke-v1.png

Create a Database Backup

The next step is to create your first backup of the database, which adds a set of tags (such as the lineage name) to the backup snapshots/file. The tags are used by the ServerTemplate's scripts to properly locate and retrieve backups for initial setups and database restorations.

  1. Go to the "current" server's Scripts tab.
  2. Run the 'DB SQLS Backup Data and Log volumes' operational script. If you are using volumes, a snapshot of the data and log volumes will be created and properly tagged. If you are not using volumes, a backup file (.bak) will be saved to the specified ROS location (REMOTE_STORAGE_CONTAINER).

 

Set up a SQL Mirroring Configuration

Important!  If you only want a standalone SQL database server your configuration is complete. However, if you want to set up a mirrored SQL configuration that contains a dedicated principal and mirror database server for high-availability purposes, please follow the remaining steps.

Generate Principal and Mirror Certificates

  1. Go to Design > Credentials > New and create credentials for the private key passwords that will be paired with the certificates you are about to create. Although you can use the same password for each certificate, it's more secure if each certificate has its own unique password.
    • SQL_PRINCIPAL_PRIVATE_KEY_PASSWORD
    • SQL_MIRROR_PRIVATE_KEY_PASSWORD
  2. Go to the "current" server's Scripts tab.
  3. Run the 'DB SQLS Generate and Save a Certificate' script two times because you want to generate unique certificates for both the principal and mirror servers. You can technically use one certificate for both servers, but that will be less secure. Select the appropriate password credential (that you just created) for the PRIVATE_KEY_PASSWORD input each time you run the script. First, create a certificate for the principal database server using the principal credential (SQL_PRINCIPAL_PRIVATE_KEY_PASSWORD), then run the script again to create a certificate for the mirror using the other credential (SQL_MIRROR_PRIVATE_KEY_PASSWORD).
     
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 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)
    • Non-alphabetic characters (for example: !, $, #, %)
  • For more information, please see Microsoft SQL Server Strong Password Guidelines
For the Principal Certificate

cred: SQL_PRINCIPAL_PRIVATE_KEY_PASSWORD

 

For the Mirror Certificate

cred: SQL_MIRROR_PRIVATE_KEY_PASSWORD

MASTER_KEY_PASSWORD

This password is used for the encryption of the master database key, which is used to protect other certificate 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 necessary.

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. Go to your deployments Servers tab and RDP into the server.
  2. Go to Start > Computer > Local Disk (C:) and open up credential.txt, which should be the credential for the principal server. The second text file (credential1.txt) was created the second time you ran the script, which should be for the mirror server.
    Note: Each time you run the script, it creates a newly-incremented credential. For example, if you ran the script a third time, there would be a credential2.txt file. 
     

screen_RDS-princCert.png

  1. Copy the two lines of text from the "credential" file.
  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_PRINCIPAL_CERTIFICATE)

screen-SQL_Principal_Certificate-v1.png

  1. Click Save.
  2. Repeat the same steps to create a credential for the mirror certificate. (e.g. SQL_MIRROR_CERTIFICATE) from the credential1.txt file

Modify Deployment Inputs

Note: Because your principal server is already operational, the inputs you modify at the deployment level will not affect the currently running principal server. The purpose of modifying these inputs is so your mirror will be able to interpret these inputs.

Note: If you want to create a witness server to monitor whether or not your principal and mirror servers stay connected, see the Database Manager for Microsoft SQL Server Witness - Beta ST - Tutorial

Go to your deployment > InputsClick Edit and modify the following inputs:

DATABASE
Input Name Description Example Value
PRINCIPAL_CERTIFICATE

This is the certificate to be used for authentication on the mirroring endpoint of the principal server. This input is required when launching a principal, mirror, or witness but not needed if you're launching a standalone SQL server.

Cred: SQL_PRINCIPAL_CERTIFICATE
PRINCIPAL_PRIVATE_KEY_PASSWORD

Password to decrypt private key contained in PRINCIPAL_CERTIFICATE input. This input is required when launching principal, mirror or witness but not needed for Standalone SQL Server. This should be the same password which was used to generate and encode certificate and private key by 'DB SQLS Generate and Save a Certificate' RightScript.

Cred: SQL_PRINCIPAL_PRIVATE_KEY_PASSWORD

MIRROR_CERTIFICATE

This is the certificate to be used for authentication on the mirroring endpoint of the mirror server. This input is required when launching a principal, mirror, or witness but not needed if you're launching a standalone SQL server.

Cred: SQL_MIRROR_CERTIFICATE
MIRROR_PRIVATE_KEY_PASSWORD Password to decrypt private key contained in MIRROR_CERTIFICATE input. This input is required when launching principal, mirror or witness but not needed for Standalone SQL Server. This sshould be the same password which was used to generate and encode certificate and private key by 'DB SQLS Generate and Save a Certificate' RightScript. Cred: SQL_MIRROR_PRIVATE_KEY_PASSWORD

Change Server from Standalone to Principal Mode

The first step is to change the existing database server from "standalone" to "principal" mode.

  1. Go to the "current" server's Scripts tab.
  2. Run the 'DB SQLS Init principal' operational script. Since this server is already operational, the deployment level inputs defined above will not affect this server.

Note: You will need to re-enter the information for the MIRROR_CERTIFICATE, MIRROR_PRIVATE_KEY_PASSWORD, PRINCIPAL_CERTIFICATE, and PRINCIPAL_PRIVATE_KEY_PASSWORD (see Modify Deployment Inputs). This is because the Deployment Level inputs that were modified in the previous step do not have an influence on operational servers.


Note: You will not see a 'completed: DNS SQLS Init Principal' audit entry on the principal server until there is a "mirror" server connected to it.

Launch the Mirror Database Server

Next, launch a new server that will become a mirror of the principal server.

If you are setting up a synchronous mirrored SQL Server environment, you will need to launch another SQL database server that will act as a "mirror" of the "principal" server. Ignore this step if you are only setting up a stand-alone SQL Server environment.

  1. Clone the 'principal' database server.
  2. Rename the cloned server. (e.g., db2)  Note: Do not put "mirror" in the name because you do not want the server's name to cause confusion if you ever need to promote the mirror to become the new principal database server.
  3. (Recommended)  If possible, change the new server's availability zone by clicking the server's Edit button to promote high availability.
  4. Before you launch the server, make sure that the first backup snapshot is 100% complete. The mirror server will use the backup file to restore the database before it starts synchronizing with the principal server.
  5. Launch the server.
  6. When you view the input confirmation page, change the database server's mode to 'Mirror' mode. 
DATABASE
Input Name Description Example Value
SERVER_MODE

Since you already have a 'Principal' server running, set this input to 'Mirror'.

text:  Mirror
  1. 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.
  2. Click the Launch (not Save and Launch) button at the bottom of the input confirmation page because you might not want this server to be launched in "mirror" mode the next time it is launched or relaunched.
  3. Once the "mirror" server becomes operational, you will need to wait a few more minutes for the mirroring setup to become completed. Wait for all of the principal and mirror tags to be updated (as seen in the screenshot below) before proceeding to the next step. Machine tags are used to clearly identify the "Principal" and "Mirror" servers. Note: You can only have a single mirror server.

screen-PM_Servers_Tags-v1.png

 

  1. (Optional) Check the servers to verify that you have a synchronous mirrored SQL Server environment. For example, you can RDP into the principal server. Open "SQL Server Management Studio" and under the "Databases" section you now see that the database is the "principal" server in a synchronized setup.
    screen-SQL_Synchronized-v1.png

Create a SQL User

Note: If you are only setting up a standalone SQL database server, you can skip this step. 

If you are going to connect an IIS application server to the database, you will want to create an application specific SQL user. Application servers will access the database using the created SQL user's username and password. If you want to use credentials to hide the actual username and password values, create appropriately named credentials prior to running the script below.

  1. Go to the Scripts tab of the Standalone/Principal SQL database server.
  2. Run the 'DB SQLS Create login' operational script.
DATABASE
Input Name Description Example Value
DB_NAME

The name of the SQL database for which you are creating a user.

For the provided sample file use:

text:  DotNetNuke

DB_NEW_LOGIN_NAME

User created with database privileges. The IIS web application will use the created SQL user to access the database. 

After the server is operational, run the 'DB SQLS Create login' script to create the SQL database user. 

Note: You can also create a user with a script used by the IIS application ServerTemplate. 

text:  dbuser

cred: SQL_SERVER_USER

DB_NEW_LOGIN_PASSWORD

Password of the SQL database user (DB_NEW_LOGIN_NAME). 

Note: Password must meet standard Windows 2008/2012 server password complexity requirements. It should be at least 7 characters long with at least one uppercase letter, one lowercase letter, and one digit. 

text:  4MicroSoft!

cred: SQL_SERVER_PASSWORD

Optimize Tempdb System Database (Recommended)

Since the size, recovery model, and growth settings for the SQL Server tempdb system database can impact server performance, you should configure the principal database according to Microsoft's optimization recommendations, described in the Microsoft Developer Network (MSDN) article Optimizing tempdb Performance.

To configure these optimization settings automatically, run the DB SQLS Configure TempDB operational script, which will:

  • Change the tempdb database's recovery model to 'Simple'
  • Change the autogrowth settings for the database and log files to autogrow by 10% (unrestricted growth)
  • Create one tempdb data (.mdf) file per virtual core (CPU) on the server, based on Microsoft's configuration recommendations. Use the OPT_TEMPDB_DATAFILE_SIZE input to specify initial file sizes (in GB) for your data files, if needed. For the sample DotNetNuke file, use a value of 1. (e.g., Text: 1)

Note: Make sure that this value multiplied by the number of instance cores does not exceed your value for LOGS_VOLUME_SIZE.

Next Steps

Launch IIS Application Servers

To launch IIS application servers that will connect to the Microsoft SQL database server, see the 3 Tier Deployment Setup (HAProxy-IIS-SQL) tutorial.

Add a Microsoft SQL Mirror for Automatic Database Failover

See Database Manager for Microsoft SQL Server Witness Beta (v14 Infinity).

Safely Shutdown Database Servers

Follow the steps below to properly shutdown a SQL database server. 

  1. If the database server is using volumes for data storage, Go to the server's Inputs tab and change the 'DISABLE_SAFETY' input to 'text: off' by using the override dropdown option. If volumes are not being used, proceed to Step 3.
  2. Go to the server's Scripts tab and run the 'DB SQLS DISABLE SERVER - backup, detach and delete volumes' script. 
  3. Click the server's Terminate button.

See also

You must to post a comment.
Last modified
13:24, 23 Jan 2015

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.