SQL Server Reference Guide

Don't worry — this tutorial won't be a screen-by-screen capture of a SQL Server installation. While there's nothing wrong with that, I don't think it would be particularly useful. This tutorial also won't try and re-cover the over 1200 printed pages of guidance you get from Microsoft in Books Online, which you can find here for SQL Server 2008, here for SQL Server 2005 and here for SQL Server 2000.

So what will I cover in this tutorial? Although Microsoft has done a fantastic job of including all the information you could possibly want to know about installing SQL Server, I want to cover a few of the other parts of information that you might need both before and after the installation.

In Books Online (which ironically you have to install before you can read the installation docs), Microsoft doesn't often delineate the information you need based on the size and use of your system. Don't get me wrong — everything you need is there, just not always in the order you need it.

So what I'll try to do is break down the process in a different way. I'll actually use the same three basic steps that Books Online does:

1. Preparing
2. Installing
3. Configuring

But I'm going to recommend a broader approach than just looking at the server. I'm going to start a little further out in the process, just after you've decided to use SQL Server in the first place.

Preparing to Install

Before you prepare a budget, before you choose a server, before you even decide if the server room has a place in the rack for the server, you have to know one thing: what you plan to do with SQL Server. There are two basic reasons you're installing SQL Server, or any database system for that matter:

• Canned Applications: Your organization bought some software that requires SQL Server
• Custom Applications: Your organization is writing some software that requires SQL Server

That might sound obvious, but you'd be surprised how many bad decisions during installation and configuration flow from not having this information to start with. Let's start with the first situation.

Installing SQL Server for third-party applications

If you're installing SQL Server because you've got an application that requires it, then your job is actually a bit easier. It's easier because that vendor should provide some guidance for what you need to do next. If they don't, contact them and ask. They should have a series of worksheets or even better they should provide some consulting time to help you evaluate all of the variables I'll discuss in a moment to help you design your server layout. This is normally true of any large package implementation. Even in smaller applications, most companies will give you some help in choosing a server and an edition of SQL Server to use with their product. If they don’t have this information, make sure you ask for it. Any responsible vendor should provide you with the guidance for their database application.

Sometimes the worksheets or other tools the companies use are based on seat-count. In other words, if you have this many users in the application, you need this much memory and so on. I'm usually a little skeptical of that arrangement, since most applications have many individual functions, and different users exercise those functions differently. Each function usually exercises the various parts of the system (network load, memory, CPU and so on) differently. The decision matrix for that many variables gets complicated quickly if the application is of any appreciable size.

The best help that a third-party firm can give you in configuration involves two parts: experience with installing the product, and a list of references you can use.

By references, I mean contacts at another company that has installed their product, preferably a few that are the same size and shape as yours. I've done many numerical and metric-based models for SQL Server installations, but none is as good as the experiences I gained from customers who installed my company's software and were using it. Sometimes my models bore out the results the clients got, and others, well; let's just say I adjusted my models often based on empirical evidence more than once.

Even if the company gives you a model to work from, experienced help with the installation, and numbers and configurations from other companies that have installed the software, you should read on. You're lucky to have all the help, but in the end, it's your system. The consultants won't have to live with the system's poor performance, or support it at 2:00 in the morning when it all goes wrong. So read ahead, as if the decisions are completely in your hands — they actually are.

Installing SQL Server for your own applications

If you're not buying software to use with SQL Server, then you're writing your own. After all, no one buys SQL Server just for itself. It's a platform that software uses to store and exploit data. In this case, your organization will design and implement a system that needs to store that data, and they've turned to you as the database architect to figure out what they need to scope, buy and install.

Over the years I've developed a methodology that I use for any SQL Server installation. Even if I think I know the parameters, even if I've done the installation multiple times, I think about these steps each time I put in the media:

1. Think about security
2. Evaluate the layout
3. Select the hardware
4. Select the edition

Whether you’re installing SQL Server because of a canned application or for something that you’re writing yourself, review these areas to make sure everything is well thought-out.

Think about security

Before you buy or install anything, review all the bulletins and considerations there are for SQL Server and security. Having this information before you start is the key to developing a system that is "secure by default." I provide a section of this guide for SQL Server security, but you should also review the Microsoft Security website, and other sites that deal with hardware and software security. It's a little insurance that goes a long way.

Evaluate the layout

The next part of the decision is how many servers you'll need, and where you'll put them. Work with your developers and ask them what kind of sizing exercises they've done that you can leverage. Even so, check their numbers by performing some tests of your own. How much network traffic do the various representative operations take? How much memory is used? How much hard drive activity does the application generate at peak, average and low use times?

Another parameter that is often forgotten is how much of a window for maintenance everyone is considering. Yes, you can back up SQL Server while users are on-line, and with SQL Server 2005and higher, you can even do many index reorganizations "live," but these activities will take a toll on performance. Also, at some point you'll need to install a Service Pack or upgrade some hardware — at least two of the operations where the system needs to be turned off. If the application has a 24x7 requirement, you'll want to consider clustering or at least database mirroring to provide uninterrupted service. By answering this one question, you already have some decisions made for you.

By evaluating the layout, I'm not just talking about the number of servers or their general capabilities such as SAN connections and so on. While those things are important, you should also consider where the servers will be located. Decide if you have enough power, rack space, backup capabilities, even where the servers sit on the network connection. If this is a "forward facing" application, meaning that it connects directly with the Internet, then you'll want to set up a Demilitarized Zone (DMZ) arrangement.

Select the hardware

The first decision is the platform that you need. Different chip architectures have different advantages and costs, so don't assume that an IA64-based server will be the fastest system for your needs. I once did some testing that showed an x64 system to be twice as fast as a "larger" IA64 server for a particular application. In another test on a different scenario the IA64 won hands-down. The point is to properly evaluate the pressure points on the system. More memory may be more beneficial than a faster processor, for instance.

In the same way, two computers may be better than one. This is especially true where the system has a large reporting or business intelligence function. Of course, the tradeoff is that you have to buy twice the hardware, twice the licensing for the operating system, SQL Server and the like, and twice the maintenance. Everything is a tradeoff.

Select the edition

It might seem strange to pick the edition of SQL Server after you've selected the hardware, but look at it this way: It's usually easier to upgrade the software than the hardware. Buy a little more server than you need and then you'll have the flexibility you need if you want to go higher.

Installing SQL Server

Before you install SQL Server, you might want to take a peek at this article series on configuring Windows for that system. Although the version of Windows you’re using might be different, there are some great strategies to think through there.

As I stated earlier, I'm not going to work through a series of screenshots or repeat the information you'll find in Books Online. Microsoft does a far better job of that than I can do here, and since they update Books Online several times throughout the year, they'll be more current than I could. I recommend before the installation that you follow their advice.

As you do, there are a few more things you need to think about.

Third-party installations

Third-party installations will either install the software for you (the ideal) or provide the instructions for installing SQL Server for their product. If they give you instructions for the installation and don't mention the Microsoft documentation, you should be asking some questions. Are they certain that they understand all of the installation documentation Ill enough to come up with their own? Ask if they've read the addendums and so on. Don't take it on faith that the technicians at the firm have time to deal with all of the intricacies of the changes that Microsoft makes.

Unattended installations

There are times when you need to install the software on multiple servers, and you want to do it the same way each time. Almost all versions and editions of SQL Server provide mechanisms to do this (called an Unattended Installation), and you should consult Books Online for the process.

And no, you can't use imaging software to install SQL Server multiple times. Imaging will indeed change not only the server name and the SID but the registry entries as well. What they can't change are the entries within the SQL Server databases and metadata that depend on the name and the SIDs. For that only an unattended installation will work. You can image the server and run an unattended installation of SQL Server when you're done, which is what I do.

Manual installations

The manual installation steps are different for various versions of SQL Server. All of them involve installing or at least checking the prerequisites that SQL Server needs, and each let you select and configure the components you install.

In the later versions of SQL Server, even if you select "client components," you won't get the sample databases unless you manually select them or download them from the Codeplex.com site. Make sure you drill into the properties of each panel and make all the selections you need.

Configuring SQL Server

After you've completed the installation, you're not done. You need to install some service packs, security bulletins and so on, and also configure the various settings in the Instance and databases.

Installing Service Packs

Unless you can't install the latest Service Pack, do. They are cumulative, so you only have to install the latest, but starting with SQL Server 2005, Microsoft doesn't always provide a "slipstreamed" release, meaning a single CD with both SQL Server and the service packs combined. You'll have to install them separately.

Notice that I said "unless you can't." That means sometimes you should not install a Service Pack on your server. As the vendors of other software release their products, they test (hopefully extensively) with a particular version and Service Pack of SQL Server. It takes them time to ramp up to the latest version and test all of their applications, so make sure you check with them before you apply a Service Pack. You could do serious damage to your system otherwise.

Setting the parameters

SQL Server has less knobs and levers than other Relational Database Management Systems, but it does have a few. You should read Books Online regarding these settings, and choose the configuration options appropriately. I've also covered these options here on InformIT.

So now you're ready to read those 1200+ plus pages of installation documentation from Microsoft. Grab a cup of coffee, find a quiet place, and have a pencil and paper handy. You won't have to read it all — but you should read the parts that apply to you. If you follow the information in this tutorial, you'll be able to make intelligent decisions about what that is.

InformIT Articles and Sample Chapters

I mentioned in this tutorial that you should perform your own testing to get application metrics. I show you how to do that in Creating a Performance Tuning Audit - Defining Components.

Books and eBooks

If you are brand-new to SQL Server, there’s a very inexpensive way to come up to speed quickly on the product. It’s a video series called The Accidental DBA.

Online Resources

An often-overlooked resource is the installation section of the MSDN forum for SQL Server at Microsoft. Several of their developers troll this board to answer many questions on SQL Server, all for free!