Friday, March 13, 2009

32-Bit To 64-Bit Upgrade

I've been busy upgrading and moving a MSSQL server and all of the associated applications from a 32-bit Windows 2000 server to a 64-bit Windows 2003 server. The process has been a lot of trial and error. Below are some tips to use when moving from 32-bit to 64-bit.

Try to find a 64-bit equivalent of your application. If you wrote the application, recompile it. If you bought the application try to find an upgrade. If you have to use the 32-bit application keep reading.

If you have to run 32-bit applications using WOW (Windows On Windows) remember that nearly all of the system configurations and libraries are separate between 32/64-bit. The problem is it is not always obvious how to handle each case. The registry for example uses the same regedit.exe for both editing both the 32-bit and 64-bit keys. The difference is that all of the 32-bit keys can be found in the [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node]. There are some exceptions for COM, but if you have a 32-bit application that just isn't working check its registry keys to make sure they are in the right place.

External drivers are another issue. From my experience many 32-bit ODBC drivers install just fine under WOW. If you come across one that doesn't, I couldn't find much else to do other than find a more recent version. While we're on ODBC it (unlike regedit grr) has two completely separate apps for managing 32-bit and 64-bit ODBC entries. 32-bit applications will only see the entries in the 32-bit ODBC manager and vice versa for 64-bit. This is another gotcha to check if a legacy application isn't working and it is database driven.

Since it is a MSSQL server that is being moved there is one final piece to remember. DTS packages only run in the 32-bit container (even when running MSSQL-64). This means that any external ODBC drivers that they may need also have to be installed in the 32-bit ODBC manager. Another small annoyance is that the DTS package editor no longer works under 64-bit windows. This means that all DTS package editing has to happen on a 32-bit machine. All the more reason to finally upgrade those last remaining DTS packages to SSIS.

Good luck!

Thursday, March 12, 2009

Agile and Cowboy Coding

All too often I meet people who claim they are agile developers. After a short discussion I realize they are not using any software development methodologies and are instead cowboy coding. Agile development is not a plan free environment. In fact, being an agile developer requires more developer discipline than even the dreaded waterfall.

Agile development requires the proper tools. Without tools to help with automated testing, refactoring, source control, etc... agile development would be dead in the water. I believe it also requires programming language support. You could try to be an agile developer writing assembly code, but I don't think you would get very far. Java really set the ball rolling for languages that supported the agile way of developing. The trend has continued with scripting languages like Python and Ruby (and it's associated frameworks like Rails).

The developer must also understand how to properly use all of these tools. Back when K&R released their book on the C programming language you had all you needed to know to write software in a nice consise book. Nowadays you need to know a multitude of languages frameworks, environments, patterns, and so on in order to create useful software.

Agile development, like other iterative development methodologies, requires proper planning. This is where many developers get tripped up. Agile does not mean jump in and code. Developers must plan short complete blocks (incidentally very similar to short runs of the waterfall) of gathering requirements, designing, developing, testing, and releasing that at some point deliver a complete product with all of the requested features. Determining the features that make the cut each iteration and maintaining the overall project vision can be challenging.

If you think you are currently an agile developer (and no, just using SCRUM does not make one agile), take a hard look at your process, or lack thereof, and verify that you are not just some cowboy in disguise.

Monday, March 9, 2009

1 TB Loaded in 30 Minutes!

This is a great new white paper out of Microsoft. In it they explain how to move a lot of data from flat files into MSSQL using SSIS. Some key take aways from the paper and my personal experiences loading data into MSSQL.
  • If at all possible get your source systems to provide flat files to the ETL processes. The popular ways like web services and ODBC are nice, but they tend not to perform as well when you start moving a lot of data.
  • Run SSIS on a separate server from the final destination database server. This is key for balancing the load and being able to easily up later.
  • Test, analyze, change, repeat. While the authors did use the standard off the shelf versions of the MSSQL and SSIS, they did have to tweak them to get the results they were looking for. It is critical to analyze your solution and make adjustments.
Provide other ETL best practices below.