Migrating SQL Server to Azure SQL Database with SQL Server Management Studio (SSMS)
Tags: database, sql, azure, vscode
Published: 2024-03-09 22:48:29

## Introduction
Databases from an on-premises SQL Server instance can be moved to an Azure SQL Database (offline) using SQL Server Management Studio (SSMS). We will learn how to use SQL Server Management Studio to move the sample AdventureWorksLT2022 database from an on-premises SQL Server instance to an Azure SQL Database instance. 

## Needed Tools
- Download and install SQL Server Management Studio (SSMS) with this link: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
- Download and install SQL server 2022 with this link https://www.microsoft.com/en-us/evalcenter/download-sql-server-2022
- Create Azure Database instance in Azure portal.

## How to create Azure SQL Database instance
- Log into Azure portal, search for SQL Database in the market place and click SQL Database
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6zme8gotnwnlffyrddk3.png)
- Click create SQL Database 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7zf7osc3qw4kmutssybu.png)
- Fill the project details by selecting the right subscription and resource group.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/njhiaeuxco99dmgco2k4.png)
- Fill the database details by supplying a name to your database and click create server if you have not configured a server before.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lgtdc8sfqjidcfm1gesg.png)
- In the Create SQL Database Server pane choose a name and location for your server. It is important to make sure that the location of your resource group and server are the same.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y7duz4hu0a5v10olr5q4.png)
Under authentication, choose use sql authentication and supply the username and password. Click Ok after.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2vhdb6f6qajj9kyg7i5h.png)
- Click configure database in compute + storage section
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rhz22zngou7qaxarkr5k.png)
- Change the service tier to basic, leave other settings as default and click Apply
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d5r3l59a0zip5bsvgegy.png)
- Leave other settings under Create SQL Database as default and click Review + create 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aicc9lvr5m7odsh9kcae.png)
- Click Create after passing validation
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/whcozdsbcpuhbcywdzt8.png)
- Wait for it to deploy
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/40ya6yn7clmqonikwh0g.png)
- Click Go to resource once deployment is complete
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/begcf2e3sw0aqycyl87c.png)
- Click on the server name to open the server
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ms4y653vi66gb90abz1.png)
- Select the Networking blade and change Public network access to Selected network
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/10r949l2gqgurjfkyexm.png)
- Tick allow Azure services and resources...and click save.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uc2pf637l4bffysfl9rw.png)
- We need to register Microsoft.DataMigration in the resource provider of our subscription by searching for subscription in the market place
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2mx96tr0jzgxfrbw0hhy.png)
- Select your subscription
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zszcovs1jrws9un6pl7i.png)
- Search for Datamigration in the filter and click register
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jr1dg0ubff6ssn6s9jzn.png)
- We are through with configuring Azure SQL Database.

## Migrating SQL server to Azure SQL database
- After installing SQL Server Management Studio (SSMS), Launch it. The Server name will be the name of the local SQL server installed and click connect
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4w032f8yz82bi6n1xm8b.png)
- After connecting, expand the database folder by clicking on it, right click on the database we want to migrate, click Tasks and select Deploy Database to Microsoft Azure SQL Database
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mfqoub7i9s7nhgvuu2f7.png)
- Click Next
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9po0uymdkhqttxi66ohz.png)
- Click connect to put the Server name which is the name of the the server created in Azure portal
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zs7s9qfschfhay80dys9.png)
- Change Authentication to SQL Server authentication, put the Log in and password details. Click connect after
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qq6wir41vxamtgybe9a.png)
- It will require you to sign in
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ppoklsz0be4vcwd2h7vl.png)
- Leave the settings as default and click Next
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f8bubi1vqt6xt4yr127f.png)
- Click Finish
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q8xzu0e0z2869dt6cxsq.png)
- Wait for it to finish
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/33dfkn941861cvqovc6k.png)
- Once the import is complete, click Close
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jo77zkgv25dc6zr5ckqs.png)
- To check if the database has been moved successfully, navigate to Azure portal and open the resource group for our database.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b1jp177uc36mw6fq5x5w.png)
- Click to open it and select Query editor. Put the Password and press Ok
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fwh4tk1cjaegbbvw95pi.png)

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