SQL Server Reference Guide

I recently gave a presentation to some friends in Richmond, Virginia, where I allowed them to pick the topic of what they wanted me to speak on. Generally there are three kinds of briefings technical professionals deliver:

1. Technical Deep-Dive
2. Career Advice
3. Theory and Practices

When economic times are tough, I get asked a lot for career advice, and at most technical conferences I get asked to do a deep-dive, as you might expect. But the group asked if I would deliver a presentation on “Patterns and Practices for the Data Professional,” and as I was preparing the topic I thought it would also make a great article - and so here we are.

I think this topic is very important. As SQL Server matures in the marketplace, the technical side of the product is becoming more well-known, which is a good thing. At the same time, the deeper penetration into more mission-critical applications demands a rigorous process, which not everyone who works with SQL Server has had a chance to learn. One of these disciplines is the application of patterns and practices.

Definition of Terms

Socrates is said to have stated: “The beginning of wisdom is a definition of terms,” and I tend to agree. So the best place to begin the discussion is by defining exactly what “Patterns and Practices” are.

The best definition I’ve see for Patterns and Practices is this: “A combination of written documentation and re-usable source code used as a reference for implementation”

What that means is you should have a series of documents that describe the best way to perform a certain task in your organization, along with code frameworks or samples that everyone can use to repeatedly create and work with data systems.

The difficulty for some shops might be in deciding what to create in the first place. Should you create a document that details the best practice and process for creating a Stored Procedure that retrieves data? Perhaps. But as I’ll explain in a moment, going to that level of detail can actually make you less likely to use them, which is of course the point.

I normally start at a fairly high level, becoming more detailed as I see the need. For instance, I have a Pattern and Practice document for “coding standards,” which certainly contains best practices for Stored Procedures, as well as Functions, Loops and other constructs. For those detailed Items I use a Template stored in SQL Server Management Studio (SSMS) that has things like SET NOCOUNT ON and other code I want repeated in my systems. By combining the documentation (which is quite short) and the code “Frameworks” or Templates, I create a repeatable implementation.

Advantages of using Patterns and Practices

I’m sure most of you are like me — you’re not looking to throw some extra work into your day! So why bother taking the time to think at the high level and create re-usable code? Isn’t that just more work? Yes, it is. But it’s work that hopefully pays off in other ways. If not, don’t do it!

The primary advantage I’ve seen for creating and using Patterns and Practices is that I get less errors in my code. I create the first code framework (just a .SQL file, really) that encompasses the best way to perform a task. The document explains and points to that/those code snippet(s) and I test both carefully. If I’ve done my homework, then each time I use that framework I’ll get the same result.

But there are other advantages. By entering a lot of the code ahead of time, I end up saving time when I go to create a new set of code or database objects. Having a document and a template helps others learn from what I’ve learned, and offloads tasks from me to folks that might not have my background. Because I involve others in the process, I also end up with things they’ve created, and I learn from them.

Using a standardized Pattern and Practice enables easier support. Since we’re all doing things the same way, and we understand why, we all know how things are working. If someone new joins the team, they can evaluate our Patterns and Practices and come up to speed quickly, making the on-boarding process faster. All of these advantages help not only the IT shop, but make the whole organization more efficient.

Disadvantages of using Patterns and Practices

It can’t all be ponies and rainbows, though. If so, we would all be creating and using Patterns and Practices for everything from Stored Procedures to how to make the coffee in the morning. There are some drawbacks — Patterns and Practices can fail, and become a real problem. Here’s how.

The easiest thing to do wrong with a Pattern and Practice is to try and make one for everything. You’ll add tons of work to your process, no one will have time to read them or use them, and it will just be another frustration in your day. Start small, with an area that only a few people know how to do, and that you do fairly regularly.

Another disadvantage is when the Pattern or Practice isn’t kept up to date. Nothing is more frustrating than taking the time to do something “by the book” only to find the “book” is incorrect.

Making the Patterns and Practices difficult to locate and implement is another way to make them hard to use, and ultimately a disadvantage. Once again, you’ll do a lot of work that no one uses, and that’s never fun. I’ll talk more about this in a moment.

Process for Creating a Patterns and Practices Environment

The first thing to do is to evaluate the areas within your IT functions. You may have developers, administrators and so on. Then look for things that are done over and over, that only a few people know how to do.

There are lots of formats for the Patterns and Practices document. I have a simple, five-point version I use that works well for me, but you can research others and use what works best for you.

I create a title, and four sections:

Title: This is where the most “searchable” way of describing the issue goes. Again, most of the time the area is fairly broad, so rather than saying: “Creating a Windows Cluster” I create one that says “Creating Highly-Available Systems” which includes Clustering, Mirroring and when to use each.

Situation: In this section I give a quick description of the problem I’m trying to solve. “The organization requests that IT give them a system with an RPO of 30 transactions and a 2 hour RTO.”

Practice: Here I detail the steps to take. It’s that simple. Again, it shouldn’t be so specific that it requires constant maintenance. If there’s documentation somewhere else on how to build a cluster, for instance, point to that (in the References section) and only include text here of where your system differs from those best practices. I even include checklists here.

Pattern: This section contains code (T-SQL, PowerShell scripts, c#, etc.) in a framework format so I can re-use it. I also put “validation” code here, such as a script that runs through a server and checks to ensure the right service packs are installed and so on.

I normally point to these scripts — they aren’t stored directly in the document. I try and put things “closest to the work,” so there’s a higher likelihood that they will get used and maintained.

References: In this section I add links to other documentation. That might be another Pattern or Practice, a checklist, best practice on the web or vendor documentation. I try to ensure I have enough in my documentation to make sure whoever is using it can follow through, but the references are there for others to “check my work” or to detail steps that are standard for a vendor.

Tools for Creating Patterns and Practices

There are lots of tools you can use for creating, storing and accessing your Patterns and Practices, but in fact it doesn’t matter which one you use. The key is to use the tools that everyone understands and can access. Those are the only real criteria.

But you do have lots of options. Here are a few that I use.

The model System Database - to begin, I change the settings on the model System Database in each Instance of SQL Server to have the parameters I want (no Auto-Shrink, that sort of thing) so that not only do I follow a standard - I don’t even have to implement it. Whenever someone types “CREATE DATABASE” I just get the result I want automatically.

Network Share — That’s right — one of the simplest places to put your documentation is to use a Word Processor and store it in a well-known share on the network. Easy-peasy.

SharePoint or other portal — Portal software, such as SharePoint, allow you not only to store the documentation, but secure it, deliver it, search it and so on. This is the current method I use.

Templates — I’ve written a complete article on Templates here, which I urge you to check out. SQL Server Management Studio (SSMS) has a great feature that allows you to store the “code framework” you want, and re-use that code easily, over and over, right inside the tool you write code with. Highly recommended.

Default SQL File — I’ve got a blog post here that explains how to use a simple text editor to change the blank SQL file that opens in SSMS when you open a new query. By placing your default code there, you have something similar to the model database, except for queries.

Database — I’ve also created a database to store my Patterns and Practices in. This works, but takes more training for a beginning DBA to learn to use.

Source Control — When you work with your developers, you may find they already have a full process for this. Many times they use their Source Control system to store the code snippets and documentation, and that’s a good place to store yours as well.

So here’s my challenge to you: find one area that is the most chaotic, and repeated incorrectly, in your shop. Create a single document and some code and see if it makes the day better. Then go after the next problem area and so on.

InformIT Articles and Sample Chapters

Lee Ackerman and Celso Gonzalez offer some great advice on a practical way to implement practices and patterns in their article The Pattern Challenge: How to Successfully Deliver Software Solutions via Patterns.

Books and eBooks

Agile is software method that makes use of patterns. Agile Principles, Patterns, and Practices in C# is a book you can read about C# patterns.

Online Resources

I have a list of links that you might find useful on this topic here.