AWS: RDS - 1

RDS

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 resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups.

Backup & Restore SQL Server

Backup database to S3

  • Assumption

    • DB name: sample_db
    • S3 bucket name: sql-server-backup
  • Backup with built-in stored proc

    exec msdb.dbo.rds_backup_database 
    @source_db_name='sample_db', 
    @s3_arn_to_backup_to='arn:aws:s3:::sql-server-backup/sample_db_20191221.bak', 
    @overwrite_S3_backup_file=1;
    
  • Track status

    exec msdb.dbo.rds_task_status @db_name='sample_db'
    

Restore DB into EC2

  • Restore with powershell

    #Copy-S3Object -BucketName 'nsw-prod-s3-sql-backups' \ 
    # -Key sample_db-20190531011003.bak \
    # -LocalFile L:\Backups\Automated\sample_db-20190531011003.bak
    
    Restore-SqlDatabase -ServerInstance 'localhost' -Database "sample_db" \
    -BackupFile "L:\Backups\Automated\sample_db-20190531011003.bak" \
    -ReplaceDatabase -KeepReplication -Verbose
    

Restore DB into RDS

  • Create IAM role which can access the backup file in the S3 bucket. e.g. RDS_RESTORE_S3_READONLY

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::sql-server-backup"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
            "Resource": [
                "arn:aws:s3:::sql-server-backup/*"
            ]
        }
    ]
    }
    
  • Create a new option group, or copy or modify an existing option group.

  • Add the SQLSERVER_BACKUP_RESTORE option to the option group.

  • Associate the IAM role with the option. The IAM role must have access to an S3 bucket to store the database backups. e.g. RDS_RESTORE_S3_READONLY

  • Associate the option group with the DB instance.

  • Make sure the RDS has enough space to restore the db backup.

  • Restore the database

    EXEC msdb.dbo.rds_restore_database 
    @restore_db_name='sample_db',
    @s3_arn_to_restore_from='arn:aws:s3:::sql-server-backup/sample_db_20191221.bak'
    GO
    
  • Check the progress

    EXEC msdb.dbo.rds_task_status 
    @db_name='sample_db'
    GO
    

Caveat

  • After you modify the storage size for a DB instance, the status of the DB instance is storage-optimization. The DB instance is fully operational after a storage modification.

  • You can’t make further storage modifications until six (6) hours after storage optimization has completed on the instance.

  • You can’t reduce the amount of storage for a DB instance after storage has been allocated.

Rename database

  • Rename db with built-in stored proc

    EXEC rdsadmin.dbo.rds_modify_db_name N'<OldName>', N'<NewName>'
    
  • Troubleshoot -The database could not be exclusively locked to perform the operation.

    # Kill connection
    EXEC sp_who 
    GO
    
    KILL <spid>
    GO
    
    EXEC sp_who 
    GO