|Table of Contents|
| || |
Long Term Support
Stable, tested ServerTemplate assets
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.
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.
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.
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.
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.
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).
In Windows Explorer, you can see the location of the system and user databases in the data (D:\) and log (E:\) drives.
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.
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.
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.
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).
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:
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:
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 (v13.5 LTS) - Tutorial and Database Manager for Microsoft SQL Server (v13.5 LTS) - Runbook offer more details on configuring the principal and mirror's certificates.
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).
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.
Connection with Principal Lost
Connection with Mirror Lost
Principal Running Exposed
Synchronized Principal with Witness
Synchronized Principal without Witness
Synchronized Mirror with Witness
Synchronized Mirror without Witness
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:
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 (v13.5 LTS) - Tutorial and Database Manager for Microsoft SQL Server (v13.5 LTS) - Runbook for more information.
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.
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:
VSS is used to take full or incremental backups.
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.
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.
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.
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.
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:\).
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.
To learn more about the following topics that apply to all Windows-based ServerTemplates published by RightScale, please see Base ServerTemplate for Windows (v13.5 LTS).
© 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.