Donahue, Crash Scene Investigator

Installing databases using Wix

Brian Donahue — Thu, 06 Nov 2008 22:07:00 GMT

In the promised second half of my Wix installment, I am going to demonstrate how you can install a SQL Server database as part of a software deployment using Wix and SQL Packager from Red Gate.

In my experience, your average software company has exactly one "Installer Guy". Typically, this is a very unappreciated fellow who does tons of tedious work to effectively jimmy components onto a user's computer using the blunt instrument known as Windows Installer. If there was a "hot or not" scale of development sexiness, you would put "Installer Guy" at the definitive "not" end of the scale. But I digress.

One of the many things Windows Installer can't do is to put a SQL database on someone's computer, and given the number of programs being shipped that have a SQL Server back-end database, this is darn shame. How to accomplish this herculean task has been approached in many different ways by many third-parties, and Wix even has an extension that will get an installer to create databases and run rudimentary SQL.

However, there is more to database deployment than just pushing a SQL script into an installer, there is the maintainability factor, or how to seperate the development of the SQL from the development of the installer code. There is the recovery factor, or which SQL to run if the installer fails. Finally, there is the upgrade factor, or how to ship database upgrade scripts with the conventional component upgrades such as files and assemblies.

Considering the above factors, SQL Packager can solve a lot of the SQL maintanence challenges by wrapping the scripts to create or upgrade database into a single program that will run the database creation or upgrade scripts when executed. Wix allows you to build "Custom Actions" that include the ability to run console-based programs and even suppress the output from them.

For my example, I have created a database package using SQL Packager for the version 1.0 of my sample product "Packager Test", called SQLPackagerBaseline.exe. I want to run this silently, and I can accomplish this using the wixca library that comes with Wix, which provides a function called CAQuietExec. In the InstallExecuteSequence table, we add these custom actions, their sequencing, and the condition which will cause them to execute. In this example, for instance, a database is only installed if this is a new installation, and if the user specified SQL authentication, the properties are set appropriately.


<Binary Id="wixca" src="$(env.WIXROOT)\wixca.dll" />

<CustomAction Id="Database.Install.Properties" Property="Database.Install" Value=""[SQLPACKAGEFOLDER]SQLPackagerBaseline.exe" /server:[SERVERNAME] /database:[DATABASENAME] /quiet /makedatabase" />
<CustomAction Id="Database.Install.Properties.Sql" Property="Database.Install" Value=""[SQLPACKAGEFOLDER]SQLPackagerBaseline.exe" /server:[SERVERNAME] /database:[DATABASENAME] /username:[DBUSERNAME] /password:[PASSWORD] /quiet /makedatabase" />

<CustomAction Id="Database.Install" Impersonate="yes" BinaryKey="wixca" DllEntry="CAQuietExec" Execute="deferred" Return="check" />

<InstallExecuteSequence>
<RemoveExistingProducts After="Database.Drop">$PackagerTest.Packages=2</RemoveExistingProducts>
<Custom Action="Database.Drop.Properties" Before="RemoveFiles">$PackagerTest.Packages=2</Custom>
<Custom Action="Database.Drop" After="Database.Drop.Properties">$PackagerTest.Packages=2</Custom>
<Custom Action="Database.Install.Properties" After="InstallFiles">$PackagerTest.Packages>2</Custom>
<Custom Action="Database.Install.Properties.Sql" After="Database.Install.Properties">$PackagerTest.Packages>2 AND USEINTEGRATEDSECURITY=0</Custom>
<Custom Action="Database.Install" After="Database.Install.Properties.Sql">$PackagerTest.Packages>2 AND USEINTEGRATEDSECURITY=1</Custom>
</InstallExecuteSequence>

The second major bit of work installing a database is to build a user interface. Wix comes with several vanilla user interfaces, such as Wix_Mondo, that you can use without having to design anything yourself. Unfortunately, there is no set of dialogs that you can use to specify the properties of a database, and you may find, like I have, that it's better to hand-code the whole user interface yourself to get exactly the kind of dialogs that you want. In my example, for instance, the "user name" and "password" edit boxes are grayed-out when Integrated Security is used. I have also included my own banner to brand my installation with.

One thing that is not documented too well is that the coordinates for placing controls on an Installer dialog are in "installer units" and nobody on planet Earth knows how many pixels correspond to an installer unit. For instance, the dialog below is 370x220 "Installer Units", but the banner is 494x59 pixels in size.

Something that you can do in Wix that you cannot do in Visual Studio Setup and Deployment is to write custom action progress messages to the progress bar. The only way to make the progress bar move, unfortunately, is to code this into your custom action. But at least your user will know what the installer is doing if your database package takes a long time to run!

<ProgressText Action="Database.Install">Installing database "[DATABASENAME]"</ProgressText>

I have highlighted a lot of the more interesting points of deploying a database as part of a product installation based on Wix and Windows Installer technologies, but there is so much more going on, that it wouldn't fit into a simple blog. In fact I think I can see you dozing off already!

If you want to see the complete Wix project, including the user interface dialogs and SQL Package file, please download the zip file at the end of this post and feel free to probe and dissect the wix code. Hopefully this sample makes it easier for someone to install a database as part of a Windows product installation.

Arrrrgh! Wix!

Brian Donahue — Tue, 21 Oct 2008 23:56:00 GMT

This week's time-eater, for me, was to try to create an installer package (MSI) with custom actions that install databases on SQL Servers. Typically, I had grown to like Microsoft Visual Studio Deployment Projects, because I could easily knock out some C# code and slam it into an installer and get it to do, basically, anything I want.

A few days ago, though, all I had at my disposal was Mister Pokey, the Little Computer That Couldn't. Since Mister Pokey is less than gifted in the processor department, all he runs is the "Express" version of Visual Studio. It's nice for sticking together little projects, writing a little puzzle game, and things like that, but it is minus the Deployment Project Wizard.

I remember some developers telling me that VS Deployment is for girls, and the manly-man programmer type uses WIX, you know, because it's bl**dy difficult to understand, which makes them feel all better about themselves, or something. How hard could that be? Well, it's not entirely obvious how to use it.

To make a long story short, you'll need to know installers inside-and-out before you can attempt to compile a WIX installer. There is no UI (well there is Votive, which I haven't tried out), and the documentation isn't brilliant. But on the plus side it is a gimmie and allows for incredibly fine-grained control.

If you do want to deploy an existing Windows Installer class that you had created using the VS deployment project in WIX, this is a hard-won battle, but possible. Even though the author of WIX is dead against it and every Installer Jockey at Red Gate tells me that managed installers cause grief and famine and cataclysm beyond imagination. Just to irk them, here is the code. If you want to know how to get these .ibd files, what you basically have to do is reverse-engineer one of your existing VS deployment MSIs using Orca. Actually you could use WIX (dark -x <msiname>.msi, to be specific) but I couldn't get that going. I used Orca to export the Binary table of my old installer and plopped the whole mess into a subfolder called "Binary".

<?xml version="1.0" encoding="utf-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2003/01/wi">
<Product UpgradeCode="79C0A2EF-7A27-4227-9323-376F8D643C42" Name="Packager Test" Id="4AD025EB-922B-44E1-84B6-B5CC05F19A00" Version="1.0.0" Manufacturer="Brian Donahue" Language="1033">
<Package Id="C83C3011-4205-40AA-8E0B-4106A33E9391" Manufacturer="Brian Donahue" InstallerVersion="200" Platforms="Intel" Languages="1033" Compressed="yes" SummaryCodepage="1252" />
<Media Id="1" EmbedCab="yes" Cabinet="Test.cab" />
<Binary Id="InstallUtil" src="Binary\InstallUtil.ibd" />
<Binary Id="MSVBDPCADLL" src="Binary\MSVBDPCADLL.ibd" />
<Binary Id="VSDNETCFG" src="Binary\VSDNETCFG.ibd" />

<Custom Action="SetPrereqs" Before="AppSearch" />
<Custom Action="TestInstaller.dllUninstall.uninstall.SetProperty" After="MsiUnpublishAssemblies">$TestInstaller.dllComponent=2</Custom>
<Custom Action="TestInstaller.dllUninstall.uninstall" After="TestInstaller.dllUninstall.uninstall.SetProperty">$TestInstaller.dllComponent=2</Custom>
<Custom Action="TestInstaller.dllInstall.install.SetProperty" After="SetPrereqs">$TestInstaller.dllComponent>2</Custom>
<Custom Action="TestInstaller.dllInstall.install" After="TestInstaller.dllInstall.install.SetProperty">$TestInstaller.dllComponent>2</Custom>
</InstallExecuteSequence>

</Product>
</Wix>

Now we can compile the WIX script using the very intuitive tools that come with it. And by "intuitive" I mean "batch file". The file above has been named "DBInstaller.wxs".

@Echo please ensure candle, light and csc are on the path!
del /Q output\*
rmdir output
mkdir output
..\candle DBInstaller.wxs /out output\DBInstaller.wixobj
..\light output\DBInstaller.wixobj /out output\DBInstaller.msi
msiexec -lv* output\log.txt -i output\DBInstaller.msi

What this WIX package does is to run a very simple installer with no UI. On Install, the SQLPackagerDeployment.dll is used with the arguments /SERVER=localhost and /DATABASE=SqlPackagerExample. You have to be very careful about the order of the arguments... whatever you do, the last two arguments to the CustomAction value have to remain intact! $TestInstaller.dllComponent=2 makes the custom action run only when uninstalling, and >2 makes the custom action run only when installing.

Of course, since managed installers will cause a rift in the space-time continuum, I have re-implemented the whole thing in VBScript. I'm saving that story for later!

ASP .NET: when a crash is not a crash

Brian Donahue — Wed, 24 Sep 2008 15:39:00 GMT

In the mysterious viscera of Microsoft Internet Information Server, nothing is as it seems. When applications are designed to work over the web, they must bristle with all sorts of bits designed to make the stateless stateful and many users become one inside the web server's inner sanctum.

What I am talking about are the fundamental differences in behavior between a bit of .NET code and ASP .NET code, and the processes that keep ASP .NET applications capable and highly available.

In traditional desktop applications, an unhandled exception would cause an exit with an error condition and probably log it to the application event log. If this were to happen in a web application, it would become unavailable without the end-user having the ability to restart it. For this reason, ASP .NET web applications have a number of features that allow it to recover almost seamlessly from an unhandled exception or a lack of resources on the server. Unfortunately for troubleshooting purposes, this means that your web application can exit gracefully before you even hit the problem you're trying to debug.

The Application Domain, or AppDomain, can isolate many "applications" which are actually running in the same process. If one of these applications misbehaves, it can be "unloaded" from the process, and in many cases the objects inside can be recovered and copied into a new AppDomain. The ASP .NET Worker Process, which is the process that can host one or more of these AppDomains, has many functions that can determine when to load and unload an application, and to detect whether or not the application is "healthy". In many cases, the parameters for determining application health are set by the administrator, whether he knows it or not!

An AppDomain recycle can occur for many reasons:

The application is using too much memory
The application has serviced a set number of requests
The application is taking a long time to respond
The application has remained idle for a long time

In the case of newer versions of IIS, particularly versions 6 and 7, the Worker Process Isolation model will log the reasons for an application recycle, but not for all reasons. In IIS 5, your worker process simply exits if there are no more AppDomains loaded into it. The fact that the worker process can just exit without leaving an event log entry or throwing an unhandled exception makes debugging incredibly frustrating.

I have not found a foolproof method for stopping an ASP .NET worker process from recycling, so I rely on ASP .NET 2.0 health monitoring to log any "conventional" application restarts due to a lack of memory or other environmental factors and then adjust my machine.config processModel and web.config to try to prevent the recycle while I am trying to debug the application. All that is needed is one line added to the master web.config, which you will find in the .NET Framework configuration folder (%systemroot%\microsoft.net\framework\v2.0.50727\config). Adding a rule to log every application recycle will actually allow you to see the reason why an application has shut down for a recycle.

First, look for the healthMonitoring node, then rules, and add the following:

<healthMonitoring>
...
<rules>
<add name="Application Lifetime Events Default" eventName="Application Lifetime Events" provider="EventLogProvider" profile="Default" minInstances="1" maxLimit="Infinite" minInterval="00:01:00" custom="" />
...

Once this has been saved, ASP .NET will leave behind a friendly reminder that it has recycled the application pool:

Event Type: Information

Event Source: ASP.NET 2.0.50727.0

Event Category: Web Event

Event ID: 1305

Date: 2008-09-23

Time: 13:33:19

User: N/A

Computer: XXXXXXX

Description:

Event code: 1002

Event message: Application is shutting down. Reason: Configuration changed.

In this case, I would recommend disabling any anti-virus software, as it may be modifying some of the ASP .NET application source code. There are many other reasons for an application shutdown that can usually be solved by modifying the Framework's machine.config file.

Object-level recovery from backup is here!

Brian Donahue — Sat, 16 Aug 2008 17:04:00 GMT

Version 7 of the Red Gate SQL Comparison SDK has finally brought the possibility of recovering individual objects from a Microsoft SQL Server backup to a live database. I found this an exciting opportunity to flex the old noggin and design a program that can restore schema and associated data from a backup file to a real database.

The reasons why you would want to do this are vaired. For example, I've heard it would be useful for database normalization, inserting test data, and some obscure disaster recovery situations. Of course, the compelling reason for me is "because I can"!

Simply restoring a table and its' data alone could have unintended consequences. For example, what happens when foreign keys exist between the table you want to restore and other tables? What if the table has dependent objects, such as a default bound to a user-defined function? The SQL Compare Engine can handle all of these things because of its' dependency engine which can create all dependencies in the proper order. The Data Compare Engine can also handle foreign key relationships, triggers, and unique constraints.

This means that you could insert a stored procedure from a backup, and the API can add the tables and other objects that the procedure needs, or even adjust the existing schema so that the stored procedure will run without errors!

The example program can do this and more: if the schema object being recovered is a table, all existing data will be altered, superfluous data deleted, and new data inserted so the live database will exactly match the data in the backup. In this example, the table must have a primary key or unique index to match rows of data together. It would be possible to add extra logic to allow the user to pick one or more columns to act as the basis for a comparison key.

The only downside to combining schema and data recovery from backup using the SDK is that, because the SQL Compare Engine is optimized to retrieve schema and the Data Compare Engine is optimized to retrieve data, it is necessary to read the backup twice in order to retrieve schema and data. Hopefully Red Gate can combine the two engines in the future to make the process more efficient!

The example C# project can be downloaded from the Red Gate Labs site: http://labs.red-gate.com/uploads/5/54/SQL_Object-Level_Restore.zip

You will also need to install the assemblies from SQL Compare and SQL Data Compare by downloading the SQL Comparison SDK.

Man eats crow, film at eleven

Brian Donahue — Tue, 12 Aug 2008 15:57:00 GMT

Last week I'd had a bit of a rant here about not using software for anything other than what it is designed to do. Thinking back, though, I have done this quite a few times myself, particularly by employing a code profiler to do the job of a debugger.

When good software goes bad, the right axe to wield at it is usually a debugger. Anyone who has used a debugger will probably tell you that this is a last resort, though, unless they are masochists who enjoy inflicting mental anguish on themselves. Even with the whizzy .NET runtime environment we have now, trying to find the cause of a crash or hang using a debugger requires quite a lot of patience, skill, and experience.

Yesterday, I had to try to diagnose a program hang -- not my favorite thing in the world. So off I went as I have done squillions of times, using the old auto-dumper from Microsoft to get a dump file for analysis:

cscript adplus.vbs -hang -pn MyFlakyProgram.exe

Only this time, the debugger died before reaching the critical part of the code, making a dump too early and detatching from MyFlakyProgram. What now? Debug the debugger?

It was time to rummage through the old toolbox and see what else I could do. I have a code profiler -- sure, why not? I had attached a code profiler to the flaky application, got to the critical bit, and took a snapshot. Then another. It seemed that the application had been running a SqlDataReader.Close() method for about 214 seconds by then, which sounds about as far from normal as Timbuktu is from where I'm sitting.

Honestly, I still haven't worked out why this had happened yet, but I am that critical one step closer, no thanks to the CDB debugger that crashed out on me.

The other time I had used a code profiler in an unorthodox way was to try to figure out why an application was crashing with a TypeInitializationException. Again, this happened too quickly to have time to attach a debugger (yes, I know about the ImageFileExecutionOptions registry key, but haven't you figured out how unapologetically lazy I am yet?).

TypeInitializationExceptions are thrown when an application is first being loaded into the .NET runtime, so there is no possibility to get a stack trace when the program bombs. You'll never, ever figure out what has happened without some sort of Intermediate Language X-Ray machine, which is sort-of what a code profiler does.

The profiler also shows the method results in more-or-less chronological order, so maybe, just maybe, I could work out what the last method executed by the errant program had been. And as by a crepuscular ray of light, the answer was illuminated -- someone had put a method that enumerates printers inside a type initializer (the bit of a class where variables are declared and have their initial values set). Here was the culprit:

internal class MyForm : System.Windows.Forms.Form

{...

private static bool s_HavePrinters = PrinterSettings.InstalledPrinters.Count > 0;

As fate would have it, this code doesn't work so well when your Print Spooler service is stopped!

.NET managed code profilers are designed to show where performace problems may exist in applications by measuring the time taken to enter and exit a method in the code. They could also, however, be leveraged as a powerful potential fault-finding tool. I wouldn't absolutely depend on one for this purpose, but it's nice to have a backup troubleshooting method when my debugger is on the fritz.

DOH! It's the DaftOperationHandler

Brian Donahue — Tue, 05 Aug 2008 16:26:00 GMT

Probably my least favourite question in the world, professionally anyway, is "Does your software support <choose a technology>". Well, first off, define support. Dictionary.com provides for no less than nineteen definitions for that particular word, and I can probably think of about a dozen more.

If my answer to this question is "yes", and when I say "yes", it means that the program has been documented, tried, and thoroughly tested, the fun is yet to begin. When customers ask this question, eight times out of ten it's purely rhetorical, and it's about to slap you in the face with a "well, we tried it, and it doesn't work" reply.

For example, we get a phone call about a log shipping problem in our backup application. "Do you support log shipping"?

"Yes, we do." (It's right there in the manual)

"Well, it doesn't work!"

*sigh* "Fine, tell me about the problem."

"Every five seconds, we do a log backup at our site in Los Angeles, copy the file over a Virtual Private Network over a T1 to La Paz, where it is copied via FTP over a 56Kb modem to Tel Aviv."

"Uh-huh."

"The log backups are only 200 megabytes each".

"Uh. Huh."

This is why the new frontier of software supportability features should be DOH, or Daft Operation Handlers.

The first generation of error messages in software told you that something has gone wrong.

The second generation of error handling messages had told you exactly what went wrong.

The third generation tell you what to do about the problem.

DOH is the fourth generation of error handling (4G!), in which you are told that you are about to do something that is more than likely going to result in an error because it defies the immutable laws of physics or would require some sort of time machine to work.

My DOH provides for four types of daft operations:

public enum DaftType

{
Insane=0x01,
NotVeryBright=0x02,
ExpectTheImpossible=0x04,
HaveLotsOfSpareTime=0x08
}

When the program detects that it is about to be used in a supported, but nevertheless impossible fashion, it should trigger a DaftOperationHandler.

public delegate void DaftOperationHandler(object o, DaftOperationArgs e);
public class DaftOperationArgs : EventArgs
{
public DaftType TypeOfDaftness;
public DaftOperationArgs(DaftType e)
{ TypeOfDaftness = e;
}
}

The registered DOH should display the appropriate message to the user:

static void f1_DaftStatus(object o, DaftOperationArgs e)

{

if ((e.TypeOfDaftness & DaftType.Insane) == DaftType.Insane)

MessageBox.Show("Are you out of your mind?", "Questioning your rationality", MessageBoxButtons.YesNo);

if ((e.TypeOfDaftness & DaftType.ExpectTheImpossible) == DaftType.ExpectTheImpossible)

MessageBox.Show("You can't get blood out of a stone", "Think again...");

if ((e.TypeOfDaftness & DaftType.HaveLotsOfSpareTime) == DaftType.HaveLotsOfSpareTime)

MessageBox.Show("For pete's sake, get a real job!!!", "Thinking about being a writer?");

if ((e.TypeOfDaftness & DaftType.NotVeryBright) == DaftType.NotVeryBright)

MessageBox.Show("There is a reason why software comes with a manual", "RTFM warning!");

}

Hopefully, implementing DOH! will prevent many unnecessary calls to the support desk and improve the customer experience and make everybody all-round more productive.

Po man's patchworkin'

Brian Donahue — Thu, 27 Mar 2008 23:03:00 GMT

If you had come of age before Component Object Model as I have, the concept of COM registration seemed rather odd at first glance. Why is your system limited to exactly one application extension which is forced to be backwards-compatible, when, for years, other operating systems have enjoyed the ability to have many versions of the same shared library and an established probing precedence to find the appropriate library file? This has landed us Windows users in deep doo-doo when we try to upgrade software that uses shared components (so-called DLL Hell).

Thankfully, the advent of Microsoft .NET had done a lot to solve this problem. Not only can the same dll exist in multiple locations, and a predictable probing behaviour has been established, but we can even override the assembly binding by creating an assembly config file. This feature can be leveraged for an easy way to 'patch' errant software, provided that the new assembly has the same public methods and objects available as the old one.

I had recently been in a situation where I could put this into practice. Our schema comparison keeps all of its' logic in a shared assembly: RedGate.SQLCompare.Engine.dll. This shared component had been updated to support a certain SQL Server 2005 feature that I won't name, suffice to say that it wouldn't require a change to the User Interface to support it. Because the UI and the Engine were of the same major version, it seemed a perfect time to exercise my assembly binding prowess. Here is the path to dll heaven when you need to upgrade a single dll in a piece of software:

Step 1: Identify the assembly name of the dll that you want to replace. The assembly name is normally the same as the file name without the extension, but this is not a given. Tools such as .NET Reflector will tell you the assembly name, and if you're really hard-core, you can code up something using Reflection: Assembly.LoadFile(@"c:\file.dll").FullName. When you configure the assemblyIdentity (below) you will need to know the assembly name.

Step 2: Copy the dll, and any dependent dlls, into a subfolder in the calling application's APPATH. This is very important because .NET will not allow you to bind to an assembly originating from a folder outside of the application's "home" folder, presumably for security reasons. I have created a folder called SQL Compare Engine 63 underneath the program's folder for this reason.

Step 3: Open Notepad and create a file named after the executable that is going to load your dll, appending ".config" to the end of the filename. In my case, the file will be called RedGate.SQLCompare.UI.exe.config. The contents of this file will be in XML, the bindingRedirect element is the magic that allows me to use the new dll in place of the old one.

You may notice a few things about the contents of this file that I haven't mentioned. First of all, in addition to the elements that identify the assembly that I want to configure, I have added the codeBase directive. This will instruct .NET to look in the sql compare engine 63 folder that I had created to hold the new version of the dll because I feel a lot safer not overwriting the shipped SQL Compare dll. I have also added the supportedRuntime tag. This may be important if the application that you were patching was built against the .NET 1.x runtime and the replacement dll was built against 2.0. If the whole shebang loads into the 2.0 runtime, then there shouldn't really be any incompatibilities (with the requisite exceptions, of course!)

An additional complication is introduced if the replacement dll relies on updated dependant dlls. Just because we have redirected the RedGate.SQLCompare.Engine.dll doesn't mean that the runtime will probe the SQL Compare Engine 63 for dependencies. One solution could be to add a probing path inside the assemblyBinding element:

However, this doesn't seem to work for me and my guess is that it's because codeBase always overrides probing. To solve this problem, I got a list of all dependent assemblies from .NET Reflector for RedGate.SQLCompare.Engine and added the file locations for all dependencies using codeBase to make the complete config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
   <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="RedGate.SQLCompare.Engine" publicKeyToken="7f465a1c156d4d57" culture="neutral" />
            <bindingRedirect oldVersion="6.2.1.36" newVersion="6.3.0.123" />
            <codeBase version="6.3.0.123" href="file://c:/Program files/red gate/SQL Compare 6/sql compare engine 63/RedGate.SQLCompare.Engine.dll" />
         </dependentAssembly>
         <dependentAssembly>
            <assemblyIdentity name="RedGate.SQL.Shared" publicKeyToken="7f465a1c156d4d57" culture="neutral" />
            <codeBase version="6.2.1.30" href="file://c:/Program files/red gate/SQL Compare 6/sql compare engine 63/RedGate.SQL.Shared.dll" />
         </dependentAssembly>
         <dependentAssembly>
            <assemblyIdentity name="RedGate.SQLCompare.ASTParser" publicKeyToken="7f465a1c156d4d57" culture="neutral" />
            <codeBase version="6.3.0.123" href="file://c:/Program files/red gate/SQL Compare 6/sql compare engine 63/RedGate.SQLCompare.ASTParser.dll" />
         </dependentAssembly>
         <dependentAssembly>
            <assemblyIdentity name="RedGate.SQLCompare.Rewriter" publicKeyToken="7f465a1c156d4d57" culture="neutral" />
            <codeBase version="6.3.0.123" href="file://c:/Program files/red gate/SQL Compare 6/sql compare engine 63/RedGate.SQLCompare.Rewriter.dll" />
         </dependentAssembly>
      </runtime>
   <startup>
      <supportedRuntime version="v2.0.50727"/>
   </startup>
</configuration>

This seems to work a treat! The only drawback is that your new dll may be incompatible with the application because public methods may have been deprecated or some other undesirable behaviour.

Optimize Prime

Brian Donahue — Wed, 26 Mar 2008 23:09:00 GMT

Ever since the dark ages of programming, certain tricks have been implemented in code compilers to eke out the maximum performance from the compiled code, the most important of which is inlining. Put simply, inlining is the process by which a child method is inserted into the parent method in its' entirety. For larger methods, this would make about as much sense as a chocolate teapot because the program would suffer from serious bloating if the same method had been invoked many times from different places. For smaller methods, however, this makes perfect sense and offers a huge performance boost, especially when methods whose compiled code is 32 bytes or less, because absolutely no space is wasted considering the method pointer occupies 32 or 64 bits anyway and performance is boosted because the CPU doesn't need to "jump" across to different memory locations as often.

The same optimizations are also available in the Microsoft .NET Runtime's Just-In-Time compiler, although this is done more or less on the fly.

With an eye towards performance analysis tools such as code profilers or coverage tools, this has some implications in the results because methods that have been inlined effectively cease to exist, so many of these tools disable inlining and other optimizations to save the end-user from confusing and nonsensical results. The disadvantage of this approach is that your code profiler doesn't exactly run your code in the same way as it would run in the "real world" due to the absence of JIT optimizations. Thankfully, ANTS Profiler allows you to profile inlined code using its fast mode profiling, and provides visual cues as to which methods had been inlined and which source code had been inlined into parent methods.

I've made a brief 5-minute presentation demonstrating the speed increase effected by inlining, and the way in which ANTS Profiler will show information related to inlined methods.

Demonstration of inlining using ANTS Profiler

Exceptionally expensive

Brian Donahue — Wed, 26 Mar 2008 18:09:00 GMT

Many years ago, when switching from programming in plain old C to the managed environment of .NET Framework, I had discovered exceptions. The idea was not completely new to me because I'd already seen try/catch blocks in JavaScript and I liked that method of error handling a lot, especially when compared to the ON ERROR GOTO handling that VBScript uses, which is why I prefer using JScript whenever I can, although sometimes in the scripting environment you begrudgingly have to use VBS.

.NET had significantly enhanced the try/catch block by allowing the programmer to extend the exceptions by adding their own properties and methods to them. In addition, the exceptions can be typed, so if you are interested in one type of exception, say a file can't be opened, but not in another type of exception, for instance an out-of-memory condition, you can have that sort of granularity.

So I thought, great!, I will use exceptions everywhere. I will use them all over the place, not only to handle catastrophic and unusual errors, but also anywhere an object could not be created or a value exceeded a certain threshold or any one of 1001 completely common situations where something happened that needed some conditional branching to happen. This, as I found out, could have some particularly nasty performance implications!

Here is a simple example to demonstrate just how much slower throwing exceptions can make your .NET Program:

using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
namespace ExceptionTest
{
class Program
{
static void Main(string[] args)
{
for (int i = 0; i < 1000000; i++)
{
HandleViaException();
HandleViaCondition();
}
}
static void HandleViaException()
{
string s = null;
try{
string ss = s.Substring(0, 2);
}
catch (System.NullReferenceException)
{
}
}
static void HandleViaCondition()
{
string s = null;
if (s != null)
{
string ss = s.Substring(0, 2);
}
}
}

In the HandleViaException method, I attempt to execute a method on a string object set to a null value, which will cause a NullReferenceException and make the exception handling code run. In the HandleViaCondition method, I simply check the value of the string, and if it is null, I do not run the Substring method on the string. Although these methods perform the same function, there should be a noticable performance difference when the methods are both run a million times. I had tested this using the ANTS Profiler code profiling tool with the following results:

HandleViaCondition -- Hit Count: 1000000 Total Time: 0.571 seconds
HandleViaException -- HitCount: 1000000 Total Time: 50.4 seconds

Using Exceptions to trap a null condition is roughly a hundred times slower than simply checking to see if the string is null. I knew that using exceptions would incur a performance penalty but mama mia that is slow! I've taken a program that should return in less than a second and turned it into an excuse to hang out at the water cooler and gossip for awhile.

Could I make this any worse? Oh, yes I can, by attaching a debugger (cdb.exe) to the program as well!

HandleViaCondition -- Hit Count: 1000000 Total Time: 0.554 seconds
HandleViaException -- HitCount: 1000000 Total Time: 54.3 seconds

Well, that's not too much worse, but then again, cdb is pretty lightweight. Let's attach to it using Visual Studio 2005's debugger:

HandleViaCondition -- Hit Count: 1000000 Total Time: 0.678 seconds
HandleViaException -- HitCount: 1000000 Total Time: 1936 seconds

See, now I have a convenient excuse to go down to the cantine and get a donut. Mmmmmm, donuts.

The Visual Studio debugger is particularly invasive when it encounters an exception in the code that you're debugging. You expect a debugger to pause your code when an exception is encountered, grab information about the stack and heap, and allow your code to continue on. With CDB.exe, the overhead is pretty minimal, but Visual Studio 2005 seems to pause my running code for much longer.

The end result is that if this code was part of a real-world application, I would probably spend all day trying to debug it, and I frankly have better things to do, like eat bacon sandwiches. On toast. With some of that nice Brown Sauce they have over here.

From now on, I use exceptions in my code very sparingly, and try to avoid using them in code loops altogether because the cumulative effect of wasting a few milliseconds in a tight code loop can turn an application into sludge if you're not careful!

Need to loosen my bindings

Brian Donahue — Tue, 13 Nov 2007 23:29:00 GMT

Microsoft .NET's runtime provides an execution engine for Just-In-Time compiled code, but it also has the clandestine capability to pre-compile code and cache it on disk. This at first seems a little odd, since the point of environments like .NET Framework and Java are supposedly designed to offer machine-independent, 'virtual' code. I suppose that .NET's native image support was introduced to solve some performance pitfalls of using Intermediate Language code, which needs to be compiled dynamically as it runs. Native Images would not suffer from this performance loss, since the compilation has been done in advance.

Native images can become invalid, however, and cause some very strange errors. In one case, one of our programs was crashing at random points in the usage, and in most cases, the program would actually fail to even start, throwing a scary-looking invalid program exception and offering the chance to debug, which presents some users with and obtuse error message and if a debugger is installed, a yellow arrow pointing at a machine instruction that nobody who has started programming a computer after 1987 really understands.

Theoretically, of course, invalid native images should not occur. But if we understand how these native images are created, it's possible to see some holes in Microsoft's design.

For starters, native images are typically created when a program is installed, and the process appears to be automatic -- Windows Installer knows that .NET assemblies are contained in an MSI by some hocus-pocus and ngen.exe is invoked to create a cache of native images for all assemblies in the MSI.

If the native images are never modified, there is a possibility that they could become invalid, if, say the .NET Framework environment had changed. If the Framework is patched or re-installed, there could be outdated reference in the native image that would cause the program to crash where it would not if it were properly loaded and JITted in near-real time. Microsoft have thought about this and designed the Framework to examine the .NET Runtime for changes that would cause a problem. If, for instance, mscorwks.dll, mscoree.dll, or other runtime libraries have changed, this will force a new native image generation when a cached assembly tries to load. Likewise, any changes to dependent assemblies, even if they have cached native images, will force a 'cascading' recompilation of native images for all dependent assemblies.

It sounds like Microsoft have thought of everything, but, alas, no, there are situations where an invalid native image is sitting on the hard disk, waiting to strike. Microsoft know that when they apply changes to the .NET Framework, such as the updates that may be distributed by Windows Update, that the runtime's native image cache needs to be updated. So these updates run NGEN.exe /update to refresh the native images so that they are no longer invalid. Sounds all well and good -- but there are some situations where multiple automatic updates have resulted in invalid native images that the runtime thinks are still valid, probably because the system really requires a reboot after running the first .NET update, but Automatic Updates silently suppresses it and applies a second .NET update.

If you come across strange behaviour in a managed .NET program, it may be useful to rule out the native image as the cause before accusing your program's vendor of releasing buggy code! It is possible to determine if the program you are running is the JITted version, or a native image, by using our old friend, the Fusion Log Viewer. Setting this tool up as described in the previous link will create a log entry any time a .NET assembly is loaded by your program. Because Fusion Log Viewer can discriminate between bindings to native assemblies and ones that are purely Intermediate Language, it's plain to see whether the assemblies are being loaded from cache or compiled dynamically by mscorjit.dll.

If the errant program is loading lots of native-image assemblies, running %systemroot%\Microsoft.net\framework\v2.0.50727\NGEN.exe /update may just put your program, and possibly many others, back in working order.

Using the kill to cure

Brian Donahue — Sun, 14 Oct 2007 15:48:00 GMT

I was watching a medical drama the other day, you probably know the one, where the diagnostician darn near kills the patient with bizarre tests in trying to figure out his illness, given a strangely conflicting set of symptoms. I've come across some similar situations with my inorganic patients recently which forced me to step back and look at the whole patient rather than the symptoms presented.

The code profiling tool that I provide support for basically works by loading one of our DLLs into the process being profiled, and there is a possibility for the process to crash or hang with our DLL hooked in when it runs perfectly fine without it. Previously, my focus was strictly on finding bugs in our DLL, but I have come to believe that this is a pretty narrow-minded approach.

Everybody over age five has heard of the proverbial camel with the lumbar malady upon which a straw too many had been loaded, causing the camel to exhibit a lack of structural integrity. Well, that's how I would word it in my bug report because I'm a stickler for the specifics.

The metaphor is a good one, though. A programmer will notice poor performance or memory usage in his application and try to find the problem using our code profiler to gather information about slow methods or poor memory allocation, so a 'sick' application is going to have to carry the additional strain of loading our dll and all of its' requirements of memory and processing time along with it. It doesn't take a master engineer to realize that this might fail for more basic reasons than a bug in our code.

This is similar to a medical case in the respect that the patient needs to be fit enough to survive a procedure that may cure the underlying problem. If a patient needs an angioplasty and presents to surgery with nervous problems, the nervous system would need to be stabilized first so he isn't flopping around on the operating table like a fish out of water while surgeons try to fix a delicate artery wall.

I'd like to say that our code profiler is the tool to help cure to all performance problems, but sometimes other diagnostic tools need to be employed to point at the serious problems first so that the code profiler can be used to find inefficient code.

In one instance, a case came to my attention where a program appeared to hang while being profiled. Sure, profiling introduces some overhead, but this was extreme (the spash screen stayed up for an hour!). I'd suggested using performance counters, specifically the .NET CLR Memory->% Time spent in GC while profiling, and found that the application was spending 90% of its' processing time just cleaning up objects in its' own memory! Without profiling, this went down to a 'respectable' 80%. This application was in too bad a shape to be profiled because it had so many objects containing self-references. Simply adding more data to the application could reproduce the hang even without profiler, so this patient was clearly running out of time.

In rare cases, it's time to wield computing's least understood tool: the debugger. But first we will need a crash or hang dump from the customer produced using adplus.vbs from the Windows Debugging Tools suite. This produces a big, fat file containing the memory of the application, including the .NET runtime, so it's seldom to have a customer who is willing to upload a 50MB file to us. I can use windbg to analyze what happened at the time of failure. It's a dark art, but can reveal interesting information about the application. This MSDN blog is probably the most useful resource I've found on how to use the Windows Debugger.

Another common remark from customers is that although the code profiler shows minimal memory usage by their applications, the total space as reported by Task Manager is monumentally huge. In this case, you may not need to worry about optimizing your code, which is what a code profiler helps to do, but rather you want to examine the process memory space and have a look at how many modules are loaded into it. Then it's a question of perhaps ridding yourself of unused references or using alternative components in your code that are smaller. A simple, free tool for doing this is Red Gate Memory Tracker, which will give you a graphical representation of what is loaded into memory. Cleaning up this memory first may give the code profiler a bit more 'elbow room' if the problem is a shortage of memory.

Once the critical problems in an application are sorted out, then the task of enhancing the application performance is much easier!