Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Appendix: Options for SQL Server Database Engine

This appendix describes options, or additional features, that are available for Amazon RDS instances running the Microsoft SQL Server DB engine. To enable these options, you can add them to an option group, and then associate the option group with your DB instance. For more information about working with options, see Option Groups Overview.

The following option is currently supported for SQL Server DB instances:

SQL Server Transparent Data Encryption

Amazon RDS supports using Transparent Data Encryption (TDE) to encrypt stored data for SQL Server 2008 R2 Enterprise Edition and SQL Server 2012 Enterprise Edition. TDE automatically encrypts data before it is written to storage and automatically decrypts data when the data is read from storage. To enable transparent data encryption for a DB instance that is running SQL Server, specify the TDE option in an Amazon RDS option group that is associated with that DB instance.

Transparent data encryption for SQL Server provides encryption key management by using a two-tier key architecture. A certificate, which is generated from the database master key, is used to protect the data encryption keys. The database encryption key performs the actual encryption and decryption of data on the user database. Amazon RDS backs up and manages the database master key and the TDE certificate. To comply with several security standards, Amazon RDS is working to implement automatic periodic master key rotation.

Transparent data encryption is used in scenarios where you need to encrypt sensitive data in case data files and backups are obtained by a third party or when you need to address security-related regulatory compliance issues. Note that you cannot encrypt the system databases for SQL Server, such as the Model or Master databases.

A detailed discussion of transparent data encryption is beyond the scope of this guide, but you should understand the security strengths and weaknesses of each encryption algorithm and key. For information about transparent data encryption for SQL Server, see Transparent Data Encryption (TDE) on the Microsoft website.

You should determine if your DB instance is already associated with an option group that has the TDE option. To view the option group that a DB instance is associated with, you can use the RDS console, the describe-db-instance CLI command, or the API action DescribeDBInstances.

The process for enabling transparent data encryption on a SQL Server DB instance is as follows:

  1. If the DB instance is not associated with an option group that has the TDE option enabled, you must either create an option group and add the TDE option or modify the associated option group to add the TDE option. For information about creating or modifying an option group, see Working with Option Groups. For information about adding an option to an option group, see Adding an Option to an Option Group.

  2. Associate the DB instance with the option group with the TDE option. For information about associating a DB instance with an option group, see Modifying a DB Instance Running the SQL Server Database Engine.

When the TDE option is added to an option group, Amazon RDS generates a certificate that is used in the encryption process. You can then use the certificate to run SQL statements that will encrypt data in a database on the DB instance. The following example uses the RDS-created certificate called RDSTDECertificateName to encrypt a database called customerDatabase.

---------- Enabling TDE -------------

-- Find a RDSTDECertificate to use
USE [master]
GO
SELECT name FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%'
GO

USE [customerDatabase]
GO
-- Create DEK using one of the certificates from the previous step
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [RDSTDECertificateName]
GO

-- Enable encryption on the database
ALTER DATABASE [customerDatabase]
SET ENCRYPTION ON
GO

-- Verify that the database is encrypted
USE [master]
GO
SELECT name FROM sys.databases WHERE is_encrypted = 1
GO
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
GO 

The time it takes to encrypt a SQL Server database using TDE depends on several factors, including the size of the DB instance, whether PIOPS is enabled for the instance, the amount of data, and other factors.

The TDE option is a persistent option than cannot be removed from an option group unless all DB instances and backups are disassociated from the option group. Once you add the TDE option to an option group, the option group can only be associated with DB instances that use TDE. For more information about persistent options in an option group, see Option Groups Overview.

Because the TDE  option is a persistent option, you can also inadvertently have a conflict between the option group and an associated DB instance. You can have a conflict between the option group and an associated DB instance in the following situations:

  • The current option group has the TDE option, and you replace it with an option group that does not have the TDE option.

  • You restore a DB instance that no longer uses TDE from a point-in-time DB snapshot that was taken when the DB instance was using TDE. The option group for the DB instance that no longer uses TDE will conflict with the restored DB instance that uses TDE.

To disable TDE for a DB instance, first ensure that there are no encrypted objects left on the DB instance by either unencrypting the objects or by dropping them. If any encrypted objects exist on the DB instance, you will not be allowed to disable TDE for the DB instance. When using the RDS Console to remove the TDE option from an option group, the console will indicate it is processing and an event will be created indicating an error if the option group is associated with an encrypted DB instance or DB snapshot.

The following example removes the TDE encryption from a database called customerDatabase.

------------- Removing TDE ----------------

USE [customerDatabase]
GO

-- Disable encryption on the database
ALTER DATABASE [customerDatabase]
SET ENCRYPTION OFF
GO

-- Wait until the encryption state of the database becomes 1. The state will be 5 (Decryption in progress) for a while
SELECT db_name(database_id) as DatabaseName, * FROM sys.dm_database_encryption_keys
GO

-- Drop the DEK used for encryption
DROP DATABASE ENCRYPTION KEY
GO

-- Alter to SIMPLE Recovery mode so that your encrypted log gets truncated
USE [master]
GO
ALTER DATABASE [customerDatabase] SET RECOVERY SIMPLE
GO 

When all objects are unencrypted, you can modify the DB instance to be associated with an option group without the TDE option or you can remove the TDE option from the option group.

Performance Considerations

The performance of a SQL Server DB instance can be impacted by using transparent data encryption.

Performance for unencrypted databases can also be degraded if the databases are on a DB instance that has at least one encrypted database. As a result, we recommend that you keep encrypted and unencrypted databases on separate DB instances.

Because of the nature of encryption, the database size and the size of the transaction log will be larger than for an unencrypted database. You could run over your allocation of free backup space. The nature of TDE will cause an unavoidable performance hit. If you need high performance and TDE, measure the impact and make sure it meets your needs. There is less of an impact on performance if you use Provisioned IOPS and at least an M3.Large DB instance class.

Multi-AZ Deployment for SQL Server Using the Mirroring Option

Amazon RDS supports Multi-AZ deployments for SQL Server using the Mirroring option. In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. For more information about Multi-AZ deployments, see High Availability (Multi-AZ). For an overview of SQL Server Mirroring and Amazon RDS, see Multi-AZ Deployments Using SQL Server Mirroring

Note

SQL Server Multi-AZ using Mirroring is currently available in the US East (N. Virginia), US West (Oregon), and EU (Ireland) AWS regions. We plan to support other regions in the future.

The process for enabling Multi-AZ using Mirroring as an option for a SQL Server DB instance is as follows:

  1. If your DB instance is not associated with an option group that has the Mirroring option enabled, you can do one of three tasks:

    • Create an option group and add the Mirroring option. For information about creating an option group, see Working with Option Groups.

    • Modify the option group currently associated with the DB instance to add the Mirroring option. For information about adding an option to an option group, see Adding an Option to an Option Group.

    • Associate one of the default option groups that have the Mirroring option already added. These option groups are available for each engine version and edition combination, such as default:sqlserver-se-10-50-mirrored or default:sqlserver-se-11-00-mirrored.

  2. Associate the DB instance with the option group with the Mirroring option. For information about associating a DB instance with an option group, see Modifying a DB Instance Running the SQL Server Database Engine.

We recommend that you create an option group with the Mirroring option and then associate the option group with the SQL Server DB instances you want to use with Multi-AZ with Mirroring. The following AWS CLI examples create a SQL Server option group, then adds the Mirroring option to the option group, and then associates that option group with a SQL Server DB instance.

The following AWS CLI example creates an option group named MirroringOG for SQL Server SE 10.50:

For Linux, OS X, or Unix:

awsrds create-option-group \
            --option-group-name MirroringOG \
            --engine-name sqlserver-se \
            --major-engine-version 10.50 \
            --option-group-description "SQLServer Mirroring" 

For Windows:

awsrds create-option-group ^
            --option-group-name MirroringOG ^
            --engine-name sqlserver-se ^
            --major-engine-version 10.50 ^
            --option-group-description "SQLServer Mirroring" 

The following AWS CLI example adds the Mirroring option to an option group named MirroringOG:

For Linux, OS X, or Unix:

aws rds add-option-to-option-group \
    --option-group-name MirroringOG \
    --option-name Mirroring 

For Windows:

aws rds add-option-to-option-group ^
    --option-group-name MirroringOG ^
    --option-name Mirroring 

You can then associate an SQL Server DB instance with the option group. The following AWS CLI example associates a SQL Server DB instance named cust_instance_id with an option group named MirroringOG:

For Linux, OS X, or Unix:

aws rds modify-db-instance \
    --db-instance-identifier cust_instance_id \
    --option-group-name MirroringOG \
    --apply-immediately 

For Windows:

aws rds modify-db-instance ^
    --db-instance-identifier cust_instance_id ^
    --option-group-name MirroringOG ^
    --apply-immediately 

When the Mirroring option is added to an option group, Amazon RDS begins the replication and synchronization process for any SQL Server DB instances that are associated with the option group.

Removing Multi-AZ (Mirroring) from a SQL Server DB Instance

SQL Server Mirroring is enabled in Amazon RDS through the use of option groups. When you create a SQL Server DB instance that uses Multi-AZ with Mirroring, Amazon RDS automatically creates a default option group that has the Mirroring option enabled. If you need to disable mirroring to perform a bulk load or other tasks that do not require mirroring, do the steps below. Note that you can't just remove the Mirroring option from the option group associated with the DB instance.

To disable mirroring for a SQL Server DB instance, do the following:

  1. Create a new option group that doesn't have the Mirroring option.

  2. Associate this new option group with the SQL Server DB instance using mirroring. This will cause the instance to stop mirroring and become a single Availability Zone instance.