In this article, we will review common database administration tasks for AWS RDS (Relational Database Service) SQL Server instance. RDS does not provide access to some of the system procedures and tables that require advanced privileges. So, we cannot perform all the administration tasks on the RDS SQL Server instance.

When we launch a database instance running on the SQL Server engine in RDS, the instance comes with the default database “rdsadmin”. The databases “msdb” and “rdsadmin” have few stored procedures which start with “rds_” to manage the databases and instance. Below are some of the administration tasks that can be done RDS SQL Server instance using stored procedures in “msdb” and “rdsadmin” database.

It is assumed that the reader has basic knowledge about AWS RDS with SQL Server.

Bringing database online

Taking the database offline in AWS RDS SQL Server instance is like taking a database offline in an on-premises SQL Server instance. i.e. Login to the SQL Server using SSMS (SQL Server management studio and navigate to the database. Right click on the database -> Tasks -> Take Offline.

Also, you can use below T-SQL script to take the database offline. Replace AdventureWorks with the name of your database.

When you try to bring the database online using SQL Server management studio, it throws an error as shown in the below image.

Similarly, we cannot bring the database online using T-SQL script as well.

To bring the database online in AWS RDS SQL Server instance, use the stored procedure called “rds_set_database_online” which is in “rdsadmin” database.

Please refer to the below T-SQL Script. Replace AdventureWorks with the name of your database.

Reading error log

We cannot use “xp_readerrorlog” on RDS SQL Server instance to read error log. Instead, RDS provides a stored procedure “rds_read_error_log”.

We can pass parameters @index and @type to filter the log. Pass @index =0 to retrieve the current log and @index=1 to retrieve the previous log.

Specify @type =1 to retrieve SQL Server error log and @type =2 to retrieve the agent log.

Alternatively, you can download logs using the RDS console.

To download the logs, Navigate to AWS RDS console. Click on databases and click on the database instance. Click on Logs and events.

Select the log file with the name log/ERROR for the current error log and click on Download.

Renaming database in RDS SQL Server instance.

We cannot rename the database using the SQL Server management studio or by using stored procedure sp_renamedb. It throws error “user does not have permission”.

To rename a database in RDS SQL Server instance, use the stored procedure “rds_modify_db_name” which is in the “rdsadmin” database. Please refer to the below T-SQL Script.

If the database instance is Multi-AZ (Availability Zone) then remove Multi-AZ, rename the database using the above procedure and apply Multi-AZ back.

Enabling and disabling CDC on AWS RDS SQL Server instance

To enable change data capture on RDS instance use stored procedure “rds_cdc_enable_db”. Please refer to the below T-SQL Script.

To disable change data capture on RDS SQL Server instance, use the stored procedure “rds_cdc_disable_db” which is in the “msdb” database.

Compress native backups to S3 Storage

By default, the compression for native backups is disabled on the RDS SQL Server instance. To check the current compression setting use the stored procedure “rds_show_configuration” which is in “rdsadmin” database.

To enable the compression on native backups, use the stored procedure “rds_set_configuration” which is in the “msdb” database.

By using the stored procedure “rds_set_configuration”, you can also set other configuration like the dump file and trace file retention period. By default, AWS RDS stores trace files and dump files which are 7 days older. If you want to change the retention period use the above stored procedure and specify the value which is in minutes.

Shrinking database files

Shrinking user databases is like shrinking databases in the on-premises SQL Server instance. You can use DBCC SHRINKFILE or use SQL Server management studio GUI. “AdventureWorks_log” is the name of the database log file.

RDS will not allow shrinking tempdb files using the above method.

To shrink tempdb database files, use stored procedure “rds_shrink_tempdbfile” located in “msdb”.

Please refer to below T-SQL script to shrink tempdb database files.

Drop a database on AWS RDS SQL Server instance

If your database instance is Multi-AZ, then use stored procedure “rds_drop_database” to drop the database. Please refer to the below T-SQL script. Replace “TEST” with the name of your database.

Accessing SQL Server agent

By default, the master account will be able to access the SQL Server agent. In case if you want to give SQL Server agent access to logins other than your master account, please follow the below steps.

Create a server level login and create a user in the “msdb” database and link to the user you created. Now add the user to the “SQLAgentUserRole” role.

Login to AWS RDS SQL Server using SQL Server management studio. Navigate to Security -> Logins -> Right click and click on New Login.

Enter the Login name, password and click on User Mapping.

Select the “msdb” database, enable “SQLAgentUserRole” and click on Ok.

You can also create a login and give the privilege to access the SQL Server agent using T-SQL. Please refer to the below T-SQL script. Replace “yourpassword” with your desired password “AgentUser2” with your login name.

You can create jobs in SQL Server agent using SQL Server management studio like how you do in an on-premises server. But when you try to delete the job in AWS RDS instance using SQL Server management studio, it throws an error as it tries to execute extended stored procedure “xp_regread” and the master user does not have permission to execute this extended stored procedure.

Use below T-SQL script to delete the job. Replace “JobToDelete” with the name of your job.

Database instance configuration

You can use “sp_configure” to check the current configuration but cannot use it to modify the existing configuration. Please follow the below steps to change the configuration of the RDS SQL Server instance.

Navigate to RDS console and click on parameter groups. Click on Create parameter group.

In Create parameter group page, select the group and enter name, description and click on Create.

In this case, I selected “sqlserver-ee-14.0” as the database instance I created is enterprise edition and engine version is “14.00.3049.1.v1”

Now select the parameter group you created, and click on Edit in the Parameter group actions drop down.

Search for the configuration you want to modify. If the Modifiable value is true, we can edit the configuration value else we cannot change the configuration value. Change the value and click on Save Changes.

In this case, I changed “Ad Hoc Distributed Queries” from 0 to 1.

Now you need to associate the parameter group you created to the RDS SQL Server instance.

To associate the parameter group to the database instance, navigate to the RDS console. Click on the database and select the database instance and click on Modify.

In Modify DB instance page, under database options, select the new parameter group which you created above and click on Continue at the bottom of the page.

Once you click on Continue, it will show the list of the modifications and scheduling of the modification. If you choose “Apply during the next scheduled maintenance window” all the listed modifications were applied during the next immediate maintenance window. It will display the maintenance window timings under the option.

If you choose “Apply Immediately”, the modifications were applied as soon as you click on “Modify DB Instance”.

Login to RDS SQL Server instance using SQL Server management studio and run the stored procedure sp_configure to verify the configuration change. Please refer to the below image. The config value has been changed from 0 to 1.