Note: Please go to docs.rightscale.com to access the current RightScale documentation set. Also, feel free to Chat with us!
Home > ServerTemplates > v12.11 LTS > ST > Database Manager for Microsoft SQL Server (v12.11 LTS)

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

 

 


Table of Contents    

Long Term Support

icon-lts-v1.png

Stable, tested ServerTemplate assets

     ►  Overview

Description

Configures a Microsoft SQL Server stand-alone database server or a pair of servers in a high-availability (HA) configuration (synchronous mirroring). The template supports graceful (no data loss) and forceful (possible data loss) manual failover, with the latter to be used for disaster-recovery situations. Also included are built-in monitoring and alerts on CPU, memory, and disk.

Features

  • Multi-cloud Support - Launch a server into one several supported cloud infrastructures. Check the ServerTemplate's Images tab to see which MultiCloud Images (MCIs) are included by default. However, additional MCIs can be added to the ServerTemplate, if desired.
  • Configures a Microsoft SQL database server to function in either Standalone, Principal, or Mirror mode.
  • Supports database storage on either attachable volumes (on clouds with volume support) or instance-based ephemeral storage.
  • Support for single or striped volumes (on clouds with volume support). Striped volumes can provide faster database performance and snapshot backups.
  • Create regular backups of the database as either snapshots (if using volumes) or SQL backup (.bak) files stored in a Remote Object Storage (ROS) container such as Amazon S3 or Rackspace Cloud Files. Currently, only Cloud Files (US) is supported. For Cloud Files (UK) support, use the v13 Infinity relelase.
  • Support for continuous backups.
  • Includes operational scripts for performing common system administrator tasks including disaster recovery scenarios.
  • Support for multiple DNS providers: DNS Made Easy, DynDNS, AWS Route53, and Rackspace Cloud DNS
  • Pre-configured alerts for monitoring common system metrics.

Technical Overview

Software Application Versions

The following applications are included in the image (and are not installed using boot scripts like other lightweight applications) in order to reduce boot times.

  • .Net 4.0
  • SQL Server 2008 (available on Windows 2008 image)
  • SQL Server 2008R2 (on Windows 2008R2 image)

Database Server Modes

The same ServerTemplate can be used to create a Microsoft SQL server that will operate in one of three modes, which is dictated by the SERVER_MODE input.

  • Standalone
  • Principal
  • Mirror


The Principal and Mirror modes are used to create a synchronous database tier for setting up high-availability architectures. Typically, you will launch a new database server in Standalone mode and then run an operational script that will change its server mode to function as either a Principal or Mirror.
Note: You should only set up a Mirror server if you have a Principal server that's already operational.

Database Filesystem

For Microsof SQL servers, the root filesystem (C:\) is located on a mounted volume (if volumes are supported). When the DB SQLS Setup volumes and services boot script is run, another filesystem is created for the database and related logs. By default, the data volume is assigned to the next available drive letter. However, you can use the OPT_DATA_VOLUME_LETTER input to assign a different letter as long as it's supported and available for use. Similarly, you can use the OPT_LOGS_VOLUME_LETTER input to assign the volume that stores the log files to a specific drive letter. 

screen-volumes-v1.png

When the data and log volumes are created and assigned to the instance, you can use the NUMBER_STRIPES input to control whether or not you want to use a stripe of volumes for the data and logs drives. By default, a single volume is used for both data and logs, as shown above. (e.g. NUMBER_STRIPES = 1) The NUMBER_STRIPES input applies to both data and log drives; you cannot specify a different stripe count for data than logs.

Use the following inputs to control the size of the data and logs storage block devices (in gigabytes).

  • DATA_VOLUME_SIZE = 10 (default)
  • LOGS_VOLUME_SIZE = 1 (default)


In Windows Explorer, you can see the location of the system and user databases in the data (D:\) and log (E:\) drives.

screen-Windows_Explorer-v1.png

System Databases

If you launch the Microsoft SQL database server in a cloud that supports volumes, the internal SQL data (such as users, list of tables, fields, temporary objects, etc.) located in the following four system databases will be moved from the C:\ drive to the D:\ drive (data volume) so that they will be included in database snapshot backups. After the databases are moved, SQL is reconfigured to retrieve the data from the new location.

  • master
  • model
  • msdb
  • tempdb

User Databases

The "Backup ALL databases" script backs up system and user databases, whereas the "backup database" only saves the user databases and does not include system databases.

Security and Firewall Permissions

The way in which you access your SQL database server depends on your cloud provider. 

Before an application (e.g. IIS) can perform an action on a SQL database (e.g. create a new record), the servers must first be granted access at the network-level before it can successfully make an application-level request. Most cloud infrastructures are unique and may have different ways of controlling egress and ingress communication with the instances launched in their clouds. For Amazon EC2CloudStack, and other clouds that support security groups, you must use security group(s) with the appropriate permissions set. See Deployment Prerequisites (Windows).

A Standalone or Principal database server must allow SQL server database connections from the IIS application servers. The DB SQLS Setup volumes and services boot script configures the database server to listen for SQL database requests on TCP port 1433, which is the default SQL port. 

A Principal database server must also allow SQL server mirroring connections from a Mirror database server. Use the OPT_MIRRORING_PORT input to control which port is used for mirroring data between a Principal and Mirror database server. By default, the input is set to TCP port 5022.

diag-sqldb_networklevel_security-v2.png

Once the database server has updated its permissions to allow access from  application servers over port 1433, the application will be able to connect the Standalone/Principal database server using a FQDN, which points to the private IP address of the database server. The FQDN of the Standalone/Principal database server is defined by the DNS_DOMAIN_NAME input (e.g. db-principal.example.com).
 

diag-sqldb_applevel_security-v2.png

IIS application servers associated with the same deployment that contains the Standalone/Principal database server will connect to the SQL database (specified by the DB_NAME input) as a SQL user (identified by the DB_NEW_LOGIN_NAME and DB_NEW_LOGIN_PASSWORD inputs) with database privileges. Use the DB SQLS Create login script to create a SQL user that the application servers will use to connect to the database.

If there is an additional server that exists outside of the deployment and/or related server array that needs to connect to the SQL database server, you can configure the database server accordingly using the following inputs:

  • DB_REMOTE_SERVER_IP - The IP address of the remote SQL database server.
  • DB_REMOTE_SQL_LOGIN - Username of a SQL Server user with database privileges.
  • DB_REMOTE_SQL_PASSWORD - Password of the SQL Server user with database privileges.

Mirroring Initialization Method

If you are setting up a SQL Mirrored setup consisting of a Principal and Mirror database server, you can specify how you want the Mirror to initialize with the Principal. The ServerTemplate supports two different methods. However, if the cloud provider does not support the use of volumes, you must use the remote storage option.

  • Volumes - Volumes are used for handling the exchange of certificates and backups between the Principal and Mirror servers to establish an authenticated SQL mirroring session. Both the Principal and Mirror servers must be launched into the same availability zone because volumes are zone-specific.
  • Remote Storage - An object storage container is used to store certificates and backups for establishing an authenticated SQL mirroring session between a Principal and Mirror server. If you are setting up a Principal-Mirror setup where the Mirror is launched into a different availability zone or cloud/region for high availability purposes, you must use the Remote Storage option since volumes can only be attached to instances in the same zone.

Volumes

If you choose to use volumes for initializing the Mirror with the Principal (INIT_MIRRORING_METHOD = volumes), a volume will be used to transfer the certificate for initialization authentication. See the outlined steps and diagrams below for details. 

  1. Launch a SQL database server in "Standalone" mode (SERVER_MODE = Standalone).
    screen-Windows_Init-1.png
     
  2. Once the server is operational, the "DB SQLS Init principal" operational script is executed to change its server mode from Standalone to Principal. A new volume is created and attached to the server. Two files are created and stored on the volume: the Principal's self-signed certificate and a new SQL backup (.bak) of the data storage drive (e.g. D:\). Once the files are loaded, the volume is detached from the server. Note: The script will not be listed as "completed" in the audit entry until several steps later when the mirroring initialization process is complete. 
    screen-Windows_Init-2.png     screen-Windows_Init-3.png
     
  3. Launch a new database server, which will become a Mirror of the Principal. Since volumes are zone-specific, you must launch the Mirror server in the same availability zone as the Principal server. Once the server is operational, the "DB SQLS Init mirror" operational script is executed to change its mode from Standalone to Mirror. Next, the previously detached volume (from the Principal) is attached to the Mirror database server. The Principal's certificate and the SQL backup are loaded onto the Mirror server. Next, a self-signed certificate is created for the Mirror database server and loaded onto the exchange volume. 
    screen-Windows_Init-4.png     screen-Windows_Init-5.png
     
  4. The volume is detached from the Mirror server and reattached to the Principal server where it loads the Mirror's self-signed certificate.  
     screen-Windows_Init-6.png    screen-Windows_Init-7.png
     
  5. Once the certificate is loaded, the exchange volume is detached and deleted because it is no longer needed.
    screen-Windows_Init-8.png
     
  6. The SQL database uses the high availability ('ha') machine tags and the self-signed certificates to authenticate a mirroring session between the Principal and Mirror servers. 
    screen-Windows_Init-9.png
    • rs_mssql_ha:ip=<private_ip> - Denotes the server's Private IP address, which is used for mirroring data between the Principal and Mirror servers.
    • rs_mssql_ha:name=<db_lineage_name> - Denotes the database lineage name, which is defined by the DB_LINEAGE_NAME input.
    • rs_mssql_ha:role=<mode> - Denotes the server's mode (Principal, Mirror, Standalone), which is defined by the (SERVER_MODE) input.
    • rs_mssql_ha:status=<status> - Denotes the status of mirroring (Ready or Connected). A Principal server's status remains in the 'ready' mode until the Mirror server initializes with the Principal.

Remote Storage

If the cloud provider does not support volumes or if you want to use an object storage container to temporarily store the certificates for authentication purposes instead of using volumes, you can use remote storage for your mirroring initialization method. (INIT_MIRRORING_METHOD = Remote Storage) The same initialization process is used except instead of using a volume as the carrier for the self-signed certificate and identification file, you can use an object storage container instead.

Note: The same container that is used for storing backups to object storage is used for the initialization routine.

Set the following inputs:

  • REMOTE_STORAGE_ACCOUNT_ID
  • REMOTE_STORAGE_ACCOUNT_PROVIDER
  • REMOTE_STORAGE_ACCOUNT_SECRET
  • REMOTE_STORAGE_CONTAINER

Backup and Storage

Backup Method

The Database Manager for SQL ServerTemplates published by RightScale support two types of backups. Use the BACKUP_METHOD input to select your method for taking backups.

  • Snapshots
  • Remote Storage


It's recommended that you use Snapshots for backups, if available. If volumes and snapshots are not supported by the cloud provider, you must use Remote Storage for your backup method. 

The following remote object storage providers are supported:

  • Amazon EC2 - Amazon EBS volumes (single or striped volumes)
  • Rackspace Cloud Files - Rackspace Cloud Files (US)  (Note: Cloud Files UK is not supported)
  • Windows Azure Storage - Volumes
  • SoftLayer Object Storage Dallas - Remote Object Storage (Swift)
  • SoftLayer Object Storage Singapore - Remote Object Storage (Swift)
  • SoftLayer Object Storage Amsterdam - Remote Object Storage (Swift)


VSS is used to take full or incremental backups.

  1. VSS is enabled to freeze the filesystem to prevent any changes to the user databases during a backup.
  2. An API call takes a backup of the data and logs. (Related volume IDs are passed, if applicable.)
  3. Filesystem is unfrozen to resume normal behavior.

Backup Policy

The SQL database server supports continuous backups as a scheduled task. By default, continuous backups are enabled at launch time by the 'DB SQLS Configure scheduled tasks' boot script, which creates a scheduled task to regularly execute the 'DB SQLS Backup Data and Log volumes' RightScript to take periodic backups. By default, backups are taken every 4 hours by default. (DB_BACKUP_FREQUENCY = 4) The backup policy is different depending on whether or not you are using volumes, which is denoted by the BACKUP_METHOD input.

Volumes

If you are using volumes, snapshot backups are taken, which are essentially snapshots of the volumes at a point in time. No compression or backup (.bak) file is created. Snapshots are tagged appropriately so that they can be used for keeping an archive of your databases that may be used at a later time for database restorations.

Use the following inputs to control the frequency of backups. By default, only 60 snapshots are preserved. (DB_BACKUP_KEEP_LAST = 60) See Archiving of EBS Snapshots for more details.

Note: These inputs only apply if you are taking snapshot backups. They are currently ignored if you are using ROS as the backup method.

  • DB_BACKUP_KEEP_DAILY
  • DB_BACKUP_KEEP_LAST
  • DB_BACKUP_KEEP_MONTHLY
  • DB_BACKUP_KEEP_WEEKLY
  • DB_BACKUP_KEEP_YEARLY
Remote Storage

If you are using ROS instead of volumes, incremental SQL backups (.bak) are taken every 4 hours (default) and a full backup is taken every day. (DB_FULL_BACKUP_INTERVAL = 1)  Note: A value of 2 would result in a full backup every 2 days, not twice a day.

Setting up a SQL Database

If you are setting up a Microsoft SQL database server for the first time, you can either initialize the database using a backup (.bak) or database (.mdf) file stored in an object storage container. 

  • SQL Backup File (.bak) - Use the 'DB SQLS Attach database from local disk / Remote Storage' operational script and MDF_FILE_NAME input.
  • SQL Database File (.mdf) - Use the 'DB SQLS Restore database from local disk / Remote Storage' operational script and BACKUP_FILE_NAME input.

 

By default, the SQL backup file is downloaded from the specified ROS location and saved to the root directory of the data drive (D:\). Use the OPT_BACKUP_TEMP_DIR input to save the file to a different location such as a specific location on the data drive (e.g. D:\tmp\) or a different drive (e.g. G:\).

Microsoft SQL Server Product Key

By default, a trial license is used to launch a SQL database server. However, if you want to apply your own product key, you can run the 'DB SQLS Update SQL Server 2008R2 Product Key' script and MSSQL_PRODUCT_KEY input to apply your own product key. For security reasons, it's strongly recommended that you create a credential for the product key.

Windows Base

To learn more about the following topics that apply to all Windows-based ServerTemplates published by RightScale, please see Base ServerTemplate for Windows - Overview

  • Windows Firewall
  • Windows Automatic Updates
  • Knowledge Base (KB) Articles 
  • Microsoft Key Management Server (KMS)
You must to post a comment.
Last modified
00:32, 17 May 2013

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.