Buck Woody : Testing

How Microsoft helps you NOT break your Windows Azure Application: Storage Services Versioning

BuckWoody — Tue, 06 Dec 2011 14:42:57 GMT

One of the advantages of using Windows Azure to run your code is that you don’t have to constantly manage upgrades on your platform. While that’s a big advantage indeed, it immediately brings up the question - how do the upgrades happen? Microsoft upgrades the Azure platform in periodic increments, and the components that are affected are documented.

This brings up another question - upgrades mean change, and change can sometimes alter the way you might implement a feature. What if you have taken a dependency on some feature in your code that has been altered by an upgrade? Windows Azure does have an Application Lifecycle Management (ALM) Process, which I’ll reference at the end of this post. But beyond that, there are some features we’ve put into place that will help you manage many of these changes. One of those is being able to set the version of storage features you would like your code to use.

Windows Azure is made up of three main component areas: Computing, Storage and a group of features called the Application Fabric. You can use these components together or separately, depending on what you would like your application to do. In this post I’ll deal with the version control in the storage subsystem - in other posts I’ll explain how to track and in some cases control the versions of the other components you work with.

When you send a request to a Windows Azure resource, you’re actually using a REST call. That’s a three-part call to the system that has a request (called a URI), a header, and a body of code you want to send. So a typical call, such as to a table, might look like this example, which changes the properties of a Blob:

URI:
PUT http://myaccount.table.core.windows.net/?restype=service&comp=properties HTTP/1.1

Header:
x-ms-version: 2011-08-18
x-ms-date: Tue, 30 Aug 2011 04:28:19 GMT
Authorization: SharedKey
myaccount:Z1lTLDwtq5o1UYQluucdsXk6/iB7YxEu0m6VofAEkUE=
Host: myaccount.table.core.windows.net

Body:
<?xml version="1.0" encoding="utf-8"?>
<StorageServiceProperties>
    <Logging>
        <Version>1.0</Version>
        <Delete>true</Delete>
        <Read>false</Read>
        <Write>true</Write>
        <RetentionPolicy>
            <Enabled>true</Enabled>
            <Days>7</Days>
        </RetentionPolicy>
    </Logging>
    <Metrics>
        <Version>1.0</Version>
        <Enabled>true</Enabled>
        <IncludeAPIs>false</IncludeAPIs>
        <RetentionPolicy>
            <Enabled>true</Enabled>
            <Days>7</Days>
        </RetentionPolicy>
    </Metrics>
</StorageServiceProperties>

(Source of this code)

You can see that I’ve highlighted a portion of the header block - that’s where you set the version of the Storage Services you would like to use. You can find a list of the features introduced in each version here. It’s not a requirement of adding that element to the header, but it’s best practices to do so.

You don’t have to use REST calls directly, however. It’s more common to use the API in the Software Development Kit to just change the property in your IDE environment - the setting you’re looking for there is the Set Storage Service Properties call.

Interestingly, rather than a breaking change you might run into an unexpected behavior if you are not aware of these parameters. In some code I recently reviewed a newer feature from the storage system failed when it was called. On inspection I found that the developer had used an older codeblock from a previous version of the storage system - he was not aware you can set the version of storage in the call. We changed the header to the latest version, and everything worked as expected.

References:

The Storage Services Versioning and the changes for each version:

http://msdn.microsoft.com/en-us/library/windowsazure/dd894041.aspx

Windows Azure Application Lifecycle Management:

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

http://channel9.msdn.com/posts/Windows-Azure-Jump-Start-03-Windows-Azure-Lifecycle-Part-1

http://channel9.msdn.com/Events/TechEd/Australia/Tech-Ed-Australia-2011/COS201

Plan for Diagnostics in Cloud Computing From the Git-Go

BuckWoody — Tue, 06 Sep 2011 13:11:22 GMT

“Git-Go” is something we say in the South that means “right at the start”. I’ve seen several applications for on-premise systems that don’t have much in the way of diagnostics - the developers rely on a debugger, the event logs on the server and client workstation, and most of all, the ability to watch the system from end-to-end.

This approach is a mistake for an on-premise system, and it’s definitely a problem for a distributed architecture. You simply do not own all of the components from end to end in a cloud environment, nor are you always able to attach a debugger or other remote monitoring tools to the various areas within the code path. So you need to make sure that from the very outset of your design that you build in diagnostics. My personal preference is to build a system such that a control file turns on deeper information gathering from the system, up to a minimal level.

When I do that, I set a high level of logging, a medium level, and a moderate level. I normally use the deepest level of information during the testing and acceptance phase of the deployment, then switch to moderate and then the least level of information gathering. Also in my design I often set an error condition to begin gathering the deeper information along with the exception, where possible.

There are decisions you need to make as to where to store the diagnostics (many operations in the cloud cost money), how often you collect them, and so on. You can get a quick overview on using the diagnostics that come with Windows Azure here: http://www.azuresupport.com/2010/03/getting-started-with-windows-azure-diagnostics-and-monitoring/ This is where you should start first. More detail on that: http://msdn.microsoft.com/en-us/library/gg433048.aspx

My friend Dave Pallman has a great tool he’s released for free: http://davidpallmann.blogspot.com/2009/03/azure-application-monitor-now-on.html

If the issue is in storage apps: http://social.msdn.microsoft.com/Forums/en-US/windowsazuredata/thread/d84ba34b-b0e0-4961-a167-bbe7618beb83

If you have System Center, this is the quickest and easiest way to implement the monitoring – really handy: http://pinpoint.microsoft.com/en-us/applications/windows-azure-application-monitoring-management-pack-release-candidate-12884907699

Database Testing Links

BuckWoody — Wed, 10 Nov 2010 12:38:00 GMT

Yesterday at the SQL PASS 2010 Summit I did a presentation on database testing - I have two sets of links I mentioned there, one for the information I presented and the other for various tools I've tried. Enjoy:

More information on Database Testing and Testing methodologies:
http://www.agiledata.org/essays/whatToTest.html
http://www.agiledata.org/essays/databaseTesting.html
http://www.testinginstitute.com/display.php?id=DATF_MS
http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx
http://blogs.technet.com/b/sql_server_isv/archive/2010/10/20/hot-fixes-and-cus-and-service-packs-oh-my.aspx
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/10/14/benefit-from-unit-testing-t-sql-speed-up-your-test-harness.aspx
Close These Loopholes in Your Database Testing - http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---reproduce-database-errors/

Database Testing Tools:
http://www.quest.com/benchmark-factory/
http://weblogs.asp.net/rosherove/archive/2004/10/05/238201.aspx
http://www.red-gate.com/
http://sourceforge.net/apps/trac/tsqlunit/
http://sqlunit.sourceforge.net/
http://www.dbunit.org/
http://gojko.net/fitnesse/dbfit/
http://www.anydbtest.com/
http://www-01.ibm.com/software/data/optim/streamline-test-data-management/
http://www.turbodata.ca/
http://www.sqledit.com/dg/
http://www.datatect.com/
http://msdn.microsoft.com/en-us/library/aa730872(VS.80).aspx
https://h10078.www1.hp.com/cda/hpms/display/main/hpms_content.jsp?zn=bto&cp=1-10^36653_4000_100__
http://www.radview.com/
http://www.webperformanceinc.com/
http://sqlity.net

Attaching a Database

BuckWoody — Tue, 26 Jan 2010 15:03:39 GMT

I had someone ask me yesterday how they could get to a database used by a product that they owned, but that was installed using SQL Server Express. They didn’t have access to the database, and they didn’t know the password for the service that started Express, so they wanted to know if they could look at the data.

There are a few ways to do this, but the quickest, safest and easiest for me is to “attach” the database on another Instance of SQL Server. To understand how this works, let me quickly explain how SQL Server uses databases from a physical perspective.

SQL Server uses two types of files, sometimes lots of each type. The two basic types are Data files and Log files. SQL Server basically writes data to the Log file first, and then on to the Data files. Of course it’s more complicated than that; but this is the basic flow.

When SQL Server first starts up, it reads (among other things) the master system database. And that’s where the crux of this post comes into play. In that database are two things that we care about right now: the logical name of the databases SQL Server knows about and the physical locations for the files that database uses.

With that information in mind, here’s a simple way to transfer a database from an Instance you don’t have access to into one that you do.

There are a LOT of disclaimers here.
First: don’t do this on a database you don’t own, and own all the data for. That’s illegal.
Second, don’t do this with a large database – the possibility of multiple files in the filegroups on a complex database is high, and you might not get them all.
Third: you have to be running the same (or higher) version of SQL Server on the destination server, and the same (or higher) edition of SQL Server on the destination server.
Fourth: you’ll have to stop the source Instance. If you have users on that system, you don’t want to stop it without consulting everyone first.

OK – with all of that stated, you first need to stop the “source” instance. Let it shut down completely and normally. This will (theoretically) commit all of the transactions from the logs to the databases.

Now copy the .MDF and .LDF files for the database you want to a location on your test Instance. If you don’t know what these are, you can try and copy what you think are those files, but don’t copy any of the system databases from the source system to your test Instance!

Start the source Instance back up and let the users back into the application.

On the test system, write-down the location where you copied both of those files (assuming there was one of each – it get’s trickier when there are multiples).

Open SQL Server Management Studio.

Right-click the “Databases” node in Object Explorer and select “Attach…” from the menu that appears.

Click “Add” to find the MDF and LDF files, and then “OK” to name the database.

You have two other options as well. You can use the sp_attachdb stored procedure (detail here - deprecated) or the newer CREATE DATABASE…FOR ATTACH statement (article on that here).

In any case, you now have the database, and you now “own” it in that test Instance. The logins will all be incorrect, since the names in the database security don’t match the new Instance’s master database. But you do have that data.

Spit it out already!

BuckWoody — Wed, 06 Jan 2010 14:11:11 GMT

You’ve probably seen that commercial where the chewing-gum company van stalks the guy who has been chewing the same piece of gum too long, and they attack him and make him chew another piece.

I feel like that with SQL Server 2000. Almost every shop I go into has at least one primary application running on SQL Server 2000. Now, don’t get me wrong – SQL Server 2000 is a fine piece of software engineering. From over TEN YEARS AGO. In “software time”, that’s like a thousand years or something.

While it was great for its day, the newer versions are faster, more secure, and more robust. And every time it doesn’t get upgraded, SQL Server is perceived as “not as fast/strong/etc” as other platforms (which are upgraded, of course).

Now, I’m not suggesting that anyone upgrade for upgrade’s sake. We all have work to do, and the last thing we need to do is change out a platform when there’s no need.

But there is a need. SQL Server 2000 isn’t in mainline support any more. That means it can be attacked easier and so on. And it doesn’t scale like the new offerings, nor does it have any of the new features the latest versions have.

“Oh”, you might say, “I don’t use those features anyway.” Well of course you don’t – you can’t if you still have SQL Server 2000! How do you know the ways you could help your organization if you don’t experiment with the new stuff?

But it isn’t the DBA I would chase down and steal gum from. It’s the vendors.

Every time I raise my eyebrows when I hear about the SQL Server 2000 installs, the DBA shrugs and says “The vendor won’t certify SQL Server X, so we have to stay at SQL Server 2000 or 2005.” And I say, that’s just lazy. Unless the vendor codes specifically for deprecated features, a simple test run during their software development should allow them to move forward. I’m not saying that’s an easy task, but certainly they’ve tested their software releases once in the last ten years, no? If not, doesn’t that make you nervous?

Anyhoo, spit out the SQL Server 2000. Or I might have to fire up the company van.

After the Upgrade, it runs differently…

BuckWoody — Tue, 01 Dec 2009 16:06:55 GMT

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I can’t troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up:

“I have recently seen a change from SQL Server from 2005 to 2008 in where it handles CASE statements differently. Previously we saw a tremendous improvement in performance by using CASE statements instead of OR statements. However when one of our client upgraded to 2008 they began to notice unusually long runtimes with a few of these queries (orders of magnitude larger runtimes). Swapping it to an OR statement allows it to run in 0.075 seconds... so my question to you is do you know of any changes to the exectution engine that would account for this and what is your recommendation?”

It’s a great question. Basically it boils down to “I changed versions, now something acts differently.” Before we talk about what might be the issue, let’s talk about some things you should do after you upgrade from one version of SQL Server to another.

First, open the database properties and change the compatibility level to 10.0, unless you know you shouldn’t. Next, update your statistics – all of them. Third, ensure you have all of the proper service packs applied to the operating system and SQL Server. And finally, check the code you have for deprecated statements, or for places where you could optimize the code to use new statements or formats. There are other steps to follow, but these basics will help.

Now, with all of that done, let’s move on to things that work differently. First, you need to find out what the code is doing – and the primary way to do that is to examine the Query Execution Plan. There are a lot of resources to teach you how to do that, but the general idea is that you turn that plan on (in the Query menu), run the query, look at the graphical plan and check three items: the overall plan, the icons that show the highest percentage of use, and the thickest arrows. Evaluating this on the “before” system and the “after” system, and that will show you what changed.

Maybe.

The point is, the query might take exactly the same path, but a different component may show stress because you might have a different box or configuration. Perhaps the drive layouts changed (or should), you have more memory (or better access to it) and so on. In that case, you simply follow standard performance tuning methodologies to locate what’s waiting, and what is showing pressure.

Now to the question at hand – does SQL Server (any version) handle a CASE statement differently than an OR statement? Well, once again, the execution plan will show you that answer, but the CASE statement is used for a different purpose than an OR statement – without having all of the code it’s difficult to say which to use in a given situation. The best thing to do is to evaluate the documentation on each and decide which fits the situation best.

CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

OR: http://msdn.microsoft.com/en-us/library/ms188361.aspx

Did we make changes to CASE or OR? Not directly – but each change to the product may have orthogonal implications, which is where I point you back to the steps I mentioned for the “after upgrade” process.