Maxybyte Technologies: Migrate SQL database to Azure SQL database

How to Migrate SQL database to Azure

Of course, it is very easy to build applications ruining on local computers and accessing stored database directly also on the local database should be very easy. One out of the reasons we build an application is to make it accessible worldwide, and we could get this possibility through the help of the internet.

The benefit of using Azure is because of its scalability, availability, tenancy, provisioning, and their payment model. I will give you the simple logic on how you can construct and transfer your database from your local computer and access it online instead.

SQL Server Management Studio(SSMS) will be useful for this tutorial, it is free, you can directly get it from Microsoft.

Follow the instructions and install it.

Then we will need to follow two steps for this migration:

Schema migration from the on-premises database to SQL Azure

Table data migration from the on-premises database to SQL Azure

Now to start migration; open SSMS and connect to a Database engine for our local SQL express instance but also you can connect to a SQL Server on the server on your network, since we presently have the database built in our computer, then connect to the Local SQL express instance.

Database LocalDB Diagram

Step 1: Once you are connected, expand the "Databases" to see what you have there

Right inside after expansion, choose the database you want to move, in my case I will use my database named OKIDOKI_7b18cd1fa3f4, then I expand the Tables and have this diagram below that you can follow:
We need to create a new SQL Azure database that we can use for the migration. We can create a new SQL Azure database via the window Azure platform management in Azure portal but we are already in Management studio, let's do it there.
In that case, we just need to open a new connection to our exciting Azure database server, If you haven't signed up you can do that in Azure portal.

Once that is done you can access it from SSMS by connecting to the sever name you used during the time you created the sever in the Azure portal mine looks migratingsql.database.windows.net and in Authentication space in SSMS select SQL sever Authentication.

SQL Server Diagram

Enter your connection information and click the connect button. Then once you log in right-click Databases and choose new database OR click on Databases to highlight it and click on new query , right inside the new query we can create and name our database to be in the cloud.
Copy and paste this code to create the database named MYCloudOki:

CREATE DATABASE MyCloudOki

And click the Execute button in SSMS.

If you follow tutorial correctly then you should have a new Azure database name MyCloudOki that we will use for our destination.

As I mentioned there are 2 steps to the migration we start with migrating our database schema and we will migrate our data.

To migrate our schema from on-premises to Azure database destination, we will right_click on the

Generate scripts Diagram

database we want to move the schema , => Tasks => Generate Scripts..
Then follow the instructions.
When you get to the Advance button make sure you click there that you are targeting SQL Azure engine, therefore scroll down and find the script for the database engine type and in the drop down select SQL AZURE DATABASE.

Give the script a name and save it to a file and click next to generate the script file. Now that we have our script generated we can deploy the script to our destination SQL Azure database.

Now let's right-click on our SQL Azure database(MYCloudOki) in SSMS and select new query then open our generated saved script file and let execute the script against this query. once this is done then we should have the same schema from on-premises in MYCloudOki.

Download : Matching Game Application

Step 2: Migrate data

Now we need to move the Data, there are numbers of options available to move the data, such as:

-SQL server integration services, -BCP , - sqlBulkCopy.

In this case, we will use the SSIS package to move the data. We are going to use SSMS To run the wizard and create an SSIS package.

Now let's right-click on our source database OKIDOKI_7b18cd1fa3f4 go to Tasks and select Export data , then from the wizard specified the source database and the destination database which is in SQL Azure therefore choose.Net framework data provider for SQL server, then scroll down a little and enter the credentials for our destination database and click next and run it immediately. Once the migration is done you can run a query to make sure the data is there.

Happy coding...

Your feedback will help others...