T-SQL Tuesday #002 A Puzzling Situation

Well this is the second T-SQL Tuesday (my first as I missed the first one). T-SQL Tuesday is a great idea put forth by Adam Machanic (@AdamMachanic on Twitter). Here’s the summary of this month’s post challenge:

Theme

Have you ever found yourself unable to figure out the intricacies of how some piece of code works? Ever been confused by the results you’ve gotten back from a query, only to find out that something totally unrelated was going on? Or have you ever been compelled to wile away your spare time working on a “challenge” posted by some blogger?

For this month’s T-SQL Tuesday, I’m asking participants to write a blog post on a “puzzling” topic, along the lines of some of the following ideas:

• Describe a confusing situation you encountered, and explain how you debugged the problem and what the resolution was
• Show a piece of code that doesn’t behave as most people might expect, and illustrate the reasoning behind the discrepancy
• Create a challenge for your readers to solve

As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!

That being said I’d like to share a little project I’ve been working on with some co-workers here at work. If you follow me on Twitter you’ll probably have seen some of this discussion and I’ve mentioned one of the other guys I’m working with, Matt Schultz (@mmzplanet on Twitter) also talking about this project. The other piece of our “development group” is another network analyst named Horace Knight. This story doesn’t contain any special programming tricks and I can’t post specific code at this time but this particular project has been an absolute pleasure to work on and work with these guys coming up with a from-scratch solution that works.

Preface

Recently here at the hospital we have put in phase I for a new GE system for our electronic medical records system. The thing about this particular system is that the application requires an XML file to be located on the desktop. This XML file contains some pertinent information for the application to work such as pathway (production/test/dev), terminal ID (an ID needed for client to be able to connect) as well as some other GE-centric information. The key thing here is that terminal ID, which allows a workstation to authenticate to the tandem system that contains our medical information. Well the way this works is that GE provides us with a pool of terminal IDs to divvy up amongst our clients and we’re supposed to manage them however we choose so long as those clients have that XML file. Now had this only been for 20-30 PC’s this might not be a HUGE deal but we’re talking about 2,000+ machines that are going to need to access this system, have their terminal IDs properly assigned to their proper locations (multiple campuses at play here as well) and be properly managed (i.e. no duplicates, correct locations, proper allotment per location, etc.). What puts the pressure on here is that the these locations REALLY need to be correct since the locations dictate where forms are printed to, forms that contain patient registration information.

Soooo…How Do We Do This?

So despite having GE tell us these things I’ve covered, the whole process was still a little confusing (took months of back and forth to finally understand and hammer out what I’ve just explained in a paragraph!). Horace, being the awesome human being he is, stepped up to the plate and took the responsibility of coming up with a solution to handle the terminal ID assignments. Horace worked hard with the folks at GE as well as hospital employees to get a high-level grasp of what was needed and expected out of this solution (such as locations, printers, workstation identifiers, etc.). We also got a few curve balls thrown at us in that not only regular workstations would be running this application but we would also need this solution to work on kiosk machines (locked down workstations being logged in with dedicated accounts) as well as workstations on our regular domain. As time went on, and our deadline loomed closer, Horace’s job became increasingly difficult because every time he thought he had it figured out something else would change and it was back to the drawing board. He had a vision of an application that would eventually manage everything but he had no programming experience and he knew this solution would require a database as well so this is where I came in. He and I started meeting frequently and fleshed out a general workflow as well as a database structure that would handle the various information we needed to do this. On the client side Horace was developing a script in VBscript that handled the various things we needed such as writing the needed XML file out with the custom terminal ID that would be assigned from a table in the database. Due to the way we had architected it at that point we were also creating another XML which contained location information for that PC. That location XML would not only allow the script to identify where the workstation was but it would let any PC technicians know where the machine should be in case of issues. This script would be delivered to workstations via kixstart script kicked off upon logging into the workstation. As some of you have probably figured out there’s lots of holes in this solution but bear with us, we know not what we do! Well our deadline date came, the script and database did their thing, and for the most part it worked as it was supposed to. Like any system there were a few flaws and things were learned on rollout day that we hadn’t anticipated or known and these were handled manually.

Enter the (.NET) Dragon

Once our go-live date came and went things finally calmed down a bit in our department for the first time in months. Now that the system was up and running it was time to go back and figure out how to clean things up. Matt came into the project when he volunteered his services by offering to build a GUI that would prompt the user at login for a location and that would then write the location to the XML file as needed. Now, this was the initial thought pattern but as Matt learned more about .NET (sorry pundits, he went with VB.NET as he had previous experience with VB syntax so VB.NET was easier/faster to pick up) and working with me more on revamping queries to the database and converting poor, simple queries into re-usable and secured stored procedures. During this time Matt took it upon himself to not only put a GUI over the script but improve the functionality and then some! I’m hoping that Matt puts up a post about this project as the current iteration really is culmination of Horace’s original vision for this application along with a lot of additional ingenuity and hard work from Matt and myself.

Evolution

The current version of the script now works like this: The application will be delivered via an as-yet-determined means. When the application first starts the user is prompted with a GUI that allows you to choose a campus as well as location (this process is to be done by PC techs on new PCs so location/campus will be correct before gets to users). If the PC already has a location XML (which the current ones in production have) the application reads the current location information and checks the database for an existing record for the PC and makes sure location matches up. Since the PC is already providing the location information the GUI won’t even show up since it has the information it needs. Now the application is running in the notification area of the taskbar. If you access the application it shows you a GUI read-only interface that shows you some pertinent information including PC name, location assignment, and assigned terminal ID per pathway (each pathway access requires individual terminal ID). Matt broke it up to show not only what value was in the database but what value was in the local XML document so that you could see at a glance if values were set properly or needed updating. In order to manage and change values there is a button you click to access the management interface of the application. This area is secured via login prompt and Active Directory groups. Only members of a certain AD group are allowed access to this portion of the application to make changes. Once this secured area is accessed you can make changes such as location and adding/removing pathway accesses. The pathway add/drop processes are handled by stored procedures which are programmed to not only add and modify records  but it can dynamically pull additional terminal IDs if none are available for a given area (this was a concern as when we initially rolled out and told there were only 20 workstations in Area X and we come to find 5 more needed IDs assigned to that area it was a manual pain to add those 5 terminal IDs to the section’s pool). Another nice thing about having the database handle everything via stored procedures is that we managed to eliminate the need for that second location XML file on the workstation since all data needed was now stored in the database. Another addition from Matt’s hockey-filled brain was the use of a global settings table. This table would hold information that would be common to all clients such as registry path of the application, location of certain file that denoted whether the application was installed and client version level.

Now The Part You DBA Geeks Are Waiting For…

So besides all of the tables and stored procedures that are underneath this application, this project gave me the chance to try my hand at something else I haven’t had a chance to play with before: mirroring. Thanks to my fellow SQL tweeps and some quality time on Books Online I figured out how to modify our connection string to recognize a mirrored instance. It took some trial and error but I finally got my test database mirrored with a witness server so that I had a High Safety mode with automatic failover setup. Moment of truth came when we tested the application by opening it up, manually failing database over to its partner, then continuing to use the application. As expected there was a slight delay while the database failed over but the application did recognize the failover and continued working! NASA-style Apollo mission high-fives handed all around!

So now we’re at the point where this week is the final week of feature additions and testing and we plan to roll out to production in the coming weeks. We are very proud about how this whole thing has turned out especially considering Matt had never touched .NET as of 3 weeks ago and yet has managed to produce a very slick, full-featured application in this time. This project has really kickstarted my desire to learn more on the development side of the house hence those of you who follow me on Twitter have noticed me asking more and more about .NET, C# and all of those fun things you guys do.