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

Database Manager for Microsoft SQL Server (v14 Infinity)

Table of Contents    
  1. Description
  2. Features
  3. Technical Overview
    1. Software Application Versions
    2. Database Server Modes
    3. Database Filesystem
      1. System Databases
      2. User Databases
    4. Security and Firewall Permissions
    5. Mirroring Initialization Method
      1. Certificate Exchange
      2. Database Transfer
        1. Snapshots
        2. Remote Storage
      3. Mirroring Session
    6. Backup and Storage
      1. Backup Method
      2. Backup Policy
        1. Volumes
        2. Remote Storage
    7. Setting up a SQL Database
    8. Microsoft SQL Server Product Key
    9. Windows Base
  4. See also

Infinity

Leading edge features
   ►  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, disk, and SQL server specific metrics.

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. Note: Rackspace Performance Cloud Servers are not supported.
  • 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.  Amazon S3, Rackspace Cloud Files, Windows Azure Storage, Softlayer Object Storage and OpenStack Swift are currently supported.
  • 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.
  • Support for Windows Management Framework 3.0
  • Support of the Mirroring Witness (Beta).
  • Support for cross-cloud and cross-region data replication.

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 3.5SP1
  • .Net 4.0
  • .Net 4.5
  • SQL Server 2008R2 SP2 or SQL Server 2012 SP1 (Standard and Enterprise Editions on Windows Azure cloud, Standard Edition only on other supported clouds)

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 defined 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 Microsoft 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 (usually D:\). 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" script only saves the user databases (selected by the DB_NAME input) 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 server (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 EC2, CloudStack, 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 to the Standalone/Principal database server using an 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-sqlapp_security_app-v1.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 to create a user, you can create that user using the DB SQLS Create Login script 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 (used to login and create the new user).
  • DB_REMOTE_SQL_PASSWORD - Password of the SQL Server user with database privileges. (DB_REMOTE_SQL_LOGIN)

Mirroring Initialization Method

If you are setting up a SQL Mirrored setup consisting of a Principal and Mirror SQL database server, you can specify how you want the Mirror to initialize with the Principal. The ServerTemplate supports two different initialization methods as specified by the INIT_MIRRORING_METHOD input. However, if the cloud provider does not support the use of volumes and snapshots, you must use the remote storage option. It's recommended that you launch your principal and mirror servers in different availability zones for high availability and disaster recovery reasons.

The SQL mirroring procedure is a three-step process:

  1. Certificate Exchange - Create credentials for configuring a SQL mirroring session using an exchange of certificates.
  2. Database Transfer - In order to minimize the amount of time needed to establish an in-sync mirroring session between the principal and mirror database servers, a backup is transfered from the principal to the mirror by either using snapshots (if supported) or an ROS container.
    • Snapshots - A snapshot is used to transport the database backup file between the principal and mirror servers to establish an authenticated SQL mirroring session. The snapshot is used to create a volume in the same availability zone as the mirror server. Once the created volume (from the snapshot) has delivered the database file it is unmounted, detached, and deleted (as well as the snapshot).
    • Remote Storage - An object storage container is used to store 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 cloud/region for high availability purposes, you must use the Remote Storage option since volumes and snapshots are cloud specific.
  3. Mirroring Session - A mirroring session between the principal and mirror database servers is established by the 'DB SQLS Start mirroring' script, which uses credentials (Design > Credentials) instead of exchanging server certificates for authentication purposes.

Certificate Exchange

To establish a mirroring session you must use credentials that contain an encoded certificate and private key. Each server in a mirroring session requires a unique credential. For example, if you add a Witness server to a principal and mirror setup, you'll need three unique credentials.

 

Creating of certificates is possible using the PRIVATE_KEY_PASSWORD input on any Microsoft SQL Server or Microsoft SQL Witness Server along with the DB SQLS Generate and Save a Certificate script. Define the PRIVATE_KEY_PASSWORD input and run the script, which will create a certificate file on the local instance disk, after which the contents of that certificate can be copied into Rightscale Credentials for use in the PRINCIPAL_CERTIFICATE, MIRROR_CERTIFICATE and WITNESS_CERTIFICATE inputs later on. The PRINCIPAL/MIRROR/WITNESS_PRIVATE_KEY_PASSWORD inputs are then used to specify the password that was used to generate the certificate initially to decode and use the certificate.

Warning! The password chosen must meet Microsoft strong password guidelines, so it should include a combination of upper and lower case alphanumeric characters along with optional special characters if possible.

 

The Database Manager for Microsoft SQL Server - Tutorial and Database Manager for Microsoft SQL Server - Runbook offer more details on configuring the principal and mirror's certificates.

 

Database Transfer

In order to expedite the setup process of a mirroring session between a principal and mirror database server, a backup of the database is transfered from the principal to the mirror by using either a snapshot or ROS location. By default, the INIT_MIRRORING_METHOD input is set to use "Snapshots" for transfering a backup of the database. However, if the chosen cloud does not support the use of volumes and snapshots, you must use the "Remote Storage" option, which saves a backup of the database to a specified ROS container (REMOTE_STORAGE_CONTAINER). 

Snapshots

If you choose to use snapshots for initializing the Mirror with the Principal (INIT_MIRRORING_METHOD = Snapshots), a snapshot will be used to transfer the certificate for initializating authentication. See the diagrams below for a more technical explanation. 

  1. Launch a SQL database server in "Standalone" mode (SERVER_MODE = Standalone).
    screen-Windows_Init-1.png
     
  2. Once the server is operational, execute the "DB SQLS Init principal" operational script to change its server mode from Standalone to Principal. A new volume is created and attached to the server. A backup (.bak) of the SQL data storage drive (e.g., D:\) is created and stored on the new volume (e.g., F:\). Once the backup file is loaded, the volume is detached from the server and a volume snapshot is created. 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-3a.png

    Once the snapshot is created, the volume is unmounted, detached, and deleted.
    screen-Windows_Init-3b.png
     
  3. Launch a new database server, which will become a mirror of the running principal server. Since snapshots are not zone-specific, you can launch the Mirror server in a different availability zone as the Principal server, but it must be in the same region/cloud. If you set the SERVER_MODE input to 'Mirror' when you launch the server, it will become a mirror of the principal server at boot time by automatically running the DB SQLS Start Mirroring boot script, which in turn runs the DB SQLS Init Mirror script. Scripts are executed that will create a volume (from the snapshot) into the appropriate availability zone and attach it to the mirror database server. Next, the SQL backup is loaded onto the Mirror server from this newly attached backup volume. 
    screen-Windows_Init-3c.png
     
  4. The volume is unmounted, detached, and deleted. The snapshot is also deleted. 
    screen-Windows_Init-6.png    
     
  5. A recent backup of the principal database server is now loaded onto the mirror server so that it can quickly become in-sync with the principal when an authenticated mirroring session is established by the DB SQLS Start mirroring script. The following depicts the tags used by the Principal and Mirror servers indicating mirrored status along with other details:
    screen-Windows_Init-9.png

 

    Tags:

  • rs_mssql_ha:private_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, Witness), which is defined by the SERVER_MODE input.
  • rs_mssql_ha:status=<status> - Denotes the status of the mirroring session. This tag changes accordingly depending on its current state. Valid states are displayed below. When the mirroring session is established, the principal and mirror servers will be in a "synchronized" state (highlighted in green below).

    • Null Notification

    • Connection with Principal Lost

    • Connection with Mirror Lost

    • Manual Failover

    • Automatic Failover

    • Mirroring Suspended

    • No Quorum

    • Synchronizing Mirror

    • Principal Running Exposed

    • Synchronizing Principal

    • Synchronized Principal with Witness

    • Synchronized Principal without Witness

    • Synchronized Mirror with Witness

    • Synchronized Mirror without Witness

Remote Storage

If the cloud provider does not support volumes and snapshots, you can use an ROS container to store the database backup file. The same container that is used for storing continuous database backups to object storage is used for this initial step.

Set the following inputs:

  • REMOTE_STORAGE_ACCOUNT_ID
  • REMOTE_STORAGE_ACCOUNT_PROVIDER
  • REMOTE_STORAGE_ACCOUNT_SECRET
  • REMOTE_STORAGE_CONTAINER
  • REMOTE_STORAGE_ENDPOINT_URL  (Required for Swift-based storage)

Mirroring Session

The DB SQLS Start mirroring script uses a set of credentials (instead of self-signed server certificates) to authenticate a mirroring session between a principal and mirror database server. The values for the user-generated credentials for the master, mirror, or witness server are created by running the DB SQLS Generate and Save a Certificate script, which generates a Base64-encoded SQL Server certificate and private key each time the script is run. It's recommended that you generate unique credentials for each server. See the Database Manager for Microsoft SQL Server (v14 Infinity) - Tutorial or Database Manager for Microsoft SQL Server (v14 Infinity) - Runbook for more information. 

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 S3 - Amazon Simple Storage Service 
  • Rackspace Cloud Files - Rackspace Cloud Files (US, UK)
  • Windows Azure Storage - Blob Storage
  • SoftLayer Object Storage - Remote Object Storage (Swift)
  • OpenStack Swift Object Storage - 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 is used and no 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 (.mdf) - Use the 'DB SQLS Attach database from local disk / Remote Storage' operational script and MDF_FILE_NAME input.
  • SQL Database File (.bak) - 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 (using a full path, 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)

See also

You must to post a comment.
Last modified
13:15, 23 Jan 2015

Tags

Classifications

This page has no classifications.

Announcements

None

Powered by MindTouch ®

© 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.