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 ORDER BY [time] DESC; -- Get number of databases by SKU for this SQL Azure account (for billing) SELECT sku, quantity, [time] FROM sys.database_usage ORDER BY [time] DESC; -- Get all SQL logins (which are the only kind) for this SQL Azure "instance" SELECT * 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;