Bus Ale Updated - http://www.lifeasbob.com/HomeBrew/busale.aspx 

Recently updated one of my favorite session beers, all grain recipe.

Jeez, I hope this doesn't become a trilogy or worse have to go the prequel!

Original Post:  Pied Piper of Grandview.

This weekend was again a good one, hopefully now that the lawn mowers are in full action I won't be seeing any more friends. 

Missouri Corn Snake, found this weekend crawling along the fence line, pictured are my neighbor Jimmy and his kids.  By far this was the biggest, most colorful and exciting one, very calm as well, never tried to coil and snap or snip, though it was quite quick and required two of us to catch.  I had to enlist Jimmy to hold the snake for pictures, as I don't mind grabbing them, but once I get them in the bucket, i don't mess with them again.  This one was about 3-4' long, I almost suspect this one to be someone's pet that they let loose, but it is now in the Horkay serpent relocation program.  The last picture is of the garden variety, curled up in a bucket(number 15 for the year).

Windows has historically had a reliability issue in the enterprise (remember daily reboots?), SQL Server received this tag right along with it.  Over many years starting with the release of SQL Server 2000 Microsoft has been working to correct this issue, now we need to start working on our vendors.

Yesterday 5 Windows servers, crash in one day, of course all of them running SQL Servers.  I've got 146 SQL Servers and go months with no issues, than BAM!  These were all different "flavors" of SQL (2K, 2K5 and different service packs).  Most likely not a SQL issue or even a windows issue, but regardless the product takes a hit and so do I.

The machines were so "unresponsive" that the only way to correct was to kill the power to the machines.  Of course no logs and no indication of what the problem was.  I highly suspect one of the many 3rd party crap that we run on these windoze machines:  backup, anti-virus, san drivers, monitoring programs etc; though we'll never know for sure what was to blame.

Even our Microsoft Clustering did not save me!

No fail-over, no nothing, had to reboot both nodes of the cluster; plenty of room still for the 3rd party clustering utilities as they also have failures as well, but I seem to have higher up time with them...try Never Fail, Golden Gate, HP Polyserve or Veritas.

We still have a long way to go to increase stability, we've got to continue now to harp on our 3rd party vendors that provide the services not built into windoze, just wish we knew which one to call for this outage.

The Fleet of vehicles has expanded with a 2006 Chevy Express Van, 1500.

What I needed was a reliable, large people mover.

I stumbled across this vehicle for sale at ICOP, a Lenexa Kansas based company (i'm always amazed at the companies I find head-quartered around Kansas City).  This is an All Wheel Drive Van, with dual doors, tow package, Rear Air and heat, 8900 miles.   Drives like large luxury car, excellent, Wife is happy and I may be able to preserve my sanity on long trips.  It's got the basic interior of Cloth and bench seats, but with young kids - that is what is needed.  For long trips we'll be adding the DVD. 

So I'm enjoying a cup of tea and decide, wouldn't it be nice to upgrade 1000's of instances of MSDE sp3a to sp4?  Nice, i love unsupported products, let's do it !

Ugh, I know, but somethings aren't up to me, I deliver the results, this was the task, it's in the skill set, get it done.  I ran into several issues where I had to upgrade a MSDE instance from SP3a to SP4.  This should be a simple exercise, and it was for many installations, but there is a consistent theme where the upgrade fails and no manner of hitting, kicking or screaming helped. 

Uninstalling the original installation and installing the SP4 version of MSDE was successful, but then the master database, logins, msdb all has to be restored to get the instance back to a working state.

I'd be fine with the uninstall and reinstall, but we're talking 1000's of installations, how many are going to have a problem ?  Surely the solution can't be to uninstall and reinstall.

I found some good documentation on troubleshooting sp3 to sp4 for MSDE (remember it's been around awhile and is out of support).  The best link I found was http://support.microsoft.com/kb/901163, unfortunately it didn't resolve the problem.

Consistently the error that keeps happening is UpgDetectBlankSaLogin Return value 3.
---------Actual Log File-----------
Starting custom action UpgDetectBlankSaLogin()
Entering Function MyMsiEvaluateCondition
MsiEvaluateCondition for VersionNT returned TRUE
End Function MyMsiEvaluateCondition
An error was encountered during connection to server: 3
Ending custom action UpgDetectBlankSaLogin().
Action ended 16:20:07: UpgDetectBlankSaLogin.2D02443E_7002_4C0B_ABC9_EAB2C064397B. Return value 3.
Action ended 16:20:07: INSTALL. Return value 3.
----------END Actual Log file----------

The sa password is definitely not blank, no matter how much the sa password was changed or it's complexity it always failed on this.

Creativity is needed to get around this, enter the windows software development kit (SDK) and Orca.  Orca is an MSI Editor, and is found in the Windows SDK.  The SP4 Service pack contains the MSI Files, and I decided to remove the step that runs the UpgDetectBlankSaLogin.    Simply find the step in the MSI File (as shown below) and Delete (drop) the row associated with UpgDetectBlankSaLogin.  Save and use it !

Now this is a calculated risk and requires testing.  But it works, and on everyone of these installations that was failing the now modified SP4 runs fine.

This MSDE task took 3 days of my life away from me, hopefully you'll be more fortunate!

MSDE is on life support, and so is this solution, but if you can't solve the problem, go around the problem.

We're still not ready to support sql 2008 (Company Infrastructure, vendors and DBA Group are a little slow), maybe this June or July we'll do the first sql 2008 implementation).

For me, most SQL Server migrations have been easy.  No mess, no fuss, hit the "easy button".  We even do the migrations the "hard" way...new servers, named instance, usually to a new clustered or consolidated environment using Polyserve or VM, and we separate all DTS (ssis) and User jobs out to an application (ETL) server.  As a DBA with 100's of servers it's a lot easier to let each application group manage their own jobs, off the sql server, than for us to handle the requests.  Usually the most difficult issue is connectivity, firewall and connection strings.  Of 170 instances we support very rarely is there a difficult issue, we've dealt with some odd performance issues here and there, but for the most part it's been smooth sailing (hah, I remember being up for several days straight monitoring a system with 5K tps and watching it crash every day, but that's the extreme end of the bell curve).

Finally we couldn't put it off any more, and it was time to tackle the elephant in the room. 

It's not a big Instance (5 databases, 60gb), but the challenges are big.  We're dealing with 88 DTS Packages and 45 sql agent jobs, Connectivity to multiple Oracle databases, AS400 and other SQL Servers, 60+ downstream consumers of data.  Excel Flat Files, Text Files, Web services, 3rd party vendor imports and exports.  Let the games begin.

I knew I was screwed when I de-attached the databases from SQL 2000 and attached them to sql 2005 and got the following errors:  "Attach database failed for Server x.  An exception occurred while executing a T-SQL Statement or batch.  Converting database x from version 539 to 611.  Microsoft SQL Server, Error 195. fn_convertdatetoint_notime is not a recognized function name.  Incorrect syntax near the keyword 'left'".  Ultimately we determined that this issues was caused by user functions in SQL 2000 setup in the master database, NOT Supported in sql 2005.  We also ran into isolated issues where there were tsql syntax errors in sql 2000 that did not throw any errors, but would not compile in 2005!

Linked Server Issues were also encountered.
   Setting up a linked server to the AS400 on 64 Bit windows
      Required a new version of the IBM iSeries driver
      Required a patch to the IBM iseries driver
      Required patching windows to install 64 bit odbc drivers
   Setting up a Linked server to SQL 2000 SP4
      Required patching sql 2000, http://support.microsoft.com/kb/906954
   Setting up a linked server to Oracle

Over-all the process took about a month (with 4 DBA's and other work), bulk of the work was the DTS to SSIS packages.  We did look at some 3rd party utilities, but they still left several hours work for each package, so we just did the brute force method and did them one at a time.  Currently the process will spend 2-3 months running in parallel and being QA'd, than onto production.

Hit the "next" button.

Remember this means buying it and paying maintenance.  SSIS is a great tool, even if you did have to buy it, but because it's free and bundled with SQL Server 2005/8, we rarely look at other ETL Tools with an open mind.  Recently my list of SSIS Pet Peeves and the Toughest SQL Server 2005 Migration ever, caused me to wonder, "Would I use SSIS if it wasn't free and bundled with SQL Server?"

I know the company i work did a RFP for ETL Tools, but I was never privy to the responses or even allowed to participate in the process ( why include DBA's in the search for an enterprise ETL Tool, that'd be foolish!).  Nothing ever came from this, so that leads me to think that SSIS must still be the best value out there, probably because it's free.  I sure would have liked to seen the responses, especially on cost.

I've found a nice web page on ETL Tools, though they want you to purchase their comparison, but if nothing else you can see a nice list of ETL Vendors:

http://www.etltool.com/etltoolslist.htm

No. List of ETL Tools Version   ETL Vendors
1. Oracle Warehouse Builder (OWB) 11gR1 Oracle  
2. Data Integrator & Services  XI 3.0 Business Objects, SAP
3. IBM Information Server (Ascential) 8.0.1 IBM
4. SAS Data Integration Studio 4.2 SAS Institute 
5. PowerCenter 8.5.1 Informatica  
6. Elixir Repertoire 7.2.2 Elixir 
7. Data Migrator 7.6 Information Builders
8. Integration Services (SSIS) 10 Microsoft  
9. Talend Open Studio 1.1 Talend
10. DataFlow Manager 6 Group 1 Software (Sagent)
11. Data Integrator 8.12 Pervasive
12. Transformation Server 5.4 IBM DataMirror
13. Transformation Manager  5.2.2 ETL Solutions Ltd.
14. Data Manager/Decision Stream 8.2 IBM Cognos
15. DT/Studio 3.1 Embarcadero Technologies
16. ETL4ALL 4.2 IKAN
17. DB2 Warehouse Edition 9.1 IBM
18. Pentaho Data Integration  3.0 Pentaho  
19. Adeptia Integration Server 4.9 Adeptia
20. Clover ETL 2.5.2 Javlin  

Interesting Blog post on evaluating some ETL Tools:
http://it.toolbox.com/blogs/evaluating-software/evaluate-etl-tools-4169

If anyone has any experiences with other ETL Tools and how they compare with SSIS, i'd love to have you comment on this, letting me know why your using a different tool.  After working with SSIS now on several large projects I'm now very comfortable with SSIS and understand it's quirks, depending on it's "cost" compared to other products, I'd use it, even if it wasn't free.  I hope Microsoft doesn't read this and decide they want to start charging for ssis (and for sure there are already some features of SSIS that aren't available under certain editions).

One of the greatest strengths of SSIS is how quickly we (DBA, User, Developer, BA, QA Etc) can get at the data either for query, analysis, import or export...SSIS and it's predecessor DTS were one of SQL Server's greatest strengths in this area.  I work in a shop with other DBMS's and consistently SQL Server is more flexible in this area, because of SSIS/DTS.  I do think they could have made SSIS Better than it is, the learning curve compared to DTS is steep and the strong data typing of the meta data can be difficult at best.

Recently I finished my first deep dive into using SQL Server Integration Services (SSIS) on a project.  This was the conversion of 88 fairly complex DTS Packages from SQL 2000 to 2005.  I've used SSIS numerous times in the past, but I still considered myself at the "beginner" level, I'd say i'm almost "Intermediate" now that I've completed this project. 

All of our SSIS Packages for this project were implemented on a separate layer from the dbms, ultimately allowing application administrators the ability to control the jobs (via windows task scheduler).   This is one of the greatest benefits of SSIS is the ability to separate them from SQL Server.  The three cons to this are loss of SQL Agent for scheduling,  SQL Licensing and Performance of some tasks (can't use SQL Destination).   The pro's out weigh these con's, and in the very few instances where a con can't be overcome we do allow the ssis package to run on the same layer (usually large loads from Oracle to SQL where the SQL Destination object must be used for performance). 

It is definitely nice to allow the administrator unfettered access to the box and they are completely responsible for developing, deploying, configuring and scheduling their own SSIS Packages, not the SQL DBA Team.  We have 4 SQL Server DBA's, 170 Instances; there are a lot more App/Dev labor hours than their our SQL DBA labor hours.  The App/Dev team loves it too, it takes them a bit to get used to it, but everyone of them loves it.  Make sure you have good SLA's as DBA's still need to understand when large loads and complex ETL's will run against a database.

During the process I compiled a list of "pet peeves" for SSIS, here they are.  Hopefully as we adopt SQL 2008 SSIS, i'll be able to cross some of these off.

• Excel (the list of issues seems endless)
• Columns longer than 500 Characters
• Strong data typing (Data conversion on 50+ columns is brutal!)
• Mapping columns after data conversion, ugh !
• Using variables for dynamic file names is not intuitive
• This is timestamps in filenames etc, once learned it's not difficult, but for others viewing the package the first time it can be convoluted to determine how the variables are used to manipulate the name of the file source
• Using wild cards for FTP (either put or get) is not intuitive
• Converting some previous DTS Tasks would perform a put (mput) of {timestamp}.*, this ended up either remaining a batch file in ssis or creating a for loop that actually performed individual puts (really not smart to login 10 times for 10 puts).
• Mapping columns in data flow tasks
• If the names don't match there is no option to just do a 1:1 in order match, frustrating and tedious when your dealing with 50 or a 100 columns to perform manually
• Configuration Files
• A must to implement, but just seems to have a high learning curve
• Restart of a failed packages (checkpoints)
• Just didn't work the way we expected, but once learned was quite successfully
• Oracle Integration
• The pain here was and is endless, 3rd party tool is really necessary if your dealing with lots of integration
• Default Properties for Fail Parent and Fail Package
• For us it seemed we always wanted this to true and the default was false, there seemed to be no way to globally change this
• Precedence Constraint Editor does not contain an expression builder
• Oh how I missed the 3 ellipses
• Executing another SSIS Package from an existing SSIS Package
• We have several "shared" packages, but we weren't able to use the execute package task due to limitations on changing the properties, so we ended up using the Execute process task, which calls a batch file with the proper configuration file...ultimately the execute package task was useless
• Readability of Configuration files makes editing difficult
• We end up using XML Notepad on most servers, it'd be nice to see the property name as part of the node or an element so one could visually scan down the side and find things quicker.

Recently had to perform an update to some VBS Scripts I wrote in the past to archive and delete logs.  (Previous post: http://www.lifeasbob.com/2008/02/26/SQLServerLogManagement.aspx)  The scripts I've used in the past automatically move all files in a specific directory to an archive directory, and then delete the archived directories that are older than 30 days.  This was nice, but most of the directory names and the number of days were hard coded in the script, in addition it did not use wild cards (*.txt) to move the files, so it was all files or nothing.

I updated the VBS Scripts to take wild cards and directories (both source and target) as parameters.   The annoying thing is I implement these jobs via windows task scheduler (no sql agent on the box), and in order to call the vb scripts with parameters I ended up using batch files as I had multiple iterations of different wildcards and directories, guess we haven't come very far after all!

Anyway you may find this stuff useful for things where someone creates a process which generates lots and lots of files of any type.  In my case these were about 10 files per day, exported to excel, and we needed to archive them to different sub-directories based on the wild card.  What you end up with looks like this in Task Scheduler and File exploder...er, i mean explorer...

I've uploaded the examples of the vbs and bat files below, all have been renamed to have a .txt extension, so rename them after download.

Archive_Daily_logs_bat.txt (.6 KB) 
Archive_Daily_Logs_vbs.txt (4.95 KB)
Archive_Delete_Dirs_bat.txt (.36 KB)
Archive_Delete_Dirs_vbs.txt (.75 KB)

For the powershell Evangelist's, yes this can all be done in powershell, but since I only have powershell installed on 1 of 170 servers, it will have to wait.  Eventually i'm sure we will push powershell to 170 servers.  Soon i'll be creating a new powershell category and begin uploading and converting all these.