How can I deploy my database from SQL Server 2008 Express to Azure SQL Database

Question

I am using Visual Studio Community 2013, SQL Server 2008 R2 Express Edition for an MVC web application using entity framework code first migrations for my database.

I am trying to get my local application hosted on the Microsoft Azure platform.

I have registered for a trial account which expires in 30 days, and deployed my MVC5 app out which has been successful.

However, this app requires a database which I am struggling to deploy.

What is the easiest way to get my database deployed out to Azure as I do not seem to have the :

"Tasks" -> "Deploy Database to Microsoft Azure SQL Database" option available to me in SSMS.

I have extracted a Data-tier Application of my local database and have it stored on my C drive, however if I connect to my Azure Account in a second window, and right click the server then select Deploy Data-tier Application, it fails on "Creating schema object in database" with the following error:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Users cannot be mapped to certificates, asymmetric keys, or Windows logins in this version of SQL Server. (Microsoft SQL Server, Error: 40528)

I'm assuming its tried to create my local account plus the IIS APPPOOL account I had to set up to host the website on my local network, however I do not see a way of removing them from the .dacpac export.

When I refresh the Databases node, there is still no database, I'm fairly lost now as to how I might get my db deployed to this server?

Answer 1

If you can extract a compatible Data-tier application and then Deploy it to your target server using your current version of Management studio, then this approach should work, see this link for more reading on ensuring your database is compatible.

Failing this, as was in my case. I downloaded Management Studio 2015, which gave me the option to deploy database to Azure by simply right clicking the required database.

Again, this threw up the error regarding my database level IISAPPOOL user, but not my server account user. As a way around this, I took a backup/restore of the database and removed this user then deployed the copy database obviously minus the problem account.

Now all that was left to do was add a transform to my Release web.config with the Azure connection string as provided on the Azure dashboard, and re-deploy the website.

Problem solved.