SQLChicken.com

SQL Server DBA Tips & Tricks

By

T-SQL Tuesday #002 A Puzzling Situation

NET h rgb 2
Image via Wikipedia

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

.NET: Yeah it's THAT badass

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.

This is what what gets done before app even opens...

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.

Reblog this post [with Zemanta]
Share

By

Goals and Themeword for 2010

Well looks like another chain-post, this time started by none other than the SQLRockstar Tom Larock (@SQLRockstar on Twitter). He poses the question “What are your goals and themeword for 2010?“. From there I actually got tagged by not only Colin Stasiuk but also Jeremiah Peschka so I guess I’d better reply!

After reading everyone’s posts I thought long and hard about what my theme word would be for this year I decided on (drum roll please):

Organization

As my wife and co-workers will gladly attest to, I battle with A.D.D. (self-diagnoses folks, no official meds/doctors were involved). As I become more involved with the SQL community and finding more things I’d like to do I find I really need to find a system that works and stick with it. Earlier this year I bought David Allen’s ‘Getting Things Done‘ book as I’ve heard great things about the methodology and how it has helped folks. Unfortunately I got a quarter of the way in and let my boss borrow it and haven’t gotten it back yet. Fortunately I follow some great folks on Twitter like Brent Ozar (@brento) who write posts about GTD such as How to Use GTD 50,000 Ft Goals which allow me to get the general idea without having to read the book (but I will dammit, I will).

Any who, I’ve already taken some steps to try and get myself organized but this year I’m going to try focusing even more. One of things I’ve done so far is download Appigo’s Todo on my iPhone as well as Notebook. I still need to organize consistent lists but so far I’ve done half-decent job of adding tasks as I get them and assign them in the few lists I have so far. What I’ve failed to do thus far and will start doing this year is weekly review of goals and tasks. Apparently this is a key part of the whole process and I’ve been slacking on it. D’oh! Well no more, I say! Another thing I’m going to try to do is little things to help myself get more organized like actually plan out my day rather than just show up to work, do morning checks and wing it the rest of the day. I need something more concrete otherwise my mind wanders all over and all hope is lost.

As far as direct goals I have one glaring goal that is most definitely my number one:

ATTEND THE PASS SUMMIT CONFERENCE!

  • Yup, that’s right it’s THAT important. I missed out this past year and while it was cool feeling like I was part of the action via Twitter it’s most definitely not the same. There are so many people in the community I’d like to meet and so much good content at that conference that I cannot let another year go by and miss out on the action. I’ll probably get shot down but I’m going to attempt to ask my boss next week if there’s a chance in hell they’ll register me and get the early bird discount of $995 (good until January 15th). Push comes to shove there’s going to be some bank robbing in my future, just sayin’.
  • Other non-felony related goals include me finishing what I started and getting the last two certifications knocked out so I can be a full-fledged MCITP:Database Administrator. My first and only certification is in 2005 so if I’m feeling froggy this year I’ll not only knock out MCITP but I’ll take the upgrade exam as well to make it a 2008 one. Also in the distant horizon I was considering doing the database developer track as well. We’ll see how that goes.
  • I’m in mid process now but I’ll add it here too: writing first book. Right now I’m currently co-authoring Pro Policy-Based Management with Ken Simmons and Colin Stasiuk. We’re all very excited about this book and hope to have it published in March/April time frame I believe. This has been a very interesting challenge so far and hopefully the next book I get to write won’t be so tough based off this learning process.
  • In the last couple of weeks myself and some coworkers have started dabbling in application development using VB.NET which has got me excited to all sorts of new opportunities. I’m hoping to learn more about application development and pick up some C# as well and start creating some truly helpful in-house applications to help support our staff at work.
  • Continue speaking/presenting/blogging. This year I’m planning on submitting presentation to PASS and see where that leads. Maybe company will pony up the cash if I’m a speaker at the conference! Honestly I just really enjoy presenting and teaching so even if I don’t get to present at a major conference I’ll continue doing rounds in Florida user groups and any other group that will have me (via LiveMeeting).
  • Compete in a major fitness event (i.e. triathlon or marathon). Seeing Tom talk about him running a marathon and seeing others go out and do similar feats I figure “why not me?”. My wife and I are also fans of the The Biggest Loser on NBC so watching them run a marathon especially after everything they’ve gone through I think I should be able to do the same. This year I downloaded the Couch to 5k app on my iPhone and I’ve stopped/started program a few times. I need to see it all the way through and keep pushing from there. Hopefully by year’s end I can say I’m a marathoner. We’ll see how that develops.
  • [Late addition] I’d like to blog more technical articles. I started this blog not really knowing how or what I’d be blogging on or about but I’d really like to add more technical content to my..err content. I’m going to aim for doing at least 4 submissions to one of the major sites like SQLServerCentral this year.

Well that’s all I can think of for now but at least this gives me something I can refer back to later and see where I’m at. Now time to tag a few folks:

Ron Dameron (Blog - Twitter)

Jonathan Gardner (BlogTwitter)

Tim & Lori Edwards (BlogHis TwitterHer Twitter)

and just for kicks lets get out of the SQL circle and have my wife try one out:

Jessica Segarra (Blog - Twitter)

Reblog this post [with Zemanta]
Share