How do -you- install SQL Server (Part 1)

No, this is not an installation guide

There is documentation for that found in many places. First good place to look is Books Online (2005/2008) but there are plenty of other community resources found at sites like SQL Server Central and SQLServerPedia as well as blogs.

What I mean by this post is to ask, “How do you install SQL Server?” Do you follow any best practices? Do you use a repeatable process and a checklist to the process? Do you review post installation to verify you did everything right? Do you know what is actually needed for the instance you are building?

If you do, then this post might be a review and not worth reading. If you do things kind of quickly, or give every instance every service then maybe some of these tips will help you out. More to follow in Part 2.

Some SQL Server Installation Tips

I used to catch a lot of grief (in good humor) from a Sharepoint developer (pot meet kettle 😉 ) about how my job as Database Administrator was a series of left and right clicks. While I enjoy scripting things out, the GUI is there and it is helpful so sure, I do some clicking. One area in particular I caught flack was for the install process (described, almost accurately, as “Next, Next, Next, Finish”).

I have worked in a lot of environments where that was the extent of the SQL Server installation – some quick clicks and poof you are live with defaults and some form of SQL Server services running ready for “prime time”. Giving a little more thought to the installation and setup may save you some invoiced consulting hours in the future.

I recently spoke about some aspects of these issues when sharing on the Quest Connect webcast for Top 10 SQL Server Administration mistakes (I blogged about that here but it is now unavailable – it was a 24 hour virtual conference). I want to zero in on some of the installation/initial setup related items now…

Read the Documentation

Ok so this one sounds like a freebie. You’d be surprised how many people don’t, though. It’s apparent in some questions online and it’s apparent in some basics that are misunderstood when choosing options. If you don’t know or you aren’t 100% sure, look it up. Most of the installation steps have a “help” dialog and Books Online really does go into a lot of detail.

Plan

Figure out what type of usage you should expect. Plan your I/O infrastructure. Some basics rules of thumb (I hate rules of thumb as a rule of thumb… Your mileage will vary but some basic thought processes to go through follow. Part 2 will have some resource links, check these out and look at Books Online thoroughly):

• Memory – the more the merrier. Now you could go overboard and waste money but on a busy database server with a fair amount of data memory is key. The less memory you have, the more frequently you are reading from disk and flushing data to disk.
• Disks – Fast is better. More spindles (physical disk drives) is better. Separation (at least Data, TempDB, Log files and backups each onto their own physical drives is generally a good start) onto the RAID arrays is even better. RAID 5 (Striping with Parity) is generally alright for most data file setups. RAID 10 makes your log and tempdb files happier (even just going with mirroring alone for Log files is fine in a lot of environments also)
• CPU – Multi cores are generally fine. In my mind the verdict on the new hyperthreading on top of the quad cores is still being decided. It used to be that hyperthreading (prior to the dual cores) was bad but it has been souped up and reintroduced. Most SQL Server environments I encounter are disk or memory bound before they are CPU bound but don’t skimp here either for a busy environment.
• 64 or 32 bit? 64 will give you better memory management, it is trusted and well used in the “wild”. Some annoyances with SSIS packages but nothing impossible to work with. Make sure your hardware and O/S and SQL installation are all friendly with one another.

The point here is to understand what makes each of these areas tick (you may grow tired of me saying this but you can find what you need from documentation out there), understand what the needs are for your environment and get a plan for what is needed before you even think about looking for the install media. Heck before you even think about putting an order in for the server.

Don’t just plan the hardware here but actually take a look through the below items, the items in Part 2 and incorporate decisions needed down the line here in the planning stage. I wrote a checklist for questions to ask vendors (Blogged about it here). An installation/pre-installation checklist is also a good idea. I know Microsoft has some for cluster installations…

Install What You Need

What was requested? Is what was requested understood properly by the requester? Depending on the version of SQL, installing Reporting Services (in 2005) means you need to install IIS. Do you want that running on your busy production database instance if it wasn’t needed? Why install Analysis Services or Free text Search if you don’t have a requirement for it? Wastes a little space, uses a little memory and it’s one more item to increase the surface area of your installation for security concerns. (SQL Server has had very few exploits in 2005 and beyond compared to many competitors but security should be a concern of a DBA and why increase even a slight chance of risk if you don’t need the tool?)

Do you want all of the client tools on the server? Maybe SSMS but do you need BI Development studio up there?

(Install What You Need) Where You Need It

So you do need Reporting Services and Analysis Services. How busy will they be? Have you done some planning to understand the capacity, usage and load factors to know what should go where? Analysis Services has a different IO pattern than SQL Server. When you need to load your cubes each night your data source is on the same box if you collocated the tools.

For Reporting Services have you considered putting the front end on an “app” or web server? Memory and CPU are generally more important (there are exceptions and report caching/subscriptions can complicate that blanket statement… See Documentation above 🙂 )  and the usage of memory and CPU is different and competitive with the Database Engine. Perhaps your reports will be so busy that having the Front End on one box, the data source on one box and the database server for the two databases that get loaded when you install and configure Reporting Services (By The Way – Reporting Services has it’s own databases and they are crucial to reporting performance)

Installation Directories

Don’t just accept the defaults without thinking through them. Do you have a directory setup for your data files? Do you like seeing your application binaries in a path other than C:\Program Files\… ?

In fact while talking about directories, do you have standards for folder naming conventions? For me, I typically like to make a “SQLData” and “SQLLogs” folder, sometimes I incorporate an instance name when going with multiple instances (I wrote a wiki article for SQLServerPedia on some factors in the decision making process there). So if you do have one, implement it before you begin the installation. If you don’t have a standard, maybe now is the time to think about one? There is no wrong answer for a standard (other than not having one or, even worse, having one and not following it consistently).

Enough for Part 1

In Part 2, we’ll talk about Security Considerations, High Availability/Recoverability Considerations and a process for testing your installation. We’ll close with Part 3 at the end of November when we discuss post installation configuration options, maintenance and defaults.

I welcome comments. If I my comments don’t backup your experiences, you have a different take or more to add, please feel free to drop a line below.