Database Manager for Microsoft SQL Server (v13.5 LTS) - Tutorial
Table of Contents
- Objective
- Prerequisites
- Overview
- Create DNS Records
- Create Credentials
- Steps
- Next Steps
- See also
| Table of Contents | |||
|
Long Term Support
Stable, tested ServerTemplate assets |
|
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.
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.
- Upload Files to an Amazon S3 Bucket
- Upload Files to a Rackspace Cloud Files Container
- Upload Files to an OpenStack (Swift) Container
- Upload Files to a Windows Azure Blob Container
- Upload Files to a SoftLayer Object Storage Container
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.
- Go to the MultiCloud Marketplace (Design > MultiCloud Marketplace > ServerTemplates) and import the most recently published revision of the following ServerTemplate into the RightScale account.
- From the imported ServerTemplate's show page, click the Add Server button.
- Select the cloud for which you will configure a server.
- Select the deployment for the new server, then click Continue.
- 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.
- 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.
| 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:
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.
| 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 Configure '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 (DynDNS, Rackspace 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.
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:
| text: 1234567 |
| 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.
| cred: DNS_PASSWORD |
| DNS_SERVICE | Select the DNS provider that will be used to update the DNS record of the principal database server.
| 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.
| 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.
| 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.
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.
| 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.
| 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.
| 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. Note: The 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:
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.
- Go to the deployment's Servers tab and launch the database server.
- 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.
| text: Standalone |
- 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.
- Click the Launch (not Save and Launch) button at the bottom of the input confirmation page.
- (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.
- 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.
- 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.
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).
- Go to the "current" server's Scripts tab.
- 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. (Use this script for the provided sample file.)
- For a database file (.mdf), run the 'DB SQLS Attach database from local disk / Remote Storage' script.
- Provide values for the appropriate inputs depending on whether you're going to load the database from either a backup or database file.
- (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. 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.
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.
- Go to the "current" server's Scripts tab.
- 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).
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.
- Go to the Scripts tab of the Standalone/Principal SQL database server.
- 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 |
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 SQL Certificates
- 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
- SQL_WITNESS_PRIVATE_KEY_PASSWORD (Required if you are going to launch a SQL Witness server.)
- Go to the "current" server's Scripts tab.
-
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). You will need to run the script a third time if you are going to launch a SQL Witness server.
| 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. Note: The password must meet the Microsoft SQL Server Strong Password requirements:
| For the Principal Certificate cred: SQL_PRINCIPAL_PRIVATE_KEY_PASSWORD
For the Mirror Certificate cred: SQL_MIRROR_PRIVATE_KEY_PASSWORD
For the Witness Certificate cred: SQL_WITNESS_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 |
- Go to your deployments Servers tab and RDP into the server.
- 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.
- Copy the two lines of text from the "credential" file.
- 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)
- Click Save.
- If you are going to launch a mirror server, repeat the same steps to create a credential for the mirror certificate. (e.g. SQL_MIRROR_CERTIFICATE). Use the contents of the credential1.txt file to create the credential.
- If you are going to launch a witness server, repeat the same steps to create a credential for the witness certificate. (e.g. SQL_WITNESS_CERTIFICATE). Use the contents of the credential2.txt file to create the credential.
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 > Inputs. Click Edit and modify the following inputs:
DATABASE
| Input Name | Description | Example Value |
| PRINCIPAL_CERTIFICATE (Required for Principle) | This is the certificate to be used for authentication on the mirroring endpoint of the principal server. This input is required for a principal server. | Cred: SQL_PRINCIPAL_CERTIFICATE |
| PRINCIPAL_PRIVATE_KEY_PASSWORD (Required for Principle) | Password to decrypt private key contained in PRINCIPAL_CERTIFICATE input. This input is required for a principal 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_PRINCIPAL_PRIVATE_KEY_PASSWORD |
| MIRROR_CERTIFICATE (Required for Mirror) | This is the certificate to be used for authentication on the mirroring endpoint of the mirror server. This input is required for a mirror server. | Cred: SQL_MIRROR_CERTIFICATE |
| MIRROR_PRIVATE_KEY_PASSWORD (Required for Mirror) | Password to decrypt private key contained in MIRROR_CERTIFICATE input. This input is required if you are going to launch a mirror server. when launching mirror server but not needed for a Standalone SQL 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_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.
- Go to the "current" server's Scripts tab.
- 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) 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.
- Clone the 'principal' database server.
- 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.
- (Recommended) If possible, change the new server's availability zone by clicking the server's Edit button to promote high availability.
- 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.
- Launch the server.
- 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 |
- 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.
- 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.
- 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.
- (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.
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 (v13 Infinity).
Safely Shutdown Database Servers
Follow the steps below to properly shutdown a SQL database server.
- 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.
- Go to the server's Scripts tab and run the 'DB SQLS DISABLE SERVER - backup, detach and delete volumes' script.
- Click the server's Terminate button.
See also
- © Copyright 2015 RightScale Technical Support