The Server Is in the Process of Being Updated Please Try Again Later

By: | Updated: 2017-08-23 | Comments (xi) | Related: > Amazon AWS

Problem

In a previous postal service of this blog series we talked near SQL Server database native backup and restore in Amazon RDS. Are there any limitations nosotros demand to be aware of?

Solution

The ability to fill-in individual databases from a SQL Server RDS case has been a long-awaited characteristic for database administrators. As we saw in the first part of this article serial, SQL Server databases in Amazon RDS can be both backed up to and restored from Amazon S3 buckets with relative ease.

Nonetheless, this capability comes with some limitations. In this post, we will talk most some of the things you need to exist aware of.

No differential, transaction log or filegroup backup or restore

The rds_backup_database stored procedure allows you lot to create a full backup of a database. Also, the rds_restore_database lets you restore a database from its full backup file.

What these commands don�t let you practise is backup or restore transaction logs. In other words, there is no betoken-in-time-recovery (PITR) with SQL Server native backups in RDS.

The same blazon of restriction applies to differential or filegroup backups�SQL Server in RDS does non support differential or filegroup backups and restores.

We don�t consider this a huge hindrance to information recovery. Amazon RDS allows you to create scheduled instance snapshots and those snapshots are kept accessible for a rolling 35-day period. Y'all can restore the instance within 5 minutes of a specified time in the last xxx-five days.

The RDS instance backup with SQL Server native backup tin provide a nice disaster-recovery (DR) solution: y'all can use both to have a copy of the database bachelor. If y'all need upwardly-to-the-minute recovery within terminal thirty-5 days, you tin create a new example from the RDS snapshot. Anything beyond that can be recovered from native fill-in files in S3.

Cannot restore KMS-encrypted backups from S3 to on-bounds or EC2 SQL Servers

Suppose you want to take ane of the encrypted backups from S3 and restore it on-premises or an EC2 case or another cloud platform. Can you do that?

The respond is no. That�s considering the encryption is done by a server-side KMS key when RDS backs up the database and that key is not bachelor to your target SQL instance. In fact this is the error bulletin y'all volition get when yous effort to restore the encrypted back to an EC2 instance:

Msg 3241, Level 16, State 0, Line i
The media family on device 'C:\AdventureWorks_Encrypted.bak' is incorrectly formed. SQL Server cannot process this media family.

The solution is unproblematic: create a manual backup without encryption and import it in the target server. If the encrypted backup has the information y'all are looking for which is no longer available in the database�s electric current state, you lot can follow these steps:

Create an RDS instance in the aforementioned region which has the aforementioned version as the source organisation and has admission to the S3 bucket and the KMS key.
Restore the encrypted fill-in in that case with the KMS central.
Backup the database without encryption to S3.
Copy the backup file to EC2 or on-bounds server and restore from at that place.

You may exist wondering why you lot need a separate RDS instance to restore the encrypted backup. We will explain presently.

Cannot restore backups of TDE-enabled databases

Conversely, yous cannot migrate an on-premises database with Transparent Data Encryption (TDE) to RDS. TDE is available in Enterprise Editions of SQL Server and requires a number of steps to take outcome. First, a chief primal is created in the master database of the instance which is so used to protect a certificate. The document is then used to protect a database level encryption key. The database is then enabled for encryption with that key. A database backup therefore does non include the certificate or the master key. Trying to restore the fill-in in the RDS instance will neglect with an error bulletin like this:

Cannot find server document with thumbprint '<large hexadecimal string>'. RESTORE FILELIST is terminating abnormally.

To migrate these databases you can utilise, transmission processes like scripting out database structure and program code, exporting and importing information etc.

Cannot restore database in the same RDS instance

How many times have y'all been asked to restore a re-create of a database in the aforementioned example with a dissimilar name? Typically, if you have a database called �abc� in your product environment and desire to restore a previous version of it, yous wouldn�t overwrite the existing database. You would rename the database with something like �abc_old� and restore the backup as �abc�. Or, you lot would perhaps restore the backup every bit �abc_restored�.

None of these work with SQL Server native restore in Amazon RDS. If you have the original database present in the instance, y'all tin�t restore its backup equally a new database, nor volition it allow y'all to overwrite the existing database. Information technology does non help even if y'all rename the original database. In the post-obit lawmaking snippet, we are trying to restore the AdventureWorks database in the same case under a unlike name:

EXEC msdb.dbo.rds_restore_database       @restore_db_name = 'AdventureWorks_Restore',       @S3_arn_to_restore_from = 'arn:aws:s3:::rds-sqlserver-backup-bucket/Backup/AdventureWorks2014.bak'

If you run the rds_task_status stored procedure later some time, the error message in the �task_info� column will be like this:

Aborted the task because of a task failure or a concurrent RESTORE_DB request.

Task ID x (RESTORE_DB) exception: Database AdventureWorks_Restore cannot exist restored because in that location is already an existing database with the same file_guids on the instance.

Nonetheless, this works if you delete the original database, which is probably something DBAs wouldn�t want. Also, even if yous delete the original database and restore its fill-in, you cannot restore a 2d copy of the backup in the instance.

This is definitely annoying and understandably frustrating. The workaround is to:

Restore the backup in a divide, new RDS instance
Create an empty database in a SQL Server instance running in EC2 which has admission the new RDS case (aforementioned VPC, same Security Grouping etc.)
Using SQL Server Import Export and Wizard, copy the data and schema to the empty database in EC2. Carry in mind, you however demand to create all the views, stored procedures, UDFs, triggers and user accounts manually.
Fill-in the database in EC2 and save the backup in S3 where the original RDS instance tin can pick information technology up from
Run the restore command from the original RDS instance to restore the newly-created fill-in file

Obviously this is a long-winded process. After all, if you backup a database from 1 instance, you would expect it to be restorable in the same instance with a different database proper noun. By the looks of it, Amazon still needs to work on this use case.

Target RDS instance needs to have access to the S3 saucepan and KMS key

To restore the backup file to a divide RDS case, the target RDS case needs to take its option group enabled for SQLSERVER_BACKUP_RESTORE. Without this, RDS simply won�t know what to do and you will become an fault bulletin like this:

Database backup/restore option is not enabled yet or is in the process of being enabled. Please try over again later.

When y'all assign the SQLSERVER_BACKUP_RESTORE pick to the target RDS case�s option group, remember the selection should employ an IAM function that has access to the backup S3 bucket.

What happens when the IAM role has access to the S3 bucket but does not have admission to the KMS key used for encryption? When yous try to restore the encrypted database with the primal specified, you lot will go an error message and this is an example of it:

User: arn:aws:sts::xxxxxxxxxx:assumed-role/RDS-SQL-BACKUP-S3-UE/RDS-SqlServerBackupRestore is not authorized to perform: kms:DescribeKey on resource: arn:aws:kms:u.s.-e-1:xxxxxxxxxx

The IAM role therefore needs to take access to the KMS key likewise.

Grant database backup and restore permissions to users

The user business relationship running RDS SQL Server native backup and restore commands needs to have advisable permissions:

The user needs to be a member of the target database�s db_backupoperator office.
The user needs to be nowadays in the msdb database.
The user needs to have execute permission on the rds_backup_database, rds_restore_database, rds_cancel_task and rds_task_status stored procedures in the msdb database.

Here is a script to create such a user:

Use main  GO  CREATE LOGIN rds_backup_operator WITH Password = 'somecomplexpassword', DEFAULT_DATABASE=chief,     CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  GO   Apply msdb  Become  CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator  GO  GRANT EXECUTE ON msdb.dbo.rds_backup_database TO rds_backup_operator  GO  GRANT EXECUTE ON msdb.dbo.rds_restore_database TO rds_backup_operator  GO  GRANT EXECUTE ON msdb.dbo.rds_task_status TO rds_backup_operator  Become  GRANT EXECUTE ON msdb.dbo.rds_cancel_task TO rds_backup_operator  GO   USE whateverdb  GO  CREATE USER rds_backup_operator FROM LOGIN rds_backup_operator  Get  ALTER ROLE db_backupoperator ADD Member rds_backup_operator  GO

Maximum Native Fill-in File Size is 1 TB

At the time of this writing, the maximum backup size supported by RDS SQL Server native backup is 1 TB.

Everything needs to exist in the same AWS region

SQL Server native fill-in and restore works fine when the RDS instance, S3 saucepan and the KMS central (when using encryption) are all in the same AWS region.

For example, if you lot try to backup a database from RDS into a saucepan in unlike region, the process fails with an �Access Denied� error bulletin. In our tests, our RDS instance was running in the us-due east-1 (Due north Virginia) region and its selection group was enabled for SQL Server native backup and restore. The IAM role for the SQLSERVER_BACKUP_RESTORE selection had full admission to S3 buckets in both us-east-1 (Due north Virginia) and us-east-2 (Ohio) regions. When we ran the �rds_backup_database� command to backup a database to the S3 saucepan in Ohio region, it failed with an error message like this:

Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automatic backup.
Access Denied

You may too see an error message like this:

Delight specify a bucket that is in the same region as RDS instance

This may be a trouble when backups need to be stored in a carve up region for regulatory compliance. In such cases, we recommend creating the backup in an S3 bucket in the aforementioned region and copying/moving information technology to the final location with S3 cantankerous-region replication.

Conclusion

Although SQL Server native backup and restore is a slap-up improver to RDS� growing features, there are few things to be mindful of when using it for production purposes. We would recommend testing for different scenario-based use cases, particularly when encrypted backups need to exist restored.

Next Steps

Learn more almost SQL Server native fill-in and restore error messages from this AWS blog mail.
Examination database fill-in and restore under various encryption strategy (TDE, client side and server side encryption, etc.).
Automate the restore process with scripts.
Learn more about S3 cross region replication.

Related Manufactures

Popular Manufactures

Well-nigh the author

MSSQLTips author Sadequl Hussain Sadequl Hussain has been working with SQL Server since version vi.5 and his life as a DBA has seen him managing mission critical systems.

View all my tips

Commodity Last Updated: 2017-08-23

ridgwayraceiziendas.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/5042/limitations-of-sql-server-native-backup-and-restore-in-amazon-rds/