Data migration is an essential activity for those organizations that are moving from on-premises database technology to cloud offerings such as AWS RDS. An extremely helpful service that simplifies this task is the AWS Database Migration Service (DMS). With AWS DMS, for example, you can migrate data from an on-premises Microsoft SQL Server database to an AWS RDS PostgreSQL. I am going to explain the process of creating SQL Server parameters and configuring an AWS DMS replication instance in this post.
Preparing the SQL Server for AWS DMS
Before setting up AWS DMS, it is essential to configure your local SQL Server to allow external connections and ensure proper networking settings.
1. Enabling TCP/IP Connections in SQL Server
By default, SQL Server does not allow remote connections unless TCP/IP is explicitly enabled. Follow these steps to enable TCP/IP connections:
Open SQL Server Configuration Manager.
Navigate to SQL Server Network Configuration → Protocols for MSSQLSERVER.
Locate the TCP/IP protocol and right-click to select Enable.
Right-click on TCP/IP, select Properties, and navigate to the IP Addresses tab.
Under the IPAll section, set TCP Port to 1433 (leave TCP Dynamic Ports blank).
2. Configuring Windows Firewall Rules
After enabling TCP/IP, you need to allow inbound connections on port 1433 through Windows Firewall:
Open Windows Defender Firewall with Advanced Security.
Navigate to Inbound Rules → Add New Rule.
Select Port and click Next.
Choose TCP and enter 1433 in the Specific local ports field.
Click Next.
Choose 'Allow the connection'
Apply the rule to Domain, Private, and Public profiles.
Name the rule and complete the setup.
3. Restarting SQL Server
For the changes to take effect, restart the SQL Server service:
Open SQL Server Configuration Manager.
Select SQL Server Services.
Right-click SQL Server (MSSQLSERVER) and select Restart.
Once the SQL Server settings are configured, test the connection from another computer again using:
sqlcmd -S IP-Address -U Username -P Password
If you can connect successfully, your SQL Server is ready for migration.
Setting Up AWS DMS for Migration
Follow these steps to configure AWS DMS:
1. Creating a Replication Instance
Navigate to the AWS DMS Console.
Select Replication Instances and click Create Replication Instance.
Provide a name and choose an appropriate instance class.
Set the replication instance to public since the SQL Server is hosted on a local computer.
Security Group Configuration
DMS relies on security groups to control inbound and outbound traffic between the replication instance and databases. To properly configure the security group:
- Navigate to EC2 Security Groups in the AWS Console.
- Locate the security group assigned to the replication instance.
-
Add the following Inbound Rules:
- MSSQL (TCP/1433): Allow traffic from your local SQL Server’s IP or security group.
- PostgreSQL (TCP/5432): Allow traffic to the target AWS RDS PostgreSQL instance.
Add an Outbound Rule that allows all traffic to leave (egress) the VPC. This ensures communication from the replication instance to the source and target database endpoints.
2. Configuring Source Endpoint (SQL Server)
Choose Source Endpoint and enter SQL Server details.
Set the Endpoint Type to Source.
Enter the Server Name (IP Address), Port (1433), Username, and Password.
Click Test Connection and ensure it succeeds.
3. Configuring Target Endpoint (AWS RDS PostgreSQL)
Navigate to Endpoints and select Create Endpoint.
Choose Target Endpoint and enter AWS RDS PostgreSQL details.
Set the Endpoint Type to Target.
Enter the RDS Endpoint, Port (5432), Username, and Password.
Click Test Connection and verify success.
Database migration tasks
After making those settings you are ready to create a database migration task. I will cover this in my next blog post.
Top comments (0)