Nuno Filipe Godinho : Microsoft, SQL Azure

Check SQL Azure Database Associated Cost

NunoGodinho — Tue, 19 Oct 2010 00:16:42 GMT

Ok so I found out exactly the size of every table in my SQL Azure Database, but now I’d like to know a little bit more, since I’m paying and I want to know how the payment is related to each of my tables. So in order to do this and after reading this blog post from the SQL Azure Team, this is the statement I’ve come up with:

DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

Select @Edition, @SizeInBytes

So now I have the Size used in Bytes and the Edition of the SQL Azure Database that I’m using, and so I can now calculate how much is the cost per byte.

DECLARE @CostPerByte float
SELECT   @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN (CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN (CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95
         END) / @SizeInBytes

Ok. Now I know exactly what is the cost per byte, and so what I need to know is exactly how much cost is associated with each Table.

select sys.objects.name, @CostPerByte * (sum(reserved_page_count) * 8192)
from sys.dm_db_partition_stats,
sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name

So with this I know exactly how my cost is being split by each table. So now I only want to see if everything in the end comes up to the correct result.

Select ROUND(SUM(Cost.TotalCost), 2)
from (    select sys.objects.name TableName, @CostPerByte * sum(reserved_page_count) * 8192 TotalCost
        from sys.dm_db_partition_stats,
             sys.objects
        where sys.dm_db_partition_stats.object_id = sys.objects.object_id
        group by sys.objects.name) Cost

Ok. So the result is correct. Sweet.

Check SQL Azure Database Size

NunoGodinho — Mon, 18 Oct 2010 23:26:39 GMT

I was looking at my SQL Azure Database and suddenly I saw that I had to clean some space because it was already beyond the maximum quota that I had defined for the Database, but I needed to know exactly where the most space was being used in order to better understand not only what was the size, but why and where was the space being used.

So my first action was, Ok so I’ll use the storeprocedure sp_spaceused.

Exec sp_spaceused

So with this I know exactly the space that my database is using, and if I use this:

Exec sp_spaceused <TableName>

I’ll get the space that is being used by the Table that I specify.

Ok. So I tried this on my local SQL Server and all was good, and so off to SQL Azure, and I get the following error:

Could not find stored procedure 'sp_spaceused'.

So. Not good. How will I get the needed results? So I used the following statement:

select sum(reserved_page_count) * 8.0 / 1024
from sys.dm_db_partition_stats

And this statement gives me the exact size that I have in my SQLAzure Database, but I need to know exactly how much space I have in each table, so I use the following statement:

select sys.objects.name, sum(reserved_page_count) * 8.0 / 1024
from sys.dm_db_partition_stats,
sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name

This last statement gives me a list of all tables that exist in the specified SQL Azure Database and the exact size that they have.

I used this based on Ryan Dunn’s blog post available here.

Hope this helps you.

PDC09 – Day Two – Developing Advanced Applications with Windows Azure

NunoGodinho — Wed, 18 Nov 2009 22:56:04 GMT

Steve Marx – Technology Strategist

Now we can have the ability to have more than a Web and a Work Role, since we have several different types of Roles.

Example:

IIS Role
SMTP Role
…

Now the there is the new Storage API that is currently being shipped with the SDK and not only a sample.

Some new features:

Inter-role communication
Non-Http endpoints
Role instance lifecycle
VM Sizes
Full-trust in the Cloud
Local Storage

Upgrading the local storage

How to model you application

Draw the boxes and arrows
Each box is a role

Which receive traffic from the internet
Which need IIS?

Receive Traffic: Input Endpoints

Different ports on same domain
There’s always load balancing
Declared in ServiceDefinition.csdef
Handled by IIS

InputEndpoint

Defined the protocol, port and name of the endpoint that will opened in order to listen for the requests
Protocols supported

Http
Https
Tcp

RoleEnvironment is the class that allow us to communicate with the Windows Azure fabric, for example in order to know the Configurations

Aside about Email

Spammers will use Windows Azure then the IP ranges can get blacklisted

Best Practices: Use a relay service in order not to have your mails marked as SPAM

Send mail via a rely mail
Receive mail is fine

Searching

Lucene.NET

Popular search API
.NET port of original Java implementation

Index data

Bring index up to speed on startup
Continually update index

Serve searched (via WCF)

Asynchronous Work without a Queue

Queues deliver messages to one worker only
We need every worker to see the message

Solution

use Table instead

Build your own secondary index
Order references

Initialization of Roles

Now we have:

OnStart() – “busy” state, initialization
Run() – do work
OnStop() – traffic stops, graceful shutdown

Internal Endpoints

Declare endpoints in ServiceDefinition.csdef
API to find out what port to listen on RoleEnvironment in Order to know if the port or anything change about the endpoint since the last call

How to Get More RAM

RAMDiectory pouts everything in memory
Choose the VM Size:

Small
Medium
Large
Extra-Large

PDC09 – Day One – SQL Azure Present & Futures

NunoGodinho — Tue, 17 Nov 2009 23:37:57 GMT

Data Platform as a Service

In the future will have Reporting, Data Analytics

SQL Azure Database

Relational Database service

SQL Server technology foundation
Scalable
Symmetrical

SQL Azure Provisioning

Each account has zero or more servers

Azure wide provisioned in a common portal
Billing instrument

Each server has one or more databases

contains metadata about the databases
Unit of authentication and security
Unit of Geo-location
The server is a logical grouping of you DBs

Each database has standard SQL Object

Unit of consistency
Contains users, tables, views, indexes, and so on,

Global Availability

North Europe
Southeast Asia
North and South USA

Futures

Goal: Provide and end-to-end experience for enterprise departmental apps and Saas ISVs scenarios
Improved Tools

Today we have

VS, SSMS, ADO.NET, ADO.NET Data Service

Data Backups

Multiple replicas of all databases with automatic failover. System backups for additional protection
Use Database Clone to create a clone of the Database with every capabilities of the original one
During 1H 2010

Continuous Backups

Configurable retention and lag period
Backups available for read operations
Local and regional options
During 2H 2010

Operational Models

SaaS

Provisioning APIs for ISVs
Template database support
Meta-data tracking
Additional billing scenarios

Scale-out support –Today

Databases and workloads partitioning is a classic

Better price/performance
Levels of throughput not possible with a single machine

What SQL Azure addresses

Highly available service on top of commodity hardware
Zero administration

There will be available several options and the 10GBs of space is not an issue

Customer Feedback

Add support for profiler, DMV’s
SQLCLR
and much more

Codename “Vidalia”

Sharing to trustworthy collaboration

Cloud is a grate place to connect
Empowers multiple parties
Businesses need control

Very interesting demos, need to see more about it.