It seems to be SQL Azure night for me tonight…  I thought I would try out some of the new features in SQL Azure Service Update 1. First I tried ALTER DATABASE to switch between the Web Edition and Business Edition size for a SQL Azure database. I discovered that you have to be connected to the master database in your SQL Azure “instance” (which makes sense). Remember, you cannot just run USE master, or switch the database context in the dropdown in the toolbar in SSMS. You have to be using the November CTP version of SQL Server 2008 R2 SSMS to work with SQL Azure in SSMS.

You have to change the connection to connect specifically to the database that you want (which is a little inconvenient). Then you can run your ALTER DATABASE command. I refreshed the Server Administration web page in the SQL Azure portal to confirm that the database edition had changed.

Then, I tried running the six DMV queries, and they all worked just fine. Finally, I waited more than five minutes (but less than 30 minutes) in SSMS, and verified that SQL Azure did not drop my connection. Everything worked just fine for these three new features.

-- Testing new features in SQL Azure Service Update 1
-- Glenn Berry
-- 2/18/2010
-- Must connect to master database first
-- Remember, there is no USE databasename allowed
-- Switch to Business Edition
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 10 GB);
-- Switch to Web Edition
ALTER DATABASE AdventureWorksLT2008R2 
MODIFY (MAXSIZE = 1 GB);
-- Connect to a user database first
-- Remember, there is no USE databasename allowed
-- Test new DMV queries in SQL Azure
-- Get row counts
SELECT OBJECT_NAME(object_id) AS [ObjectName], *
FROM sys.dm_db_partition_stats; 
SELECT *
FROM sys.dm_exec_connections; 
SELECT *
FROM sys.dm_exec_requests; 
SELECT *
FROM sys.dm_exec_sessions; 
SELECT *
FROM sys.dm_tran_database_transactions;  
SELECT *
FROM sys.dm_tran_active_transactions;