Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and re-sizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need.
In this blog we are looking into common database admin tasks for AWS RDS.
We cannot perform all the admin task on MS SQL RDS.
When we launch a RDS instance its come up with a 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.
Tasks
How to Read error log….?
How to Bring Database online…?
How to Rename database in RDS SQL Server instance….?
How to do backups/restore of database….?
How to Shrink database files…?
How to Drop a database on RDS SQL Server instance…?
How to Bring Database online…?
Taking the database offline in SQL Server is simple as it is , i.e Login to the SQL Server using SQL Server management studio and navigate to the database. Right click on the database ->Tasks->Take Offline.
But in AWS RDS SQL Server its quiet different we can,t bring it online using :
Alter DATABASE cloudsbaba SET ONLNE
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.
EXEC rdsadmin.dbo.rds_set_database_online 'cloudsbaba'
How to Read 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.
EXEC rdsadmin.dbo.rds_read_error_log
Alternatively, you can download logs using the RDS console.
Navigate to AWS RDS Console. Click on database and navigate.
Click Logs and Event
Scroll down
Select any log/error file and Download
How to Rename database in RDS SQL Server instance….?
To rename a database in RDS SQL Server instance,we use the stored procedure rds_modify_db_name
exec rdsadmin.dbo.rds_modify_db_name 'Demodb','Cloudsbaba'
How to do backups/restore of database….?
Database backups is the most important tasks for all database admins. In RDS there is a concept of native backup to S3.
This script is used to take backup of database to S3 bucket:
exec msdb.dbo.rds_backup_database
@source_db_name='cloudsbaba',
@s3_arn_to_backup_to='arn:aws:s3:::Demobucket/cloudsbaba.bak';
check the status of running backup
exec msdb.dbo.rds_task_status
compress the database backups and check the configuration
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
exec rdsadmin.dbo.rds_show_configuration
compression
EXEC rdsadmin.dbo.rds_set_configuration 'S3 backup compression', 'true'
This script is used to take restore of database from S3 bucket to RDS
exec msdb.dbo.rds_restore_database @restore_db_name='Demodb', @s3_arn_to_restore_from='arn:aws:s3:::Demobucket/cloudsbaba.bak'
How to Shrink database files…??
RDS is not allowing you to shrink tempdb files with this script:
USE [tempdb] GO DBCC SHRINKFILE (N'tempdev' , 50) GO
For shrinking the database file we use the store procedure
exec msdb.dbo.rds_shrink_tempdbfile @temp_filename = N'tempdev', @target_size = 50;
How to Drop a database on RDS SQL Server instance…?
if your database is in Multi AZ then use this script to drop database
EXECUTE msdb.dbo.rds_drop_database N'Demodb'
