Fotia Ltd

Fotia Ltd

Tuesday, 27 December 2005

Delivering on SQL Server

posted by Stefan Delmarco

Having been at the sharp end of many successful SQL Server solutions, I believe there are some fundamental principles that all successful SQL Server installations have in common. Whilst the challenges in delivering an enterprise-scale database-intensive application can be wide and varied, there are a few basic practises that will give you the best possible chance of success.

Play to the Strengths of the Platform

Yes, SQL Server is an RDBMS (Relation Database Management System). Yes, there are many things that all RDBMSs (Oracle, DB2, etc.) share in common. They all have tables, they all have indexes, and they all largely implement the same SQL language functionality. However, each vendor's implementation will have its own strengths and weaknesses. The key to success with your chosen RDBMS is to understand what exactly these strengths and weaknesses are.

SQL Server can either be that incredibly powerful, reliable platform on which your mission-critical applications can depend, or it can be that RDBMS your developers and DBA are always fighting to keep their applications just about running. These are both running the one-and-the same SQL Server product. The difference is that the first is being used in a manner that SQL Server was designed for. The developers understand the features available in SQL Server / TSQL and exploit them at every opportunity. They can read and interpret a query plan and know their way around Query Analyzer. In the second instance the developers are database-agnostic. They write their queries without regard for scalability, use a procedural approach for their TSQL instead of set-based, do not have SQL Server installed on their local developer workstations and have never opened up SQL Server's online help (BOL - Books Online Download / MSDN).

Just because a developer has prior experience in Access or Oracle doesn't mean that the experiences gained working with those RDBMSs translate directly to SQL Server. In fact I once had a developer ask me why we "don't just use NTEXT columns wherever we use NVARCHAR, you know, just like Memo in Access?". It is the responsibility of any developer or DBA working with SQL Server to understand how SQL Server works and what it does well, in detail.

If you don't know the difference between a clustered and non-clustered index then you need to pull up BOL and make sure you find out. Did you know that most of SQL Server's internals are actually included in the online documentation? In my opinion, BOL is probably more thorough, better written and has more extensive examples than most of the SQL Server books sold on Amazon!

As an example what it means to play to the strength of the platform let me tell you about a customer that implemented a complicated equity management system entirely in DTS! When all the data was in the right place it ran like a dream. However, when there was a problem it was the worst application in the world to try and debug. I'm pretty sure that the designers of DTS never intended it to be used in this manner (opening up one of these DTS packages was like looking at a map of the London Underground!). In this instance the customer was definitely not playing to the strengths of the platform and ended up experiencing a significant measure of pain before being forced to learn this lesson.

Embrace Microsoft Integration

I'm not talking about open-standards or interoperability here. I'm talking about not introducing plumbing complexity within an application. We all know how hard it is to connect disparate systems together, disparate sub-systems or components are orders of magnitude more troublesome as they often struggle to map cleanly into your application. Keep that kind of complexity out of your system.

Look at it this way, there is no other vendor that can give you all of the end-to-end development tools you need to build a successful solution. How much of it is ready-made (e.g. BizTalk Server) and how much of it you need to build from scratch obviously depends on the specifics of what you're delivering. This is the beauty of delivering on the Microsoft platform. Whether you're writing device drivers, an order processing system or a screen saver, the tools are the same, the documentation is the same, the support website is the same.

Don't let your design or code be constrained by trying to keep your SQL vendor agnostic. Unless you have a very specific requirement to deliver a vendor-independent database design, don't choose ANSI-compliance above TSQL-specific features. Even then, recognise and acknowledge that you will be sacrificing performance to achieve vendor independence. You may even be unable to deliver scalability in conjunction with such a restrictive requirement.

Don't build a patchwork system unless you are prepared to allocate the time and budget to play Luigi-the-plumber. Keeping it homogenous will put you in a position where you can concentrate on the design of the solution rather than having to worry if all the plumbing fixtures are going to play nicely together. Remember, any weakness in any piece of the complex jigsaw that makes up an enterprise solution determines what the lowest common-denominator is that the solution must be built on.

At worst you'll have a single vendor to deal with to sort out your problems. As an ex-support engineer it was vital to be able to reproduce a customer's problem in order to arrive at a solution. Cases that used Microsoft technologies were much easier to progress and solve than those that required inter-vendor cooperation.

At best you'll be free to concentrate on delivering business value knowing that connectedness is taken care of.

If you are building an enterprise-scale 3-tier system, keep it Microsoft. I have delivered enough successful enterprise-scale system to be able to say that with confidence!

It All Depends...

This is the answer I hate to give to some of the SQL Server design questions I am asked. Unfortunately it is the truest. There are very few hard and fast rules when it comes to which database design is appropriate for your application. Questions like "Do I need a clustered index?" or "What is the fastest way to load data into my database?" can never get a simple answer.

So why does it always have to depend? Different features of SQL Server scale differently depending on how they're being used. For example, bulk loading data into SQL Server will always be fastest initially when the table has no indexes, till it gets to a certain size. Then factors like the width of a row, the number of PFSs (Page Free Space) pages spanned by the table start to become a factor. Even then, there are no hard-and-fast rules when these other factors come into play.

So how do go about making a decision here? The simple answer is to try it out. All of the projects I have worked on that delivered successfully spent a significant time upfront, and sometimes during delivery, trying out various permutations of the design under simulated conditions to determine the feasibility, complexity and scalability of various options. That is the only way to validate the design and progress with confidence.

Keeping these types of test rigs around after a decision has been made is always useful for when service packs or hotfixes need to be tested. In addition, you never know when you may get that "Aha!" moment and want to try out something new you've just thought of!

Develop with Production-Level Data Load

Developers like to write the code only once, so you might as well make sure they develop it using a development database that contains the same data volume as is expected in production. Often this can be costly to the business as SAN disk space can get pricey. However, IDE SATA disks are now so cheap that you can pick up a 250 GB HDD for about £70. There really is no excuse...

If you aren't able to develop with a representative data load you'll catch your performance problems either during testing (you are doing stress/soak testing right...?), or worse, in the production environment. By that stage the cost of the changes required to the application may be prohibitive either in terms of dollars or risk. This is where the sticky tape and plasters start to make their first appearance.

Keep Your War Chest Current and Well Stocked

Know your tools and know them well! I had the pleasure of working with a Microsoft developer support engineer that had more than 14 years of dealing with the most critical development issues under his belt. Whenever I discussed a problem with him he always came up with some command or technique in the Windows debugger (WinDbg) to help further the analysis of the problem. He never said "The problem is caused by XYZ". It was always "Give this a go and see where it takes you".

This engineer had the most extensive bag of tricks I have ever seen! He had an incredible knowledge and understanding of diagnostic tools and techniques. The net result was that in all his years he never came across a support case / issue that he could not solve. Sure, some of the cases ran for weeks but he never ran out of options or techniques to progress the issue. In addition, he never allowed himself to be rushed. He took his time to perform the proper analysis of the output from his investigation and then decide on the next step.

I have a directory in My Documents called Toolbox. In there I have every tool I have ever found useful. Here are my top 5:

  • Windows Debugger / WinDbg (nothing can hide from the debugger!)
  • PsTools (pslist \\RemoteServer -s, some Anti-Viruses don't like psexec)
  • Process Explorer (best feature is being able to drill down into process / thread performance counters and take a quick peek at the stack)
  • TCPView (easiest network monitoring tool around)
  • Process Monitor (your best friend when debugging COM)

Having mastery over your tools not only makes you a more effective trouble shooter, it also serves as a learning experience that, in turn, allows you to play to the strengths of the platform.

Hopefully this article has given an idea of what we feel are important practices in the successful delivery of a SQL Server solution.

Good luck!


Previous Posts