Generate Database project from Azure database

Azure Database

In this topic I will show you how to generate own database project from SQL Azure Database. We used SQL Management Studio 2012 to create and generate a database. Also, I will use our solution MvcApplicationWebRole that I have created and used in the previous post.

First you need to have created SQL Server in Azure. You can do it in Manage Windows Azure site

New SQL server 

To create new SQL server go by the link above and press button “+ New” in the bottom of the page. Then use the wizard and select action “Data Services – SQL database – Custom create”:

Wizard to create new Database on existing or new SQL Server

Wizard to create new Database on existing or new SQL Server

  

 

After click on “Custom Create” new window will occur:

Create new database in Azure

Create new database

 

 

We should enter the new database name in combo box “Name” and n drop down list “Server” we can select “New SQL database server”. In this after press next we will receive new window for creating new SQL server:

Create new SQL server

Create new SQL server

 

You can use this window to create new SQL Server. You should remember login and password. Also, you should select Region for this server. We will need this credentials later on to get a connection to our SQL Server. But I will not do it. I will use already existing SQL server. So in a drop down list from I will select my server, in combo box “Name” will enter new database new name “MvcApplicationWebRoleDatabase”.  After click button Ok it will create empty database name:

New database in Azure

New database “MvcApplicationWebRoleDatabase”

 

 

So now we can use SQL Management Studio to connect to our Azure SQL server:

Connect to Azure SQL Server

Connect to Azure SQL Server 

 

After the connection has been established we can see our new database in Databases List:

Database in SQL management studio 2012

Database “MvcApplicationWebRoleDatabase” in SQL Management Studio 2012

 

Script to create table Document

Script to create table Document

 

Script to create table Person with foreign key

Script to create table Person with foreign key

Database project 

So we have a database in Azure SQL server. Now we can generate database project from this database.

We need to create the new solution in Visual Studio “File – New – Project”. In occurred window select “Other Languages – SQL Server”. As a name, you can type “MvcApplicationWebRole.Database” and press Ok:

Create new database project

Create new database project

 

You will receive empty project. Right click on a project, select “Import – Database”. A new window will occur. Set “Source database connection” our MvcApplicationWebRoleDatabase as target:

Select Azure database for import

Select Azure database for import

New database project 

After press button “Start” – it will generate new database project from your database. We can see the result in the image below:

Result window

Result window

 

 

As result of this generation, we receive database project with scripts for our tables:

Tree of database project

Tree of database project

Changes

It is very useful to have this database project. From this moment, we can make any changes to our database project. Now let’s make a change to our Person. I will add new column GenderId to entity Person, will add a foreign key and will create new table Gender. After this rebuild project. Now we can publish this changes to our database project. Right click on MvcApplicationWebRole.Database and select “Schema compare”. In new window from the left side will be our database project. From right side will be our database MvcApplicationWebRoleDatabase. It means we will push all changes from source database project to target database in Azure:

Schema compares between database project and Azure database

Schema compares between database project and Azure database

 

Before start compare we need to click on button “Options” (Right side from button Update on the image above). In menu “Options” select “Allow incompatible platforms”. Now click button “Compare”. It will show as differences between database project and Azure database:

Differences in table Person

Differences in table Person

Differences in table Gender

Differences in table Gender

 

Now we can press button “Update”. It will push all changes to our source database. As a successful result we will see schema compare result message:

Schema compare result

Schema compare result

Azure database changes

Now we can go back to our Azure database. Refresh it. We can see our changes in the database after schema compare and Update:

Updated database

Updated database

 

And that's it. Here we generated Database project from Azure database.

Best Regards. Andrew Tsopych CTO of Diceus
Loading