This is the first post in transitioning a SaaS application that Red Jungle hopes to offer as a Product from its privately hosted Virtual Machine on Myhosting.com to run instead on Windows Azure as an Azure Website running against SQL Azure.
Currently the application is an ASP.NET MVC 3 application running on .Net Framework 4.5 using the Entity Frameworks. The data is maintained on a Sql Server 2008 R2 Database.
This post is about transitioning the Database layer from it’s hosted SQL Instance on the VPN server to SQL Azure. This walkthrough depends on your having Visual Studio 2012, all of the Latest Azure SDKs and SSMS 2012 installed on your development machine. Trying to do this with SSMS 2008 is just too much work. Please note this article was correct as of the 9th of March 2013, but be aware that the Azure Platform is changing and improving daily (thanks @ScottGu) so these instructions may have been superseded, it may be even easier than it seems here.
The Steps necessary to pull this off are:
- Log into and Backup the Database on the Virtual Machine, and bring that backup down to your development machine.
- Restore the database on your local Sql Server instance
- Open the VS2012 solution that contains your SaaS project (or you can start a new solution just for the database if you want).
- Add a new Sql Server Database Project
Right mouse click on the Database project Select Import From , then Select Database… Import the database using the Import Database Dialog
- Name the project what ever you want the eventual database to be named in SQL Azure
Once that is done you will have Database structure in a solution that can not be added to source control, modified, deployed, etc
- Personally I prefer to have the folder structure set up by Object Type, since especially with Azure we find everything ends up being DBO
- Don’t bother with importing the Referenced logins or the permissions, or the database settings, cause they are all going to be changed for Azure
Double click on the Properties tab of the project and in the Project Settings | Target platform: change to Windows Azure SQL Database
- We won’t be using the Security on SQL Azure so we delete those
- And Extended properties are not supported on SQL Azure so if you have any delete those as well
Here is where things begin to become different, some of the stuff I’m about to do is not specific to SQL Azure, It can apply to any Sql Database running on any SQL 2012 instance. Basically Microsoft is changing the way we think of databases, and has introduced some new terminology. Recognizing that Versioning is a continuing problem with databases, they are asking us to think of a database as a “Data-Tier Application” that runs on a SQL Hosting machine. It’s still a Database, but they want to support some development scenarios that just don’t translate smoothly to how we as developers have been thinking about database, so they are hoping some new terminology will assist us in making that transition.
So from here on out don’t think of you data storage as a database, but rather as a Data-Tier Application that runs on a SQL engine. So why Data-Tier Application? Well to be honest a SQL “database” really isn’t a database. We have stored procedures, triggers, functions, etc, which encapsulate usually business rules and logic, which to be totally honest isn’t data, its process. Microsoft is just trying to get us developer to recognise this and realise that what we call a Database is more then just a place we store some bytes, it’s a place where actual coding, logic, and versioning is needed. Hence why they are calling it now a Data-Tier Application. So along those lines from here on out, I won’t be calling the item we are working on a Database I will be referring to it as a Data-Tier.
So now, looking at that Project Settings page, click on the Properties… button and a new dialog opens where we get to enter our Data-tier Application Properties
And finally here you can see where they have begun to introduce the concept of a Version Number in the Data-Tier.
The other interesting thing to note about the Project Settings Tab (in the same place where we clicked the Properties… button
They specifically have noted that the Output type is Data-tier Application which they have given a .dacpac file extension. You do have the option to instead of using the .dacpac having the output created as a .sql file (the old database way), but if you read Microsoft’s documentation on SSMS 2012 and SQL Azure, the writing is pretty large on the wall, they REALLY want us to start using .dacpac.
The .dacpac can be thought of as the definition of the Data-tier without the data. To read more of the concepts and way to think of a Data-tier application and how it relates to a .dacpac file, check out Data-tier Applications documentation on the Microsoft MSDN website. I’ll just be giving you the specific highlights you need to know for this particular part of the operation.
Also of note is that this new type of Database project can be “built” like any other project type, if you go to the Build section of the Properties for the project
you will see a build output path. The data-tier (remember data-tier === to database, just a new more inclusive name) is dropped then recreated according to the scripts and specifications that have been saved in the project. This uses the new localdb/v10 functionality of Sql (Microsoft is also doing away with SQL Express for on the fly attached files (single instance) database, and having us move to the localdb approach). This data-tier once built can then be put thru various SQL and code analysis tools and warn you of problems with the Data-tier.
For example, imagine you have created a stored procedure that references the “User” table, but in a future version you decide to rename the table to “Person”. Before you wouldn’t get an error until some piece of code in your business layer tried to access that stored procedure. With this project type and being a Data-Tier, VS2012 will give you an immediate Error in the output window on a Build. You find out about the error before you even get close to deploying the mistake. Think of it as Precompiling for Databases.
Now once that is all done, and you can successfully build and cleared all your errors (it will use the Target Platform we set earlier to make sure all features specified in the data schema and stored procedures, etc are compatible) we can publish the .dacpac to SQL Azure
On the Build Menu, there is a Publish <DatabaseName>… option, you can also find this by right mouse clicking on the project and finding “Publish…” there.
Now we end up with a dialog that was obviously designed by a developer and a UI consultant didn’t get a chance to review it. Once you understand all its options and in what order to access them, it’s pretty easy, but let’s just say the UI is not intuitive. Also the dialog can’t quite make up its mind whether or not it is going to embrace the new terminalogy and instead is a mishmash that just confuses the new conceptual approach.
First place to start, is at the bottom left with the Create Profile button (unless of course you have already created a profile previously in the project in which case you start with the Load Profile… button).
This will now Create a new publish profile and add it to the Project (for next time you need to use it)
Next we go up to the top left of the dialog and click the Edit button and you will get the dialog we have all seen for creating a connection to a Database, here we will put in the name of the SQL Azure Server url and the Sql Authentication credentials from the SQL Azure website. http://manage.windowsazure.com
Once that is done, we have two options on how to proceed. Staying with the “let’s jump around on the dialog” theme the developer who put this together has started with, we need to next look at the middle of the dialog at the “Register as a Data-tier Application” checkbox on the right, and the currently enabled “Advanced…” button to the left of that.
Let’s start by first clicking on the “Register as a Data-tier Application”. You may notice that the “Advanced…” button has been disabled. The Advanced button would allow you to set all sorts of Scripting publishing rights and defaults. A Data-tier has those rules “built-in” to it’s schema so we don’t need to worry about them for this operation.
The other thing you will notice is the “Block publish…” checkbox has become active. This will prevent a publish of the changes to the database if the target database connection already has a database published, and the version number is the same as was set back in the Data-tier application dialog, but the structure has changed. This means you need to go back and modify the Version Number before it will allow you to publish the changes. Of course if working on your local development machine you may not care, but on a production machine, if the database has changed at all we really do need to make sure we have modified the Data-tier application version number.
If all the settings are right, you should now go back to the bottom left of the dialog and “Save Profile”. With that done, finally you can go to the bottom right and click “Publish”, and the database and structure will be published to SQL Azure and the database will automatically be set up to keep track of its “versions” as a Data-tier Application
Here you can see that the Data-tier functionality has added a __RefactorLog where it will keep track of Versioning and changes for you as part of being a Data-tier application. This is a good thing.
Last thing to do is go into Visual Studio and Check in your Data-Tier Application database project to TFS (or Git-Hub, or SVN, or what ever source control system you use, just please use source control!)
Now to move the data
Ok, so I have actually been moving a database from our VPN to Azure as I have been making up this walk-thru (this is more for my co-workers at Red Jungle, but I thought I might as well share it with you as well). So up to this point we have
- Moved and versioned the database from the original hosting Virtual Machine onto my Development machine
- Converted the Database structure to a SQL Database Project
- Targeted the SQL Azure platform
- “Converted” the database to be a Data-Tier Application
- Set Versioning up.
So that’s great, I now have the database that our SaaS product uses deployed to SQL Azure, I could now in theory just change the connection string in our SaaS product and it should start using the SQL Azure database (and in fact it does…). Only one small problem, there is no data in that database, Red Jungle’s clients won’t like having to re-enter all their data…
So I guess I should have just restored the Backup I took of our SaaS database to SQL Azure. Well there is a problem there as well, you see, SQL Azure doesn’t allow for Backups and Restores as you and I have come to know them in SSMS. In order for SQL Azure to be massively parallel and scalable and replicable, Microsoft had to make things work a little bit different.
This is Where we now learn about the .bacpac file format and Exporting a Data-tier Application. Again you can follow that link to read in depth about it, but I’ll give you the highlights here.
First let’s start by going back to that database backup I downloaded and restored to my development machine. Now first things first, if any changes had to be made to the Data-tier application project in VS2012, I’ll need to add a Schema Compare to my project,
and run it comparing my Project to my target database on my machine, and update the database on my machine so it is the correct schema.
Next if the database I grabbed off the Virtual Machine was not deployed as a Data-tier application, (which in this case it wasn’t) I will need to “publish” the project again, but this time against the local version of the database. This will add the versioning information to the database making it a true Data-tier application as well.
Once that is done, next I right mouse click on the database in my local SQL server and under task look at the Data-tier Application options
Hang on a moment what’s that “Deploy Database to SQL Azure…” option and why didn’t we use it before? Well 3 reasons
- The Database to be moved to Azure STILL needs to be converted to a Data-tier application, so we still would have had to do 80% of the work we already did
- When deploying a database to SQL Azure you can only deploy a NEW database; that is the <funkystring>.database.windows.net,1433 SQL Azure server you have set up thru the Azure Portal won’t let you overwrite the existing database, you will have to add a new one, which as long as you plan ahead could work for you, but still you do need to learn about how to backup and restore in this new context of Data-tier Application, so this is as good a time to learn and practice as any.
- Because when I started this tutorial I had never yet done a right mouse click on the latest edition of SQL Data Tools since the update was installed on my machine and I didn’t know they had added this feature until I got to this point in the article… and I don’t feel like re-writing it from scratch. ;-p
You might also notice the “Extract Data-tier Application…” option, the difference between Extract and Export, is Extract will just pull out the Data-tier schema, basically giving you an empty database of the current version, whereas the Export option pulls out the data AS WELL as the schema, it is akin to the old .bak file we are used to.
So back to the “correct” way, Now that we have everything set up correctly, select the “Export Data-tier Application…” option.
In this case we want to save it to Windows Azure (if you haven’t yet, go set up a Storage Account and a Container in Azure).
Click the Connect… button
Now retrieve your values for Windows Azure from online, you can find the account key by clicking on the Manage Keys on the Storage Home Page
Once you have those values fill out the settings dialog appropriately.
Click Next and you are taken to the “Summary” page, finally click Finish.
This basically makes what we have know as a .bak file, compresses it all up and uploads it to the Windows Azure Storage Account in a Blob.
The rest now all happens from the Azure Management portal.
at the bottom of the page in the ribbon you should see “import” Click the Import then follow the dialog to connect to the storage container where you dropped the .bacpac file, and fill in the settings. You will need to import this database using a new name, for example in the dialog below I just tacked on the word “Backup” to the original database name Making sure the Configure Advanced database settings are set, click to go to the next page, set the edition and database size and click Go. Then go get yourself a cup of coffee, this is going to take a while!
- Open the Azure Management Portal and login
- Click to enter the DB section
Then once the database with the data has been properly imported you rename it, using the old Silverlight Manage Console, and issue T-SQL commands into a sql command prompt window.
ALTER DATABASE Database1
MODIFY NAME = Database1_OLD
WAITFOR DELAY '00:00:30'
ALTER DATABASE Database1_copy_02_01_2012
MODIFY NAME = Database1
Okay, that’s the “official” way to do it, but if I have to fall back to the Silverlight management console, and do esoteric T-Sql commands well I’d rather do Powershell (and if you know me you know that means I don’t want to do that!).
So instead the easiest thing to do is just delete the original database, and use that “Deploy Database to SQL Azure…” command that I didn’t know about! I just specified the name for the Database to be the same as the one I had just deleted, and called it a day.
Which is exactly what I did!
Well that gets the database off the Virtual Machine and onto SQL Azure, the last step was to go onto the virtual machine and change the connection string to connect the new SQL Azure database.
Next up Loading Certificates for your Azure Website from a Blob.