Managing Windows Azure SQL Database’s newest feature – Premium Database

The latest feature to make an appearance in Windows Azure SQL Database (actually it’s still a preview program) is the Premium Database. Physically, when you have a WASD database, you’re sharing that database and server with others. It’s possible that if one of your database “neighbors” decides to run a stress test, your database (and you) could be stressed as well. This is known as the “noisy neighbor” syndrome. Just like noisy neighbors in a hotel, you can call the management and complain and they may even “move” you, just like a hotel would. To guarantee a certain level of resources, WASD made the premium database available.

There’s two different reservation sizes, P1 and P2, and these sizes are defined in terms of CPU cores, concurrent active workers, number of sessions, data IOPS, and memory. The relevant reservation size guarantees you these resources. The reservations are defined using the term “service level objective” (SLO).

Once you’ve been accepted for the preview program, you can create a new premium level database using the Azure portal or PowerShell Azure cmdlets. You can also upgrade an existing database to premium and also downgrade from premium to “regular”, subject to limitations, as well as change the reservation size. BTW, the cmdlets are a boon for those of us that would like to manage WASD but don’t want to perform repetitive operations via a web-based GUI. I strongly suggest you give them a try if you’re doing any Azure work.

One of the things you need to get used to when moving from SQL Server to WASD is that WASD does not expose all the DMVs and metadata views that SQL Server does, BUT ALSO, WASD contains some DMV and metadata views that SQL Server does not have. To be able to work with the Premium Database feature, there are six new metadata tables, all beginning with “slo” in the DBO schema in the master database. For example, if I have a premium database named “consistant_db”, I can monitor the premium change history by using the SQL query:

SELECT *
FROM dbo.slo_assignment_history
WHERE database_name = ‘consistant_db’
ORDER BY operation_start_time DESC;

I can also monitor resource activity by looking at worker_counts in sys.resource_stats to make sure I’m getting my money’s worth or to decide when it’s best to update/downgrade to premium.

Keeping on top of WASD changes, as well as knowing how and when to use WASD can be, like keeping up with anything in the Azure space, chasing a moving target. At the SQLIntersection conference in Orlando in April, I’ll be presenting a full day post conference workshop on “Windows Azure SQL Database from A to Z”. Join me there for the whole story on managing, developing on, and interacting with this beast.

Cheers, @bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.