Once you've successfully set up your Microsoft SQL environment on a Windows Server in the cloud using the Database Manager for Microsoft SQL Server (v13.5 LTS) - Tutorial you may need to perform the following common Microsoft SQL runbook operations.
Note: The following instructions apply to the Database Manager for Microsoft SQL Server (v13.x) ServerTemplate.
DATABASE
Input Name | Description | Example Values |
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:
| 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'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 |
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
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.
Important! VSS does not guarantee consistency of log files.
To perform this operation, follow the steps below:
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.
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 |
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.
Note: It's recommended that you set up credentials for password values and any other sensitive data as shown in the examples below.
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 |
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.
In the event that a principal server becomes unreachable, a new mirror server can be set up to prevent data loss.
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.
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.
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:
To perform this operation, follow the steps below:
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 |
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.
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.
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.
Previously, if you created a SQL backup snapshot using a ServerTemplate before the v13.5 LTS version, a random password was used to create the master key for the database. If you restored the database from an old snapshot or ROS backup which already contains a master key encrypted with unknown password, you can use this script to delete the existing master key of the master database and create a new one encrypted by the provided password, as specified by the MASTER_KEY_PASSWORD input.
Warning! You should only run this script if you no longer want the existing master key to be used. Services that are currently using the master key to access the database will be disrupted when this script is executed.
As a safety precaution, you must turn off the safety mechanism by overriding the MASTER_KEY_SAFETY input and setting it to 'Text: off'.
Use this script to delete existing master key of master database and create new one encrypted by provided password. This could be useful after restoring from snapshot or ROS backup which already contains a master key encrypted with unknown password. Note that before deleting existing master key all dependent objects (e.g. mirroring endpoints and certificates) are also deleted. The script requires the MASTER_KEY_SAFETY parameter to be set to "off" to avoid accidental deletion of existing master key.
To perform this operation, follow the steps below:
Input Name | Description | Example Values |
MASTER_KEY_PASSWORD | Specify a password for encrypting the master database master key. This password is used to encrypt master key when it's created or decrypt when opening existing master key. It's strongly recommended that you use a credential (Design > Credentials) to hide the value of this input. (e.g. cred:MY_MASTER_KEY_PASSWORD) | Cred:MY_MASTER_KEY_PASSWORD |
MASTER_KEY_SAFETY | Prevents the accidental running of the "DB SQLS Recreate master key" script. This script will only run if the input is set to "off". | Text:off |
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:
rs_backup:lineage=mydatabase
rs_backup:timestamp=1347359186
SQLS_BAK_mydatabase_full_20120726911410.ini
Important! Use the values in bold for the DB_LINEAGE_NAME and OPT_DB_RESTORE_TIMESTAMP inputs. Do not include the full 'tag' in the input value!
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.
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:
© 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.