Database Manager for Microsoft SQL Server (v12.11 LTS) - Runbook

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. The SQL server will continue to operate normally during this entire process and does not even know that its volumes were snapshot. Additionally, this script can create a differential or full SQL Server backup of all databases and upload the backup to a Remote Object Storage. 

Note: VSS does not guarantee consistency of log files.

To perform this operation, follow the steps below:

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

CLOUD

DATABASE

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.

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.

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.

Note: It's recommended that you set up credentials for password values and any other sensitive data as shown in the examples below.

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.

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. Relaunch the old principal server and set SERVER_MODE to "Mirror." 
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 increated, 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 script successfully executed on the principal and mirror servers.

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.
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 script successfully executed on the principal and mirror servers.

Restore from the latest backup

If you've previously completed a backup of your MS SQL database, you can restore a standalone or synchronized pair from the latest backup.

1. Navigate to the Inputs tab for the server you'd 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. 
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 S3 / Cloud Files
   • DB SQLS Restore database from disk / S3 / Cloud Files -This is primarily used for 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 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

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 MS SQL data volume (d:\) should be retained for archiving purposes.  The backup and cleanup operations are performed by “DB SQLS backup data volume” script which is automatically scheduled for execution every 4 hours on a running server.  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 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 MS SQL database volume (e.g. D:\) according to Microsoft recommendations (http://msdn.microsoft.com/en-us/library/ms175527.aspx). The script accomplishes the following tasks:

• Sets recovery model to simple
• Creates as many data files of the same size as number of CPU cores on the system.  The default size is 1GB.  (default size is 1 GB, could be overridden using 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 MS SQL Server.
2. Run the DB SQLS Configure tempdb Operational Script.

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 set to "off" (default), 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's 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:
   • Stops SQL Server
   • Takes EBS Snapshot of the EBS Volume (d:\).  The Snapshot will receive a tag based on the DB_LINEAGE_NAME input
   • Un-mounts and detaches EBS Volume (d:\) from the Server
   • Deletes the EBS Volume
5. Once the script has been successfully completed, it's now safe to terminate the Server.  Click the Server's Terminate action button.