Home » Softwares » Azure » Migrating your Local SQL Server Database to Microsoft Azure

Migrating your Local SQL Server Database to Microsoft Azure

Migrating a SQL Server Database into Azure is easy but not obviously. Here is few steps that might save you some hours. These steps are migrating the schema by creating a new database at destination with all data.

First of all, you need to open Microsoft SQL Server Management Studio. Right click the database you want to migrate to Azure and select Task, and Export Data-tier Application. It’s important that you do not choose the option Deploy Database to Windows Azure Sql Database because this option doesn’t keep the identity (primary keys) of you tables.

ExportDataTierApplication
This will generate a bacpac file that needs to be imported into Azure. To do so, you need to connect with Microsoft SQL Server Management Studio to your Azure server. To connect to Azure Server Database you need to have your IP allowed by your Azure SQL Server firewall and knows your server password.

To import into Azure, you need to right click the Databases folder of Azure SQL Server. Select Import Data-tier Application.
ImportDataTierApplication

A short wizard will open and let you select the name of the destination database as well as some primitive Azure configuration. This may take a while to transfer depending of the size of your database.

Once it’s done, it’s always a good idea to verify if everything has been transferred.

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.rows AS [Row Count]
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'
ORDER BY [Row Count], [Schema], [Table], [Index]

That query will show the information about the number of rows per table. You can compare from the local database and the Azure one to see any disperency.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.