The Sheep-Pen of the Shaun

News

Shaun, the author of this blog is a semi-geek, clumsy developer, passionate speaker and incapable architect with about 10 years’ experience in .NET and JavaScript. He hopes to prove that software development is art rather than manufacturing. He's into cloud computing platform and technologies (Windows Azure, Amazon and Aliyun) and right now, Shaun is being attracted by JavaScript (Angular.js and Node.js) and he likes it.

Shaun is working at Worktile Inc. as the chief architect for overall design and develop worktile, a web-based collaboration and task management tool, and lesschat, a real-time communication aggregation tool.

.NET

Today Microsoft announced the new service tiers in Azure SQL Database in the blog in preview phase. Currently SQL Database offers two type of service: web and business with the database size limitation from 100MB to 150GB. And now, the new service tiers provides database size up to 500GB with many cool features. And one of them I'm interested in is the point-in-time restore (a.k.a. PITR).

Create SQL Database in New Edition

First of all, we need to sign up to activate this preview feature. Just go to azure account page and select preview features tab, find the "New Service Tiers for SQL Database" and click "try it now". Below is the page I was approved for this feature.

Once we got this preview feature available we can back to the portal and create a new SQL Database. In the popup dialog we will find that there are three new editions next the original "web" and "business". These are the new service tiers introduced in this update, "basic", "standard" and "premium".

Below is the official description about these editions.

1, Basic: Designed for applications with a light transactional workload. Performance objectives for Basic provide a predictable hourly transaction rate. 2, Standard: Standard is the go-to option for getting started with cloud-designed business applications. It offers mid-level performance and business continuity features. Performance objectives for Standard deliver predictable per minute transaction rates. 3, Premium: Designed for mission-critical databases, Premium offers the highest performance levels and access to advanced business continuity features. Performance objectives for Premium deliver predictable per second transaction rates.

The database size limitation for "basic" is 100MB - 2GB, "standard" is up to 250GB while "premium" is up to 500GB. I believe 500GB is big enough for all services.

Besides the database size, each tiers offers different performance level which is in the unit named DTU (database throughput units) that is a combination of CPU, memory, physical reads, etc. into a single unit, which we can compare with other editions simply. More information of DTU is described here.

The performance levels for each editions are listed below that we can select when we create a new database, and modify when it's running.

Edition

Performance Level

DTU

Max Database Size

Basic

1

2GB

Standard

S1

5

250GB

S2

25

Premium

P1

100

500GB

P2

200

P3

800

Below I selected "Premium" edition with P1 performance level, which provides at least 100 times power than the "Basic" edition.

Next I need to create a new SQL Database Server and we must ensure that the server must support the new editions. You will receive an error message if you attempt to create a database in new editions on a server that supports old editions.

Then after several minutes our database (and the server) will be created and read to use.

As below I created a new table named "account" and insert some data.

Restore Database to a Previously Time Point

With new editions SQL Database provides automatically backup feature, which means our database will be backup by Azure and we can restore them in case any data broken, such as forgot adding the "WHERE" statement when we want to delete some records.

The backup executed automatically in background, without any manual effort, stored the database in Azure Storage with geo-replication. "Basic" edition provides daily backup and stores in the past 24 hours. Both "Standard" and "Premium" editions provides point-in-time backup. The only different is that, "Standard" stores backups in the past 7 days while "Premium" stores 35 days.

Now let's back to the portal we will found there's a button named "Restore" in SQL Database list page.

When I clicked there will a dialog appeared where I can specify which time point I would like to restore for this database. As below I was going to restore my database to the first point.

Then we will find there's a new SQL database appeared in the list which is the one I was restoring. So this is another feature called "side-by-side restore", which means SQL Database will restore in a new database instead of overwrite. With this feature we can restore one database with multiple points and compare the data between them.

After several minutes the restore operation was finished and it's a full, dedicate SQL Database that we can use.

For example we can run query on it and as you can see, since I restore the first time point there's no "account" table I created.

Restore a Deleted Database

Besides point-in-time restore, SQL Database also provides the feature that we can restore a deleted database. For example, I deleted my two databases form portal.

Then if we open "Deleted Databases" tab we will find these two databases listed.

And we can select the database we wanted to restore, click "Restore" button and specify the time point we'd like. SQL Database will restore it back to us.

Below is the screenshot that I performed query on the database I restored from the deleted list, and you can see the table and data are all backed.

If we wants to remove all databases, backups we need to delete the SQL Database Server.

Summary

In this post I described the new service tiers for Azure SQL Database, especially its automatically backup feature.

We need to be very carefully when dealing with data, especially working on the production environment. I recommended to backup all data before we perform any actions so that we can restore in any mistakes. But if we forgot backup, then we will have a big trouble if deleted or dropped anything wrong.

The new SQL Database edition provides point-in-time restore which protect us. It performs automatically backup so that we can restore the database is anything wrong we did.