Step by Step approach to create a Azure SQL Database and export data from Non-Azure SQL Server 2014 Database

Introduction

SQL Azure can be defined as

SQL Azure is Microsoft's cloud database service. Based on SQL Server database technology and built on Microsoft's Windows Azure cloud computing platform, SQL Azure enables organizations to store relational data in the cloud and quickly scale the size of their databases up or down as business needs change.

Step by step approach

Step 1: Create a blank Azure SQL Database from Azure portal.

Open the Azure Portal(https://portal.azure.com) and choose Databases ->SQL Database

Click on the SQL Database and enter the details as shown below

Create a new Server as shown under

Next choose pricing details as under(for us it is S2 Standard) and finally Collation as SQL_Latin1_General_CP1_CI_AS

The complete details of the settings is as under

Click on Create button and then we will find our RNAAzureDB in the resources section

Now click on the RNAAzureDB link and in the overview we will get

The we will find Connection strings -> Show database connection strings section. Click on that to get the connection string. Open a notepad and store the Server Name for later use. Observe that a default database by the name RNAAzureDB is being set which indicates that a database by that name will be created.

Now click on the Properties tab to view

and store the SERVER ADMIN LOGIN in a notepad for later use.

Step 2: Open SSMS and connect to SQL Azure DB.

Open the MicrosoftSQL Server 2014 Management Studio , provide the Azure SQL Server Name, Login and Password (which we stored in notepad earlier) and click on Connect

The error message states that

TITLE: Connect to Server
------------------------------

Cannot connect to tcp:rnaazuretestdb.database.windows.net,1433.

------------------------------
ADDITIONAL INFORMATION:

Cannot open server 'rnaazuretestdb' requested by the login. Client with IP address '182.XX.XXX.xxx' is not allowed to access the server.  To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect. (Microsoft SQL Server, Error: 40615)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=40615&LinkId=20476

That means that we have to by pass the firewall for which we have to set the firewall rules from the Set Server Firewall tab available in the Azure Portal

Now from the Firewall Setting, let us set the firewall rule

and finally click on the Save Button

Now again from SSMS, try to connect by providing the right credentials and choosing the right Azure DB server as described earlier. And it's a success

Step 3: Open SSMS and connect to a Non Azure SQL DB.

Now open a Non Azure SQL DB. At this point we can notice that there is not table being exported from a Non Azure SQL DB (e.g. ExperimentalDB) to Azure SQL DB (e.g. RNAAzureDB).

Step 4: Use Import/Export for exporting data

Now open the "Import/Export Data wizard" from ExperimentalDB.

Choose the ExperimentalDB as Data Source and provide the correct credentials.

Choose the RNAAzureDB as Data Destination and provide the correct credentials.

Choose the table that we want to move from ExperimentalDB to RNAAzureDB

Click "Next" and proceed further to complete the wizard.

Step 5: Verify resources in the Azure SQL DB

Now expand the Tables node of the RNAAzureDB and we will find that all the tables has been exported.

Simply run a select command on the tblWayPoints table to verify that the data has equally exported.

Conclusion

In this article we saw how to create a blank Azure SQL Database in Azure portal and export data from Non-Azure SQL Server 2014 Database in a step by step manner. Hope this will be helpful. Thanks for reading.