Database Updates without Downtime

I was forwarded in interesting article, Developing Low-Maintenance Databases by Alex Kuznetsov, that talked about good database design and implementation practices.  Many I already adhere to, such as limiting access to the database through stored procedures, views and functions.  However, there was one section in the article that I found particularly intriguing, How to Refactor a Table without Downtime.

In many of the systems I work on we have many components on many servers accessing the same database, coordinating the simultaneous upgrade of those components can be tricky at best.  Although in some cases access can be controlled through a web service and thus limiting upgrade points, performance factors have led us to have many of the components access the database directly.

I definitely intend on looking at incorporating Alex’s pattern into our future upgrade procedures.

COALESCE vs. ISNULL in T-SQL

A colleague of mine forwarded the following article along to me:

Deciding between COALESCE and ISNULL in SQL Server (http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/)

I switched over from ISNULL to COALESCE a couple of years ago and haven’t looked back, primarily to standardize and avoid nested ISNULLs.  This article seems to affirm my thought that there are few disadvantages to COALESCE except in certain unusual circumstances.  It’s worth a quick read.

Tracing WCF Calls

I ran into an issue with one of my WCF services and was having the quite a time trying to track it down, especially since it was failing before it was getting to the actual service.  I came across a nice way of tracing the call all the way through the process.  Add the following section to your web.config file:

<system.diagnostics>
  <sources>
    <source name="System.ServiceModel"
            switchValue="Information, ActivityTracing"
            propagateActivity="true">
      <listeners>
        <add name="traceListener"
            type="System.Diagnostics.XmlWriterTraceListener"
            initializeData= "C:\Logs\Traces.svclog" />
      </listeners>
    </source>
  </sources>
</system.diagnostics>

This will create a trace file that you can open up in the Microsoft Service Trace Viewer.  You can read about the details here.

Quick note – Don’t forget to remove it when you’re done!

WCF Security Debugging Trick

Not infrequently, while deploying my WCF web services to various dev and QA environments I run into security issues, where for some reason I can’t get authenticated.  I recently found an article that (WCF: "An error occurred when verifying security for the message." and Service Security Audit) that talks about how to get more info on these errors logged in the application log using the following serviceSecurityAudit behavior:

<serviceSecurityAudit 
	auditLogLocation="Application"
	serviceAuthorizationAuditLevel="Failure"
	messageAuthenticationAuditLevel="Failure"
	suppressAuditFailure="true"/>

Text Files, ASCII, ANSI, Unicode and Code Pages

OK, so according to Joel Spolsky, I should have known this 7 or so years ago, but I’m only now dealing with multi-lingual text files so you’ll have to forgive me.  Anyway, this is a very instructive article on the encoding of text files:\

The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

C# Case-Insensitive Dictionary

Here’s a neat little feature I wasn’t aware of until recently.  You can make a .NET Dictionary object case-insensitive.  i.e. myDictionary[“ABC”] returns the same as myDictionary[“aBc”].  You can read more at:

C# Case-Insensitive Dictionary on Dot Net Perls (http://www.dotnetperls.com/case-insensitive-dictionary)

Returning Multiple Disparate Result Sets as XML From SQL Server Using FOR XML EXPLICIT

OK.  So I’m finally returning to a SQL Server topic.  The integration tool I’m working on does a great deal of processing of XML.  In some cases, a single XML document consists of multiple result sets.  I won’t get into the details of why we need this, but a good example of where you might use it is to return an Order object from a database for an edit form and additional return a list of Order Types to populate the Order Type drop down box.  In this case the XML might look like this:

<orderformdata>
    <orders>
        <order>
            <orderdate>1/1/2011</orderdate>
            <ordertypeid>1</ordertypeid>
            <orderamount>1000.00</orderamount>
        </order>
        <order>
            <orderdate>1/1/2011</orderdate>
            <ordertypeid>1</ordertypeid>
            <orderamount>1000.00</orderamount>
        </order>
    </orders>
    <references>
        <ordertypes>
            <ordertype id="1" name="Professional Services"/>
            <ordertype id="2" name="Product"/>
            <ordertype id="3" name="Support Contract"/>
        </ordertypes>
    </references>
</orderformdata>

Using SQL Servers FOR XML clause, it’s quite easy to output a result set as XML.  With the EXPLICIT directive you can have a great amount of control over how the XML is rendered.  However, the examples tend to show how to create XML from a single result set or nested records (say orders and order details).  So how to return the above?