SQL Azure Database MAXSIZE increased from 50 GB to 150 GB

From today, SQL Azure databases can be of size 150 GB with no additional cost. Also, they have introdued a price cap which lowers the effective cost per GB for customers with large databases.  Effective today, the maximum price per SQL Azure DB is $499.95. This change allows customers with 50 GB databases and larger to continue to grow without additional costs. Customers that use 150 GB DBs will see their effective price per GB drop by 67%.

Other cool features released include Reduced Data Transfer Prices and Simplified Pricing and Free Promotional Period for Service Bus.

• Relay Hours - $0.10 per 100
• Message Operations - $0.01 per 10,000

Check out the details here :
http://blogs.msdn.com/b/windowsazure/archive/2011/12/12/improved-developer-experience-interoperability-and-scalability-on-windows-azure.aspx

SQL Server Feature Limitations (SQL Azure Database)

SQL Azure database is very similar to the various versions of SQL Server in terms of T-SQL compatibility and the tabular data stream (TDS) protocal. However, not every feature in SQL Server is supported in SQL Azure at this time.

Here's a complete list of features that are not supported in SQL Azure.

http://msdn.microsoft.com/en-us/library/windowsazure/ff394115.aspx 

How to change Edition and MAX Size of a SQL Azure database

SQL Azure databases come in two editions - Web and Business.

Edition	Max Size Options (GB)
Web	1, 5
Business	10, 20, 30, 40 50

How to check what's your current Edition and Max Size? Check this post here: http://azure-howto.blogspot.com/2011/10/how-to-check-edition-and-max-size-of.html

A database gets created as Web edition with 1GB as the max size if you execute just a CREATE DATABASE DATABASE_NAME. We could specify the desired edition and max size during the creation of the database or we could alter the database later.


1. Example for CREATE DATABASE (SQL Azure Database)
http://msdn.microsoft.com/en-us/library/windowsazure/ee336274.aspx

CREATE DATABASE DATABASE_NAME
(EDITION='WEB', MAXSIZE=5 GB)

 2. ALTER DATABASE (SQL Azure Database)

http://msdn.microsoft.com/en-us/library/windowsazure/ff394109.aspx

ALTER DATABASE DATABASE_NAME

MODIFY (EDITION='BUSINESS', MAXSIZE=50 GB)

How to get row count of every table in a SQL Azure Database

Getting rowcount of a table using the count() function is not a good idea as it would take too long for a large table.  Worse, if you want to get the rowcount of every table in a database. Thankfully there is a dynamic view sys.dm_db_partition_stats that has the rowcounts readily available.

SELECT so.name as TableName, ddps.row_count as [RowCount]

FROM sys.objects so
JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID
JOIN sys.dm_db_partition_stats AS ddps
       ON si.OBJECT_ID = ddps.OBJECT_ID  AND si.index_id = ddps.index_id
WHERE si.index_id < 2  AND so.is_ms_shipped = 0
ORDER BY ddps.row_count DESC

Hope this is usefull!

How to check Edition and Max Size of SQL Azure Database trough T-SQL query

Its very often requierd to check what's the edition (Web, Business) and maximum size (1GB, 5GB, 10GB, 20GB, 30GB, 40GB, 50GB) of a SQL Azure database.  There are two ways to do this.

1. Check the database properties in the Azure portal https://windows.azure.com/
2. Run a T-SQL query against your database

I find the latter approach quite handy. Here's the query:

SELECT DATABASEPROPERTYEX('Database_Name', 'EDITION')

SELECT DATABASEPROPERTYEX('Database_Name', 'MaxSizeInBytes')
OR
SELECT CONVERT(BIGINT,DATABASEPROPERTYEX ( 'Database_Name' , 'MAXSIZEINBYTES'))/1024/1024/1024 AS 'MAXSIZE IN GB'

Remember that it needs to be executed on the database you want to check, not on the master database. Then you might ask why the database_name is required? I don't know! :)

Here's the full list of properties supported in this function: http://msdn.microsoft.com/en-us/library/ee621786.aspx

How to create a database copy in SQL Azure for backup

You can back up a SQL Azure database by copying it to a new database in SQL Azure. The new database that is created from the copy process is a fully functioning database, independent of the source database once the copy is complete. The new database will have the same edition and maximum size as the source database.

Here's the syntax.

CREATEDATABASE destination_database_name
AS COPY OF [source_server_name.]source_database_name

Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Azure server is not needed for the full duration of the copy process. The CREATE DATABASE statement will return control to the user before the database copy operation is complete. In other words, the CREATE DATABASE statement returns successfully when the database copy is still in progress. You can monitor the copy process with the sys.dm_database_copies and sys.databases views. After the copy process completes successfully, the destination database is transactionally consistent with the source database

Here's the code to monitor the copy process.

SELECT* FROM SYS.DM_DATABASE_COPIES
SELECT* FROM SYS.DATABASES WHERE NAME ='DESTINATION_DATABASE_NAME'

Read the complete MSDN article here: http://msdn.microsoft.com/en-us/library/ff951624.aspx

How to: Calculate SQL Azure DB/Database Size (sp_spaceused unavailable)

The good old sp_spaceused is unavailable in SQL Azure. But fortunately we have the underlying data view available. Its called sys.dm_db_partition_stats.

Here's a simple query to get the total "reserved space" of a database.

SELECT SUM(reserved_page_count) * 8.0 / 1024 AS 'Total Reserved Space (MB)' FROM sys.dm_db_partition_stats

You might also want to get the size of each table seperately. You just have to join it with the sys.objects table and remove the summation.

SELECT Name, reserved_page_count * 8.0 / 1024 AS 'Total Reserved Space (MB)' FROM sys.dm_db_partition_stats PS
JOIN sys.objects SO ON PS.object_id=SO.object_id

Hope this helps!