Import a MS SQL Database to Amazon RDS

Introduction

Nowadays, almost all applications use databases to store and process information. Sometimes, there are problems with database maintenance. It may come sooner or later, it doesn't matter, but this moment will come, and it may happen that the database will become unavailable. I have more than 5 years of experience in the development of program software and all the companies where I worked this moment when the database became unavailable came. Another problem that I encountered was the lack of capacity of the server that hosts the database, or the excessive capacity and high cost of the server. More recently, I began to think about whether it is better to transfer data to a cloud provider and pay for its storage. I was especially bribed by the fact that I do not need to maintain the operating system on which it all works. In this article, I will show you how to convert any MS SQL database to Amazon RDS, I will show you how to create a database in the cloud, connect it to MS SQL, deploy from a database backup. Immediately, I note that this article was created for training and I will use a bus-planned AWS account and a free database from Microsoft.

Creating a RDS Database

The first thing to do is create a database in Amazon RDS and configure it. Let's go to the RDS tab and choose to create a new MS SQL Server database (Image 1). For our example, using the RDS Free Usage Tier is fine, but for real projects, if you have a large database, you need to think carefully about the correct instance size for the database.

Next, click the Next button and set the RDS parameters such as the version of SQL Server DB instance type and ect. In the DB instance identifier section, select a name for the database and enter the login and password (Image 2).

Next, go to the Configure advanced settings section and be sure to check the port 1433 for MS SQL and specify the VPC, if not, then create a new one and set Public acceescability to true. As you can see in image 3, we have created a database.

Next, let's check the connection to the database. To do this, go to MS Server Management Studio and specify the parameters for connecting to the database, Endpoint and the login and password specified earlier. If the connection is successful, you should see a list of databases from RDS. Since we do not have any database yet, we will only display rdsadmin (Image 4).

This completes the first stage.

Creating a S3 Bucket for Backup

The second step for successful completion is creating an S3 bucket in the same region as rds. To do this, let's create an S3 bucket in the same region, and this is important, and load the file into the .bak extension. For our example, I used Microsoft Adventure works, which is fine for our example, but for a real project, you should use your database .bak file (Image 5).

This completes the second stage.

Creating a New Option Group

The third step is to create a new option group, because by default, you cannot create any changes in it.
To do this, go to Option Group in Amazon RDS (Image 6) and click to Create Group.

Next, we will give a name to our group and indicate the engine and its version (Image 7).

Next, add a new option to the option we just created and add SQLSERVER_BACKUP_RESTORE (Image 8).

This step is complete.

Connecting the New Option Group

Fourth, we connect our option group instead of the default.
To do this, change the mind in the settings of our RDS and change the OptionGroup in the settings (Image 9).

Next, tap to agree with the modification of the DB instance (Image 10).

This step is complete.

Restore the Database

Fifth - connect and run the database recovery script.
Now we just have to put in a script to restore the database. To do this, let's create a new request.

exec msdb.dbo.rds_restore_database

@restore_db_name='CodeProjectDatabase',

@s3_arn_to_restore_from='arn:aws:s3:::s3bucketrestore/AdventureWorks2016.bak'

Where:

• restore_db_name is the DB name
• s3bucketrestore - S3 bucket name
• AdventureWorks2016.bak is the name of the .bak file

The result is shown in Image 11.

You can check the status with the command:

exec msdb.dbo.rds_task_status

The result should be successful, then your database is exactly restored.

Conclusion

As shown in this article, the transition to cloud storage is not a difficult process if you follow all the suggested steps.

History

• 19^th July, 2021: Initial version