Migrate SQL Server 2008 Database to Azure
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.
Join the DZone community and get the full member experience.Join For Free
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
- First, you have to log in to the Azure portal.
- Now, create a blank SQL database by clicking on the Create a resource in the upper left corner of the Azure portal.
- From the New page, select Databases. Select Create under SQL Database.
- 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.
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
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.
Opinions expressed by DZone contributors are their own.