|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, and disk.
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 dictated 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 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.
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" only saves the user databases and does not include system databases.
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 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 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).
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:
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.
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.
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:
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 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.
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 (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 - Overview.
© 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.