Moving to SQL Azure DB

At some point of time you will possibly want to move you database to Windows Azure. It might be that the perception about data ownership and Co. might be some issue. In my case I do not have any issue with data. I just have a DB which from now have to be accessed from many different applications. For this reason I had provide infrastructure around DB. After several tries, this just didn’t work because of many, many reasons. So I decided to promote my DB to Windows Azure. It worked perfectly – almost.
First I have created SQL Server in the cloud.

With option Quick Create you can create your database by specifying a name. You can do tasks like deployment and configuration later.
This option is useful, if you already have SQL servers running in Windows Azure.

Custom Create is useful when you want to select existing one or to create new server.

Import option allows Import a saved database from your BLOB storage account.

Whatever you do, you can manage more or less everything later by using management Portal. The only important here is decision about purchasing of the new server or using of the existing one. After the server has been provisioned I wanted to move existing database to SQL Azure Db.

Migrate by using SQLAzure Migration Wizard

One of easiest ways to do that is SQL Azure Migration Wizard which can be downloaded on CodePlex: http://sqlazuremw.codeplex.com/

 
By using of this wizard you can migrate both the database schema and data.

Publish Database within Visual Studio

You can also deploy database to Windows Azure directly from Visual Studio 2012. To do that you have to first create the DbProject. Right click on project and following context menu appears.

Select import from DACPAC (if you have some) or connect to database and import DB from there. DACPAC is interesting option, because Windows Azure Management Portal has an easy way to export SQL Azure databases in DACPAC.
VS does this internally by default anyhow.After you build the DB-Project just go to to debug/bin folder and look for .dacpac.

This will create/update the database in Windows Azure, but not publish the data.

Import from DACPAC

Database can also be created by import of DACPAC. This can be done as already described above, but also directly in Management Portal while managing existing server.

Adding the Database User

At the end there is one important thing, which has to be described in more details. When the new database is created you will be prompted to enter the name of the username and password who is will become the System Administrator.
Unfortunately up to now there is no way to manage users in Management Portal. This is highly important, because you cannot share and use connection string of the initial login which is admin.
To create the new user you have to use T-SQL in SQL Management Studio.
Once you are connected to your Windows Azure Database, select the master database, right mouse click and select new query.

This will open the new query wit connection to master database. This is the most important step you should be aware of. Then type following in the query:

CREATE LOGIN yourusername WITH password=’YourPwd123‘;

This will create the new LOGIN in the master database.
After that we need to select the database (as in previous step, but this time you will not select master database) and open the query window.

In that window type following:

CREATE USER yourusername FROM LOGIN yourusername ;

This statement will create the user from specified login in the master database.

At the end we need to append some roles to the user. They can be enlisted as follows:


For example if you want to add it in the role of owners do following:

EXEC sp_addrolemember 'db_owner', yourusername ;

Now you can use use DB in the cloud. I know that last few steps are not very common task of every developer. I have tried to keep them simple. T-SQL can look much worse, believe me.
An alternative to these steps was to setup completely new infrastructure which will publicly provide secure access to database. My steps have taken about 20 mins to provision and migrate one DB in the cloud.
To do all that On-Prem will take much, much more time.