November 2010 - Posts
Azure Summit in Stockholm was a two day event filled with great presentations about the Azure Platform. I think people are now beginning to understand that the cloud will change our businesses a lot! 15 years ago not many understood the real potential of internet and now we are connected everywhere. Will cloud computing lead to as dramatic changes as the internet did?
All the presentations, including mine, are available on the Azure Summit website. However they are available in Swedish only.
By the way, my presentation was voted as the winner of the speaker contest. What did I win? A balloon flight with Microsoft's Azure balloon.
Next time I present SQL Azure will be at TechDays 2011 in Örebro.
I've so far not seen any article on why anyone should use SQL Azure so I thought it is time someone writes about that. This article is an introduction to why you might be interested in a cloud database and a summary of the differences compared to "ordinary" SQL Server. Finally I've included links to some useful resources.
Why would you need a cloud database?
Maybe you've read the story about the unknown IT multi-millionaire Markus Persson? If not, here is an article (in Swedish only). He's not so unknown any longer but I think he never imagined what a tremendous success his game Minecraft would be. Even if he haven't spent a single dollar on marketing it became so successful that the server capacity was not sufficient for the all the new paying players.
Of course it is very hard to predict how popular an online game or service will be. It can also change dramatically if you get good publicity (or bad). How can you then make sure you always have the right server capacity for your users?
This is where Azure and the cloud comes in. It is an elastic platform which provides you the means to instantly increase or decrease your server capacity. You only pay for what you use and don't have to estimate capacity needs and buy a reserve capacity. You won't run into the problem of "how can I do database maintenance when I always have millions of players online".
Database-as-a-service vs physical databases
What kind of demands can you make on a database? Why would anyone use a database? Would it not be easier to just write data to files instead?
I can give you at least four reasons why you should in most applications use a database:
Availability - let's say you are running business critical applications (like in healthcare). Then you don't want to rely on a single computer or disk. SQL Server comes with built-in support for high availability such as clustering, database mirroring, etc which can be configured by a DBA. With SQL Azure you automatically get high availability with zero configuration.
Security - with a database you have a much finer control of security than on a file. You can also audit the usage, who has accessed what and who has updated what. Auditing has become increasingly important since the last financial crisis.
Correctness - you can enforce consistency on a database through foreign keys, unique indexes, check constraints, etc. Also you can use transaction isolation to prevent chaos when thousands of users work with the database simultaneously. In almost any OLTP application you need to use transactions if you want the application to work correctly with many users.
Performance - databases have several mechanisms to optimize performance such as caching, query optimizers, etc.
I'm not saying that the above features cannot be achived by files and custom code, but with a database you needn't reinvent the wheel every time.
As a developer you just want the database features but probably don't want to spend time setting up clustering, applying security patches, worry about disk space, etc. Those are the things a DBA does for you.
With SQL Azure you get at least three physical databases for every single database your create. That is how SQL Azure ensures you get the 99.9% uptime. Also you don't need to spend any time on security patches, disk space, moving databases between different servers to handle increased load, etc.
SQL Server Consolidation
It has become increasingly popular to consolidate SQL Server databases, because it can save you money on hardware, licenses and maintenance work.
I've seen three main methods to consolidate databases:
Virtualization - which simply means moving physical machines to virtual machines. The easiest way, but also gives the least savings because every virtual machine needs its own OS and occupy a lot of resources.
Instance consolidation - which means that you move several SQL Server instances to the same physical server and let them share the OS. Better efficiency than virtualization, but still resources are not shared between instances.
Database consolidation - where you merge SQL Server instances. Even more efficient than instance consolidation.
I've added SQL Azure as a method in the picture above. It takes more effort than any of the other methods because SQL Azure is a bit different from a physical database but it also gives you the biggest savings.
Some differences between SQL Server and SQL Azure
So what really are the differences between SQL Server and SQL Azure? You can find a list of unsupported features in SQL Azure here. But what are the fundamental differences?
In SQL Server you usually rely on Integrated Security. In SQL Azure you have to use SQL Server authentication.
In SQL Server an idle connection almost never goes down. If it does, there is usually a serious error. In SQL Azure an idle connection goes down after 5 minutes and can go down for a lot of other reasons than timeout. You need to think about this especially if you use connection pooling.
The default transaction isolation level in SQL Azure is READ_COMMITTED_SNAPSHOT while it is READ_COMMITTED in SQL Server. That can make applications behave differently.
There is no SQL Server Agent in SQL Azure. Therefore it does not provide any functionality that relies on the SQL Server agent, such as CDC or similar.
You cannot currently backup a SQL Azure database. However you can easily copy them instead. But you don't have access to transaction log backups so you won't be able to restore the database to an arbitrary state in time.
Heap tables are not supported in SQL Azure (except in tempdb). A heap table is a table which does not have a clustered index. This is not a big issue since clustered indexes almost always are a much better choice than heap tables.
You cannot write cross-database references in SQL Azure. In SQL Server you can combine data from databases in the same instance.
Also you cannot access linked servers in SQL Azure.
Default database collation in SQL Azure is SQL_LATIN_1 and cannot be changed. However Microsoft has promised support for other default database collations in the future.
Fulltext indexing is currently not supported in SQL Azure.
The above list may look long, but most existing applications that use SQL Server would work well also on SQL Azure. The most common issue I think will be connection pooling and handling of lost connections. The second most common issue (for people who live outside of US like me) will probably be the default database collation.
Tempdb in SQL Azure
When you start using SQL Azure you will notice that you automatically get a master database. Do you also get a tempdb database?
Yes, there is a tempdb database. However there are some limitations how you can access it. Which of the following queries do you think works?
CREATE TABLE tempdb.dbo.Test ( col1 int not null )
CREATE TABLE #Test ( col1 int not null )
CREATE TABLE ##Test ( col1 int not null )
Only the second option works. As mentioned earlier you cannot write cross-database references (like the first option). The third option is forbidden because you cannot create global tempdb objects.
Can you have heap tables (non clustered) in tempdb? Yes you can. Still it doesn't support the SELECT INTO statement, which I believe was disabled because it creates heap tables.
SQL Azure is very useful for applications where you don't know how many users you will have in the future, since it provides you with the ability to scale up and down on demand. Especially with the new SQL Azure Federation support (that I will write about later). Also it is of course a very useful storage platform for Windows Azure based applications.
Links and resources
This article is a follow up to my earlier article with tips on SQL Azure.
Data-tier applications (DACs)
There are two main options on how to deploy your database solutions to SQL Azure, unless you want to do it manually. These options are to use Data-tier applications or to use the SQL Azure Migation Wizard. The latter is an open source tool that copies your database (including the data) in any direction. Data-tier applications however is a new feature in SQL Server 2008 R2 and SQL Azure that enables you to develop a database in Visual Studio like you would develop a C# applications or similar. Existing databases can easily be converted to a Data-tier applications as long as you don't use any features that are not supported (such as XML).
Deploying a Data-tier application in SQL Azure
You can't deploy it from Visual Studio. Instead build your project, connect to SQL Azure through SQL Server Management Studio, right-click on your instance and choose "Deploy Data-tier Application..." in the menu. Click Next in the wizard and browse to your .dacpac file.
Upgrading a Data-tier application in SQL Azure is a manual process. You'll need to deploy it under a new name, copy the data manually from the old database and rename it. How to rename a SQL Azure database? Issue the T-SQL statement: "ALTER DATABASE database_name MODIFY NAME = new_database_name".
To add logins and users in your Data-tier application you need to create the login with the MUST_CHANGE password option.
CREATE LOGIN [username] WITH PASSWORD='' MUST_CHANGE, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
You map it to a database user in the same way as "ordinary" SQL Server:
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
Then you need to add it to one or more roles by executing this T-SQL statement within your database (make sure you don't execute it within the master database):
EXEC sp_addrolemember N'my_db_role', N'username'
Finally you need to assign a password and enable the login by executing the following T-SQL statement on the master database.
ALTER LOGIN [username] WITH PASSWORD='mypassword'
ALTER LOGIN [username] ENABLE
Changing the edition of your SQL Azure database or increasing the size
You can increase or decrease the size of the database or change the edition at any time. Just issue the following T-SQL statement on the master database.
ALTER DATABASE database_name
MODIFY (MAXSIZE = x GB, EDITION = 'x')
Edition can currently be either web or business. Database size can currently be either 1, 5, 10, 20, 30, 40 or 50 GB.
Resizing or changing the edition of your database takes almost no time so you can easily start with a smaller database and increase it on demand (like you should be able to with a cloud database).
Want to learn about SQL Azure? I will present the differences compared to "ordinary" SQL Server and what you as a developer or database administrator need to know to successfully move to SQL Azure. I will also give a guided tour on hour to get started with SQL Azure.
The event will be held at Microsoft's office in Stockholm on the 18th - 19th of November.
Click here to go to the event website.