In this article we will look into 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.
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.
In this article we will look into 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.
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.