# Common DBA tasks for Amazon RDS for Microsoft SQL Server This section describes the Amazon RDS-specific implementations of some common DBA tasks for DB instances that are running the Microsoft SQL Server database engine. In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges. **Note** When working with a SQL Server DB instance, you can run scripts to modify a newly created database, but you cannot modify the [model] database, the database used as the model for new databases. **Topics** + [ # Accessing the tempdb database on Microsoft SQL Server DB instances on Amazon RDS ](SQLServer.TempDB.md) + [ # Analyzing your database workload on an Amazon RDS for SQL Server DB instance with Database Engine Tuning Advisor ](Appendix.SQLServer.CommonDBATasks.Workload.md) + [ # Changing the `db_owner` to the `rdsa` account for your Amazon RDS for SQL Server database ](Appendix.SQLServer.CommonDBATasks.ChangeDBowner.md) + [ # Managing collations and character sets for Amazon RDS for Microsoft SQL Server ](Appendix.SQLServer.CommonDBATasks.Collation.md) + [ # Creating a database user for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.CreateUser.md) + [ # Determining a recovery model for your Amazon RDS for SQL Server database ](Appendix.SQLServer.CommonDBATasks.DatabaseRecovery.md) + [ # Determining the last failover time for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.LastFailover.md) + [ # Troubleshooting point-in-time-recovery failures due to a log sequence number gap ](Appendix.SQLServer.CommonDBATasks.PITR-LSN-Gaps.md) + [ # Deny or allow viewing database names for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.ManageView.md) + [ # Disabling fast inserts during bulk loading for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.DisableFastInserts.md) + [ # Dropping a database in an Amazon RDS for Microsoft SQL Server DB instance ](Appendix.SQLServer.CommonDBATasks.DropMirrorDB.md) + [ # Renaming a Amazon RDS for Microsoft SQL Server database in a Multi-AZ deployment ](Appendix.SQLServer.CommonDBATasks.RenamingDB.md) + [ # Resetting the db\$1owner role membership for master user for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.ResetPassword.md) + [ # Restoring license-terminated DB instances for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.RestoreLTI.md) + [ # Transitioning a Amazon RDS for SQL Server database from OFFLINE to ONLINE ](Appendix.SQLServer.CommonDBATasks.TransitionOnline.md) + [ # Using change data capture for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.CDC.md) + [ # Using SQL Server Agent for Amazon RDS ](Appendix.SQLServer.CommonDBATasks.Agent.md) + [ # Working with Amazon RDS for Microsoft SQL Server logs ](Appendix.SQLServer.CommonDBATasks.Logs.md) + [ # Working with trace and dump files for Amazon RDS for SQL Server ](Appendix.SQLServer.CommonDBATasks.TraceFiles.md) # Accessing the tempdb database on Microsoft SQL Server DB instances on Amazon RDS You can access the `tempdb` database on your Microsoft SQL Server DB instances on Amazon RDS. You can run code on `tempdb` by using Transact-SQL through Microsoft SQL Server Management Studio (SSMS), or any other standard SQL client application. For more information about connecting to your DB instance, see [Connecting to your Microsoft SQL Server DB instance](USER_ConnectToMicrosoftSQLServerInstance.md). The master user for your DB instance is granted `CONTROL` access to `tempdb` so that this user can modify the `tempdb` database options. The master user isn't the database owner of the `tempdb` database. If necessary, the master user can grant `CONTROL` access to other users so that they can also modify the `tempdb` database options. **Note** You can't run Database Console Commands (DBCC) on the `tempdb` database. # Modifying tempdb database options You can modify the database options on the `tempdb` database on your Amazon RDS DB instances. For more information about which options can be modified, see [tempdb database](https://msdn.microsoft.com/en-us/library/ms190768%28v=sql.120%29.aspx) in the Microsoft documentation. Database options such as the maximum file size options are persistent after you restart your DB instance. You can modify the database options to optimize performance when importing data, and to prevent running out of storage. ## Optimizing performance when importing data To optimize performance when importing large amounts of data into your DB instance, set the `SIZE` and `FILEGROWTH` properties of the tempdb database to large numbers. For more information about how to optimize `tempdb`, see [Optimizing tempdb performance](https://technet.microsoft.com/en-us/library/ms175527%28v=sql.120%29.aspx) in the Microsoft documentation. The following example demonstrates setting the size to 100 GB and file growth to 10 percent. ``` 1. alter database[tempdb] modify file (NAME = N'templog', SIZE=100GB, FILEGROWTH = 10%) ``` ## Preventing storage problems To prevent the `tempdb` database from using all available disk space, set the `MAXSIZE` property. The following example demonstrates setting the property to 2048 MB. ``` 1. alter database [tempdb] modify file (NAME = N'templog', MAXSIZE = 2048MB) ``` # Shrinking the tempdb database There are two ways to shrink the `tempdb` database on your Amazon RDS DB instance. You can use the `rds_shrink_tempdbfile` procedure, or you can set the `SIZE` property, ## Using the rds\$1shrink\$1tempdbfile procedure You can use the Amazon RDS procedure `msdb.dbo.rds_shrink_tempdbfile` to shrink the `tempdb` database. You can only call `rds_shrink_tempdbfile` if you have `CONTROL` access to `tempdb`. When you call `rds_shrink_tempdbfile`, there is no downtime for your DB instance. The `rds_shrink_tempdbfile` procedure has the following parameters. **** | Parameter name | Data type | Default | Required | Description | | --- | --- | --- | --- | --- | | `@temp_filename` | SYSNAME | — | required | The logical name of the file to shrink. | | `@target_size` | int | null | optional | The new size for the file, in megabytes. | The following example gets the names of the files for the `tempdb` database. ``` 1. use tempdb; 2. GO 3. 4. select name, * from sys.sysfiles; 5. GO ``` The following example shrinks a `tempdb` database file named `test_file`, and requests a new size of `10` megabytes: ``` 1. exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N'test_file', @target_size = 10; ``` ## Setting the SIZE property You can also shrink the `tempdb` database by setting the `SIZE` property and then restarting your DB instance. For more information about restarting your DB instance, see [Rebooting a DB instance](USER_RebootInstance.md). The following example demonstrates setting the `SIZE` property to 1024 MB. ``` 1. alter database [tempdb] modify file (NAME = N'templog', SIZE = 1024MB) ``` # TempDB configuration for Multi-AZ deployments If your RDS for SQL Server DB instance is in a Multi-AZ Deployment using Database Mirroring (DBM) or Always On Availability Groups (AGs), keep in mind the following considerations for using the `tempdb` database. You can't replicate `tempdb` data from your primary DB instance to your secondary DB instance. When you fail over to a secondary DB instance, `tempdb` on that secondary DB instance will be empty. You can synchronize the configuration of the `tempdb` database options, including its file sizing and autogrowth settings, from your primary DB instance to your secondary DB instance. Synchronizing the `tempDB` configuration is supported on all RDS for SQL Server versions. You can turn on automatic synchronization of the `tempdb` configuration by using the following stored procedure: ``` EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'TempDbFile'; ``` **Important** Before using the `rds_set_system_database_sync_objects` stored procedure, ensure you've set your preferred `tempdb` configuration on your primary DB instance, rather than on your secondary DB instance. If you made the configuration change on your secondary DB instance, your preferred `tempdb` configuration could be deleted when you turn on automatic synchronization. You can use the following function to confirm whether automatic synchronization of the `tempdb` configuration is turned on: ``` SELECT * from msdb.dbo.rds_fn_get_system_database_sync_objects(); ``` When automatic synchronization of the `tempdb` configuration is turned on, there will be a return value for the `object_class` field. When it's turned off, no value is returned. You can use the following function to find the last time objects were synchronized, in UTC time: ``` SELECT * from msdb.dbo.rds_fn_server_object_last_sync_time(); ``` For example, if you modified the `tempdb` configuration at 01:00 and then run the `rds_fn_server_object_last_sync_time` function, the value returned for `last_sync_time` should be after 01:00, indicating that an automatic synchronization occurred. If you are also using SQL Server Agent job replication, you can enable replication for both SQL Agent jobs and the `tempdb` configuration by providing them in the `@object_type` parameter: ``` EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'SQLAgentJob,TempDbFile'; ``` For more information on SQL Server Agent job replication, see [Turning on SQL Server Agent job replication](Appendix.SQLServer.CommonDBATasks.Agent.md#SQLServerAgent.Replicate). As an alternative to using the `rds_set_system_database_sync_objects` stored procedure to ensure that `tempdb` configuration changes are automatically synchronized, you can use one of the following manual methods: **Note** We recommend turning on automatic synchronization of the `tempdb` configuration by using the `rds_set_system_database_sync_objects` stored procedure. Using automatic synchronization prevents the need to perform these manual tasks each time you change your `tempdb` configuration. + First modify your DB instance and turn Multi-AZ off, then modify tempdb, and finally turn Multi-AZ back on. This method doesn't involve any downtime. For more information, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). + First modify `tempdb` in the original primary instance, then fail over manually, and finally modify `tempdb` in the new primary instance. This method involves downtime. For more information, see [Rebooting a DB instance](USER_RebootInstance.md). # Analyzing your database workload on an Amazon RDS for SQL Server DB instance with Database Engine Tuning Advisor Database Engine Tuning Advisor is a client application provided by Microsoft that analyzes database workload and recommends an optimal set of indexes for your Microsoft SQL Server databases based on the kinds of queries you run. Like SQL Server Management Studio, you run Tuning Advisor from a client computer that connects to your Amazon RDS DB instance that is running SQL Server. The client computer can be a local computer that you run on premises within your own network or it can be an Amazon EC2 Windows instance that is running in the same region as your Amazon RDS DB instance. This section shows how to capture a workload for Tuning Advisor to analyze. This is the preferred process for capturing a workload because Amazon RDS restricts host access to the SQL Server instance. For more information, see [Database Engine Tuning Advisor](https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor) in the Microsoft documentation. To use Tuning Advisor, you must provide what is called a workload to the advisor. A workload is a set of Transact-SQL statements that run against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when tuning databases. When working with Amazon RDS, a workload can be a file on a client computer or a database table on an Amazon RDS for SQL Server DB accessible to your client computer. The file or the table must contain queries against the databases you want to tune in a format suitable for replay. For Tuning Advisor to be most effective, a workload should be as realistic as possible. You can generate a workload file or table by performing a trace against your DB instance. While a trace is running, you can either simulate a load on your DB instance or run your applications with a normal load. There are two types of traces: client-side and server-side. A client-side trace is easier to set up and you can watch trace events being captured in real-time in SQL Server Profiler. A server-side trace is more complex to set up and requires some Transact-SQL scripting. In addition, because the trace is written to a file on the Amazon RDS DB instance, storage space is consumed by the trace. It is important to track of how much storage space a running server-side trace uses because the DB instance could enter a storage-full state and would no longer be available if it runs out of storage space. For a client-side trace, when a sufficient amount of trace data has been captured in the SQL Server Profiler, you can then generate the workload file by saving the trace to either a file on your local computer or in a database table on a DB instance that is available to your client computer. The main disadvantage of using a client-side trace is that the trace may not capture all queries when under heavy loads. This could weaken the effectiveness of the analysis performed by the Database Engine Tuning Advisor. If you need to run a trace under heavy loads and you want to ensure that it captures every query during a trace session, you should use a server-side trace. For a server-side trace, you must get the trace files on the DB instance into a suitable workload file or you can save the trace to a table on the DB instance after the trace completes. You can use the SQL Server Profiler to save the trace to a file on your local computer or have the Tuning Advisor read from the trace table on the DB instance. # Running a client-side trace on a SQL Server DB instance **To run a client-side trace on a SQL Server DB instance** 1. Start SQL Server Profiler. It is installed in the Performance Tools folder of your SQL Server instance folder. You must load or define a trace definition template to start a client-side trace. 1. In the SQL Server Profiler File menu, choose **New Trace**. In the **Connect to Server** dialog box, enter the DB instance endpoint, port, master user name, and password of the database you would like to run a trace on. 1. In the **Trace Properties** dialog box, enter a trace name and choose a trace definition template. A default template, TSQL\$1Replay, ships with the application. You can edit this template to define your trace. Edit events and event information under the **Events Selection** tab of the **Trace Properties** dialog box. For more information about trace definition templates and using the SQL Server Profiler to specify a client-side trace, see [Database Engine Tuning Advisor](https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor) in the Microsoft documentation. 1. Start the client-side trace and watch SQL queries in real-time as they run against your DB instance. 1. Select **Stop Trace** from the **File** menu when you have completed the trace. Save the results as a file or as a trace table on you DB instance. # Running a server-side trace on a SQL Server DB instance Writing scripts to create a server-side trace can be complex and is beyond the scope of this document. This section contains sample scripts that you can use as examples. As with a client-side trace, the goal is to create a workload file or trace table that you can open using the Database Engine Tuning Advisor. The following is an abridged example script that starts a server-side trace and captures details to a workload file. The trace initially saves to the file RDSTrace.trc in the D:\$1RDSDBDATA\$1Log directory and rolls-over every 100 MB so subsequent trace files are named RDSTrace\$11.trc, RDSTrace\$12.trc, etc. ``` DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace'; DECLARE @max_file_size BIGINT = 100; DECLARE @on BIT = 1 DECLARE @rc INT DECLARE @traceid INT EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size IF (@rc = 0) BEGIN EXEC sp_trace_setevent @traceid, 10, 1, @on EXEC sp_trace_setevent @traceid, 10, 2, @on EXEC sp_trace_setevent @traceid, 10, 3, @on . . . EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @traceid, 1 END ``` The following example is a script that stops a trace. Note that a trace created by the previous script continues to run until you explicitly stop the trace or the process runs out of disk space. ``` DECLARE @traceid INT SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) WHERE property = 5 AND value = 1 AND traceid <> 1 IF @traceid IS NOT NULL BEGIN EXEC sp_trace_setstatus @traceid, 0 EXEC sp_trace_setstatus @traceid, 2 END ``` You can save server-side trace results to a database table and use the database table as the workload for the Tuning Advisor by using the fn\$1trace\$1gettable function. The following commands load the results of all files named RDSTrace.trc in the D:\$1rdsdbdata\$1Log directory, including all rollover files like RDSTrace\$11.trc, into a table named RDSTrace in the current database. ``` SELECT * INTO RDSTrace FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); ``` To save a specific rollover file to a table, for example the RDSTrace\$11.trc file, specify the name of the rollover file and substitute 1 instead of default as the last parameter to fn\$1trace\$1gettable. ``` SELECT * INTO RDSTrace_1 FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); ``` # Running Tuning Advisor with a trace Once you create a trace, either as a local file or as a database table, you can then run Tuning Advisor against your DB instance. Using Tuning Advisor with Amazon RDS is the same process as when working with a standalone, remote SQL Server instance. You can either use the Tuning Advisor UI on your client machine or use the dta.exe utility from the command line. In both cases, you must connect to the Amazon RDS DB instance using the endpoint for the DB instance and provide your master user name and master user password when using Tuning Advisor. The following code example demonstrates using the dta.exe command line utility against an Amazon RDS DB instance with an endpoint of **dta.cnazcmklsdei.us-east-1.rds.amazonaws.com**. The example includes the master user name **admin** and the master user password **test**, the example database to tune is named machine named **C:\$1RDSTrace.trc**. The example command line code also specifies a trace session named **RDSTrace1** and specifies output files to the local machine named **RDSTrace.sql** for the SQL output script, **RDSTrace.txt** for a result file, and **RDSTrace.xml** for an XML file of the analysis. There is also an error table specified on the RDSDTA database named **RDSTraceErrors**. ``` dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors ``` Here is the same example command line code except the input workload is a table on the remote Amazon RDS instance named **RDSTrace** which is on the **RDSDTA** database. ``` dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors ``` For a full list of dta utility command-line parameters, see [dta Utility](https://docs.microsoft.com/en-us/sql/tools/dta/dta-utility) in the Microsoft documentation. # Changing the `db_owner` to the `rdsa` account for your Amazon RDS for SQL Server database When you create or restore a database in an RDS for SQL Server DB instance, Amazon RDS sets the owner of the database to `rdsa`. If you have a Multi-AZ deployment using SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs), Amazon RDS sets the owner of the database on the secondary DB instance to `NT AUTHORITY\SYSTEM`. The owner of the secondary database can't be changed until the secondary DB instance is promoted to the primary role. In most cases, setting the owner of the database to `NT AUTHORITY\SYSTEM` isn't problematic when executing queries, however, can throw errors when executing system stored procedures such as `sys.sp_updatestats` that require elevated permissions to execute. You can use the following query to identify the owner of the databases owned by `NT AUTHORITY\SYSTEM`: ``` SELECT name FROM sys.databases WHERE SUSER_SNAME(owner_sid) = 'NT AUTHORITY\SYSTEM'; ``` You can use the Amazon RDS stored procedure `rds_changedbowner_to_rdsa` to change the owner of the database to `rdsa`. The following databases are not allowed to be used with `rds_changedbowner_to_rdsa`: `master, model, msdb, rdsadmin, rdsadmin_ReportServer, rdsadmin_ReportServerTempDB, SSISDB`. To change the owner of the database to `rdsa`, call the `rds_changedbowner_to_rdsa` stored procedure and provide the name of the database. **Example usage:** ``` exec msdb.dbo.rds_changedbowner_to_rdsa 'TestDB1'; ``` The following parameter is required: + `@db_name` – The name of the database to change the owner of the database to `rdsa`. **Important** You can't use `rds_changedbowner_to_rdsa` to change ownership of a database to a login other than `rdsa`. For example, you can't change the ownership to the login with which you created the database. To restore lost membership in the `db_owner` role for your master user when no other database user can be used to grant the membership, reset the master user password to obtain membership in the `db_owner` role. For more information, see [Resetting the db\$1owner role membership for master user for Amazon RDS for SQL Server](Appendix.SQLServer.CommonDBATasks.ResetPassword.md). # Managing collations and character sets for Amazon RDS for Microsoft SQL Server This topic provide guidance on how to manage collations and character sets for Microsoft SQL Server in Amazon RDS. It explains how to configure collations during database creation and modify them later, ensuring proper handling of text data based on language and locale requirements. Additionally, it covers best practices for maintaining compatibility and performance in SQL Server environments in Amazon RDS. SQL Server supports collations at multiple levels. You set the default server collation when you create the DB instance. You can override the collation in the database, table, or column level. **Topics** + [ ## Server-level collation for Microsoft SQL Server ](#Appendix.SQLServer.CommonDBATasks.Collation.Server) + [ ## Database-level collation for Microsoft SQL Server ](#Appendix.SQLServer.CommonDBATasks.Collation.Database-Table-Column) ## Server-level collation for Microsoft SQL Server When you create a Microsoft SQL Server DB instance, you can set the server collation that you want to use. If you don't choose a different collation, the server-level collation defaults to SQL\$1Latin1\$1General\$1CP1\$1CI\$1AS. The server collation is applied by default to all databases and database objects. **Note** You can't change the collation when you restore from a DB snapshot. Currently, Amazon RDS supports the following server collations: | Collation | Description | | --- | --- | | Arabic\$1CI\$1AS | Arabic, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Chinese\$1PRC\$1BIN2 | Chinese-PRC, binary code point sort order | | Chinese\$1PRC\$1CI\$1AS | Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Chinese\$1Taiwan\$1Stroke\$1CI\$1AS | Chinese-Taiwan-Stroke, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Danish\$1Norwegian\$1CI\$1AS | Danish-Norwegian, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Danish\$1Norwegian\$1CI\$1AS\$1KS | Danish-Norwegian, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive | | Danish\$1Norwegian\$1CI\$1AS\$1KS\$1WS | Danish-Norwegian, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive | | Danish\$1Norwegian\$1CI\$1AS\$1WS | Danish-Norwegian, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive | | Danish\$1Norwegian\$1CS\$1AI | Danish-Norwegian, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Danish\$1Norwegian\$1CS\$1AI\$1KS | Danish-Norwegian, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive | | Finnish\$1Swedish\$1100\$1BIN | Finnish-Swedish-100, binary sort | | Finnish\$1Swedish\$1100\$1BIN2 | Finnish-Swedish-100, binary code point comparison sort | | Finnish\$1Swedish\$1100\$1CI\$1AI | Finnish-Swedish-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Finnish\$1Swedish\$1100\$1CI\$1AS | Finnish-Swedish-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Finnish\$1Swedish\$1CI\$1AS | Finnish, Swedish, and Swedish (Finland), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | French\$1CI\$1AS | French, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Greek\$1CI\$1AS | Greek, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Greek\$1CS\$1AS | Greek, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Hebrew\$1BIN | Hebrew, binary sort | | Hebrew\$1CI\$1AS | Hebrew, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Japanese\$1BIN | Japanese, binary sort | | Japanese\$1CI\$1AS | Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Japanese\$1CS\$1AS | Japanese, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Japanese\$1XJIS\$1140\$1CI\$1AS | Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters, variation selector insensitive | | Japanese\$1XJIS\$1140\$1CI\$1AS\$1KS\$1VSS | Japanese, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters, variation selector sensitive | | Japanese\$1XJIS\$1140\$1CI\$1AS\$1VSS | Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters, variation selector sensitive | | Japanese\$1XJIS\$1140\$1CS\$1AS\$1KS\$1WS | Japanese, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters, variation selector insensitive | | Korean\$1Wansung\$1CI\$1AS | Korean-Wansung, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Latin1\$1General\$1100\$1BIN | Latin1-General-100, binary sort | | Latin1\$1General\$1100\$1BIN2 | Latin1-General-100, binary code point sort order | | Latin1\$1General\$1100\$1BIN2\$1UTF8 | Latin1-General-100, binary code point sort order, UTF-8 encoded | | Latin1\$1General\$1100\$1CI\$1AS | Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Latin1\$1General\$1100\$1CI\$1AS\$1SC\$1UTF8 | Latin1-General-100, case-insensitive, accent-sensitive, supplementary characters, UTF-8 encoded | | Latin1\$1General\$1BIN | Latin1-General, binary sort | | Latin1\$1General\$1BIN2 | Latin1-General, binary code point sort order | | Latin1\$1General\$1CI\$1AI | Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive | | Latin1\$1General\$1CI\$1AS | Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Latin1\$1General\$1CI\$1AS\$1KS | Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive | | Latin1\$1General\$1CS\$1AS | Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Modern\$1Spanish\$1CI\$1AS | Modern-Spanish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Polish\$1CI\$1AS | Polish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | SQL\$11xCompat\$1CP850\$1CI\$1AS | Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 49 on Code Page 850 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP1\$1CI\$1AI | Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page 1252 for non-Unicode Data | | **SQL\$1Latin1\$1General\$1CP1\$1CI\$1AS (default)** | Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP1\$1CS\$1AS | Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP437\$1CI\$1AI | Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 34 on Code Page 437 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP850\$1BIN | Latin1-General, binary sort order for Unicode Data, SQL Server Sort Order 40 on Code Page 850 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP850\$1BIN2 | Latin1-General, binary code point sort order for Unicode Data, SQL Server Sort Order 40 on Code Page 850 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP850\$1CI\$1AI | Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 44 on Code Page 850 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP850\$1CI\$1AS | Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 42 on Code Page 850 for non-Unicode Data | | SQL\$1Latin1\$1General\$1Pref\$1CP850\$1CI\$1AS | Latin1-General-Pref, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 183 on Code Page 850 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP1256\$1CI\$1AS | Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 146 on Code Page 1256 for non-Unicode Data | | SQL\$1Latin1\$1General\$1CP1255\$1CS\$1AS | Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 137 on Code Page 1255 for non-Unicode Data | | Thai\$1CI\$1AS | Thai, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | | Turkish\$1CI\$1AS | Turkish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive | You can also retrieve the list of supported collations programmatically using the AWS CLI: ``` aws rds describe-db-engine-versions --engine sqlserver-ee --list-supported-character-sets --query 'DBEngineVersions[].SupportedCharacterSets[].CharacterSetName' | sort -u ``` To choose the collation: + If you're using the Amazon RDS console, when creating a new DB instance choose **Additional configuration**, then enter the collation in the **Collation** field. For more information, see [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + If you're using the AWS CLI, use the `--character-set-name` option with the `create-db-instance` command. For more information, see [create-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html). + If you're using the Amazon RDS API, use the `CharacterSetName` parameter with the `CreateDBInstance` operation. For more information, see [CreateDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html). ## Database-level collation for Microsoft SQL Server You can change the default collation at the database, table, or column level by overriding the collation when creating a new database or database object. For example, if your default server collation is SQL\$1Latin1\$1General\$1CP1\$1CI\$1AS, you can change it to Mohawk\$1100\$1CI\$1AS for Mohawk collation support. Even arguments in a query can be type-cast to use a different collation if necessary. For example, the following query would change the default collation for the AccountName column to Mohawk\$1100\$1CI\$1AS ``` CREATE TABLE [dbo].[Account] ( [AccountID] [nvarchar](10) NOT NULL, [AccountName] [nvarchar](100) COLLATE Mohawk_100_CI_AS NOT NULL ) ON [PRIMARY]; ``` The Microsoft SQL Server DB engine supports Unicode by the built-in NCHAR, NVARCHAR, and NTEXT data types. For example, if you need CJK support, use these Unicode data types for character storage and override the default server collation when creating your databases and tables. Here are several links from Microsoft covering collation and Unicode support for SQL Server: + [Working with collations](http://msdn.microsoft.com/en-us/library/ms187582%28v=sql.105%29.aspx) + [Collation and international terminology](http://msdn.microsoft.com/en-us/library/ms143726%28v=sql.105%29) + [Using SQL Server collations](http://msdn.microsoft.com/en-us/library/ms144260%28v=sql.105%29.aspx) + [International considerations for databases and database engine applications](http://msdn.microsoft.com/en-us/library/ms190245%28v=sql.105%29.aspx) # Creating a database user for Amazon RDS for SQL Server You can create a database user for your Amazon RDS for Microsoft SQL Server DB instance by running a T-SQL script like the following example. Use an application such as SQL Server Management Suite (SSMS). You log into the DB instance as the master user that was created when you created the DB instance. ``` --Initially set context to master database USE [master]; GO --Create a server-level login named theirname with password theirpassword CREATE LOGIN [theirname] WITH PASSWORD = 'theirpassword'; GO --Set context to msdb database USE [msdb]; GO --Create a database user named theirname and link it to server-level login theirname CREATE USER [theirname] FOR LOGIN [theirname]; GO ``` For an example of adding a database user to a role, see [Adding a user to the SQLAgentUser role](SQLServerAgent.AddUser.md). **Note** If you get permission errors when adding a user, you can restore privileges by modifying the DB instance master user password. For more information, see [Resetting the db\$1owner role membership for master user for Amazon RDS for SQL Server](Appendix.SQLServer.CommonDBATasks.ResetPassword.md). It is not a best practice to clone master user permissions in your applications. For more information, see [How to clone master user permissions in Amazon RDS for SQL Server](https://aws.amazon.com/blogs/database/how-to-clone-master-user-permissions-in-amazon-rds-for-sql-server/). # Determining a recovery model for your Amazon RDS for SQL Server database In Amazon RDS, the recovery model, retention period, and database status are linked. It's important to understand the consequences before making a change to one of these settings. Each setting can affect the others. For example: + If you change a database's recovery model to SIMPLE or BULK\$1LOGGED while backup retention is enabled, Amazon RDS resets the recovery model to FULL within five minutes. This also results in RDS taking a snapshot of the DB instance. + If you set backup retention to `0` days, RDS sets the recovery mode to SIMPLE. + If you change a database's recovery model from SIMPLE to any other option while backup retention is set to `0` days, RDS resets the recovery model to SIMPLE. **Important** Never change the recovery model on Multi-AZ instances, even if it seems you can do so—for example, by using ALTER DATABASE. Backup retention, and therefore FULL recovery mode, is required for Multi-AZ. If you alter the recovery model, RDS immediately changes it back to FULL. This automatic reset forces RDS to completely rebuild the mirror. During this rebuild, the availability of the database is degraded for about 30-90 minutes until the mirror is ready for failover. The DB instance also experiences performance degradation in the same way it does during a conversion from Single-AZ to Multi-AZ. How long performance is degraded depends on the database storage size—the bigger the stored database, the longer the degradation. For more information on SQL Server recovery models, see [Recovery models (SQL Server)](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server) in the Microsoft documentation. # Determining the last failover time for Amazon RDS for SQL Server To determine the last failover time, use the following stored procedure: ``` execute msdb.dbo.rds_failover_time; ``` This procedure returns the following information. **** | Output parameter | Description | | --- | --- | | errorlog\$1available\$1from | Shows the time from when error logs are available in the log directory. | | recent\$1failover\$1time | Shows the last failover time if it's available from the error logs. Otherwise it shows `null`. | **Note** The stored procedure searches all of the available SQL Server error logs in the log directory to retrieve the most recent failover time. If the failover messages have been overwritten by SQL Server, then the procedure doesn't retrieve the failover time. **Example of no recent failover** This example shows the output when there is no recent failover in the error logs. No failover has happened since 2020-04-29 23:59:00.01. | errorlog\$1available\$1from | recent\$1failover\$1time | | --- | --- | | 2020-04-29 23:59:00.0100000 | null | **Example of recent failover** This example shows the output when there is a failover in the error logs. The most recent failover was at 2020-05-05 18:57:51.89. | errorlog\$1available\$1from | recent\$1failover\$1time | | --- | --- | | 2020-04-29 23:59:00.0100000 | 2020-05-05 18:57:51.8900000 | # Troubleshooting point-in-time-recovery failures due to a log sequence number gap When attempting point-in-time-recovery (PITR) in RDS for SQL Server, you might encounter failures due to gaps in log sequence numbers (LSNs). These gaps prevent RDS from restoring your database to the requested time and RDS places your restoring instance in `incompatible-restore` state. Common causes for this issue are: + Manual changes to the database recovery model. + Automatic recovery model changes by RDS due to insufficient resources for completing transaction log backups. To identify LSN gaps in your database, run this query: ``` SELECT * FROM msdb.dbo.rds_fn_list_tlog_backup_metadata(database_name) ORDER BY backup_file_time_utc desc; ``` If you discover an LSN gap, you can: + Choose a restore point before the LSN gap. + Wait and restore to a point after the next instance backup completes. To prevent this issue, we recommend you don't manually change the recovery model of your RDS for SQL Server databases, as it interrupts instance durability. We also recommend you choose an instance type with sufficient resources for your workload to ensure regular transaction log backups. For more information about transaction log management, see [ SQL Server transaction log architecture and management guide](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16) in the Microsoft SQL Server documentation. # Deny or allow viewing database names for Amazon RDS for SQL Server The master user cannot set `DENY VIEW ANY DATABASE TO LOGIN` 
 to hide databases from a user. 
 To change this permission, use the following stored procedure instead: + Denying database view access to *LOGIN*: ``` EXEC msdb.dbo.rds_manage_view_db_permission @permission=‘DENY’, @server_principal=‘LOGIN’
 go ``` + Allowing database view access to *LOGIN*: ``` EXEC msdb.dbo.rds_manage_view_db_permission @permission='GRANT', @server_principal='LOGIN' 
go ``` Consider the following when using this stored procedure: + Database names are hidden from the SSMS and internal DMV (dynamic management views). However, database names are still visible from audit, logs and metadata tables. These are secured `VIEW ANY DATABASE` server permissions. For more information, see [
 DENY Server Permissions](https://learn.microsoft.com/en-us/sql/t-sql/statements/deny-server-permissions-transact-sql?view=sql-server-ver16#permissions). + Once the permission is reverted to `GRANT` (allowed), the *LOGIN* can view all databases. + If you delete and recreate *LOGIN*, the view permission related to the LOGIN is reset to `ALLOW`. + For Multi-AZ instances, set the `DENY` or `GRANT` permission only for the *LOGIN* on the primary host. The changes are propagated to the secondary host automatically. + This permission only changes whether a login can view the database names. However, access to databases and objects within are managed separately. # Disabling fast inserts during bulk loading for Amazon RDS for SQL Server Starting with SQL Server 2016, fast inserts are enabled by default. Fast inserts leverage the minimal logging that occurs while the database is in the simple or bulk logged recovery model to optimize insert performance. With fast inserts, each bulk load batch acquires new extents, bypassing the allocation lookup for existing extents with available free space to optimize insert performance. However, with fast inserts bulk loads with small batch sizes can lead to increased unused space consumed by objects. If increasing batch size isn't feasible, enabling trace flag 692 can help reduce unused reserved space, but at the expense of performance. Enabling this trace flag disables fast inserts while bulk loading data into heap or clustered indexes. You enable trace flag 692 as a startup parameter using DB parameter groups. For more information, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). Trace flag 692 is supported for Amazon RDS on SQL Server 2016 and later. For more information on trace flags, see [DBCC TRACEON - trace flags](https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql) in the Microsoft documentation. # Dropping a database in an Amazon RDS for Microsoft SQL Server DB instance You can drop a database on an Amazon RDS DB instance running Microsoft SQL Server in a Single-AZ or Multi-AZ deployment. To drop the database, use the following command: ``` --replace your-database-name with the name of the database you want to drop EXECUTE msdb.dbo.rds_drop_database N'your-database-name' ``` **Note** Use straight single quotes in the command. Smart quotes will cause an error. After you use this procedure to drop the database, Amazon RDS drops all existing connections to the database and removes the database's backup history. To grant backup and restore allowance to other users, follow this procedure: ``` USE master GO CREATE LOGIN user1 WITH PASSWORD=N'changeThis', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE msdb GO CREATE USER user1 FOR LOGIN user1 GO use msdb GO GRANT EXECUTE ON msdb.dbo.rds_backup_database TO user1 GO GRANT EXECUTE ON msdb.dbo.rds_restore_database TO user1 GO ``` # Renaming a Amazon RDS for Microsoft SQL Server database in a Multi-AZ deployment To rename a Microsoft SQL Server database instance that uses Multi-AZ, use the following procedure: 1. First, turn off Multi-AZ for the DB instance. 1. Rename the database by running `rdsadmin.dbo.rds_modify_db_name`. 1. Then, turn on Multi-AZ Mirroring or Always On Availability Groups for the DB instance, to return it to its original state. For more information, see [Adding Multi-AZ to a Microsoft SQL Server DB instance](USER_SQLServerMultiAZ.md#USER_SQLServerMultiAZ.Adding). **Note** If your instance doesn't use Multi-AZ, you don't need to change any settings before or after running `rdsadmin.dbo.rds_modify_db_name`. You can't rename a database on a read replica source instance. **Example: **In the following example, the `rdsadmin.dbo.rds_modify_db_name` stored procedure renames a database from **MOO** to **ZAR**. This is similar to running the statement `DDL ALTER DATABASE [MOO] MODIFY NAME = [ZAR]`. ``` EXEC rdsadmin.dbo.rds_modify_db_name N'MOO', N'ZAR' GO ``` # Resetting the db\$1owner role membership for master user for Amazon RDS for SQL Server If you lock your master user out of the `db_owner` role membership on your RDS for SQL Server database and no other database user can grant the membership, you can restore lost membership by modifying the DB instance master user password. By changing the DB instance master user password, RDS grants the `db_owner` membership to the databases in the DB instance that might have been accidentally revoked. You can change the DB instance password by using the Amazon RDS console, the AWS CLI command [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html), or by using the [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) API operation. For more information about modifying a DB instance, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). # Restoring license-terminated DB instances for Amazon RDS for SQL Server Microsoft has requested that some Amazon RDS customers who did not report their Microsoft License Mobility information terminate their DB instance. Amazon RDS takes snapshots of these DB instances, and you can restore from the snapshot to a new DB instance that has the License Included model. You can restore from a snapshot of Standard Edition to either Standard Edition or Enterprise Edition. You can restore from a snapshot of Enterprise Edition to either Standard Edition or Enterprise Edition. **To restore from a SQL Server snapshot after Amazon RDS has created a final snapshot of your instance** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Snapshots**. 1. Choose the snapshot of your SQL Server DB instance. Amazon RDS creates a final snapshot of your DB instance. The name of the terminated instance snapshot is in the format `instance_name-final-snapshot`. For example, if your DB instance name is **mytest.cdxgahslksma.us-east-1.rds.com**, the final snapshot is called** mytest-final-snapshot** and is located in the same AWS Region as the original DB instance. 1. For **Actions**, choose **Restore Snapshot**. The **Restore DB Instance** window appears. 1. For **License Model**, choose **license-included**. 1. Choose the SQL Server DB engine that you want to use. 1. For **DB Instance Identifier**, enter the name for the restored DB instance. 1. Choose **Restore DB Instance**. For more information about restoring from a snapshot, see [Restoring to a DB instance](USER_RestoreFromSnapshot.md). # Transitioning a Amazon RDS for SQL Server database from OFFLINE to ONLINE You can transition your Microsoft SQL Server database on an Amazon RDS DB instance from `OFFLINE` to `ONLINE`. **** | SQL Server method | Amazon RDS method | | --- | --- | | ALTER DATABASE *db\$1name* SET ONLINE; | EXEC rdsadmin.dbo.rds\$1set\$1database\$1online *db\$1name* | # Using change data capture for Amazon RDS for SQL Server Amazon RDS supports change data capture (CDC) for your DB instances running Microsoft SQL Server. CDC captures changes that are made to the data in your tables. It stores metadata about each change, which you can access later. For more information about how CDC works, see [Change data capture](https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server#Capture) in the Microsoft documentation. Before you use CDC with your Amazon RDS DB instances, enable it in the database by running `msdb.dbo.rds_cdc_enable_db`. After CDC is enabled, any user who is `db_owner` of that database can enable or disable CDC on tables in that database. **Important** During restores, CDC will be disabled. All of the related metadata is automatically removed from the database. This applies to snapshot restores and point-in-time restores. After performing one of these types of restores, you can re-enable CDC and re-specify tables to track. To enable CDC for a DB instance, run the `msdb.dbo.rds_cdc_enable_db` stored procedure. ``` 1. exec msdb.dbo.rds_cdc_enable_db 'database_name' ``` To disable CDC for a DB instance, run the `msdb.dbo.rds_cdc_disable_db` stored procedure. ``` 1. exec msdb.dbo.rds_cdc_disable_db 'database_name' ``` To grant CDC permissions to a user, use the following procedure: ``` 1. go 2. GRANT EXECUTE ON msdb.dbo.rds_cdc_enable_db TO User1 3. GRANT EXECUTE ON msdb.dbo.rds_cdc_disable_db TO User1 ``` **Topics** + [ ## Tracking tables with change data capture ](#Appendix.SQLServer.CommonDBATasks.CDC.tables) + [ ## Change data capture jobs ](#Appendix.SQLServer.CommonDBATasks.CDC.jobs) + [ ## Change data capture for Multi-AZ instances ](#Appendix.SQLServer.CommonDBATasks.CDC.Multi-AZ) ## Tracking tables with change data capture After CDC is enabled on the database, you can start tracking specific tables. You can choose the tables to track by running [sys.sp\$1cdc\$1enable\$1table](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql). ``` 1. --Begin tracking a table 2. exec sys.sp_cdc_enable_table 3. @source_schema = N'source_schema' 4. , @source_name = N'source_name' 5. , @role_name = N'role_name' 6. 7. --The following parameters are optional: 8. 9. --, @capture_instance = 'capture_instance' 10. --, @supports_net_changes = supports_net_changes 11. --, @index_name = 'index_name' 12. --, @captured_column_list = 'captured_column_list' 13. --, @filegroup_name = 'filegroup_name' 14. --, @allow_partition_switch = 'allow_partition_switch' 15. ; ``` To view the CDC configuration for your tables, run [sys.sp\$1cdc\$1help\$1change\$1data\$1capture](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-help-change-data-capture-transact-sql). ``` 1. --View CDC configuration 2. exec sys.sp_cdc_help_change_data_capture 3. 4. --The following parameters are optional and must be used together. 5. -- 'schema_name', 'table_name' 6. ; ``` For more information on CDC tables, functions, and stored procedures in SQL Server documentation, see the following: + [Change data capture stored procedures (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/change-data-capture-stored-procedures-transact-sql) + [Change data capture functions (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/change-data-capture-functions-transact-sql) + [Change data capture tables (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/change-data-capture-tables-transact-sql) ## Change data capture jobs When you enable CDC, SQL Server creates the CDC jobs. Database owners (`db_owner`) can view, create, modify, and delete the CDC jobs. However, the RDS system account owns them. Therefore, the jobs aren't visible from native views, procedures, or in SQL Server Management Studio. To control behavior of CDC in a database, use native SQL Server procedures such as [sp\$1cdc\$1enable\$1table](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql) and [sp\$1cdc\$1start\$1job](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-start-job-transact-sql). To change CDC job parameters, like `maxtrans` and `maxscans`, you can use [sp\$1cdc\$1change\$1job.](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql). To get more information regarding the CDC jobs, you can query the following dynamic management views: + sys.dm\$1cdc\$1errors + sys.dm\$1cdc\$1log\$1scan\$1sessions + sysjobs + sysjobhistory ## Change data capture for Multi-AZ instances If you use CDC on a Multi-AZ instance, make sure the mirror's CDC job configuration matches the one on the principal. CDC jobs are mapped to the `database_id`. If the database IDs on the secondary are different from the principal, then the jobs won't be associated with the correct database. To try to prevent errors after failover, RDS drops and recreates the jobs on the new principal. The recreated jobs use the parameters that the principal recorded before failover. Although this process runs quickly, it's still possible that the CDC jobs might run before RDS can correct them. Here are three ways to force parameters to be consistent between primary and secondary replicas: + Use the same job parameters for all the databases that have CDC enabled. + Before you change the CDC job configuration, convert the Multi-AZ instance to Single-AZ. + Manually transfer the parameters whenever you change them on the principal. To view and define the CDC parameters that are used to recreate the CDC jobs after a failover, use `rds_show_configuration` and `rds_set_configuration`. The following example returns the value set for `cdc_capture_maxtrans`. For any parameter that is set to `RDS_DEFAULT`, RDS automatically configures the value. ``` -- Show configuration for each parameter on either primary and secondary replicas. exec rdsadmin.dbo.rds_show_configuration 'cdc_capture_maxtrans'; ``` To set the configuration on the secondary, run `rdsadmin.dbo.rds_set_configuration`. This procedure sets the parameter values for all of the databases on the secondary server. These settings are used only after a failover. The following example sets the `maxtrans` for all CDC capture jobs to *1000*: ``` --To set values on secondary. These are used after failover. exec rdsadmin.dbo.rds_set_configuration 'cdc_capture_maxtrans', 1000; ``` To set the CDC job parameters on the principal, use [sys.sp\$1cdc\$1change\$1job](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql) instead. # Using SQL Server Agent for Amazon RDS With Amazon RDS, you can use SQL Server Agent on a DB instance running Microsoft SQL Server Enterprise Edition, Standard Edition, or Web Edition. SQL Server Agent is a Microsoft Windows service that runs scheduled administrative tasks that are called jobs. You can use SQL Server Agent to run T-SQL jobs to rebuild indexes, run corruption checks, and aggregate data in a SQL Server DB instance. When you create a SQL Server DB instance, the master user is enrolled in the `SQLAgentUserRole` role. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For more information, see [SQL Server Agent](http://msdn.microsoft.com/en-us/library/ms189237) in the Microsoft documentation. **Note** Avoid scheduling jobs to run during the maintenance and backup windows for your DB instance. The maintenance and backup processes that are launched by AWS could interrupt a job or cause it to be canceled. In Multi-AZ deployments, SQL Server Agent jobs are replicated from the primary host to the secondary host when the job replication feature is turned on. For more information, see [Turning on SQL Server Agent job replication](#SQLServerAgent.Replicate). Multi-AZ deployments have a limit of 10,000 SQL Server Agent jobs. If you need a higher limit, request an increase by contacting Support. Open the [AWS Support Center](https://console.aws.amazon.com/support/home#/) page, sign in if necessary, and choose **Create case**. Choose **Service limit increase**. Complete and submit the form. To view the history of an individual SQL Server Agent job in SQL Server Management Studio (SSMS), open Object Explorer, right-click the job, and then choose **View History**. Because SQL Server Agent is running on a managed host in a DB instance, some actions aren't supported: + Running replication jobs and running command-line scripts by using ActiveX, Windows command shell, or Windows PowerShell aren't supported. + You can't manually start, stop, or restart SQL Server Agent. + Email notifications through SQL Server Agent aren't available from a DB instance. + SQL Server Agent alerts and operators aren't supported. + Using SQL Server Agent to create backups isn't supported. Use Amazon RDS to back up your DB instance. + Currently, RDS for SQL Server does not support the use SQL Server Agent tokens. ## Turning on SQL Server Agent job replication You can turn on SQL Server Agent job replication by using the following stored procedure: ``` EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'SQLAgentJob'; ``` You can run the stored procedure on all SQL Server versions supported by Amazon RDS for SQL Server. Jobs in the following categories are replicated: + [Uncategorized (Local)] + [Uncategorized (Multi-Server)] + [Uncategorized] + Data Collector + Database Engine Tuning Advisor + Database Maintenance + Full-Text Only jobs that use T-SQL job steps are replicated. Jobs with step types such as SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Replication, and PowerShell aren't replicated. Jobs that use Database Mail and server-level objects aren't replicated. **Important** The primary host is the source of truth for replication. Before turning on job replication, make sure that your SQL Server Agent jobs are on the primary. If you don't do this, it could lead to the deletion of your SQL Server Agent jobs if you turn on the feature when newer jobs are on the secondary host. You can use the following function to confirm whether replication is turned on. ``` SELECT * from msdb.dbo.rds_fn_get_system_database_sync_objects(); ``` The T-SQL query returns the following if SQL Server Agent jobs are replicating. If they're not replicating, it returns nothing for `object_class`. ![\[SQL Server Agent jobs are replicating\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/SQLAgentJob.png) You can use the following function to find the last time objects were synchronized in UTC time. ``` SELECT * from msdb.dbo.rds_fn_server_object_last_sync_time(); ``` For example, suppose that you modify a SQL Server Agent job at 01:00. You expect the most recent synchronization time to be after 01:00, indicating that synchronization has taken place. After synchronization, the values returned for `date_created` and `date_modified` on the secondary node are expected to match. ![\[Last time server objects were synchronized was 01:21:23\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/SQLAgentJob_last_sync_time.png) If you are also using `tempdb` replication, you can enable replication for both SQL Agent jobs and the `tempdb` configuration by providing them in the `@object_type` parameter: ``` EXECUTE msdb.dbo.rds_set_system_database_sync_objects @object_types = 'SQLAgentJob,TempDbFile'; ``` For more information on `tempdb` replication, see [TempDB configuration for Multi-AZ deployments](SQLServer.TempDB.MAZ.md). # SQL Server Agent roles RDS for SQL Server supports the following SQL Server Agent roles with different levels of permissions for managing jobs: + **SQLAgentUserRole** Permissions + Create and manage their own jobs, schedules, and operators + View the properties of their own jobs and schedules + Cannot view or manage jobs created by other users This role is suitable for users who need to create and manage their own jobs but do not require access to jobs created by other users. + **SQLAgentReaderRole** Permissions + All permissions of SQLAgentUserRole + View a list of all jobs and schedules, including those created by others + View the properties of all jobs + Review job history This role is suitable for users who need to monitor the status of all jobs but do not need to manage them. + **SQLAgentOperatorRole** Permissions + All permissions of SQLAgentUserRole and SQLAgentReaderRole + Execute, stop, or start jobs + Manage job history + Enable/disable jobs and schedules + View operators and proxies This role provides the most comprehensive permissions and is suitable for users who need to have full control over all jobs. Use the following command to assign the roles to your SQL Server login: ``` USE msdb; EXEC sp_addrolemember 'SQLAgentOperatorRole', 'username'; ``` ## Managing SQLAgentOperatorRole in RDS for SQL Server To view the current jobs, you must add the SQLAgentOperatorRole to your SQL Server login and remove it before disconnecting from your database. To visualize the SQL Server Agent tree in the SQL Server Management Studio, follow these instructions: **View SQL Server Agent on SQL Server Management Studio (SSMS)** 1. Using the RDS master credentials, login into the RDS SQL Server instance and grant the desired user the SQLAgentUserRole. ``` USE msdb GO IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = 'UserName') BEGIN CREATE USER UserName FROM LOGIN UserName END GO ALTER ROLE SQLAgentUserRole ADD MEMBER UserName GO GRANT ALTER ON ROLE::[SQLAgentOperatorRole] to UserName GO ``` These commands create the user on the `msdb` database, in case it doesn’t exists. It also adds the user on the SQLAgentUserRole, so the SQL Server Agent tree on SSMS can be seen. Finally, it grants alter permissions on the SQLAgentOperatorRole to the user. This allows the user to add/remove itself from that role. 1. To add yourself to the above-mentioned role, connect to the RDS SQL Server instance, with the user that needs to see the jobs, and run the following script. ``` use msdb go ALTER ROLE SQLAgentOperatorRole ADD MEMBER UserName GO ``` After this, right click on the **Jobs** folder, and choose **Refresh**. 1. When you perform this action, the **Jobs** tab displays a **\$1 ** (plus) button. Click to expand the the list of SQL Server Agent Jobs. 1. **Important** Before you disconnect from the RDS SQL Server instance, you need to remove yourself from the SQLAgentOperatorRole. To remove your login from the SQLAgentOperatorRole, run the following query before disconnecting or closing the Management Studio: ``` USE msdb GO ALTER ROLE SQLAgentOperatorRole DROP MEMBER UserName GO ``` For more information, see [Leveraging SQLAgentOperatorRole in RDS SQL Server](https://aws.amazon.com/blogs/database/leveraging-sqlagentoperatorrole-in-rds-sql-server/). # Adding a user to the SQLAgentUser role To allow an additional login or user to use SQL Server Agent, log in as the master user and do the following: 1. Create another server-level login by using the `CREATE LOGIN` command. 1. Create a user in `msdb` using `CREATE USER` command, and then link this user to the login that you created in the previous step. 1. Add the user to the `SQLAgentUserRole` using the `sp_addrolemember` system stored procedure. For example, suppose that your master user name is **admin** and you want to give access to SQL Server Agent to a user named **theirname** with a password **theirpassword**. In that case, you can use the following procedure. **To add a user to the SQLAgentUser role** 1. Log in as the master user. 1. Run the following commands: ``` --Initially set context to master database USE [master]; GO --Create a server-level login named theirname with password theirpassword CREATE LOGIN [theirname] WITH PASSWORD = 'theirpassword'; GO --Set context to msdb database USE [msdb]; GO --Create a database user named theirname and link it to server-level login theirname CREATE USER [theirname] FOR LOGIN [theirname]; GO --Added database user theirname in msdb to SQLAgentUserRole in msdb EXEC sp_addrolemember [SQLAgentUserRole], [theirname]; ``` # Deleting a SQL Server Agent job You use the `sp_delete_job` stored procedure to delete SQL Server Agent jobs on Amazon RDS for Microsoft SQL Server. You can't use SSMS to delete SQL Server Agent jobs. If you try to do so, you get an error message similar to the following: ``` The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'. ``` As a managed service, RDS is restricted from running procedures that access the Windows registry. When you use SSMS, it tries to run a process (`xp_regread`) for which RDS isn't authorized. **Note** On RDS for SQL Server, only members of the sysadmin role are allowed to update or delete jobs owned by a different login. For more information, see [ Leveraging SQLAgentOperatorRole in RDS SQL Server](https://aws.amazon.com/blogs/database/leveraging-sqlagentoperatorrole-in-rds-sql-server/). **To delete a SQL Server Agent job** + Run the following T-SQL statement: ``` EXEC msdb..sp_delete_job @job_name = 'job_name'; ``` # Working with Amazon RDS for Microsoft SQL Server logs You can use the Amazon RDS console to view, watch, and download SQL Server Agent logs, Microsoft SQL Server error logs, and SQL Server Reporting Services (SSRS) logs. ## Watching log files If you view a log in the Amazon RDS console, you can see its contents as they exist at that moment. Watching a log in the console opens it in a dynamic state so that you can see updates to it in near-real time. Only the latest log is active for watching. For example, suppose you have the following logs shown: ![\[An image of the Logs section from the Amazon RDS console with an error log selected.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/logs_sqlserver.png) Only log/ERROR, as the most recent log, is being actively updated. You can choose to watch others, but they are static and will not update. ## Archiving log files The Amazon RDS console shows logs for the past week through the current day. You can download and archive logs to keep them for reference past that time. One way to archive logs is to load them into an Amazon S3 bucket. For instructions on how to set up an Amazon S3 bucket and upload a file, see [Amazon S3 basics](https://docs.aws.amazon.com/AmazonS3/latest/userguide/AmazonS3Basics.html) in the *Amazon Simple Storage Service Getting Started Guide* and click **Get Started**. ## Viewing error and agent logs To view Microsoft SQL Server error and agent logs, use the Amazon RDS stored procedure `rds_read_error_log` with the following parameters: + **`@index`** – the version of the log to retrieve. The default value is 0, which retrieves the current error log. Specify 1 to retrieve the previous log, specify 2 to retrieve the one before that, and so on. + **`@type`** – the type of log to retrieve. Specify 1 to retrieve an error log. Specify 2 to retrieve an agent log. **Example** The following example requests the current error log. ``` EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 1; ``` For more information on SQL Server errors, see [Database engine errors](https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors) in the Microsoft documentation. # Working with trace and dump files for Amazon RDS for SQL Server This section describes working with trace files and dump files for your Amazon RDS DB instances running Microsoft SQL Server. ## Generating a trace SQL query ``` 1. declare @rc int 2. declare @TraceID int 3. declare @maxfilesize bigint 4. 5. set @maxfilesize = 5 6. 7. exec @rc = sp_trace_create @TraceID output, 0, N'D:\rdsdbdata\log\rdstest', @maxfilesize, NULL ``` ## Viewing an open trace ``` 1. select * from ::fn_trace_getinfo(default) ``` ## Viewing trace contents ``` 1. select * from ::fn_trace_gettable('D:\rdsdbdata\log\rdstest.trc', default) ``` ## Setting the retention period for trace and dump files Trace and dump files can accumulate and consume disk space. By default, Amazon RDS purges trace and dump files that are older than seven days. To view the current trace and dump file retention period, use the `rds_show_configuration` procedure, as shown in the following example. ``` 1. exec rdsadmin..rds_show_configuration; ``` To modify the retention period for trace files, use the `rds_set_configuration` procedure and set the `tracefile retention` in minutes. The following example sets the trace file retention period to 24 hours. ``` 1. exec rdsadmin..rds_set_configuration 'tracefile retention', 1440; ``` To modify the retention period for dump files, use the `rds_set_configuration` procedure and set the `dumpfile retention` in minutes. The following example sets the dump file retention period to 3 days. ``` 1. exec rdsadmin..rds_set_configuration 'dumpfile retention', 4320; ``` For security reasons, you cannot delete a specific trace or dump file on a SQL Server DB instance. To delete all unused trace or dump files, set the retention period for the files to 0.