How to Upload Database to Aws Postgres Rdb

There are multiple means to deploy the databases in the AWS RDS SQL Server. Suppose you want to migrate your on-premises SQL Server database to AWS managed relational database service and yous have a critical application and require minimal (nearly zilch) downtime. For this purpose, you tin use the AWS Database Migration Service (AWS DMS) in a combination of SQL Server native backups.

You can use the on-premises SQL Server as a source database; however, for sit-in of the steps, I volition utilise the SQL Server on EC2 instance.

Configuring SQL Server on EC2 instance every bit a source database

For the demonstration, nosotros use the Amazon Machine Prototype (AMI) preconfigured with the SQL Server 2019. Log in to your AWS console and search for SQL Server 2019 AMI.

Information technology highlights all available AMI for SQL Server 2019. I choose the Microsoft Windows Server 2019 with SQL server 2019 standard.

You can get through the remaining steps and build the SQL Server on an EC2 case. Refer to the article for detailed steps on AWS SQLShack for detailed instructions.

An EC2 case is in a running state, and it takes some time to perform the two/2 status check.

Open the EC2 instance properties, and click on Connect.

In the Connect to the instance page, click on RDP client. From here, download the remote desktop file.

Click on Go Countersign, input your PEM secret fundamental, and information technology displays the password on the screen. You lot require this password for an RDP session on the EC2 Windows server.

Specify your administrator password and become an RDP session, as shown beneath.

This EC2 example is having preinstalled SQL Server 2019. Therefore, for a sample database, browse to Microsoft docs and download the [AdventureWorks2019.bak] file.

Restore the [AdventureWorks2019] database in SQL Server on EC2 case with the post-obit script.

Employ [ primary ] RESTORE DATABASE [ AdventureWorks2019 ] FROM DISK = Northward 'C:\SQL\AdventureWorks2019.bak' WITH FILE = 1 , Motility Due north 'AdventureWorks2017' TO Due north 'C:\Programme Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Information\AdventureWorks2019.mdf' , Motion N 'AdventureWorks2017_log' TO N 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Information\AdventureWorks2019_log.ldf' , NOUNLOAD , STATS = 5

Before starting the database migration, yous can take a Total database fill-in for the [AdventureWorks2019] database. Nosotros will restore this backup on the AWS RDS SQL Server.

BACKUP DATABASE [ AdventureWorks2019 ] TO DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Fill-in\AdventureWorks2019.bak' WITH NOFORMAT , NOINIT , NAME = Due north 'AdventureWorks2019-Total Database Backup' , SKIP , NOREWIND , NOUNLOAD , STATS = x Become

SQL Server uses the log sequence number for the transaction log records in the database. We can use this LSN in the database migration job configurations.

Execute this script on the [AdvenutreWorks2019] database and note-downward the LSN value.

SELECT [ Current LSN ] , [ Begin Fourth dimension ] , Description FROM fn_dblog ( Cipher , Cipher ) Where [ Transaction Proper noun ] = 'Backup:CommitDifferentialBase'

Restore the full database backup on the AWS RDS SQL Server

In this step, we volition restore the total fill-in taken earlier in the AWS RDS SQL Server instance. Earlier you movement forrard, you should have the post-obit prerequisites.

• AWS S3 bucket: Hither, I use the S3 bucket [SQLShackDemo17112020]
• RDS SQL Server: Here, I employ the RDS instance [myrdssql] with SQL Server 2019

Both the S3 bucket and RDS example should exist in an AWS region.

Upload the backup file in the AWS S3 bucket

Open up the AWS S3 bucket [SQLShackDemo17112020] and upload the full backup of the [AdventureWorks2019] database.

Restore database fill-in from S3 to RDS SQL

We need to restore the backup stored from the S3 bucket to AWS RDS SQL Server. To enable the restoration, practise the following tasks.

• Create an IAM policy and part to integrate S3 saucepan and RDS
• Create an pick group with SQLSERVER_BACKUP_RESTORE, modify the RDS example to use the new option grouping

You tin can refer to the article, AWS RDS SQL Server migration using native backups for detailed instructions.

In the beneath script, specify the ARN for backup stored in AWS S3 saucepan and execute on the RDS instance.

exec msdb . dbo . rds_restore_database @ restore_db_name = 'AdventureWorks2019' , @ s3_arn_to_restore_from = 'arn:aws:s3:::sqlshackdemo17112020/AdventureWorks.bak' ;

Information technology initiates a database restoration task and returns the job id.

Track the database restoration task condition using the post-obit script.

Execmsdb . dbo . rds_task_status@ task_id = 4

As shown beneath, the database restoration task is successful for the [AdventureWorks2019] database.

Creating an AWS DMS replication instance

We need to configure an AWS DMS replication example for database migration. In the AWS Web Panel, launch Database Migration Service. It gives the following AWS DMS dashboard.

Click on Create replication case. On the replication instance page, enter the following information.

• DMS instance proper name
• Descriptive Amazon resource name (Optional)
• Description
• Choose an instance class
• AWS DMS version: You can go with the prepopulated DMS version

• VPC: Select the Amazon Virtual Private deject for your replication instance
• Multi-AZ: If yous want to configure the high availability for the DMS example, cull the Multi-AZ configuration
• Publicly accessible: If you want to assign a public IP address to your AWS DMS instance, put a tick on the checkbox

We can go with the default configuration in the remaining options. Click on Create.

Information technology creates the AWS DMS instance in a few minutes. Its condition should be Available before we proceed to the next step.

Configure the Source and Target endpoints

We need endpoints for both the source (On-premise or SQL Server on EC2 instance) and Target (RDS SQL Server) for the database migration task.

In the AWS DMS, click on the Endpoints.

Source Endpoint

• Endpoint type: Source endpoint
• Endpoint identified: Give a unique proper noun for your source endpoint
• Descriptive Amazon Resource Name( optional)
• Source engine: Choose Microsoft SQL Server
• Server name: Specify the public DNS name of your AWS EC2 instance or the FQDN of your on-premise SQL Server.
• Port: Specify the SQL Server network port. The default port is 1433.
• User name and countersign: Enter the credentials for connecting to SQL Server example. Information technology should accept sysadmin permissions so that DMS can configure the replication or Change data capture(CDC) procedure.
• Database name: Enter the source database name that we want to migrate to the AWS RDS SQL Server.

Specify VPC for your AWS account and click on Create endpoint.

Open the source endpoint and go-to connection. Click on Test Connection; its status should be Successful, as shown below.

Target Endpoint

Similar to the source endpoint, we need to create the target endpoint for AWS RDS SQL Server.

• RDS instance: Put a check on Select RDS DB instance and cull your target RDS from the drop-down

The remaining configurations are similar to the source endpoint.

In one case the target endpoint for the RDS instance is created, its test connection should be successful, as shown below.

Create a database migration task

In the AWS DMS, navigate to database migration tasks. Nosotros exercise not have whatever existing DMS chore. Therefore, it shows the blank screen.

Click on Create database migration chore.

In the database migration task, do the following configurations.

• Chore identifier: Requite a unique name for the DMS task
• Descriptive Amazon Resource Proper noun (optional)
• Replication example: Select the DMS replication instance from the driblet-down
• Source database endpoint: Choose the source database endpoint from the drop-down
• Target database endpoint: Cull the target database endpoint from the drop-down value

• Migration type: Nosotros already restored the total backup from the source to RDS SQL Server. Therefore, choose the selection – Replicate information changes merely

  

• In the CDC state mode, choose selection Specify log sequence number and enter the LSN in the System change number box
• In the Target tabular array preparation fashion, cull to Do Zippo

• Put a cheque on Enable Data Validation. Using this pick, AWS DMS compares data at source and target databases. It also ensures that data migration is successful and consistent

  

• In the Selection rules, we can filter the objects for specific schema, table or pattern. Hither, we did not specify any specific schema or pattern. Therefore information technology replicates complete data changes

Click on the Create task button.

It starts configuration for database migration chore.

Its status changes to replication ongoing after a few minutes.

Y'all can click on tabular array statistics. It populates all tables. Hither, you can monitor the inserts, delete, updates, DDL, full rows once the table is validated on both source and target. Information technology takes a few minutes for populating the information depending upon your database size.

You can also become back to your source case and view that a local publication exists. The AWS database migration service configures it for populating data betwixt the source and destinations.

AWS DMS uses the replication and CDC for the database migrations.

• SQL Server replication for capturing data changes for tables with primary keys. AWS DMS automatically configures the replication if you provide an endpoint user with the sysadmin permissions
• Change Data Capture ( CDC) for tables without primary keys. You lot demand to enable the MSCDC at the database level and add the tables manually

Refer to the commodity, Using a Microsoft SQL Server database every bit a source for AWS DMS for more details.

Information technology continuously replicates the data from the source to the target AWS RDS SQL Server. You can stop data replication during the cutting-off time and betoken your awarding to connect with RDS endpoints.

Conclusion

In this commodity, we explored the AWS database migration service for migrating a database from source on-premise or SQL Server on EC2 instance to AWS RDS SQL Server with zero reanimation. It is suitable for disquisitional databases where nosotros cannot afford much downtime.

• Author
• Recent Posts

Howdy! I am Rajendra Gupta, Database Specialist and Builder, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of feel.

I am the author of the volume "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical manufactures on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of ane of the biggest free online collections of manufactures on a single topic, with his fifty-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I take been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions honor in 2020.

Personal Web log: https://www.dbblogger.com
I am e'er interested in new challenges so if you lot need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

beattydeme1968.blogspot.com

Source: https://www.sqlshack.com/migrating-your-on-premises-sql-databases-to-aws-rds-sql-server-using-aws-dms/

Share this post