With an efficient database, it becomes easy for the organization to manage and store data. MS SQL Server is the first choice for users when it comes to data management. But nowadays, the users are migrating from SQL Server 2008 database to Azure SQL database. The reason behind this migration is that the end of life support for SQL Server 2008.

Therefore, the SQL Server 2008 database users are searching for a reliable and simple method to move data from SQL Server 2008 to Azure without any data loss issue.

Thus, in this article, we are going to introduce a step-by-step method to transfer data from SQL Server to Azure SQL database in a trouble-free way.

Steps to Migrate SQL Server 2008 Database to Azure

The migration process is divided into two steps; the user first has to create a blank SQL database and then migrate SQL Server to Azure database using DMA.

Step 1: Create a Blank SQL Database

1. First, you have to log in to the Azure portal.
2. Now, create a blank SQL database by clicking on the Create a resource in the upper left corner of the Azure portal.
3. From the New page, select Databases. Select Create under SQL Database.
4. After that, you have to fill out the SQL database by entering the correct information related to:

Database Name: mySampleDatabase

Subscription: Your subscription

Resource group: myResourceGroup

Select source: Blank Database

5. Now, to create and configure a new server for your new database, click on the Server. Fill out the New Server form details with the accurate information:

Server Name: Any globally unique name

Server admin login: Any valid name

Password: Any valid password

Location: Any valid location

6. Click on the Select button, which is present on the right side.

7. Now, to specify the service tier, number of DTUs, and the amount of the storage, click on the Pricing Tier.

8. Select the Standard service tier and then select 100 DTUs(S3) and 400 GB of storage.

9. After that, accept the preview terms to use the Add-on Storage option.

10. Click on Apply after selecting the server tier, DTUs, and storage amount.

11. Select a collation for the blank database. Here, you have to use the default value.

12. After completing the SQL database form, click on Create to provision the database.

13. Now, on the toolbar, click on Notifications to check and examine the deployment process.

Note: Before moving towards step 2, you have to create a server-level firewall rule. Also, check SQL server connection information.

Step 2: Migrate SQL Server 2008 Database to Azure

1. Open Data Migration Assistant. The user can run DMA on any system with the connection to the SQL Server instance contains the database. The user needs to install it on the system hosting the SQL Server instance that the user is migrating. The firewall rule that the user will create must be for the system on which he/she is running the Data Migration Assistant.

2. In the menu on the left side, click on + New to create an Assessment project. Fill all the required details and then click on Create:

Project type: Migration

Project name: Migrating Database

Source server type: SQL Server

Target server type: Azure SQL Database

Migration Scope: Select Encrypt connection and Trust server certificate as per your environment.

3. Now, from your source server select a single database to migrate to Azure SQL Database and then click on the Next button.

4. Fill all the required detail on the Select target page. Then click on the Connect button:

Server name: Your fully qualified Azure Database server name

Authentication type: SQL Server Authentication

Username: Your login name

Password: Your password

Connection properties: Select Encrypt connection and Trust server certificate according to your environment.

5. Select the database from the target server, which you created in the previous procedure, and then, to start the source database schema assessment process, click on the Next button.

6. Once the source database schema assessment process is completed, review the objects selected for the migration and review the object containing issue on the Select objects.

7. Now, to script the schema object in the source database, click on the Generate SQL script option.

8. After that, you have to check the generated script and then click on Next.

9. You have to click on Deploy schema and the schema migration process will begin.

10. After the completion of the migration process, review the results and then click on Migrate data.

11. Review the tables selected for the migration on the Select tables. Now, click on Start data migration.

12. The migration process will begin. Once the migration process is completed you will be able to access your SQL Server data in Azure.

Alternate Solution:

Migrate SQL Server Database to Azure SQL Database with SysTools

• Download and run SysTools SQL Server to Azure DB Migrator on your local machine.

• After that, Open and Scan SQL database file (.mdf file) of your choice

• Preview all database objects before migrating to Azure SQL database

• Migrate SQL Server to Azure Database by filling required fields

Final Words

Most users search for a way to migrate the SQL Server 2008 database to Azure, but they are not able to find a reliable solution. Therefore, in the above section, we have explained a step-by-step process to move the complete migration from SQL Server to Azure with a manual and alternate approach to accomplish the task.