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

SQL University: History Week Pt. 1

stockxchng-history-lesson-3-by-lusi Well this is going to be an interesting week as we have the PASS Summit going on. What’s the PASS Summit? What’s PASS? Why are the people on Twitter so excited about #sqlpass? Well by the end of the week my hope is you’ll have an understanding of where the Microsoft SQL Server (the product) came from, where its going, what PASS is and why its important to us.

So the past few weeks you’ve been working with Microsoft SQL Server. Some of you may have worked with it for years and others may be using it for the first time. So how did this fantastic relational database product come to be? Microsoft SQL Server’s humble beginnings started in 1989 with the release of SQL Server 1.0. This was Microsoft’s first entry in to the database market and the product’s codebase was based on Sybase SQL Server 3.0 code.Eventually Sybase and Microsoft went their separate ways. Starting from version SQL Server 7.0, released in 1998, the codebase was re-written from the legacy Sybase code.

In 2000 Microsoft released the first version that did away with the previous numbering scheme and was released as (and the originality award goes to…) SQL 2000. This product marked the first time the product was available with an edition aimed at the IA-64 architecture. The IA-64 version of SQL 2000 was available some time after the x86 version. It was also a little finicky, being more prone to crashes than the x86 edition (thanks to Gail Shaw for this info!). This evolution of the product also saw the introduction of SQL Server Reporting Services (SSRS) as an add-on in 2004. Reporting Services would re-emerge in SQL Server 2005 with many improvements in regards to end-user tools, self-service ad-hoc reporting and ease-of-usability. This release also gave us a built-in ETL (extract,transform,load) tool called Data Transformation Services (DTS).

For the 2005 release, SQL Server 2005 was considered a revolutionary release by many. Quite a few things were overhauled and improved upon. The Enterprise Manager was replaced by the SQL Server Management Studio (SSMS). SSMS is basically a Visual Studio shell with SQL Server components built in which made for a cohesive development/management environment for users. As well as getting a new management interface there were huge new features and enhancements to the database engine itself. SQL Server 2005 was the first SQL Server to include support for managing XML data types. Some other improvements 2005 brought were better indexing algorithms, better recovery systems, Dynamic Management Views, instant file initialization, better security (granular role/schema/object permissions), introduction of SQL CLR which allows developers to use native .NET code within SQL Server (assuming your DBA allows it, hehehe). Another huge change in this release was the replacement of DTS with a far superior and more polished ETL solution in SQL Server Integration Services (SSIS).

The most recent iteration of the product is SQL Server 2008. This version built upon the leaps from its previous release and improved upon them. These improvements came in the form of new features such as Policy-Based Management, the Performance Data Collector, data compression, resource governor (which allows dba’s to restrict resources for certain queries), transparent data encryption, data auditing, server group management in the form of the Central Management Server, the introduction of the MERGE statement, introduction of LINQ, support for geospatial data, filtered indexes, new DATE/TIME data types (until this point date/time was one column and together, this release allowed you to separate the two), BI improvements, and much more.

So what’s next? Glad you asked! The next announced release is referred to as SQL Server 2008 R2. This release offers mostly higher spec bumps (such as support for more processors, more memory, etc.) but also offers a few new feature additions as well. Some of these new features include Master Data Services, SQL Server Utility (a new way of managing databases as Data-Tier Applications), Data-Tier Application capabilities (DAC), PowerPivot (formerly known as Project Gemini) and more. Here’s a full list of all the various improvements available in the R2 release.

Note: This article was heavily drawn upon entries from Wikipedia. Click here to read the full SQL Server entry from there.

Continue on to part II

Share

By

SQL University: Basic Tools Pt. II

In Tuesday’s class we covered the different authentication methods as well as did a quick walk-through of the SQL Management Studio interface. In today’s lecture we’ll continue looking at SSMS. Some of the things we look at include how to create a query against a database, brief intro to the Profiler tool as well as a brief intro to the Database Engine Tuning Advisor.

Warning: Video is hosted by YouTube. If you cannot see it your company might be blocking that site. My apologies, I will have an alternative method available in future.

In addition to watching the video you guys have a little bit of homework. Don’t worry, it’s not hard. Just try a few of the things on this list to help you get accustomed to the environment.

  • Connect to your local database using SSMS using Windows authentication
  • Browse to the AdventureWorks Database, Open up the Tables folder and select the TOP 1000 Rows from Person.Address table. In comments below, post the AddressLine1 for AddressID 17.
  • [Extra Credit] Browse to the Security folder, Open up the Logins folder. Create a new login called testlogin. Make it SQL Server authentication, give it password of ‘P@$$w0rd’ (NOTE: Thanks to Brian Kelley for pointing this out. Do NOT use your production servers/databases for this excercise. Also NEVER use password this simple in your production environment…ever. Security will be covered in a few weeks.), make its default database AdventureWorks.

Next week we’ll be covering Backup and Restore with Argenis Fernandez (Blog | Twitter). The full syllabus for the semester will be posted shortly.

Share

By

SQL University: Basic Tools

Welcome to the first day of SQL University. Today we’re going to be talking about basic tools you’ll be using as a database administrator (DBA).

Throughout our lessons you will notice I will be linking heavily to SQL Server Books Online. Books Online is the official documentation for all things SQL Server. This is important to know as many administrators and developers refer to this documentation on a daily basis as well as in everyday conversation. You can access Books Online in one of two ways. One is directly via the website on MSDN or you can actually download Books Online (Click to Download Latest as of 9/23/09) so that you can access and refer to the documentation even when no network access is available. While downloading it for offline use can be beneficial (and portable) be aware that Microsoft does update Books Online with new information which means you would have to download and install the latest version of Books Online when this occurs. The good news is that when you use the local version of Books Online it does ask you up front if you want to use the internet as the first point of reference. Another advantage of having Books Online locally installed is that you can bookmark topics and searches so you can save time if you find yourself referring to a certain topic (which I can almost guarantee you will!). That being said make sure you explore the various links given to fully get the most out of the content delivered here at SQL U.

The most basic tool in the SQL Server toolset for an administrator or developer is a management graphical interface called the SQL Server Management Studio (SSMS). SSMS is where you can access, configure, manage and administrate your servers. The following video walks you through the basics of SSMS so you can become familiar with it. Before you watch the video there are a few things you need to know about SQL Server.

Authentication

In order to connect to a server or database you need to provide it valid credentials. This method is referred to as authentication. SQL Server recognizes two different types of authentication: Windows authentication and SQL Server Authentication. Windows authentication (sometimes also referred to as Integrated Security)  is when you provide SQL Server Windows account credentials. This can be either a Windows domain account (i.e. domainusername) or a local Windows account (i.e. local-machineusername). By default Windows authentication is the default authentication method selected when you open SSMS, and of note, is also more secure. We’ll get in to the hows and whys of that in another class. When you open SSMS, the Windows credentials for the account you are logged into the machine as will automatically pass to SSMS. For instance if I’m logged into my computer as a user called JSEGARRA, that is on a domain called MSDOMAIN, SSMS will open and you will see in the box for username (will be greyed out) MSDOMAINJSEGARRA.

The second method of authentication is SQL Server Authentication (sometimes also referred to as just SQL Authentication). This method of authentication is useful for instances that, for whatever reason, do not have access to a Windows domain account or just a domain in general. SQL accounts are created and kept within the database instance itself.  An example of a use for this type of authentication method would be a database server that resides outside of a company firewall so that the public needs to get to it. Typically these servers are kept in what’s called the DMZ (demilitarized zone), which is an area that belongs to the company but is segregated from the internal network for security reasons. Since the DMZ is outside of the normal network you wouldn’t be able to authenticate with a domain account so instead we use local credentials like a SQL account.

Best of Both Worlds

For those curious, yes you CAN have both Windows authentication and SQL authentication enabled on your database server. This mode is called Mixed mode since you’re mixing both types of authentication methods. Be aware, however, that this increases your attack surface as you’re opening more holes to access your database server. Microsoft best practices recommend using Windows authentication for security reasons (account is managed at domain level, leverage AD groups, etc.).

Video: Walkthrough of SSMS pt 1. (9:02)
Warning: Video is hosted by YouTube. If you cannot see it your company might be blocking that site. My apologies, I will have an alternative method available in future.

Click here to leave course feedback

Share

By

OPASS User Group Meeting: Review

Well last night I presented my Policy Based Management presentation for the Orlando SQL PASS User Group aka OPASS. The meeting was held at the End-to-End Training (now called SQLShare.com) offices which is a nice facility ran by Andy Warren (Blog | LinkedIn). The meeting started off with a short bit of networking where Andy has everyone introduce themselves to their neighbors and get some discussion going. I thought this was a nice little touch and lets people work on their networking skills.

First up for the night was a mini presentation on Backup Basics with Todd Holmes (LinkedIn), a DBA for Channel Intelligence in Celebration. The mini presentation is a 15-minute presentation slot that Andy came up with to encourage new speakers to cut their teeth on public speaking and technical presentations. Todd did a great job with such a broad topic and even went the extra mile in showing examples via T-SQL code. Todd will also be doing this mini presentation at the upcoming SQL Saturday #21 in Orlando.

After Todd’s presentation there was a short dinner break and I got setup for my PBM presentation. Andy said he was curious to see an hour-long presentation went on Policy Based Management since he thought it was a topic that could be covered rather quickly. Funnily enough my presentation ran just a tad over an hour and I could have kept going! There were some hiccups here and there with my VM taking a little longer than I would have liked to open certain things but demos didn’t blow up like they did at the last SQL Saturday. I also got a chance to show the audience EPMF in action (sort of). I showed the script run that used PowerShell to apply existing policies and dump results into a database repository. The example failed because I tried to open Reporting Services page on VM which had the hardened IE settings enabled that didn’t allow scripts to run so I wound up just showing a screenshot of the dashboard view. Hopefully this demo helps people take SQL 2008 and PBM back to their jobs and look like rock stars for virtually no money (except for cost of SQL 2008 Standard license after they see how awesome this is).

After the meeting I stuck around and talked shop with Andy, Jack Corbett (Blog | Twitter) and Kendal Van Dyke (Blog | Twitter) which was pretty awesome as we talked about all things SQL. Always a good time when you get quality geek time in. Overall it was a great time and a big thank you to the group for having me out there. If you’re in the Orlando area make sure to check out the group!

Share

By

Database Mirroring Freak Show – SQL Quiz from Chris Shaw

Chris Shaw ( Blog | Twitter ) started another great web chainpost. He tagged Brent Ozar, Brent tagged Thomas LaRock and Tom tagged me. This particular quiz is a two-part question and here are my responses. Excuse my lack of wit and charm. It’s late, my caffeine supply is running low and my cat is eyeballing me in a most peculiar fashion.

Do you feel that you have a reliable SAN solution? If so, what’s the secret?

It’s hard for me to answer this one given that just a few weeks ago our data center (SAN included) came crashing down hard. Now given that the problem was a faulty generator test and not the SAN itself that was the problem I can’t really place blame there. Honestly I can’t say anything really bad about our SAN. Tons of disk space, tons of cache, it does what its supposed to do. We’re also in the middle of finding a replacement for a SAN administrator so what SAN solutions we have in place now could radically change in the coming months. As far as secrets go I’d say you just need to make sure you have an open line of communication with your SAN administrator. They have no idea what’s going on in your world and you have no idea what’s going in theirs. Clear communication of needs need to be there as a SAN admin worth his salt is going to know what they need to do on their end to make sure you get the best performance on your end (i.e. proper RAID levels for your LUNs depending on needs, I/O throughput, etc.).

Explain Database Mirroring in layman’s terms

Everyone else seems to be giving off-the-wall answers to this so I’ll give it a go as well. Not going to lie, took me a good portion of my drive to Melbourne last week to finally come up with an example.

Think of clustering as a conjoined twin. You’re talking to the same body. Both heads can hear the conversation but ultimately the two are stuck together because they have to share the same base trunk. Now if you were to punch one twin in the face and knock him out you’d still be able to talk to the other head but you’re still lugging around that base. Now, imagine a set of regular twins (non-conjoined). This is your database mirroring in that you have two separate entities. You knock one out but the other is still chugging along just fine. Only difference being that the location of the second one doesn’t really matter because he doesn’t have a shared trunk to deal with. I’m sorry if that explanation sucks, if you’re bored check out the whitepaper written by someone who doesn’t have a fascination for using genetic defects to compare feature sets. Ok time for me to tag a couple of unwilling victims fellow bloggers:

Kendal Van Dyke

Jack Corbett

K. Brian Kelley

Share