Determine how my SQL memory is being used

by Joe Havelick 26. March 2010 12:22


Tech Tips

Cloud Computing on the Windows Azure Platform

by Joe Havelick 10. February 2010 10:08

The Windows Azure Platform is Microsoft's cloud computing platform.  The goal of this article is to give a basic familiarity with this platform, what it can be used for, and some of the benifits and risks. In order to explain Windows Azure, one must understand the concept of cloud computing.  Cloud computing is the concept of hosting technology applications and data on the internet, AKA "the cloud".  It is characterized by:

  • Shared resources - Multiple applications and data reside on the same hardware (and often redundantly across multiple pieces of hardware).  They are logically isolated from other customers' data.
  • Scalability - The ability to acquire as many resources of a virtually unlimited size as one might require.  This may be measured in terms of CPU cores, RAM, storage space, or databases.
  • Rapid provisioning - Resources can be added dynamically, often through an API, as additional capacity is needed.  The same is true in reverse, that capacity can be taken offline as demand decreases.
  • Utility Service - Consumers pay for the resources they consume.  Different vendors have different metrics, but you are paying for small units of computing power, rather than the cost of capital to build out a server farm that will never reach 100% efficiency due to peaks and valleys in demand.

Typically, this is achieved through the use of virtualization on commodity hardware.  As a caveat to the concept of internet based computing in the public cloud, there is also the concept of private clouds, which are virtualization farms hosted in a private data center.  These clouds can often interoperate with the public cloud to supplement their own capacity as needed.
There are three primary categories of cloud computing services available:

  • Software as a Service (SAAS) – Software built on a cloud computing platform.  This includes some free services, such as Gmail, and paid services such as Mozy or Adobe Photoshop Express.  Customers are often billed on a subscription or by use basis, rather than a onetime software license cost.
  • Platform as a Service (PAAS) - Software development platforms hosted in the cloud.  This includes Windows Azure, Google App Engine,, and Engine Yard.  Developers may build applications using and SDK which they can deploy, manage and access natively in a cloud platform.
  • Infrastructure as a Service (IAAS) – A cloud based infrastructure.  In the case that computing power is required, but the PAAS model does not provide the required flexibility.  IAAS is offered by a few vendors, including Amazon AWS and Rackspace.

As we have said, the Windows Azure Platform is a PAAS.  The Windows Azure Platform consists of:

  • Windows Azure – The application runtime environment Windows Azure Storage Services
  • SQL Azure – A cloud based service for hosting MS SQL databases.
  • AppFabric – The management tools for Azure based applications, Service Bus, and Access Control Service
  • VS 2010 SDK – The toolkit for building Azure applications.

(Note: the MS Marketing team seems to be having a field day with the nomenclature.  There used to be entities like ‘.NET Services’, which has now become AppFabric.)
Using the SDK, Windows Azure allows the deployment of 4 types of solutions; ASP.NET Web Role, WCF Service Web Role, Worker Role, and CGI Web Role.  Not all .NET functionality is available, but the feature set is extensive and pretty well documented on MSDN.  When deploying applications, the management interface forces the deployment to a “staging” environment, where you can test your application before swapping to production.  The changeover is almost instantaneous in that it changes the DNS records to point to the new app, and limits based on propagation and caching may apply.
Windows Azure Storage Services is the storage solution.  There are three types of entities that can be stores and retrieved.  The Blob Service handles entities such as binary and text files, much what you would traditionally store on disk.  The Queue Service allows for the use of messaging between services, to enable a persistent connection, even if services become backlogged or shut down.  The Table Service stored named pairs and is not related to SQL tables as one might think.
SQL Azure allows you to host SQL databases on the cloud using most features available in a Standard SQL 2008 license.  You may connect using SQL authentication only, but you will be able to use SQL management studio, ODBC, ADO.NET, etc.  It has a SQL firewall that denies all connection by default, and can be configured to allow as small of a surface area as required. This makes SQL Azure something that might be useful not only for cloud applications, but as a standalone service for hosting SQL databases in the cloud.
The service bus is a toolkit/service for allowing more seamless connectivity between applications and data stores between the cloud and your LAN.  There are all sorts of demons between you and the cloud (firewalls, proxy servers, routers).  The service bus is a way of simplifying those connections (without VPN) and seems especially useful if you’re utilizing storage or SQL in the cloud, when connecting from an application hosted locally.
The Access Control Service (ACS) is a way to provide cross domain authentication of users without a federated identity service.  Mind you, it is not limited to Windows domains.  A pretty quick and dirty video explaining the Service Bus and ACS can be found here.

The following are some common uses for Windows Azure:

  • The obvious; Platform for SAAS solutions
  • Running batch processes which require a large amount of resources for a limited duration
  • Hosting commodity processes, which may require a frequently changing number of users or resources based on a changing demand.  For example, a university bookstore “used book website” will see massive spikes in its use just before and during the first week of each semester, and minimal use otherwise.  Alternately, build your own Amazon Mechanical Turk.

There are several advantages to cloud computing with Windows Azure:

  • High Availability (HA) – Without any effort on your behalf, if you provision a SQL database in SQL Azure, it automatically takes advantage of the HA features in SQL.  It is promised that your database is replicated to at least 2 datacenters within a specified region, and will failover automatically if something faults on the primary.  In the meantime, it will begin replicating to another server to become the new failover.
  • Scalability – If you’re building out your own datacenter, you must build for peak demand plus a safety factor.  This can be costly and creating a accurate estimate can be like trying to catch a cat in the dark.  PAAS allows for commissioning and decommissioning of resources in near real time as needed.
  • Pay per use – You only pay for what you use, and since the use is often coupled directly with the number of users or subscribers, you can then much more easily put a “per-head” cost on the application.  This makes calculating and achieving your ROI that much easier.
  • Lower Total Cost of Operations – Beyond the cost of acquiring hardware and software licenses, there are the costs of commissioning, operating, patching, maintaining, upgrading, backing up, replacing, recycling, and securing them.  There are the costs for maintaining the supporting services (network, power, cooling).  There are the indirect costs of benefits for those people that perform this work.  The total cost associated with running a datacenter can be incredible for a small organization.  Using Azure can mitigate those costs.

And there are several risks:

  • Security/Privacy – Some organizations will simply never put their data in another vendor’s data center.  Vendors can be certified under industry standard certifications, but my feeling is that you can never completely preclude a data compromise.  Out of sight is not out of mind in this case.  You’re putting a lot of trust in your vendor and every one of its employees to do no harm.
  • Latency/Throughput – Although there are a number of options available to build fast connections to your datacenter, they come with and additional cost and can never reach the same performance as having your application server right next to your database server on the same LAN as your users.
  • Connectivity – You now rely very heavily on your internet connection.  Although the datacenters will have backups for everything, how reliable is your connection?  Can you deal without your application if the internet is not available?
  • Infrastructure Surface Area – Albeit a minimal change, you are increasing the surface area of your infrastructure, and thereby increasing the potential that it may be a target and a victim of hackers.

Pricing for the service can be found at Pricing.  Note that the compute time is CPU running time, not CPU/proccess time.


Discourse | Tech Tips

Creating a delimited field from a number of rows

by Joe Havelick 24. July 2009 09:55

I found a couple of tricks in solving the problem of generating a single comma separated field from a number of rows. The function looks a little like:


(     @CompanyID int)

RETURNS nvarchar(128)





      SELECT @NAICS = COALESCE(@NAICS + ', ', '') + NAIC

      FROM NAIC_Codes

      WHERE CompanyID=@CompanyID




So how do we generate the list? Simply appending to a string with each row added. But this always gets 10 times more complex because you don’t know whether to add a comma. That’s where my favorite new keyword COALESCE comse into play. You can read the details below, but basically it’s like an IIF statement but it assesses for the value to be null or not null. So, it will add a comma before the next value only if @NAICS is not NULL.

COALESCE (Transact-SQL) -
Using COALESCE to Build Comma-Delimited String -


Tech Tips

URL Decoding in SQL

by Joe Havelick 16. July 2009 14:19

CREATE FUNCTION dbo.UrlDecode(@url varchar(3072))
RETURNS varchar(3072)
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072
    SET @count = Len(@url
    SET @i =
    SET @urlReturn = '' 
    WHILE (@i <= @count
        SET @c = substring(@url, @i, 1
        IF @c LIKE '[!%]' ESCAPE '!' 
            SET @cenc = substring(@url, @i + 1, 2
            SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 1, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int
                            END * 16
                            CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' 
                                THEN CAST(SUBSTRING(@cenc, 2, 1) as int) 
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int) 
            SET @urlReturn = @urlReturn + @c 
            SET @i = @i +
            SET @urlReturn = @urlReturn + @c 
        SET @i = @i +
    RETURN @urlReturn




Tech Tips

Logging in to a remote terminal sessions when you have reached the maximum number of connections.

by Joe Havelick 16. March 2009 09:50

Windows XP only allows for a single (console) instance of an interactive logon. Windows Server 2003, without terminal services licensed and enabled, will allow for an additional two remote connections. Occasionally, usually because users forget to logout from remote desktop, but occasionally because remote desktop doesn't reuse preexisting connections, you may be prompted with:

The terminal server has exceeded the maximum number of allowed connections.

In order to connect, you may logout users from existing connections.  To do so, there are a couple of command prompt commands you must know:

qwinsta /server:servername 

This will list all active connections (including via the console). You can identify who has the open connection via the username column. Find the ID of the connection you wish to terminate, and pass it into the following command:

rwinsta /server:servername ID


Tech Tips

Automatically setting compilation debug to false in production

by Joe Havelick 15. March 2009 12:24

When running an ASP.NET configuration in production, it is important to assure that a couple of things are in place.

First, you don't want to publish in debug compilation mode.  See the first reference below for a more detailed explanation, but long story short, you're wasting a lot of processor time and memory if you do.  The way to go about doing this is to set <compilation debug="false"> in the web.config.  Unfortunately, there is no simple way to automate this (although you can make it part of the build process, see second link below), and it becomes something that is easily overlooked in deployment.

Second, you want to preclude users from receiving the ASP.NET errors, which although very helpful, can contain sensitive information such as connection strings (possibly with passwords) or stack traces which can give hackers an edge on cracking your system.  You can preclude this using the <customErrors mode="RemoteOnly" ...> or <customErrors mode="True" ...> settings in the web.config, which should be done regardless of the fix below, because you will want to be presenting users witha  "friendly" error page.

As an administrator, you should deploy the following key into the machine.config file (located at C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG):



          <deployment retail=”true”/>



This will override the compilation debug mode to be false for all web applications on that machine, as well as preclude the ASP.NET error pages from occurring.  Of course this means you will need to build appropriate error handling into your application, but you already did that anyway, right?




Tech Tips

About Me

Joe Havelick is a reasonable facsimile of this photo.

profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites