Migrating a SQL Server Database to Amazon RDS
Tags: devops, aws, azure
Published: 2022-08-05 10:09:05

**Why use Amazon RDS?**
**Amazon RDS is a fully managed service for running relational databases on Amazon Web Services (AWS). It has support for six different database engines, including commercial options such as SQL Server and Oracle.**

**With Amazon RDS, your database operations are managed by AWS, leaving your team free to focus on innovation. Amazon RDS handles instance failover, data backups, and software updates, so you can enjoy the efficiencies of the AWS Cloud for a fast and reliable database option.**

**AWS Database Migration Service (AWS DMS) is a cloud service that makes it easy to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores. You can use AWS DMS to migrate your data into the AWS Cloud or between combinations of cloud and on-premises setups.**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r6zxg0poxdqtuf366m5s.png)

## Steps

- • First of all create a client database using SQL schema , which will be 
- present inside
-  on premise server or inside client local computer.
- • Create your own VPC [Network] and SUBNET [Sub-Network] on AWS.
- • Attach a Internet gateway to VPC.
- • Create routes table and subnet association.
- • Create a Amazon Linux server with SQL configuration [Source server] 
- inside your Subnet.
- • Attach a volume to that server.
- • Copy that database to your Linux server.
- • Create a RDS [Target].
- • Now create a Database migration service.
- 1. Create a Replication Instance
- i. Initiates the connection between the source and target databases.
- ii. Processing power to migrate data from source database to target.
- 2. Specify Source and Target Endpoints
- I. The source and target data stores can be on an Amazon EC2 instance, 
- an Amazon RDS DB instance.
- 3. Create a Task
- I. Create new tables, tables to migrate.

## Source Server

## Create an Instance [Sql server standard edition]

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jgm507l3oguya3jo50yz.png)

**Here I have used t3xlarge type**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tbygwg31thexwtlr8vz0.png)

**After that create a volume and attached to that server.**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b4lkld7tzbclunwiq8y6.png)

**When you’ve successfully connected to the Linux EC2 instance running SQL Server, execute the following so you can run commands using the root user account:**

```
sudo su
```
**To view volumes and devices you’ve added to the Linux instance, run lsblk to see a list of all Amazon EBS volumes that you attached during launch.**


**Because this AMI includes SQL Server 2017, you reset the SA password for the SQL Server instance. To do this, first stop the SQL Server instance by using the following command:**

```
sudo systemctl stop mssql-server
```
**Then run the following mssql-conf script to reset the SA password:**

```
sudo /opt/mssql/bin/mssql-conf set-sa-password
```
**Format the volume of your choice to use the ext4 file system using the following:**

```
sudo mkfs –t ext4 /dev/devicename
```
**For example: sudo mkfs -t ext4 /dev/xvdc**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5zjjdrl28xmbx9d5w0h1.png)

**Create your directory. This example uses /SQLServerData:**

```
sudo mkdir /SQLServerData
```
**Mount the volume to the /SQLServerData directory using the following (we use the /dev/xvdc volume for this example):**

```
sudo mount /dev/xvdc /SQLServerData
```
**Change the owner and group of the directory to the mssql user:**

```
sudo chown mssql /SQLServerData
```

```
sudo chgrp mssql /SQLServerData
```
**To change the settings, use the following steps (the example uses /SQLServerData as the default data directory):**

```
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /SQLServerData
```
**After you complete the preceding step, SQL Server needs to be restarted to apply the new settings. Before you do so, let’s also mount a different volume to a directory specifically for database log files.**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvfhaegc8p9emzgdfktk.png)

**Now connect to mysql using server public ip,username & password**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/erhveha48xh3js8whqiw.png)

**Create a sql database**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f4k7piqismur6vsab716.png)

**Now our Source server is ready**

## 1)Lets create AWS RDS [Target server]

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j7zrsz332084zc8emuq2.png)
**Make your rds public as we have to connect it to my sql
If you create it as private then your have to setup openvpn to access it.**
**Before that create your subnet group**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/drtiad680gh7n8qtb4dj.png)
**Here create a database.**

## 2)Create a replication instance in AWS database migration services

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nj3ukofswt9z3pvyc8xm.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f0ptaj8smq3736oftb59.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tp2xquvtw1si9r3oo6ri.png)

## 3) Create a source and target endpoints for your data migration

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ui4fwwlsx637vcmxetv1.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s6ywk266lma7lyxbyi6u.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i5nslifib955qd94z6x4.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pyee8qthpke68wukwh6v.png)

## 4) Create a Replication in AWS DMS[Data migration task]

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1peav36zgosso7ag9k1i.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h4te5o86ul5zzv5t98zd.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l1z4bdwyw9v09an4jry9.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m9lma1y8a7oqgb6y0q1m.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dextj7ts0f1s61ptrenq.png)

**After 100% status you can see your whole data got migrated to RDS**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ka0vjflvftf1xaxm4ia.png)