Ramasankar Molleti Blog

It’s been a long time since i posted in my blog. One of the common request i have been asked by my colleagues, friends about “Is there a way to automate backups that are taken on the SQL Server database server running on EC2 instance to Amazon S3 storage?”  Today, I would like to show how we can achieve this.

Requirement: I would like to keep one days of backups on the disk and also maintain remaining backups on Amazon S3 storage for 30 days and later archive them.

I’m assuming that you have a mechanism to take backups to Disk.

•      Attach a S3 policy to Current Database Server running on EC2 instance. Instance need access to read and write permissions. You may use the below policy.

{    "Version": "2012-10-17",
"Statement": [        {
"Effect": "Allow",
"Action": [
"s3:Get*",
"s3:List*", "s3:Put*"
],
"Resource": [ "arn:aws:s3:::Name_of_the_bucket/*"
]
}
]
}

• Once you attach above the policy to IAM ROLE on EC2 instance, EC2 Instance gets access to the Amazon s3 bucket.
• Next, Install AWS CLI command line tool on the EC2 instance. You may download here 
• Now, let’s begin the actual process.  I’m assuming that you already have backups jobs running on database server based on your schedule which takes the backups on disk.  Include the below powershell script in the second step after taking backups to disk.

 
$bucketname = 'S3_Bucket_Name/' # S3 bucket name for copying backups
$backuppath = 'B:\MSSQL\Backups' # Backup location on the disk
$Region = 'us-east-1'
$env:Path += ';C:\Program Files\Amazon\AWSCLI'
 $cmd="aws s3 sync $backuppath s3://$bucketname --region $Region" 
 push-location 'C:\Program Files\Amazon\AWSCLI\'
 Get-Location 
 Invoke-Expression $cmd
 Pop-Location

• 
• The above step will sync the backups to S3, something like this.

• Have we go you have backups sync back to S3. The advantage of this is you can reduce the cost of disk storage and have backups highly available and reliable. You don’t have to worry about disk capacity and maintaining redundancy at disk level. Amazon s3 storage has 99.999999999% durability and 99.99% availability.
• Now, you can create a life cycle policy to keep the backups on s3 storage for 30 days and archive them to Glacier storage (Much Cheaper storage). You may follow the link here

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Recently i worked on migrating sql server databases running on amazon EC2 instances to Amazon RDS instances. Starting from 27th Jul 2016 Amazon announced you can ship your databases from ec2 to rds using native backup and restore method. Let’s see how it works.

Steps:

• Assume you have RDS Instance ready with default setting
• Create a custom option group “NativeBackup” and enable S3 bucket full access. You may use the below IAM rule

RDSAccessToS3
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:*",
"Resource": "*"
}
]
}

• Right click on Custom option group and then click on Add option –> Select SQLSERVER_BACKUP_RESTORE option — > Apply Immediately

• Now you have custom group setup with native backup and restore feature and correct permissions to S3 bucket. Now, Add this group to RDS instance. Right click on RDS instance –> Click on Modify –> Navigate to Option group —> Assign the NativeBackup group which was created in the step 2 –> Click Modify instance

• Now, Copy the backup of EC2 instances to S3 bucket. You may use AWS CLI command line tool to copy from one EC2 instance to S3 bucket

Aws s3 cp \\ec2backuplocations <a href="s3://Infra_Bucket" target="_blank" rel="noreferrer" data-mce-href="s3://Infra_Bucket">s3:<wbr>/<wbr>/</a>Buket_Name &nbsp;--profile “your profilename"

• Once you are done with copying the backup to S3 location, then you may use the below script to restore the database from backup

  
  use master
  
  go
  
  exec msdb.dbo.rds_restore_database
  
  @restore_db_name='TestDB',
  
  @s3_arn_to_restore_from='arn:aws:s3:::Bucket_Name/testdb.bak';

• Restore will begin, you may use the below stored procedure to get the status of the restore process

  exec msdb.dbo.rds_task_status

• That’s all about the restore! Below are the possible errors you may encounter while migrating to RDS instance

Error1:

Aborted the task because of a task failure or a concurrent RESTORE_DB request.
Database TestDB cannot be restored because there is already an existing database wih the same family_guid on the instance.

Reason: If you have same file guid for data or log file exists then you cannot restore to rds instance. The file guid’s are unique to each database. There is a possibility to get the same fileguid based on the name.

You may use sys.master_files view to get the details of file guid

Another reason for this error is you have already restored the same database and trying to restore, hence the error. RDS has the limitation that you cannot restore the same database file names on the same instance.

Resolution: Do not restore the same database names on the same RDS instance

Error2:

Msg 229, Level 14, State 5, Procedure rds_restore_database, Line 1 [Batch Start Line 45]
The EXECUTE permission was denied on the object ‘rds_restore_database’, database ‘msdb’, schema ‘dbo’.

Reason: You do not have permission to perform the restore

Resolution:

Please make sure the login you used have execute permission for the stored procedure rds_restore_database. If you are using master credentials and you still getting the same error then you have not granted full permission to S3 bucket or missing IAM roles for the S3 bucket. Please grant the access and re-try

Error3:

Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 73 [Batch Start Line 22]
A task has already been issued for database:TestDB  with task Id: 3, please try again later.

Reason: You have issued the restore command multiple times and hence the error.

Resolution: Do not issue the restore command twice for the same database. Kill the multiple restore processes and re-issue the command

References:

https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

https://www.brentozar.com/archive/2016/07/holy-cow-amazon-rds-sql-server-just-changed-everything/

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter

This is one of the handy script to get sql server error logs via email.

declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();

create table #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(max) )

insert #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';

create table #SQL_Log_Errors (
[logdate] datetime,
[Message] varchar (500) )

insert into #SQL_Log_Errors
select LogDate, Message FROM #ErrorLog
where (Message like '%err%'
or Message like '%warn%'
or Message like '%kill%'
or Message like '%dead%'
or Message like '%cannot%'
or Message like '%could%'
or Message like '%fail%'
or Message like '%not%'
or Message like '%stop%'
or Message like '%terminate%'
or Message like '%bypass%'
or Message like '%roll%'
or Message like '%truncate%'
or Message like '%upgrade%'
or Message like '%victim%'
or Message like '%recover%'
or Message like '%critical%'
or Message like '%IO requests taking longer than%')
AND Message not like '%errorlog%'
AND Message not like '%dbcc%'
AND Message not like '%checkdb%'
order by logdate desc

drop table #ErrorLog

declare @cnt int
select @cnt=COUNT(1) from #SQL_Log_Errors
if (@cnt > 0)
begin

declare @strsubject varchar(100)
declare @oper_email nvarchar(100)

set @oper_email = (select email_address from msdb.dbo.sysoperators where name = 'DBA')
select @strsubject='There are errors in the SQL Error Log on ' + @@SERVERNAME

declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H1>SQL Error Log Errors - ' + @@SERVERNAME + '</H1>' +
N'
<table border="1">' +
N'
<tr>
<th>Log Date</th>
' +
N'
<th>Message</th>
</tr>
' +
CAST ( ( SELECT td = [logdate], '',
td = [Message]
FROM #SQL_Log_Errors
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>
' ;

EXEC msdb.dbo.sp_send_dbmail
@from_address='',
@recipients= @oper_email,
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name='YourProfileName'

end

drop table #SQL_Log_Errors

go

Cheers

Ramasankar Molleti

MSDN:LinkedIn:Twitter