Search results matching tag 'Development'

One more reason to to understand query plans, not directly performance related

drsql — Wed, 23 Jan 2013 04:28:00 GMT

One of the things that separates a good programmer from a great one is a firm understanding about what is going on inside the computer. For some programming languages, it is very obvious what is going on inside the computer because you are working at a very low level. For example, if you are a C/C++ programmer writing an OS, you will know a lot about the hardware as you will interact with it directly. As a .NET programmer you are more encapsulated from the hardware experience, making use of the .NET framework.

None of the aforementioned programming languages comes anywhere close to the level of encapsulation that we SQL programmers work with. When you execute a statement like:

SELECT *
FROM Tablename

A firestorm of code is executed to optimize your query, find the data on disk, fetch that data, format it for presentation, and then send it to the client. And this is the super dumbed down version. SQL is a declarative language, where basically we format a question or task for the system to execute without telling it how. It is my favorite type of language because all of the pushing bits around get tedious. However, what is important for the professional SQL programmer is to have some understanding of what is going under the covers, understanding query plans, disk IO, CPU, etc. Not necessarily to the depth that Glenn Alan Berry (http://sqlserverperformance.wordpress.com/) does, but certainly a working knowledge.

Performance is the obvious reason, since it is clearly valuable to be able to optimize a query, but sometimes it can come in handy to debug an issue you are having with a query. Today, I ran across an optimizer condition that, while perfectly understandable in functional terms, would have driven me closer to nuts if I hadn’t been able to read a query plan. The problem came in based on the number of rows returned, either it worked perfectly or it failed with an overflow condition. Each query seemingly touches the exact same rows in the table where the overflow data exists…or did it.

The setup. The real query that the problem was discovered in was our data warehouse, and was a star schema configuration with 20+ joins. In the reproduction, I will use a simple table of numbers to serve as the primary table of the query.

create table ExampleTable -- It really doesn’t matter what this table has. The datevalue column will be used to
                           -- join to the date table, that I will load from the
(                          -- from the values I put in this table to make sure all data does exist
    i int constraint PKExampleTable primary key,
    dateValue date
)

;with digits (i) as( --(The code for this comes from my snippet page: http://www.drsql.org/Pages/Snippets.aspx).
                    select 1 as i union all select 2 as i union all select 3 union all
                    select 4 union all select 5 union all select 6 union all select 7 union all
                    select 8 union all select 9 union all select 0)
,sequence (i) as (
                    select D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i)
                    from digits as D1, digits AS D2, digits AS D3 ,digits AS D4, digits as D5, digits As D6
)
insert into ExampleTable(i, dateValue)
select i, dateadd(day, i % 10,getdate()) -- Puts in 10 different date values
from sequence
where i > 0 and i < 1000
order by i

Next I will load the date table with all of the distinct dateValue values that we loaded into the ExampleTable, plus one, which is the max date value for the datatype. In the “real” world case, this is one of our surrogate null values we use to indicate that it is the end date. (Yes, we are ignoring the Y10K problem.)

create table date
(
datevalue date constraint PKDate primary key
)

insert into date
select distinct dateValue
from ExampleTable
union all
select '99991231'
go

In the typical usage, the number of rows is quite small. In our queries, we are adding 1 to the dateValue to establish a range of a day (in the real query it was actually a month). Executing the following query that returns 99 rows is successful:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue
where i < 100

However, remove the where clause (causing the query to return 999 rows):

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
          join date
             on date.dateValue = ExampleTable.dateValue

And you will see that this results in an overflow condition...

Msg 517, Level 16, State 3, Line 2
Adding a value to a 'date' column caused an overflow.

Hmmm, this could be one of those days where I don’t get a lot of sleep :). Next up, I check the max date value that can be returned.

--show that the top value that could be returned is < maxdate
select max(date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

At this point, I start feeling like I am going nuts. The value returned is 2013-01-30. So no data is actually returned that should be too large for our date column… So then I think, well, let's add one to that value and take the max:

select max(date.dateValue), max(dateadd(day,1,date.dateValue))
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

This returns, mockingly:

Msg 517, Level 16, State 3, Line 2
Adding a value to a 'date' column caused an overflow.

So, since it worked with fewer rows earlier. I decide to try lowering the number of rows again, this time using a derived table, and it DOESN’T error out, even though it is obvious (because I stacked the deck…data) that the same data is just repeated for the dateValue, particularly since we get the same max dateValue as we did earlier.

select max(date.dateValue), max(dateadd(day,1,date.dateValue))
from   (select top 100 * from ExampleTable order by i) as ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

Well, you are possibly thinking, this just doesn't make sense. It is how I felt too after trying to do the logic in my head. I will admit that I didn’t know about query plans I would have been completely lost. But alas, the answer was fairly easily located in the plan. Taking a look at the plan for the query version that returns 99 rows:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue
where i <= 100

We get the following estimated plan:

In this plan, it uses a nested loops operator, which basically will do 100 seeks from the top input (the ExampleTable), for each row fetching the date value, and then calculating the scalar value (dateadd(day,1,date.dateValue) ) on the values that match in the plan. Since the 9999-12-31 date is never used, there is no overflow.

However, when the number of rows in the when the size of the output reaches a certain tolerance (in this case 999 instead of 99) from the following query:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue

We get a different plan, one that is causing us issues:

Instead of nested loops, it uses a Hash Match Join, which takes the entirety of the smaller table and builds an internal hash index (basically setting up buckets that can be scanned much faster than an entire table…in our case, probably just a single bucket), and then scan the other set checking to see if the row exists in the hash index.

It is in the process of building the hash index that our query runs into trouble. Since the date table is so much smaller, it plans to build the hash index on that table, and pre-creates the scalar values as it is doing the scan, since there are 11 rows in the date table, rather than having to calculate the value 999 times if it did it after the join. When it adds a day to the 9999-12-31 date, it fails.

I know, the question of how practical is this scenario is bound to arise. I won’t lie to you and suggest that it is likely to happen to you as it it did to me. However, the point of this blog isn’t that this one scenario is bound to happen to you, but rather that understanding how SQL Server executes queries will help to give you insight to fix problems with your system, mostly performance, but sometimes every esoteric issues that won't just leap out as being based on the query plan that was chosen. (For more reading on query plans, check out Grant Fritchey’s Simple-Talk book on query plans: http://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026).

In the end, the fix to my problem was simple. Make sure that the value that has meaning in the table, but not in the query, was filtered out:

select *, dateadd(day,1,date.dateValue)
from   ExampleTable
         join date
            on date.dateValue = ExampleTable.dateValue
               and date.dateValue < '9999-12-31'

Note: a commenter noted that in some cases, excluding the offensive data using the ON criteria/WHERE clause may not solve the issue. This is very true, and really will be made evident in the plan. I would expect it to be more likely to be definitely excluded in the JOIN clause, but you really can't guarantee anything that the optimizer might do without changing the source data (or representing the source data using a derived table as):

select *, dateadd(day,1,date.dateValue)
from ExampleTable
       join (select * from date where date.dateValue < '9999-12-31') as date
            on date.dateValue = ExampleTable.dateValue

Looking at the different variances to the plan you should be able to diagnose a "hidden" problem such as I have described by finding the flow of data and making sure that the filtering operation happens before the calculating of the scalar that causes the overflow error. This may harm performance in my query for even the more "ideal" case where it could have used indexes, so you may yet have more work to do...But this is what makes data programming fun, now isn't it?

Cloud Computing - just get started already!

BuckWoody — Tue, 30 Oct 2012 14:43:27 GMT

OK - you've been hearing about "cloud" (I really dislike that term, but whatever) for over two years. You've equated it with just throwing some VM's in some vendor's datacenter - which is certainly part of it, but not the whole story. There's a whole world of - wait for it - *coding* out there that you should be working on. If you're a developer, this is just a set of servers with operating systems and the runtime layer (like.NET, Java, PHP, etc.) that you can deploy code to and have it run. It can expand in a horizontal way, allowing massive - and I really, honestly mean massive, not just marketing talk kind of scale. We see this every day.

If you're not a developer, well, now's the time to learn. Explore a little. Try it.

We'll help you. There's a free conference you can attend in November, and you can sign up for it now. It's all on-line, and the tools you need to code are free.

Put down Facebook and Twitter for a minute - go sign up. Learn. Do. :)

See you there. http://www.windowsazureconf.net/

Utility Queries–Structure of Tables with Identity Column

drsql — Sun, 02 Sep 2012 20:05:00 GMT

Edit: At the suggestion of a much knowledgable commenter who shall remain named Aaron, I changed from using schema_name() function to using sys.tables. When writing code that is expected to have reuse, it can be safer to use the tables rather than functions because the tables will work in the context of the database that is in the from clause, so if you changed the code to database1.sys.tables because you wanted the tables from database1, and you were executing the code in database2, the columns of the table would give you the answer you expected, but the functions would be context of database2.

I have been doing a presentation on sequences of late (last planned version of that presentation was last week, but should be able to get the gist of things from the slides and the code posted here on my presentation page), and as part of that process, I started writing some queries to interrogate the structure of tables. I started with tables using an identity column for some purpose because they are considerably easier to do than sequences, specifically because the limitations of identity columns make determining how they are used easier.

In the future (which will probably be after PASS, since I have a lot of prep and 3 more presentations to do before PASS), I will start trying to discern the different cases where you might want to use a sequence and writing queries to make sure the table structures are as I desire. The queries presented here are really the first step in this direction, as in most cases I foresee a mixture of identity and sequence based surrogate keys even once people get to SQL Server 2012 as a typical set up. The queries I am presenting here will look for tables that meet certain conditions, including:

Tables with no primary key – Very common scenario, no idea about uniqueness, or sometimes that identity property alone makes the table an adequate table.
Tables with no identity column – Abolutely nothing wrong with this scenario, as the pattern of using an identity based primary key is just a choice\preference. However, if you you expect all of your tables to have identity columns, running this query can show you where you are wrong. I usually use this sort of query as part of a release, making sure that the tables I expected to have a surrogate actually do.
Tables with identity column and PK, identity column in AK – This query is interesting for looking at other people’s databases sometimes. Not everyone uses the identity value as a surrogate primary key, and finding cases where it is in a non-key usage can help you find “interesting” cases.
Tables with an identity based column in the primary key along with other columns – In this case, the key columns are illogical. The identity value should always be unique and be a sufficient surrogate key on it's own. By putting other columns in the key, you end up with a false sense of uniqueness. Ideally, you want your tables to have at least one key where all of the values are created outside of SQL Server. Sometimes people with use this for an invoice line item and make the pk the invoiceId and an identity value like invoiceLineItemId.

I can’t say that this is “wrong” but if the only key includes a system generated value, it means that you can have duplicated data along with the system generated value. So you need to monitor the data more carefully.
Tables with a single column identity based primary key but no alternate key. – This is the classic ‘bad’ use of surrogate key abuse. Just drop a surrogate key on the table and viola!, uniqueness. If you can’t see why this wouldn’t be the desirable case, it is like the previous case, except the only uniqueness criteria is a monotonically increasing value.

You can download the code directly from here or you can see all my downloadable queries on my downloadable package page: DownloadablePackages.

The queries:

--Tables with no primary key

SELECT schemas.name + '.' + tables.name AS tableName
FROM    sys.tables
          JOIN sys.schemas
             ON tables.schema_id = schemas.schema_id
WHERE   tables.type_desc = 'USER_TABLE'
        --no PK key constraint exists
    AND NOT EXISTS ( SELECT *
                        FROM   sys.key_constraints
                        WHERE key_constraints.type = 'PK'
                            AND key_constraints.parent_object_id = tables.object_id )

--Tables with no identity column

SELECT schemas.name + '.' + tables.name AS tableName
FROM    sys.tables
           JOIN sys.schemas
               ON tables.schema_id = schemas.schema_id
WHERE   tables.type_desc = 'USER_TABLE'
--no column in the table has the identity property
    AND NOT EXISTS ( SELECT *
                     FROM   sys.columns
                     WHERE tables.object_id = columns.object_id
                       AND is_identity = 1 )

--Tables with identity column and PK, identity column in AK

SELECT schemas.name + '.' + tables.name AS tableName
FROM   sys.tables
        JOIN sys.schemas
            ON tables.schema_id = schemas.schema_id
WHERE tables.type_desc = 'USER_TABLE'
        -- table does have identity column
AND   EXISTS (    SELECT *
                    FROM   sys.columns
                    WHERE tables.object_id = columns.object_id
                        AND is_identity = 1 )
        -- table does have primary key
AND   EXISTS (    SELECT *
                    FROM   sys.key_constraints
                    WHERE key_constraints.type = 'PK'
                      AND key_constraints.parent_object_id = tables.object_id )
        -- but it is not the PK
AND   EXISTS (    SELECT *
                    FROM   sys.key_constraints
                        JOIN sys.index_columns
                            ON index_columns.object_id = key_constraints.parent_object_id
                                AND index_columns.index_id = key_constraints.unique_index_id
                        JOIN sys.columns
                            ON columns.object_id = index_columns.object_id
                                AND columns.column_id = index_columns.column_id
                    WHERE key_constraints.type = 'UQ'
                        AND key_constraints.parent_object_id = tables.object_id
                        AND columns.is_identity = 1 )

--Tables with an identity based column in the primary key along with other columns

SELECT schemas.name + '.' + tables.name AS tableName
FROM   sys.tables
        JOIN sys.schemas
            ON tables.schema_id = schemas.schema_id
WHERE tables.type_desc = 'USER_TABLE'
        -- table does have identity column
AND   EXISTS ( SELECT *
                 FROM   sys.columns
                 WHERE tables.object_id = columns.object_id
                   AND is_identity = 1 )
        --any PK has identity column
AND   EXISTS( SELECT *
                FROM    sys.key_constraints
                           JOIN sys.index_columns
                                ON index_columns.object_id = key_constraints.parent_object_id
                                   AND index_columns.index_id = key_constraints.unique_index_id
                           JOIN sys.columns
                                ON columns.object_id = index_columns.object_id
                                   AND columns.column_id = index_columns.column_id
                WHERE    key_constraints.type = 'PK'
                  AND    key_constraints.parent_object_id = tables.object_id
                  AND    columns.is_identity = 1 )
    --and there are > 1 columns in the PK constraint
    AND ( SELECT COUNT(*)
           FROM    sys.key_constraints
                      JOIN sys.index_columns
                          ON index_columns.object_id = key_constraints.parent_object_id
                             AND index_columns.index_id = key_constraints.unique_index_id
            WHERE   key_constraints.type = 'PK'
              AND   key_constraints.parent_object_id = tables.object_id
        ) > 1

--Tables with a single column identity based primary key but no alternate key

SELECT schemas.name + '.' + tables.name AS tableName
FROM sys.tables
         JOIN sys.schemas
             ON tables.schema_id = schemas.schema_id
WHERE tables.type_desc = 'USER_TABLE'
        --a PK key constraint exists
AND   EXISTS ( SELECT *
                 FROM   sys.key_constraints
                 WHERE key_constraints.type = 'PK'
                   AND key_constraints.parent_object_id = tables.object_id )
    --any PK only has identity column
AND ( SELECT COUNT(*)
        FROM   sys.key_constraints
                  JOIN sys.index_columns
                      ON index_columns.object_id = key_constraints.parent_object_id
                         AND index_columns.index_id = key_constraints.unique_index_id
                  JOIN sys.columns
                      ON columns.object_id = index_columns.object_id
                         AND columns.column_id = index_columns.column_id
        WHERE key_constraints.type = 'PK'
          AND key_constraints.parent_object_id = tables.object_id
          AND columns.is_identity = 0
        ) = 0 --must have > 0 columns in pkey, can only have 1 identity column

--but no Unique Constraint Exists
AND NOT EXISTS ( SELECT *
                   FROM   sys.key_constraints
                   WHERE key_constraints.type = 'UQ'
                     AND key_constraints.parent_object_id = tables.object_id )

--Test Cases

--The following are some sample tables that can be built to test these queries. If you have other ideas
--for cases (or find errors, email louis@drsql.org)

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.NoPrimaryKey'))
        DROP TABLE dbo.NoPrimaryKey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.NoIdentityColumn'))
        DROP TABLE dbo.NoIdentityColumn;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.IdentityButNotInPkey'))
        DROP TABLE dbo.IdentityButNotInPkey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.TooManyColumnsInPkey'))
        DROP TABLE dbo.TooManyColumnsInPkey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.MultipleColumnsInPkeyOk'))
        DROP TABLE dbo.MultipleColumnsInPkeyOk;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.NoAlternateKey'))
        DROP TABLE dbo.NoAlternateKey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.IdentityInAlternateKey'))
        DROP TABLE dbo.IdentityInAlternateKey;

--very common scenario, assuming identity makes the table great
CREATE TABLE NoPrimaryKey
(
NoPrimaryKeyId int not null identity,
AnotherColumnId int not null
)
go

--absolutely nothing wrong with this scenario, unless you expect all of your
--tables to have identity columns, of course...
CREATE TABLE NoIdentityColumn
(
NoIdentityColumnId int primary key,
AnotherColumnId int not null
)
go

--absolutely nothing wrong with this scenario either, as this could be desired.
--usually it is some form of mistake in a database using surrogate keys though
CREATE TABLE IdentityButNotInPkey
(
IdentityButNotInPkeyId int primary key,
AnotherColumnId int identity not null
)
go

--absolutely nothing wrong with this scenario either, as this could be desired.
--usually it is some form of mistake in a database using surrogate keys though
CREATE TABLE IdentityInAlternateKey
(
IdentityInAlternateKeyId int primary key,
AnotherColumnId int identity not null unique
)
go

--In this case, the key columns are illogical. The identity value should always be unique and
--be a sufficient primary surrogate key. I definitely want to know why this is built this
--way. Sometimes people with use this for an invoice line item and make the pk the
--invoiceId and an identity value like invoiceLineItemId. I generally prefer the surrogate key
--to stand alone and have the multi-part key to be something that makes sense for the user
CREATE TABLE TooManyColumnsInSurrogatePkey
(
    TooManyColumnsInPkeyId int identity,
    AnotherColumnId int,
    primary key (TooManyColumnsInPkeyId,AnotherColumnId)
)
go

CREATE TABLE MultipleColumnsInPkeyOk
(
    TooManyColumnsInPkeyId int not null,
    AnotherColumnId int not null,
    primary key (TooManyColumnsInPkeyId,AnotherColumnId)
)
go

--this is my pet peeve, and something that should be avoided. You could end up having
--duplicate rows that are not logical.
CREATE TABLE NoAlternateKey
(
NoAlternateKeyId int not null identity primary key,
AnotherColumnThatShouldBeUnique int not null
)
go

Windows Azure End to End Examples

BuckWoody — Tue, 29 May 2012 13:45:59 GMT

I’m fascinated by the way people learn. I’m told there are several methods people use to understand new information, from reading to watching, from experiencing to exploring.

Personally, I use multiple methods of learning when I encounter a new topic, usually starting with reading a bit about the concepts. I quickly want to put those into practice, however, especially in the technical realm. I immediately look for examples where I can start trying out the concepts. But I often want a “real” example – not just something that represents the concept, but something that is real-world, showing some feature I could actually use.

And it’s no different with the Windows Azure platform – I like finding things I can do now, and actually use. So when I started learning Windows Azure, I of course began with the Windows Azure Training Kit – which has lots of examples and labs, presentations and so on. But from there, I wanted more examples I could learn from, and eventually teach others with. I was asked if I would write a few of those up, so here are the ones I use.

CodePlex

CodePlex is Microsoft’s version of an “Open Source” repository. Anyone can start a project, add code, documentation and more to it and make it available to the world, free of charge, using various licenses as they wish. Microsoft also uses this location for most of the examples we publish, and sample databases for SQL Server.

If you search in CodePlex for “Azure”, you’ll come back with a list of projects that folks have posted, including those of us at Microsoft. The source code and documentation are there, so you can learn using actual examples of code that will do what you need. There’s everything from a simple table query to a full project that is sort of a “Corporate Dropbox” that uses Windows Azure Storage.

The advantage is that this code is immediately usable. It’s searchable, and you can often find a complete solution to meet your needs. The disadvantage is that the code is pretty specific – it may not cover a huge project like you’re looking for. Also, depending on the author(s), you might not find the documentation level you want.

Link: http://azureexamples.codeplex.com/site/search?query=Azure&ac=8

Tailspin

Microsoft Patterns and Practices is a group here that does an amazing job at sharing standard ways of doing IT – from operations to coding. If you’re not familiar with this resource, make sure you read up on it. Long before I joined Microsoft I used their work in my daily job – saved a ton of time. It has resources not only for Windows Azure but other Microsoft software as well.

The Patterns and Practices group also publishes full books – you can buy these, but many are also online for free. There’s an end-to-end example for Windows Azure using a company called “Tailspin”, and the work covers not only the code but the design of the full solution. If you really want to understand the thought that goes into a Platform-as-a-Service solution, this is an excellent resource.

The advantages are that this is a book, it’s complete, and it includes a discussion of design decisions. The disadvantage is that it’s a little over a year old – and in “Cloud” years that’s a lot. So many things have changed, improved, and have been added that you need to treat this as a resource, but not the only one. Still, highly recommended.

Link: http://msdn.microsoft.com/en-us/library/ff728592.aspx

Azure Stock Trader

Sometimes you need a mix of a CodePlex-style application, and a little more detail on how it was put together. And it would be great if you could actually play with the completed application, to see how it really functions on the actual platform.

That’s the Azure Stock Trader application. There’s a place where you can read about the application, and then it’s been published to Windows Azure – the production platform – and you can use it, explore, and see how it performs.

I use this application all the time to demonstrate Windows Azure, or a particular part of Windows Azure.

The advantage is that this is an end-to-end application, and online as well. The disadvantage is that it takes a bit of self-learning to work through.

Links: Learn it: http://msdn.microsoft.com/en-us/netframework/bb499684 Use it: https://azurestocktrader.cloudapp.net/

Preparation is key to a successful cloud deployment

BuckWoody — Tue, 01 May 2012 13:09:16 GMT

If you want to be wise, watch the actions and outcomes of others. Emulate the successful actions, and avoid the actions that cause failure. That’s true in life in general - and in technology projects in specific.

I’ve worked with several clients who have created or migrated an application to “the cloud” - meaning using Microsoft Windows Azure or another provider. Although the statement in the title of this post is trite, I cannot over-emphasize how accurate it is. In every case of those who had a great experience with a distributed computing environment (which is thankfully the vast majority of my projects),

What kind of preparation do you need to do? Here are some tips I’ve learned in the successful (and not-so-successful) deployments I’ve seen:

Follow standard recommendations for successful projects in general

You and your organization have probably done a few projects before - this one should have the same general attributes: a well-defined goal, a small, motivated team, a realistic timeline, and an adequate budget. I know, I know, you *never* seem to get those things - but if you don’t, you’ll fail. Simple as that.

Educate yourself

Computing technology started out on a single set of hardware for a single purpose - and realizing the limits of the hardware at hand, systems designers quickly realized that scale-out and virtualization was key. No, that’s not new - mainframes almost always worked on the concept of scale-out and virtual machines. But we switched in the 1980’s to single-user systems again, and we’ve been there ever since. By that I mean you install an OS on the things you work on. Now we move back to distributed system concepts, and there are some real differences. You’ll need to learn how those work, and do things a new way. Hey, we’re IT - we LOVE learning new things, right?

Get a partner if needed

There are a few of us white-haired Gandalf’s around that remember how to work in a distributed system, but if it’s new to you, that’s completely OK. You can save yourself a world of trouble by working with someone who’s done this before - a partner you hire, someone from Microsoft Consulting, whatever.

And don’t forget support - who will handle each issue, what is the escalation model, who are your contacts at Microsoft, and what is your “light’s out” strategy?

“A new broom sweeps clean”, the old adage goes, but the old brooms know where the dirt is.

Build a model

Take some time to do a Proof of Concept on your local system and using your Azure hours from your MSDN account if you have one. Going through this build - and being willing to throw it away and try it a different way - is invaluable.

Test your theories

Three statisticians are walking in a field. They see a rabbit - the first guy raises his gun, firing far in front of the rabbit. The second guy simultaneously raises his gun and fires far behind the rabbit. The third guy yells “We got him!”

Not every theory is correct - not every attempt is the right one. Build in your success tests while you’re building your model. Then check them - don’t leave this step out.

Rinse, lather, repeat

This is advice from a shampoo bottle - which I’ve never used (I don’t really have that much hair - especially now). But in a “Cloud” project, it’s important. It’s an evolving system, that gains new improvements at an amazing rate. As soon as you deploy and stabilize you need to start the process over again. If you created your system in a Services model, with contracts for the APIs and abstracted code, this is far easier.

It’s not hard to do a cloud project right. But it’s really simple to do it wrong. Follow these guidelines and you’ll learn from the successes - and mistakes - of others.

Java Resources for Windows Azure

BuckWoody — Tue, 13 Mar 2012 00:45:00 GMT

Windows Azure is a Platform as a Service – a PaaS – that runs code you write. That code doesn’t just mean the languages on the .NET platform – you can run code from multiple languages, including Java. In fact, you can develop for Windows and SQL Azure using not only Visual Studio but the Eclipse Integrated Development Environment (IDE) as well.

Although not an exhaustive list, here are several links that deal with Java and Windows Azure:

Resource	Link
Windows Azure Java Development Center	http://www.windowsazure.com/en-us/develop/java/
Java Development Guidance	http://msdn.microsoft.com/en-us/library/hh690943(VS.103).aspx
Running a Java Environment on Windows Azure	http://blogs.technet.com/b/port25/archive/2010/10/28/running-a-java-environment-on-windows-azure.aspx
Running a Java Environment on Windows Azure	http://blogs.technet.com/b/port25/archive/2010/10/28/running-a-java-environment-on-windows-azure.aspx
Run Java with Jetty in Windows Azure	http://blogs.msdn.com/b/dachou/archive/2010/03/21/run-java-with-jetty-in-windows-azure.aspx
Using the plugin for Eclipse	http://blogs.msdn.com/b/craig/archive/2011/03/22/new-plugin-for-eclipse-to-get-java-developers-off-the-ground-with-windows-azure.aspx
Run Java with GlassFish in Windows Azure	http://blogs.msdn.com/b/dachou/archive/2011/01/17/run-java-with-glassfish-in-windows-azure.aspx
Improving experience for Java developers with Windows Azure	http://blogs.msdn.com/b/interoperability/archive/2011/02/23/improving-experience-for-java-developers-with-windows-azure.aspx
Java Access to SQL Azure via the JDBC Driver for SQL Server	http://blogs.msdn.com/b/brian_swan/archive/2011/03/29/java-access-to-sql-azure-via-the-jdbc-driver-for-sql-server.aspx
How to Get Started with Java, Tomcat on Windows Azure	http://blogs.msdn.com/b/usisvde/archive/2011/03/04/how-to-get-started-with-java-tomcat-on-windows-azure.aspx
Deploying Java Applications in Azure	http://blogs.msdn.com/b/mariok/archive/2011/01/05/deploying-java-applications-in-azure.aspx
Using the Windows Azure Storage Explorer in Eclipse	http://blogs.msdn.com/b/brian_swan/archive/2011/01/11/using-the-windows-azure-storage-explorer-in-eclipse.aspx
Windows Azure Tomcat Solution Accelerator	http://archive.msdn.microsoft.com/winazuretomcat
Deploying a Java application to Windows Azure with Command-line Ant	http://java.interoperabilitybridges.com/articles/deploying-a-java-application-to-windows-azure-with-command-line-ant
Video: Open in the Cloud: Windows Azure and Java	http://channel9.msdn.com/Events/PDC/PDC10/CS10
AzureRunMe	http://azurerunme.codeplex.com/
Windows Azure SDK for Java	http://www.interoperabilitybridges.com/projects/windows-azure-sdk-for-java
AppFabric SDK for Java	http://www.interoperabilitybridges.com/projects/azure-java-sdk-for-net-services
Information Cards for Java	http://www.interoperabilitybridges.com/projects/information-card-for-java
Apache Stonehenge	http://www.interoperabilitybridges.com/projects/apache-stonehenge
Channel 9 Case Study on Java and Windows Azure	http://www.microsoft.com/casestudies/Windows-Azure/Gigaspaces/Solution-Provider-Streamlines-Java-Application-Deployment-in-the-Cloud/400000000081

Cloud Computing Patterns: Using Data Transaction Commitment Models for Design

BuckWoody — Tue, 14 Feb 2012 20:45:47 GMT

There are multiple ways to store data in a cloud provider, specifically around Windows and SQL Azure. As part of a “Data First” architecture design, one decision vector – assuming you’ve already done a data classification of the elements you want to store – is to decide the transaction level you need for that datum. Once you’ve decided on what level of transactional commitment you need, you can make intelligent decisions about the storage engine, method of access and storage, speed and other requirements.

Although the list below is neither original nor exhaustive, these are the general considerations I use for a given data set. It’s important to note that in many on premises systems the engine choice at hand overrides these concerns. If you have a large Relational Database Management System (RDBMS) for instance, you might simply place all data there without further consideration. In a Platform as a Service (PaaS) like Windows and SQL Azure, however, selection of the proper engine for a particular dataset has implications ranging from cost to performance, and selecting the right engine is critical when you want to leverage the data across “Bid Data” analysis like Hadoop or other constructs.

Monolithic Consistent Transactional
The first selection is analogous to a local RDBMS system. The dataset is retrieved in a functionally single, monolithic transaction, i.e. kept together with ACID properties in mind. This is the most reliable type of data design for datasets that require a high degree of safety in the read/write pattern. As an example, a bank ATM transaction should be modeled in a monolithic way. If I make a transfer of funds from one account to another, I want the money to be subtracted from one account if and only if it is successfully added to the other. The bank, on the other hand, wants the money added to the second account if and only if it is subtracted from the first. This is a prime example of a monolithic (single atomic transaction), Consistent (if and only if) and Transactional (as a unit, with provision for roll-back and reporting if unsuccessful) data requirement.

The primary engine used for this type of data is often SQL Azure – an RDMBS in the same datacenters as Windows Azure. Placing both the calling application, whether that is a Data Access Layer-based code widget or a direct call from a Web or Worker Role, means that data is retrieved quickly and in a monolithic way. The costs for this method is based on overall database size. A consideration is how much data you can store this way. Database sizes have limits, although there are ways of overcoming size issues using technologies such as Sharding or SQL Azure Federations. There is also the consideration of performance. In an RDBMs that conforms to ACID properties, locking and other overhead for safety is at conflict with the highest possible read performance. But in some cases the ACID properties are worth the cost, as in the banking example.

You are not limited to SQL Azure in this model. Windows Azure Table storage, while similar to NoSQL offerings is different in that it is immediately consistent across all three replicated copies of data, offering a higher degree of safety. And while Table storage does not offer built-in support for transactions, there are ways to achieve certain transaction levels.

Monolithic Realtime
If consistency can be relaxed – meaning that a guaranteed read/write patter is not essential – then more options arise in Windows and SQL Azure. You can still use SQL Azure for this type of storage, with either automatic or programmatic hints allowing for “dirty reads”. Windows Azure Table storage is still consistent, but the selection of the method for querying the data such as separate copies of read and write data can be employed. Because of the relaxed transaction nature, higher speeds are possible by querying cached or separate datasets.

An example here is that same transaction from the bank, but a statement inquiry. Just after the money is deposited, the user wishes to query the current balance. The current balance – minus the transaction that just occurred – is retrieved and shown to the user, perhaps even combining the amount with the latest transaction, perhaps saved as a local cached object, with a caveat to the user.

Distributed Realtime
At some point, the data becomes too large to fit inside a single processing session, and parallelism is used. In this case, either separate databases in SQL Azure or Windows Azure Tables, local data storage on the Web or Worker Role, or a combination of all with Caches is the right approach for the data design.

The biggest implication in this type of system is speed – a higher degree of data separation is essential, and so the dataset selection must fit the pattern. It is unacceptable to force an ACID-properties type workload into this environment. Typical examples here are the actual data asset payload for streaming video or music, read-only documents and so on. This pattern is often separated from the meta-data, which is kept in more of a transactional model.

As an example, assume you log on to a website to watch a movie or listen to music. The provider needs to verify your identity and account balance, which are transactional data loads. After that process is complete, the workload shifts to a copy – perhaps one of several – of the asset to stream to your location.

In this case, Windows Azure Blob storage, along with the Content Delivery Network (CDN – a series of servers closer to the user) is employed along with the transactional realtime requirements for the metadata.

Distributed Eventual
At the furthest end of the data scale are large datasets that need deeper analysis, but not necessarily in realtime. Examples here are terrabytes of data requiring a Business Intelligence view, but with a tolerance of a few seconds to minutes or hours. In this case, Storage, Processing and Query methods, such as the Hadoop offering in Windows Azure, or perhaps the High Performance Computing (HPC) Windows Server in Windows Azure fit well. Here, the design of the data is often dictated by the source, and more emphasis is placed on the algorithms around processing and re-assembling the data.

There are, of course, other patterns. In many cases a single dataset may have needs in one or more of these categories – in fact, sitting at 30,000 feet typing this entry, I’m having that very design discussion with a gentleman sitting next to me. The key is to design data-first, and fit the technology to the requirement for each datum. Allow each function and engine to handle the data in the most efficient, effective way for cost, performance and utility.

Application Lifecycle Management Overview for Windows Azure

BuckWoody — Tue, 07 Feb 2012 14:58:39 GMT

Developing in Windows Azure is at once not that much different from what you’re familiar with in on-premises systems, and different in significant ways. Because of these differences, developers often ask about the specific process to develop and deploy a Windows Azure application - more formally called an Application Lifecycle Management, or ALM.

There are specific resources you can use to learn more about various parts of ALM - I’ve referenced those at the end of this post. But ALM has multiple definitions, from the governance of code injection, domain upgrade, testing, process flow and more. Many developers are interested in the finer-grained information, like how do I develop and deploy an application? What tools do I need, and how do I get the code running somewhere that I can test?

I’ll cover the very high-level process here, and refer you to specifics at the end of each section, so that you can take it all in at one viewing, and then bookmark for more detail when you need more information. I won’t be covering processes like Continuous Integration or Agile and other methodologies in this post - I’ll blog those later.

Initial Development

You start with writing code. You have three ways to do this. You can use Visual Studio (even the Express Edition Works), Eclipse, or by leveraging the REST API format. You can do this in a standalone (non-connected) environment like your laptop.

Using Visual Studio is one of the simplest methods to create an Azure application, allowing you to combine the Azure components you want to leverage (Storage, Compute, SQL Azure, the Service Bus, etc.) along with the on-premises code you have now or are creating. Once you’ve installed and patched Visual Studio, just download and install the Windows Azure Software Development Kit (SDK) and you’ll have not only all the API’s you need to talk to Azure, but a fully functioning local environment to run and test your code before you deploy it. You’ll also get a robust set of samples. You can download what you need for all of that (free) here: http://www.windowsazure.com/en-us/develop/downloads/ . There’s a step-by-step process here: http://msdn.microsoft.com/en-us/magazine/ee336122.aspx

You can also use Eclipse to develop for Windows Azure. You won’t get the full runtime environment in just that kit alone, but you can use this successfully on a Linux system. I have several folks using this method. The downloads and documentation for that is here: http://www.windowsazure4e.org/

You can use REST API’s to hit Azure Assets and control them. Not my preferred method, but possible. There are REST API’s for various sections of Azure. You can find the main reference for that here: http://msdn.microsoft.com/en-us/library/windowsazure/ff800682.aspx

Note: We recently demonstrated using a Cloud-based Integrated Development Environment (IDE) for Node.js deployment to Windows Azure. More on that here: http://www.readwriteweb.com/cloud/2012/01/cloud9-ide-to-enable-nodejs-ap.php

Deploying to a Test Instance

After you write the code, you’ll need to test it somewhere. The Azure Emulator on your development laptop is for a single user on that laptop, and it also has some subtle differences from the production fabric as you might imagine. Normally you’ll set up a small subscription to run and test the application, just like you would have a set of test servers. Each subscription has its own management keys and certificates, so this assists in keeping the testing environment separate for billing and control.

More on that general information here: http://msdn.microsoft.com/en-us/library/ff803362.aspx

Deploying to Production

Once you have developed the code and tested it, you need to move it to a location where users can access it. In reality, there is no physical difference in the type of machines, fabric or any other component in “Production” Windows Azure accounts and the “Test” accounts, but you’ll most often pick smaller systems to deploy on in testing, and you’ll probably keep the URL in the plain format.

In the Production Windows Azure account, the team normally limits the access to the account for deployment to a separate set of developers. This ensures code flow and control. A DNS name is normally mapped to the longer, Microsoft-generated URL so that your users access the application or data the way you want them to.

More on setting up an account here: http://techinch.com/2010/06/14/setup-your-windows-azure-account/

Managing Code Change

With the application deployed, there are two broad tasks you need to consider. One is managing changes through the application, and the other involves management, monitoring and performance tuning for an application.

To make a code change, the standard ALM process is followed, just as above. You can use command-line tools to automate the process as you would with an on-premises system. A vide on that shows you how: http://www.microsoftpdc.com/2009/SVC25. Normally this is used with an “In-Place” upgrade into Production Account, since your testing is completed in a separate account. More on that process here: http://msdn.microsoft.com/en-us/library/windowsazure/ee517255.aspx

One difference is the “VIP Swap” process you can use for the final push to Production. In essence, this allows you to have two copies of the application running on the Production account, with a quick way to cut over and back when you’re ready. The process for that is detailed here: http://msdn.microsoft.com/en-us/library/windowsazure/ee517253.aspx

For monitoring, you have several options. You should enable the Windows Azure Diagnostics in your code - more on that here: http://archive.msdn.microsoft.com/WADiagnostics.

You can observe uptime and other information on the Windows Azure Service Dashboard, where you can also consume the uptime as an RSS feed: http://www.windowsazure.com/en-us/support/service-dashboard/

From there, you can also use System Center to monitor not only Windows Azure deployments but internal applications as well. The Management Pack and documentation for that is here: http://www.microsoft.com/download/en/details.aspx?id=11324.

There are also 3rd-party tools to manage Windows Azure. More on that here: http://www.bing.com/search?q=monitor+Windows+Azure&form=OSDSRC

Other References:

There is a lot more detail in this official reference: https://www.windowsazure.com/en-us/develop/net/fundamentals/deploying-applications/

Bryan Group explains the ramifications of the Secure Development Lifecycle (SDL) with lots of collateral you can review: http://blogs.msdn.com/b/bryang/archive/2011/04/26/applying-the-sdl-to-windows-azure.aspx

Team Foundation Server (TFS) in the Cloud - My Experience So Far

BuckWoody — Tue, 24 Jan 2012 12:45:13 GMT

I recently joined a software development project that involves not only myself and other internal Microsoft employees, but a partner and a customer as well. We are building a hybrid solution that uses assets on premises as well as Windows Azure for processing. When we put the team together we picked a methodology (Agile) for the project (we use multiple methodologies at Microsoft - whatever the project needs) and then we started talking about Source Control.

We’re all comfortable with various tools for check-in-check-out, branching, and so on. We have all used GIT, SVN, and TFS. Some of us have even used Source Safe in past, but that’s another post. Each company has a full set of Source Control systems in place. But using each other’s systems requires logins, firewalls and the like - so we decided to use the TFS Service Preview to run the entire project from “the cloud”. Here are my experiences with that.

The process was really simple. In fact, we talked about using the cloud TFS in the first SCRUM, and the team was working from the Work Items list that afternoon. The original account login provides a web interface to allow people to join the team. Each of us happened to have a Live.Com address, so we just invited those addresses to join and they got a link, like this:

projectname.tfspreview.com

I’m using Visual Studio, and it’s a requirement for TFS preview to have SP1 installed, and this patch: KB2581206

From there, I opened Visual Studio and navigated from the main menu to Team and then Connect to Team Foundation Server. I’m given this menu:

Selecting port 443 and HTTPS (for security) and then ensuring the lower link has the “tfs” appended as the location, I opened the project.

(This VSTS screenshot is of a project I did in my University of Washington class I teach - I never show client code or names in a blog post)

From there it’s a normal set of operations. Right now the preview doesn’t have some things I’d really like, such as an automated build or some of the testing tools, but you can read this blog entry to learn more about the entire sign-up process, and what the team has planned.

Each day I log in to the project, and I’m given this new sign-in option:

I click the option, and I open the environment, hit My Work Items query, and get to work. All in all, a seamless - although basic - experience. The speed at which we could set up and work on a project was really sweet. It’s remarkable how un-remarkable this is - I just do my work each day, everything is running and backed up in the cloud. I think that’s the point.

Developing a Cost Model for Cloud Applications

BuckWoody — Tue, 08 Nov 2011 15:30:49 GMT

Note - please pay attention to the date of this post. As much as I attempt to make the information below accurate, the nature of distributed computing means that components, units and pricing will change over time. The definitive costs for Microsoft Windows Azure and SQL Azure are located here, and are more accurate than anything you will see in this post: http://www.microsoft.com/windowsazure/offers/

When writing software that is run on a Platform-as-a-Service (PaaS) offering like Windows Azure / SQL Azure, one of the questions you must answer is how much the system will cost. I will not discuss the comparisons between on-premise costs (which are nigh impossible to calculate accurately) versus cloud costs, but instead focus on creating a general model for estimating costs for a given application.

You should be aware that there are (at this writing) two billing mechanisms for Windows and SQL Azure: “Pay-as-you-go” or consumption, and “Subscription” or commitment. Conceptually, you can consider the former a pay-as-you-go cell phone plan, where you pay by the unit used (at a slightly higher rate) and the latter as a standard cell phone plan where you commit to a contract and thus pay lower rates. In this post I’ll stick with the pay-as-you-go mechanism for simplicity, which should be the maximum cost you would pay. From there you may be able to get a lower cost if you use the other mechanism. In any case, the model you create should hold.

Developing a good cost model is essential. As a developer or architect, you’ll most certainly be asked how much something will cost, and you need to have a reliable way to estimate that. Businesses and Organizations have been used to paying for servers, software licenses, and other infrastructure as an up-front cost, and power, people to the systems and so on as an ongoing (and sometimes not factored) cost. When presented with a new paradigm like distributed computing, they may not understand the true cost/value proposition, and that’s where the architect and developer can guide the conversation to make a choice based on features of the application versus the true costs.

The two big buckets of use-types for these applications are customer-based and steady-state. In the customer-based use type, each successful use of the program results in a sale or income for your organization. Perhaps you’ve written an application that provides the spot-price of foo, and your customer pays for the use of that application. In that case, once you’ve estimated your cost for a successful traversal of the application, you can build that into the price you charge the user. It’s a standard restaurant model, where the price of the meal is determined by the cost of making it, plus any profit you can make.

In the second use-type, the application will be used by a more-or-less constant number of processes or users and no direct revenue is attached to the system. A typical example is a customer-tracking system used by the employees within your company. In this case, the cost model is often created “in reverse” - meaning that you pilot the application, monitor the use (and costs) and that cost is held steady. This is where the comparison with an on-premise system becomes necessary, even though it is more difficult to estimate those on-premise true costs. For instance, do you know exactly how much cost the air conditioning is because you have a team of system administrators? This may sound trivial, but that, along with the insurance for the building, the wiring, and every other part of the system is in fact a cost to the business.

There are three primary methods that I’ve been successful with in estimating the cost. None are perfect, all are demand-driven. The general process is to lay out a matrix of:

components
units
cost per unit

and then multiply that times the usage of the system, based on which components you use in the program. That sounds a bit simplistic, but using those metrics in a calculation becomes more detailed. In all of the methods that follow, you need to know your application. The components for a PaaS include computing instances, storage, transactions, bandwidth and in the case of SQL Azure, database size. In most cases, architects start with the first model and progress through the other methods to gain accuracy.

Simple Estimation

The simplest way to calculate costs is to architect the application (even UML or on-paper, no coding involved) and then estimate which of the components you’ll use, and how much of each will be used. Microsoft provides two tools to do this - one is a simple slider-application located here: http://www.microsoft.com/windowsazure/pricing-calculator/

The other is a tool you download to create an “Return on Investment” (ROI) spreadsheet, which has the advantage of leading you through various questions to estimate what you plan to use, located here: https://roianalyst.alinean.com/msft/AutoLogin.do?d=176318219048082115

You can also just create a spreadsheet yourself with a structure like this:

Program Element	Azure Component	Unit of Measure	Cost Per Unit	Estimated Use of Component	Total Cost Per Component	Cumulative Cost

Of course, the consideration with this model is that it is difficult to predict a system that is not running or hasn’t even been developed. Which brings us to the next model type.

Measure and Project

A more accurate model is to actually write the code for the application, using the Software Development Kit (SDK) which can run entirely disconnected from Azure. The code should be instrumented to estimate the use of the application components, logging to a local file on the development system. A series of unit and integration tests should be run, which will create load on the test system.

You can use standard development concepts to track this usage, and even use Windows Performance Monitor counters. The best place to start with this method is to use the Windows Azure Diagnostics subsystem in your code, which you can read more about here: http://blogs.msdn.com/b/sumitm/archive/2009/11/18/introducing-windows-azure-diagnostics.aspx This set of API’s greatly simplifies tracking the application, and in fact you can use this information for more than just a cost model.

After you have the tracking logs, you can plug the numbers into ay of the tools above, which should give a representative cost or in some cases a unit cost.

The consideration with this model is that the SDK fabric is not a one-to-one comparison with performance on the actual Windows Azure fabric. Those differences are usually smaller, but they do need to be considered. Also, you may not be able to accurately predict the load on the system, which might lead to an architectural change, which changes the model. This leads us to the next, most accurate method for a cost model.

Sample and Estimate

Using standard statistical and other predictive math, once the application is deployed you will get a bill each month from Microsoft for your Azure usage. The bill is quite detailed, and you can export the data from it to do analysis, and using methods like regression and so on project out into the future what the costs will be. I normally advise that the architect also extrapolate a unit cost from those metrics as well. This is the information that should be reported back to the executives that pay the bills: the past cost, future projected costs, and unit cost “per click” or “per transaction”, as your case warrants.

The challenge here is in the model itself - statistical methods are not foolproof, and the larger the sample (in this case I recommend the entire population, not a smaller sample) is key.

References and Tools

Articles:

http://blogs.msdn.com/b/patrick_butler_monterde/archive/2010/02/10/windows-azure-billing-overview.aspx

http://technet.microsoft.com/en-us/magazine/gg213848.aspx

http://blog.codingoutloud.com/2011/06/05/azure-faq-how-much-will-it-cost-me-to-run-my-application-on-windows-azure/

http://blogs.msdn.com/b/johnalioto/archive/2010/08/25/10054193.aspx

http://geekswithblogs.net/iupdateable/archive/2010/02/08/qampa-how-can-i-calculate-the-tco-and-roi-when.aspx

Other Tools:

http://cloud-assessment.com/

http://communities.quest.com/community/cloud_tools