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!

How To: Manage SQL Azure Security - Logins, Users, Roles, Schemas and Permissions

SQL Azure too has its security management system very similar to the SQL on-premise versions. It bascially consists of the following:

1. Logins - Server level
2. Users - Database level, mapped to server logins
3. Schemas - Database level, authorized/owned by a user or another schema
4. Roles - Database level, authorized/owned  by a user or another role
5. Permissions - Database level, permission like SELECT, DELETE, ALTER etc. on objects/schemas granted to users/roles

Let's take a closer look  at each of these.

When you create a server in SQL Azure, it asks you to create a login at the same time.  That login acts as the administrative login, which has access to all databases in that server. However, you might want to create other logins with less privileges. As of now, the SQL Azure portal doesn't have any UI to create these extra logins. So you'll have to resort to running T-SQL statements.

Note: All of the below steps are performed using the administrative login mentioned above.

Creating Logins
Login to the master database and run the following t-sql statement.

CREATE LOGIN MyServerLogin WITH password='My#Password123'

This statement creates a login ID in the server. This a a normal login ID, which doesn't have access to any of the databases in that server. So if you try to login to the azure server with this account (either from SQL Server Management Studio 2008 R2 - SSMS  OR from Azure Portal) you would get an error saying this user doesn't have access to the master database. So the next step is to map this login to the required databases, not necessarily to the master db.

Mapping to Databases
Login to the required database where you want to grant access to the new login and run the following statement.

CREATE USER MyDatabaseUser FROM LOGIN MyServerLogin;

Now this login ID "MyServerLogin" has 'public' access the above database. Now you can successfully login to the azure serer using this login id and password and it will automatically connect to the above database.

Mapping to existing database roles
There are several pre-defined database roles. You have to add the user into any of the roles to give the user sufficient permissions to operate on the database.
 

EXEC sp_addrolemember 'db_datareader', 'MyDatabaseUser';

Creating custom roles
If none of the pre-defined roles suit your purpose, then you can create custom roles and grant hand-picked permissions to it.

-- Create the database role
CREATE ROLE MyDBRole AUTHORIZATION [dbo]
GO

Granting permissions to roles
Permissions to perform operations on a whole schema or individual objects can be granted to a whole role or a specific user.

Here in this example we are giving all possible permissions on the DBO schema (All database objects belong to this schema normally. However, custom schemas can be very well created)

-- Grant access rights to a specific schema in the database
GRANT
      ALTER,
      CONTROL,
      DELETE,
      EXECUTE,
      INSERT,
      REFERENCES,
      SELECT,
      TAKE OWNERSHIP,
      UPDATE,
      VIEW DEFINITION
ON SCHEMA::dbo
      TO MyDbRole
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'MyDBRole', 'MyDBUser'
GO

Revoking permissions from roles
Permissions once given can be revoked from it as well using the DENY statement:

-- Revoke access rights on a schema from a role
DENY ALTER
ON SCHEMA::dbo
      TO MyDbRole

For more information on the T-SQL syntax, refer the below articles on MSDN:

CREATE LOGIN
CREATE USER
CREATE ROLE

Hope this helps to understand the fundamental security aspects of SQL Azure!

How To: Set Firewall rule to connect to Sql Azure - sp_set_firewall_rule

When you try to connect to SQL Azure and get an error like the one below, your IP address is not configured access to the server.

Cannot connect to xxxx.database.windows.net.
Cannot open server 'xxxx' requested by the login. Client with IP address '192.168.1.1' is not allowed to access the server.  To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range.  It may take up to five minutes for this change to take effect.

Login failed for user 'azureuser'.

This session has been assigned a tracing ID of '41c2afa1-0097-4db8-b17a-c7bbef547287'.  Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 40615)

For help, click:go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=40615&LinkId=20476

You need to add your IP Address to the firewall rules of the SQL Azure to get yourself connected. There are two ways to do this. One using the UI and the other using the stored procedure - sp_set_firewall_rule.

1. Login to the Azure management portal https://windows.azure.com by providing your Live ID credentialsClick on Database
2. Expand subscrptions, choose your subscription, expand it and select your server
   
    
3. On the main area, click on the "Firewall Rules" box.
4. Click on "Add"
5. Provide a name. (For example - "To Allow Me, The Azure Pro")
6. Provide your IP Address in the start and end boxes. (Your current IP Address is displayed at this dialog box at the bottom. You might also like to provide an address range. For example from 192.168.1.0 to 192.168.1.255)
7. You are done! Start connecting now.

Using the sp_set_firewall_rule

1. After step #3 above, expand the server to see the databases
2. Select MASTER and clik on MANAGE on the ribbon above
3. Database Manager for SQL Azure opens up in a new window (https://manage-sgp.sql.azure.com/)
4. Provide the credentials for the Database (normally a different one)
5. Once you are in, click on "New Query" on the toolbar/ribbon
6. Execure the SP: exec sp_set_firewall_rule N'Example setting','YOU IP ADDRESS', 'YOUR IP ADDRESS' (You might also like to provide an address range. For example from 192.168.1.0 to 192.168.1.255)
7. Your done!

Hope this helped!

How To: Add storage accounts (blob, table, queue) to your Azure Subscription

So you have a Windows Azure account and want to consume the azure storage services viz. Table Service, Blob Service and Queue Service. Alright. Here's how you can go and create storage accounts to your azure account.

1. Login to the Azure Management Portal (https://windows.azure.com) with your Live ID credentials.
2. Click on the "Hosted Services, Storage Accounts and CDN" on the left menu bar
   
3. You can see the storage accounts count on the left menu bar (For example: Storage Accounts (0))
4. Now click on the "New Storage Account" on the toolbar
   
5. Provide the name for the storage account, your desired region where it should get created and click OK.
6. Now you have a storage account with Azure Table services, Azure Blob Services and Azure Queue Services.
   
7. Note the URLs of the storage services on the right toolbar.
8. Typically the URLs will be:
1. storage_account_name.blob.core.windows.net
2. storage_account_name.table.core.windows.net
3. storage_account_name.queue.core.windows.net
9. You are done and now you can connect to these storage services using  these URLs and the "Primary Access Key" displayed at the top.

Hope this helped!