Amazon RDS for MSSQL

This topic gives you reference information about the Amazon RDS for MSSQL (csb-aws-mssql) service. It details the plans, configuration parameters, and binding credentials.

Important
For secure connections to work, install the AWS certificate bundle in Ops Manager.

Plan Configuration Parameters

When configuring Cloud Service Broker for AWS, you can add additional plans. For how to configure plans, see Configure services with Cloud Service Broker for AWS.

The following table lists parameters that can only be configured for additional plans:

Parameter Name	Description	Default	Required
`name`	The plan name	n/a	Yes
`id`	A unique GUID	n/a	Yes
`description`	Description of the new plan	n/a	Yes
`free`	When false, service instances of this service plan have a cost	true	No
`bindable`	Specifies whether service instances of the service plan can bind to applications	true	No
`plan_updateable`	Whether the plan supports upgrading, downgrading, or sidegrading to another version	true	No
`metadata.displayName`	Name to use when displaying the plan in Marketplace	n/a	No
`metadata.bullets`	List of bullet points to display in Apps Manager	n/a	No

You can also add any of the parameters listed in the Configuration Parameters section to your plan.

Note
If you set a parameter at plan level, developers cannot change the value when creating or updating service instances.

Configuration Parameters

You can provision a service by running:

cf create-service csb-aws-mssql PLAN-NAME SERVICE-INSTANCE-NAME -c '{"PARAMETER-NAME": "PARAMETER-VALUE"}

You can update the configuration parameters for a service instance by running:

cf update-service SERVICE-INSTANCE-NAME -c '{"PARAMETER-NAME": "PARAMETER-VALUE"}'

The following table lists the parameters that you can configure, by using the -c flag, when provisioning or updating a csb-aws-mssql service. The Operation column displays whether a parameter is supported for both provision and update, or for provision only:

Parameter Name	Type	Description	Default	Operation
`instance_name`	String	The name of the AWS instance to create.	`csb-mssql-INSTANCE-ID`	provision
`db_name`	String	The name of the database.	`vsbdb`	provision
`region`	String	The AWS region to deploy the service in. For more information about available regions, see the AWS Documentation.	`us-west-2`	provision
`mssql_version`	String	(Required) The version for the MSSQL instance. It can be any version supported by the provider. For more information about Microsoft SQL versions on Amazon RDS, see the AWS Documentation.	None	provision and update
`engine`	String	(Required) The edition for the MSSQL instance. Amazon RDS supports the following Microsoft SQL Server editions: Enterprise: `sqlserver-ee` Express: `sqlserver-ex` Standard: `sqlserver-se` Web: `sqlserver-web`	None	provision
`storage_gb`	Integer	(Required) Size of storage volume for service instance. Minimum admitted value: 20 GB. For more information about Amazon RDS DB instance storage for MSSQL, see the AWS Documentation.	None	provision and update
`max_allocated_storage`	Integer	Upper limit in gibibytes to which Amazon RDS can automatically scale the storage of the DB instance. Must be greater than or equal to `storage_gb`. Set it to `null` or `0` to deactivate storage autoscaling. For more information, see Managing capacity automatically with Amazon RDS storage autoscaling.	None	provision and update
`instance_class`	String	(Required) The database instance class determines the computation and memory capacity of an RDS MSSQL database instance. Accepted values depend on the selected `engine` and `mssql_version`. For information about the restrictions associated with the different types of instances that RDS MSSQL accepts, see the AWS Documentation.	None	provision and update
`option_group_name`	String	Name of the DB option group to associate. MSSQL offers additional features such as SQL Server Audit, Transparent Data Encryption, etc. Setting `option_group_name: ""` during an update is ignored. If you want to replace the current option group with the default one, you need to explicitly specify its name. For more information, see Amazon RDS options for the Microsoft SQL Server database engine in the AWS documentation.	None	provision and update
`parameter_group_name`	String	Name of the custom parameter group to associate with this instance. If left unset, a new parameter group is created automatically with containment enabled. When specifying a custom parameter group, the custom parameter group must have `contained database authentication` set to `1`. Setting `parameter_group_name: ""` during an update is ignored. If you want to replace the current parameter group with the default one, you need to explicitly specify its name. For more information, see Amazon RDS Working with parameter groups.	None	provision and update
`monitoring_interval`	Number	The interval, in seconds, between points when Enhanced Monitoring metrics are collected for the database instance. To stop collecting Enhanced Monitoring metrics, enter 0. Valid values: 0, 1, 5, 10, 15, 30, 60. A `monitoring_role_arn` value is required if you enter a `monitoring_interval` value other than 0.	`0`	provision and update
`monitoring_role_arn`	String	Enhanced Monitoring requires permission to act on your behalf to send OS metric information to CloudWatch Logs. This property represents the Amazon Resource Name (ARN) for the IAM role that permits RDS to send enhanced monitoring metrics to CloudWatch Logs.	`""`	provision and update
`storage_type`	String	Type of storage to be used. One of `standard` (magnetic), `gp2` (general purpose SSD), `gp3` (general purpose SSD), or `io1` (provisioned IOPS SSD). For more information check Amazon RDS storage types.	`io1`	provision and update
`iops`	Integer	The amount of provisioned IOPS. For this property to take effect, `storage_type` must be set to `gp3` or `io1`. For more information on customizing IOPS with gp3, see General Purpose SSD storage. For more information on customizing IOPS with io1, see Provisioned IOPS storage.	`1000`	provision and update
`publicly_accessible`	Boolean	If `true`, make instance available to public connections. Note that setting `publicly_accessible: true` is not sufficient for making the DB instance public. For more information, see Unreachable publicly accessible database.	`false`	provision and update
`aws_vpc_id`	String	The Virtual Private Cloud (VPC) to connect the instance to.	The default VPC	provision and update
`rds_subnet_group`	String	The name of the subnet to attach the database instance to. When set, you must also specify the corresponding `aws_vpc_id`. When both `rds_subnet_group` and `rds_vpc_security_group_ids` are provided, they must be coherent and depend on the same `aws_vpc_id`.	None	provision and update
`rds_vpc_security_group_ids`	Comma-separated String	Security group IDs to assign to the database instance. When set, you must also specify the corresponding `aws_vpc_id`. When both `rds_subnet_group` and `rds_vpc_security_group_ids` are provided, they must be coherent and depend on the same `aws_vpc_id`.	None	provision
`deletion_protection`	Boolean	Whether deletion protection is enabled. The database cannot be deleted when this value is set to `true`.	`false`	provision and update
`aws_access_key_id`	String	The AWS Access Key to use for an instance.	The value the operator entered for AWS Access Key in Ops Manager.	provision and update
`aws_secret_access_key`	String	The corresponding secret for the AWS Access Key to use for an instance.	The value the operator entered for AWS Secret Access Key in Ops Manager.	provision and update
`storage_encrypted`	Boolean	Specifies whether a database instance is encrypted. For more details on encrypting a database instance, see the AWS Documentation. Do not confuse this with Transparent Data Encryption (TDE).	`true`	provision
`kms_key_id`	String	The ARN for the user-managed Key Management Service (KMS) encryption key. When setting this value, `storage_encrypted` must be enabled. When not set, the AWS managed key is used for encrypting the database.	`""`	provision
`backup_retention_period`	Number	The number of days for which automatic backups are kept. Set the value to `0` to deactivate automated backups. An outage occurs if you change the backup retention period from `0` to a nonzero value or the reverse. For more information, see Amazon RDS Working with backups in the AWS documentation.	`7`	provision and update
`backup_window`	String	The daily time range in UTC during which automated backups are created. For example, `09:46-10:16`. It must not overlap with the maintenance window. Set to `null` to revert to default. For more information, see the AWS Documentation.	Uses AWS default value for the region	provision and update
`maintenance_day`	String	The preferred maintenance day: `Sun`, `Mon`, `Tue`, `Wed`, `Thu`, `Fri`, or `Sat`. It can only be updated to a non-null value.	Uses AWS default value for the region if all `maintenance_*` properties are null when creating the instance	provision and update
`maintenance_start_hour`	String	The preferred maintenance start hour. Valid values are `00` through `23`. It can only be updated to a non-null value.	Uses AWS default value for the region if all `maintenance_*` properties are null when creating the instance	provision and update
`maintenance_start_min`	String	The preferred maintenance start minute. Valid values are `00`, `15`, `30`, `45`. It can only be updated to a non-null value.	Uses AWS default value for the region if all `maintenance_*` properties are null when creating the instance	provision and update
`maintenance_end_hour`	String	The preferred maintenance end hour. Valid values are `00` through `23`. It can only be updated to a non-null value.	Uses AWS default value for the region if all `maintenance_*` properties are null when creating the instance	provision and update
`maintenance_end_min`	String	The preferred maintenance end minute. Valid values are `00`, `15`, `30`, `45`. It can only be updated to a non-null value.	Uses AWS default value for the region if all `maintenance_*` properties are null when creating the instance	provision and update
`delete_automated_backups`	Boolean	Specifies whether to remove automated backups immediately after the database instance is deleted	`true`	provision and update
`copy_tags_to_snapshot`	Boolean	Copy all instance tags to snapshots	`true`	provision and update
`allow_major_version_upgrade`	Boolean	Indicates that major version upgrades are allowed. Changing this parameter does not result in an outage, and the change is asynchronously applied as soon as possible.	`true`	provision and update
`auto_minor_version_upgrade`	Boolean	Indicates that minor engine upgrades are applied automatically to the database instance during the maintenance window. If `auto_minor_version_upgrade` is enabled, you must specify a major engine version. AWS command to see the available engine versions for MSSQL Standard Edition: `aws rds describe-db-engine-versions --engine sqlserver-se --include-all --region us-west-2` See the `engine` property for available engines.	`true`	provision and update
`performance_insights_enabled`	Boolean	Specifies whether Performance Insights are enabled. Performance Insights expand on existing Amazon RDS monitoring features to illustrate your database performance and help you analyze it. From the Performance Insights dashboard, you can visualize the database load on your Amazon RDS database instance load and filter the load by wait types, SQL statements, hosts, or users.	`false`	provision and update
`performance_insights_kms_key_id`	String	The ARN for the KMS key to encrypt Performance Insights data. If you set `performance_insights_kms_key_id`, you must also set `performance_insights_enabled` to `true`. After the KMS key is set, it can never be changed.	`""`	provision and update
`performance_insights_retention_period`	Number	The number of days to retain Performance Insights data. The value must be `7`, `NUMBER-OF-MONTHS` * 31 (where `NUMBER-OF-MONTHS` is `1`-`23`), or `731`. For example, the following values are valid: `93` (3 months * 31) `341` (11 months * 31) `589` (19 months * 31) `731`. If you specify an invalid retention period, such as `94`, RDS issues an error.	`7`	provision and update
`character_set_name`	String	The default server collation when you create the database instance. This cannot be modified, but apps can specify a different collation at table or column level. For more information, see the AWS Documentation.	None	provision
`multi_az`	Boolean	Allows you to activate or deactivate Multi-AZ using either SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs) depending on the chosen SQL Server version. For Multi-AZ to work correctly, your security group must be configured to allow UDP and TCP traffic for port 3343. Multi-AZ requires backups enabled, so you need to ensure `backup_retention_period` is greater than `0`. For more information, see Multi-AZ instance ports requirement and Multi-AZ deployments for Amazon RDS for Microsoft SQL Server in the AWS documentation.	`true`	provision and update
`enable_export_agent_logs`	Boolean	If `true`, it enables the `agent` `cloud_watch_log_export` on the RDS instance. When activated, it creates an associated `agent` CloudWatch log group. When deactivated, the associated `agent` CloudWatch log group is deleted.	`false`	provision and update
`cloudwatch_agent_log_group_retention_in_days`	Integer	If provided, it specifies the number of days to retain log events in the `agent` log group. It is used in conjunction with `enable_export_agent_logs`. If you select 0, the events in the log group are always retained and never expire.	`30`	provision and update
`enable_export_error_logs`	Boolean	If `true`, it enables the `error` `cloud_watch_log_export` on the RDS instance. When activated, it creates an associated error CloudWatch log group. When deactivated, the associated error CloudWatch log group is deleted.	`false`	provision and update
`cloudwatch_error_log_group_retention_in_days`	Integer	If provided, it specifies the number of days to retain log events in the `error` log group. It is used in conjunction with `enable_export_error_logs`. If you select 0, the events in the log group are always retained and never expire.	`30`	provision and update
`cloudwatch_log_groups_kms_key_id`	String	Log group data is always encrypted in CloudWatch Logs. By default, CloudWatch Logs uses server-side encryption for the log data at rest. As an alternative, you can use AWS Key Management Service (AWS KMS) for this encryption. If you use AWS KMS, the encryption is done by using an AWS KMS customer-managed key. This property, if provided, sets the customer-managed key to use for encrypting the CloudWatch log group created for the `agent` and `error` logs. It is used in conjunction with `enable_export_agent_logs` and `enable_export_error_logs`.	`""`	provision and update
`require_ssl`	Boolean	Only allow secure database connections.	`true`	provision and update

Binding

You can bind a service by running:

cf bind-service APP-NAME SERVICE-INSTANCE-NAME --binding-name BINDING-NAME

Binding Credentials

The format for binding credentials for MSSQL is as follows:

{
    "name" : "DATABASE-NAME",
    "hostname" : "DATABASE-SERVER-HOST",
    "port" : "DATABASE-SERVER-PORT",
    "username" : "AUTHENTICATION-USERNAME",
    "password" : "AUTHENTICATION-PASSWORD",
    "uri" : "DATABASE-CONNECTION-URI",
    "jdbcUrl" : "JDBC-FORMAT-CONNECTION-URL",
    "require_ssl": "boolean: represents whether only secure database connections are allowed"
}

JDBC URL

RDS for MSSQL configures the JDBC URL depending on the require_ssl property. When enabling require_ssl, the next parameters are configured as follows:

encrypt: true
trustServerCertificate: false
hostNameInCertificate: Common Name (CN) or DNS name in the Subject Alternate Name (SAN) in the server certificate. When deactivating require_ssl, encrypt is set as false. For more information, see Microsoft documentation.