A Small Selection of SQL Azure System Queries

I had a question come up during my “Getting Started with SQL Azure” presentation at SQLSaturday #52 in Denver last Saturday that I did not know the answer to. It had to do with how you could see what your data transfer usage was in and out of a SQL Azure database. This is pretty important to know, since you will be billed at the rate of $0.10/GB In and $0.15/GB Out (for the North American and European data centers).  Knowing this information will help prevent getting an unpleasant surprise when you receive your monthly bill for SQL Azure.

SQL Server MVP Bob Beauchemin gave me a nudge in the right direction, and I was able to put together these queries, which I hope you find useful:

-- SQL Azure System Queries
-- Glenn Berry 
-- September 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- You must be connected to the master database to run these

-- Get bandwidth usage by database by hour (for billing)
SELECT database_name, direction, class, time_period, 
       quantity AS [KB Transferred], [time]
FROM sys.bandwidth_usage

-- Get number of databases by SKU for this SQL Azure account (for billing)
SELECT sku, quantity, [time]
FROM sys.database_usage

-- Get all SQL logins (which are the only kind) for this SQL Azure "instance"
FROM sys.sql_logins;

-- Get firewall rules for this SQL Azure "instance"
SELECT id, name, start_ip_address, end_ip_address, create_date, modify_date
FROM sys.firewall_rules;

-- Perhaps a look at future where you will be able to clone/backup a database?
SELECT database_id, [start_date], modify_date, percent_complete, error_code,
error_desc, [error_severity], [error_state]
FROM sys.dm_database_copies
ORDER BY [start_date] DESC;

-- Very unclear what this is for, beyond what you can infer from the name
SELECT instance_id, instance_name, [type_name], type_version, [description],
       type_stream, date_created, created_by, database_name
FROM dbo.sysdac_instances;

This entry was posted in SQL Azure. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s