This post explains how to export a local database and import it to SQL Azure.
Having setup the SQL Azure database and established a connection using “SQL Server Management Tool” (SSMS), I know want to import the database schema from my local database. The simplest way for me was to use the “Generate Scripts” option. But first, let's check one security model of Microsoft SQL.
I manage all the security of my database objects with “Database Roles”.
All my applications which connect to a database have their dedicated “Database Role”. While creating the “Database role”, you grant permissions to one or more users (SQL Users or Active Directory Users).
CREATE ROLE DiveApp AUTHORIZATION sqlUserDiver
I never give the user direct access to a table. Either create a view or a stored procedure. In this example I created a stored procedure called spgetAllBoats001.
Grant exec ON sp_getAllBoats_001 TO DiveApp
The stored procedure spgetAllBoats001 performs a SELECT on the table “BOATS”. The user “sqlUserDiver” is now allowed to execute the stored procedure but he still doesn’t see the table “BOATS”.
Now let’s export the data from our local database and import it to SQL Azure. First thing to do is connecting via SSMS on our local SQL instance. Right-Click on the database you want to export and navigate to “Tasks” -> “Generate Scripts”:
On the “Introduction Screen” press “Next>”.
In the “Choose Objects” Screen I selected all the available Objects and pressed “Next>”:
In the “Set Scripting Options” Screen press “Advanced”:
This screen gives you more advanced options to what to export. The modifications I made are in the red boxes:
What I usually do is exporting the script to a new query windows. This allows me to quickly check it. Select the option “Save to new query window” to do so:
When you press “Next>” you get a summary of the export. Pressing “Next>” again will then trigger the export.
When the script generation completed a new query window opened. Press “Finish” to close the dialog.
Always take your time to review the script before deploying them!
Now comes the deployment to SQL Azure part. Here is how to proceed:
1) Copy the script you just created (Ctrl + A, Ctrl + C)
2) Open SSMS and connect to your Azure SQL Server Instance
3) Press “New Query” and select the database into which you want to deploy the script
4) Paste the script in the Query Window and Press “F5” (this will execute the script)
Now that you imported the data and schema to SQL Azure, we still need to adapt the permissions of the SQL Stored procedures. In the next blog I explain how to set the permissions for all the Stored Procedures via script.
This kept me busy for: 5 minutes