Overview

Once you have successfully set up your Microsoft SQL environment on a Windows Server in the cloud using the Database Manager for Microsoft SQL Server - Tutorial, you may need to perform the following common Microsoft SQL runbook operations.

Common Operational Tasks

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. Note: The 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
cred: SQL_PRINCIPAL_PRIVATE_KEY_PASSWORD
cred: SQL_MIRROR_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. Note: It is 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. st-RDS-princ-cert.png
  3. Copy the two lines of text from the credential file.
  4. 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) st-SQL-principal-certificate.png
  5. Click Save.
  6. Repeat the same steps to create a credential for the mirror certificate. (e.g. SQL_MIRROR_CERTIFICATE) from the credential1.txt file.

These certificates and credentials are then used to fill in the PRINCIPAL_CERTIFICATE, MIRROR_CERTIFICATE and WITNESS_CERTIFICATE inputs along with their respective * PRIVATE_KEY_PASSWORD inputs for configuring a mirroring session. More details can be found in the 'Setup a Mirroring Session' section of the Database Manager for Microsoft SQL Server - Tutorial.

Create a Microsoft SQL Backup of the Data and Log Volumes

Run the following script to create a backup of data and log volumes. This script uses Microsoft Volume Shadow Copy Service (VSS) to freeze disk activity on data files and then issues the RightScale API backup call to snapshot all the volumes. Once this backup call completes, VSS is disabled and the volume(s) are unfrozen. The SQL server will continue to operate normally during this entire process and does not even know that its volumes were backed up. Additionally, this script can create a differential or full SQL Server backup of all databases and upload the backup to a Remote Object Storage.

To perform this operation, follow the steps below:

  1. Go to the Scripts tab of a running Microsoft SQL Server.
  2. Run the DB SQLS Backup Data and Log volumes Operational Script.

DATABASE

Input Name Description Example Values
DB_LINEAGE_NAME Used to create a common name for each backup snapshot. (e.g., MySnapshot)  Also used to locate the appropriate backup for database restorations. (e.g., MyLineage) text: mylineage

REMOTE OBJECT STORAGE

Input Name Description Example Values
REMOTE_STORAGE_CONTAINER Name of S3 bucket or RackSpace Cloud Files container to be used as storage for database backups and certificate exchange. text: mycontainer
REMOTE_STORAGE_ACCOUNT_PROVIDER Type of remote storage used for database backups and certificate exchange. Amazon S3 and RackSpace Cloud Files are currently supported. text: Amazon_S3

To verify that the script was successfully executed, go to the storage container you specified to view your file. If you used AWS to store your backups, you can go to Clouds > AWS Global > S3 Browser and navigate to your container name. Open up your container and you should see .bak files with the name specified for the DB_LINEAGE_NAME input.

Create an EBS Backup Volume

Use the SYS Create Backup volume operational script to create an EBS volume of a specified size and attach it to the instance under a specified drive letter. This is useful if you need additional disk space for backup operations.

Input Name Description Example Values
BACKUP_VOLUME_SIZE Size of backup volume in GB or name of appropriate volume type to be created. Ex: 10 text: 10
BACKUP_VOLUME_TYPE Name of the volume type for a backup volume. Currently only RackSpace OpenCloud and CloudStack are support. On RackSpace OpenCloud, both SATA and SSD volume types are supported. The size of the volume should be specified in the BACKUP_VOLUME_SIZE input. On CloudStack-based clouds, volume types depend on how your cloud was configured. text: mybackupvolume
BACKUP_VOLUME_LETTER Specify the drive letter to be used for the newly created backup volume (e.g., G:) text: G
BACKUP_VOLUME_IO_OPERATION_PER_SECOND The number of I/O operations per second (IOPS) that the volume supports. Currently IOPS is only supported on Amazon EC2. This number also has limitations based on the total size (in GB) of the volume (BACKUP_VOLUME_SIZE). text:500

Create a SQL Server User

Use the DB SQLS Create login operational script to create a new SQL Server user (login) and password for your application's use. Enter the following RightScript inputs before running this script.

Input Name Description Example Values
DB_NAME Default database to assign to the new SQL Server user. text: MyDatabase
DB_NEW_LOGIN_NAME User name for the new SQL Server user to be used by the application server. cred: SQL_SERVER_USER
DB_NEW_LOGIN_PASSWORD Password for the new SQL Server user to be used by the application server. cred: SQL_SERVER_PASSWORD
DB_REMOTE_SERVER_IP IP address of the target SQL Server. The script assumes that the default SQL Server port, 1433, is used for server communications. text: 180.100.100.38
DB_REMOTE_SQL_LOGIN Login ID for a SQL Server user with administrative permissions, used to create the new application-specific user account. cred: SQL_APPLICATION_USER
DB_REMOTE_SQL_PASSWORD Login password for a SQL Server user with administrative permissions. cred: SQL_APPLICATION_PASSWORD

Graceful Failover

A graceful failover will switch one of the standby servers to the primary server in the event that a mirror instance type or storage is increased and roles need to be switched, or if maintenance is required on the principal server. To perform a graceful failover, you'll need synchronized principal and mirror servers that are in a healthy state. However, if there is a problem with the principal server and you are in a disaster recovery scenario, follow the Disaster Recovery Failover steps instead.

  1. Navigate to your mirror server and click on the Inputs tab.
  2. Make sure FORCE_SERVICE_ALLOW_DATA_LOSS is set to False.
  3. Click on the Scripts tab and make sure all inputs required by the DB SQLS Promote to Principal and DNS Register IP scripts are set.
  4. Navigate to the principal server and click on the Scripts tab and run the DB SQLS Manual failover operational script.
  5. Verify the script successfully executed on the principal and mirror servers. Once completed, the two servers should have switched roles and the DNS (sub)domain should now point to the new principal server.

Disaster recovery failover

In the event that a principal server becomes unreachable, a new mirror server can be set up to prevent data loss.

  1. Navigate to the mirror server and go to the Inputs tab.
  2. Make sure FORCE_SERVICE_ALLOW_DATA_LOSS is set to True.
  3. Click on the Scripts tab of the mirror server and run the DB SQLS Promote to Principal operational script.
  4. Set the SERVER_MODE input to 'Mirror' on the old principal server, then re-launch it, making it a 'new' mirror server.
  5. Once the new mirror server becomes operational, verify that the two nodes are synchronizing.
  6. Change FORCE_SERVICE_ALLOW_DATA_LOSS input to False on the new principal server.

Replace mirror server

In the event that a mirror server becomes unreachable, needs to be moved to a different zone, or the instance type or data/log volumes for the mirror server has changed, the mirror server can be replaced. To replace a mirror server, an initialized mirroring session between principal and mirror server is required.

  1. Navigate to principal server and click on the Scripts tab.
  2. Run the DB SQLS Switch mirroring off operational script.
  3. (Re)launch the mirror server and set SERVER_MODE to Mirror.
  4. Navigate to the Scripts tab of the principal server and run the DB SQLS Init principal script.
  5. Verify the scripts successfully executed on the principal and mirror servers by viewing each server's audit entries.

Increase instance type or data/log drives for principal and mirror servers

To gracefully change the instance type used by the principal and mirror servers, the space allocated for data and log volumes, the mirror will have to be relaunched using the new settings and synchronized with the existing principal server. After this, the Mirror will be gracefully promoted into a principal and a new mirror will be launched using the new settings.

  1. Follow the Replace Mirror Server steps and ensure your changes to the new mirror are made prior to step 3 (re-launch the mirror server).
  2. Follow the Graceful Failover steps.
  3. Follow the Replace Mirror Server steps.

Add a new database (schema)

To add a new database to the principal server and enable synchronization for all non-system databases, follow the steps below.

  1. Add new database(schema) to the principal server.
  2. Navigate to the Scripts tab of the principal server and execute the DB SQLS Switch mirroring off operational script.
  3. Navigate to the Scripts tab of the mirror server and execute the DB SQLS Switch mirroring off operational script.
  4. Navigate to the Scripts tab of the principal server and run the DB SQLS Init principal script.
  5. Navigate to the Scripts tab of the principal server and run the DB SQLS Init mirror script.
  6. Verify the scripts successfully executed on the principal and mirror servers by viewing each server's audit entries.

Restore from the latest backup

If you have previously completed a backup of your Microsoft SQL database, you can restore a standalone or synchronized pair of servers from the latest backup.

  1. Navigate to the Inputs tab for the server you would like to restore and configure the OPT_FORCE_CREATE_VOLUMES input. Make sure the input is properly set:
    • If the OPT_FORCE_CREATE_VOLUMES input is using the default value of False, then the boot script DB SQLS Setup volumes and services will restore from the latest volumes/ROS (Remote Object Storage) backup in the lineage (which is defined by the DB_LINEAGE_NAME input).
    • If the OPT_FORCE_CREATE_VOLUMES is set to True, then the server will not attempt to restore at boot time, and fresh empty volumes will be created instead.
  2. Once configured, you can go to the Scripts tab and run one of the following scripts to restore from a backup:
    • DB SQLS Restore all databases from Remote Storage
    • DB SQLS Restore database from Local Disk / Remote Storage  -This is primarily used for user database imports.
  3. Once the restore is complete, the server can stay as a standalone or be initiated as a principal as long as there is a mirror server.

Restore from a specific backup

The steps used to restore a specific backup differs slightly from restoring from the latest backup.

For example, The OPT_DB_RESTORE_TIMESTAMP input will have to reference the exact backup to restore from, but it cannot be used here because it is being used by the DB SQLS Setup volumes and services boot script. Therefore, the value for OPT_DB_RESTORE_TIMESTAMP can be obtained from:

  • Tags associated with the volume snapshot, For example:

rs_backup:lineage= mydatabase
rs_backup:timestamp= 1347359186

  • Backup filenames uploaded to ROS (Remote Object Storage), For example:

SQLS_BAK_mydatabasefull20120726911410.ini

Verify and repair log files

Microsoft VSS is used for volume snapshot based backups to guarantee consistency of data files. However, Microsoft VSS does not guarantee consistency of log files. To verify consistency and repair log files after performing a database restoration, run the DB SQLS Repair log files operational script, which checks log files for all attached databases. No inputs are required to run the script.

Change the backup retention policy

There are several inputs that can be modified to control how many backups of the Microsoft SQL data volume (D:\ by default) should be retained for archiving purposes. The backup and cleanup operations are performed by the DB SQLS backup data volume script which is automatically scheduled for execution every 4 hours on a running server (by default). The script can also be executed manually at any time.

  1. Go to the Deployment's Inputs tab.
  2. Update the following Inputs to control the backup retention policy. See Archiving EBS Snapshots for more details. Hover over the input tooltip to view the default settings for each input. If the input is set to '-ignore-' the default settings will be used.
    • DB_BACKUP_KEEP_DAILY
    • DB_BACKUP_KEEP_LAST
    • DB_BACKUP_KEEP_MONTHLY
    • DB_BACKUP_KEEP_WEEKLY
    • DB_BACKUP_KEEP_YEARLY

Configure 'tempdb'

Use the following script to configure the 'tempdb' directory on the Microsoft SQL database volume (e.g., D:\ by default) according to Microsoft recommendations ( http://msdn.microsoft.com/en-us/library/ms175527.aspx). The script accomplishes the following tasks:

  • Sets recovery model to simple
  • Discovers CPU core count on system, then creates the same number of data files as core count. All files are the same size by default. Default size for each file is 1 GB, but can be overridden using the OPT_TEMPDB_DATAFILE_SIZE input.
  • Sets the growth mode of data files to 10%

To perform this operation, follow the steps below:

  1. Go to the Scripts tab of a running Microsoft SQL Server.
  2. Run the DB SQLS Configure tempdb Operational Script.
Input Name Description Example Values
OPT_TEMPDB_DATAFILE_SIZE Use this parameter to specify a custom initial size of the data files for the 'tempdb' database. If set to 'ignore' a 1GB default data file will be created. Text:4

Graceful termination or shutdown

It's important to terminate the Windows server properly. Before you can use the standard Terminate action button in the Dashboard to terminate the instance, you will first need to execute an Operational Script that will ensure a graceful shutdown of the Server. If the script is not run prior to shutting down the server, a backup snapshot of the detached volume will not be taken and the volume will become orphaned (un-deleted). In order to protect against data loss and orphaned (un-deleted) volumes, follow the steps below:

  1. Click on the Inputs tab of the Server that you want to terminate/shutdown.
  2. Make sure that the DISABLE_SAFETY input, which prevents the Server from being accidentally disabled, is overridden and set to off (e.g., text: off), otherwise you will not be able to run the following script.
  3. Click on the Server's Scripts tab.
  4. Run the DB SQLS DISABLE SERVER - backup, detach and delete volumes Operational Script. The only input that is required to run the script is DB_LINEAGE_NAME. Other inputs can be set to ignore. If you defined the inputs at the Deployment level, you will not be prompted to specify any missing inputs. The script performs the following clean-up actions:
  5. Stops SQL Server
  6. Takes EBS Snapshot of the EBS data volume(s) (D:\ by default). The Snapshot will receive a tag based on the DB_LINEAGE_NAME input
  7. Un-mounts and detaches EBS data volume(s) (D:\ by default) from the Server
  8. Deletes the EBS Volume(s)

  9. Once the script has been successfully completed, it is now safe to terminate the Server. Click the Server's Terminate action button.

Install RightScale PowerShell Library

Installs the RightScale PowerShell Library, which is a prerequisite before you can run the SYS Setup firewall rule and SYS Dump firewall rules operational scripts.

Join or Leave Active Directory Domain Controller

To connect a remote server to a domain controller that was launched in a cloud using the Microsoft Active Directory ServerTemplate, you must first configure the domain controller to accept ingress communication from the remote server. See the Create a Windows firewall permission steps.

By default, remote servers will connect to the domain controller using tags. However, you may need to connect servers where tags are not available or supported. For example, perhaps the remote server is located in a different cloud/region where it does not have access to the domain controller on the private network or the tag scope of the deployment that contains the domain controller is not set to be account-wide and the remote server is located in a different deployment than the domain controller. In such cases, you can either connect to the primary AD server using an IP address or FQDN.

  1. Make sure the domain controller is configured to accept requests from the remote server. You may need to update the domain controller's firewall permissions accordingly.
  2. If you used a ServerTemplate to launch the remote server, run the following RightScripts as 'Any Scripts' on the running server. If you anticipate performing this action more than once, you may want to edit the ServerTemplate and add these RightScripts to the Operational Scripts list.
  3. Execute the SYS Install RightScale PowerShell Library (v14.x) operational RightScript to satisfy the prerequisites for the subsequent join/leave scripts. Wait for the script to be completed.
  4. Run the SYS Join AD domain (v14.x) operational RightScript. Once the script is completed, check the server's tags to verify that it properly joined the correct AD domain.
Input Name Description Example Value
AD_ADMIN_ACCOUNT This is a login for account with administrative right in Active Directory. Example: Administrator. (Note: Administrator will be used as a default value for the AD_ADMIN_ACCOUNT input if it's unset at launch time. However, the input must match the value specified for the ADMIN_ACCOUNT_NAME input that was set for the AD servers.) text: Administrator
AD_ADMIN_PASSWORD Specify the new password for the 'administrator' user specified by the AD_ADMIN_ACCOUNT input. It's strongly recommended that you use a credential to hide this value. cred: AD_ADMIN_PASSWORD
AD_CONTROLLER_IP If the remote server is going to connect to the AD domain controller using an IP address , specify the IP address of the AD domain controller that the server will connect to. Make sure the IP address matches the selected interface. (AD_NETWORK_INTERFACE) text: 192.23.45.678
AD_DOMAIN_NAME If the remote server is going to connect to the AD domain controller using a  FQDN , specify the FQDN that points to the primary Active Directory controller that the server will join. text: ad-primary.example.com
AD_NETWORK_INTERFACE The interface used for domain communication.
* private
* public
text: public

Enable or Disable Windows Firewall

Run the SYS Enable Windows Firewall and SYS Disable Windows Firewall operational scripts to enable or disable Windows firewall settings. Add the script to the Boot Scripts list to enable Windows Firewall by default at boot time when a new instance is launched.

Add or Remove Firewall Rule

Run the SYS Setup firewall rule script to creates or remove firewall rule based on the following inputs.

Input Name Description Example Values
FIREWALL_RULE_ACTION Specify whether you want to enable or disable network access by either creating or removing a firewall rule.
* Enable
* Disable
text: Enable
FIREWALL_RULE_PORT Number of port or comma-separated list of ports. Each port should be integer value in range 1..65535. Ex: 80 text: 80
FIREWALL_RULE_REMOTE_IP Specify the IP address or range of IP addresses in CIDR notation with optional inversion flag. This parameter could be of one of the formats below:
* Individual IP address in dot-decimal notation, ex: 10.10.1.32
* IP address followed by a slash and decimal number of leading bits in subnet mask. Ex: 92.16.2.0/24
* IP address followed by a slash and subnet mask in 4-dots decimal format. 92.16.2.0/255.255.255.0
* Any of the format above with preceding exclamation mark so the whole thing represents inverted set of IP addresses (i.e. all IPs except specified by IP address or range). Ex: !92.16.2.0/24
* 'Any' keyword, case insensitive.
text: 192.16.2.0/24
FIREWALL_RULE_PROTOCOL The protocol for the firewall rule. Supported protocols are TCP and UDP.
* tcp
* udp
* both
text: tcp

List all Firewall Rules

Run the SYS Dump firewall rules script to display a list of all firewall rules or only rules created by RightScripts (those which have 'RightScale' prefix in the name). This behavior is controlled by FIREWALL_RULE_DISPLAY_ALL input. If displaying only RightScale rules is selected the script also collects list of all IP addresses that are granted network access to the current instance to simplify a network security audit.

Input Name Description Example Values
FIREWALL_RULE_DISPLAY_ALL Specify whether you want to display a list of all firewall rules or only the rules created by RightScripts (those which have 'RightScale' prefix in the name).
* True
* False (default)
text: True

Configure the Windows Updates Policy

By default, no Windows updates are applied to a running server. However, you can set the following input and run the SYS Set Windows Automatic Updates Policy operational script to define how Windows automatic updates should be applied to an operational Windows server.

Input Name Description Example Values
WINDOWS_AUTOMATIC_UPDATES_POLICY Define the Windows automatic updates policy. Default is no auto updates.
* Disable automatic updates
Install updates automatically
Notify before download
* Notify before installation
text: Install updates automatically

Configure the Windows Reboot Policy

Some Windows updates require a server to be rebooted in order to complete the installation process. However, you might want to reboot the instance at a more convenient time in order to reduce the amount of site downtime or negative impact to your end users. In such cases, you can set the following input before running either the SYS Install All Windows Updates or SYS Install Microsoft update by KB number operational script to specify whether or not an instance is allowed to reboot after the script is run. Reboot is not allowed by default.

Input Name Description Example Values
WINDOWS_UPDATES_REBOOT_SETTING Defines whether or not the instance is rebooted after installing a Windows update.
* Do Not Allow Reboot
* Allow Reboot
text: Allow Reboot

Install Windows Updates

Run the SYS Install All Windows Updates operational script to download and install all new Windows updates. Any updates that require a user to accept a EULA are NOT installed. Use the WINDOWS_UPDATES_REBOOT_SETTING input to control whether or not the instance is rebooted after the updates are installed, if required.

Input Name Description Example Values
WINDOWS_UPDATES_REBOOT_SETTING Defines whether or not the instance is rebooted after installing a Windows update.
* Do Not Allow Reboot
* Allow Reboot
text: Allow Reboot

Install Microsoft Knowledge Base (KB) Article Updates

Set the following input and run the SYS Install Microsoft update by KB number operational script to install a specific KB update.

To apply the KB updates to the running server at boot time, add the script to the end of the Boot Scripts list.

Input Name Description Example Values
KB_ARTICLE_NUMBER Microsoft KB number of update to be installed. Use format KBxxxxxx (x is digit) or just xxxxxx to specify the number. You can either specify a single KB article number or a comma-separated list. text: 961402
WINDOWS_UPDATES_REBOOT_SETTING Defines whether or not the instance is rebooted after installing a Windows update.
* Do Not Allow Reboot
* Allow Reboot
text: Allow Reboot

Register with a Windows KMS Server

Set the following inputs and run the SYS Activate Windows with KMS server operational script to activate the instance's license with a Microsoft Key Management Server (KMS). Before you run the script, make sure that the KMS server's firewall settings are configured to accept a request from the instance.

Input Name Description Example Values
KMS_HOST Specify the FQDN or IP address of the KMS server with whom the instance will activate its license e.g., kms.mydomain.com text: kms.example.com
text: 10.567.333.45