Synchronization between local and Azure SQL DB using SLQ Management Studio "Export Data..."

Introduction

Here I'm going to show you step-by-step instructions how to create Local and Azure databases and do synchronization between them. This article is based on a real project activity that I performed.

Steps

1. First of all let's create Local DB. For this purpose I run SQL Management Studio, right click on Databases and Select  "New database…"  

2. Enter DB name and press OK. In my case DB name is iSpeakDB:

3. In my case I need to access this DB using my applications, so I also went to Security->Logins and created a user for accessing my DB and mapped it to iSpeakDB. If you don't plan to fill your SQL data with some other tools, you can skip this step

4. Fourth step for me is to create data base tables. I have SQL scripts created for this. And run a query on iSpeakDB. Below is a simplest script example that you can use to create a table:

USE [iSpeakDB]

GO

CREATE TABLE [dbo].[tblUser](

[UserID] [nchar](100) NOT NULL,

[ExpirationDate] [datetime] NULL,

[OperationSystem] [nchar](100) NULL,

[Country] [nchar](100) NULL,

)

GO

5. Now when our data base structure is ready and created it is time to populate it with data. In my case I use utility that we build and that fill my DB with data.

6. When data is ready and is on local DB it is time to switch to Azure and create our DB schema there. I connect to my Azure portal, created there SQL DB called iSpeakDB in my case. Next step I opened management portal for DB and selected "New Query". There I run similar script as for local DB but with one additional string. See screenshot below:

As you can see I used CREATE CLUSTERED INDEX command to create the index for table. The problem is that Azure DB has its own limitations and requirements and it doesn't support tables without clustered indexes. You can read more about Azure limitations here:

https://msdn.microsoft.com/en-us/library/azure/ee336245.aspx

7. I didn't find any way how to see if my tables were created correctly and I had to run simple select query on each table to see if all columns appear and table is correct

8. Now when tables in cloud are ready and I have same DB as local I need to sync between two databases. For this purpose I use SQL Server Management studio. Navigate to your DB and by right click select Tasks->Export Data

9. Click next in wizard that appears. Now you're in "Choose Data Source" page. You need to make sure that proper server, DB and authentication mode is selected. If everything is correct and data is related to DB that you want to transfer to Azure, click Next.

10. Next page in wizard is named "Choose Destination". Here we have sensitive moment. You should choose: ".NET Framework Data Provider for SqlServer". Now in list of possible parameter you should find Data->Connection string and copy there connection string that you use by your Azure service to access Azure DB. I copied connection string from SQL Management portal and inserted it to property field called "ConnectionString" it looks like this:

Server=<your server>,1433;Database=<your DB>;User ID=<your user>;Password=<your password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

Bold-italic things should be replaced by your data.

One more thing that we need to do before pressing Next button is to change value of "TrustedServerCertificate" property to "True"

11. Clicking "Next" navigates us to menu where we should set radio button for "Copy data from one or more tables or views" and press "Next"

12. On the next page named "Select Source Tables and Views" you should select tables that you want to synchronize and click Next

13. In next page you can save "SSIS Package" that you can later restore or run data transfer immediately. I use "Run immediately" and press "Next" and "Finish" on next page to run data transfer

14. When data transfer finished you will see results as they shown below:

15. You may receive error while connecting to Azure DB. One of possible fixes is to go to Azure management portal and add current IP to list of IPs that can access DB for data modifications. Open there DB  page and go to "Set up Windows Azure firewall rules for this IP address".