How do -you- install SQL Server? (Part 2)

Welcome to this series on a thought process around SQL Server Installation. In Part 1, we started talking about some basic steps and a thought process for a repeatable success story with your SQL Server installations. Today let’s continue that story and talk some more about installation planning and success. We’ll finish up talking about some post installation options (You aren’t done when you click finish!).

If you have questions or concerns along the way, please shoot me a note in the comments and I’ll answer, get you an answer or update this post if anything isn’t clear.

SQL Server Installation Tips (Cont.)

Again this isn’t a step by step installation guide. This isn’t even a series to read in order as you install SQL Server. In fact, when reading documentation (which I hope you write when required), always read through first before you start doing. I learned that the hard way when making cookies with my daughter once. Just started combing ingredients then got to the step in the recipe that said, “Combine the dry ingredients separately from the wet.” Those cookies were salty, crunchy and, well, pretty bad.

Instead these are some things to read to help inspire questions to ask and think about when planning to roll out a SQL Server Installation.

Security

What kind of industry do you work in? What kind of auditing/compliance regulations and secuirty best practices do you fall under? Regardless, it’s in your best interest as DBA to think of security from the beginning. Some questions to consider ahead of time are:

• What rights will your SQL Server and SQL Server Agent service accounts run under? Certainly doesn’t need Domain Admin, doesn’t even need local admin necessarily. Brian Kelley (Blog|Twitter) is a great resource on security. He blogs about the woes of using Domain Admin here.
• Unless you are installing 2008, the BUILTIN\Administrators group will have Sys Admin level access in SQL. Do you want this? You can change it (but you have to change it to do so)
• Windows Authentication only? I prefer it that way, it relies on the security of your AD and network, less chance of clear text password being stored someplace. Will your apps support it? (Talk to your vendors from time to time.. Ask them questions) If you go this route, make sure you have the right AD groups in the sysadmin role.
• Least Privilege –> A philosophy to think about with all of the above is least privilege. Grant the minimal permissions needed to do the job required.
• Groups instead of Accounts –> I prefer to use AD groups as SQL logins in most cases. This can make administration easier (new member joins a dev team, they get added to the proper group and have developer rights in the dev SQL instance) and if you already do a great job with approvals and audit trails of adding people to groups you just inherited a best practice for “free”. 

Availability/Recovery

What Service Level Agreement (SLA) will this instance fall under? Along with planning for I/O for performance and capacity make sure you consider your recovery solution. Do you have a separate drive for backups? Will you be using a third party program for SQL backups? If so, does it work on your proposed hardware and software setup? As I write this there are some interesting Operating System choices – Windows Server 2003 is still going strong, Windows 2008 has been in the wild for some time and Windows Server 2008 R2 is out and being used. What is the plan for your organization’s future? Do you implement that now? Better make sure the backup tool works and is supported first, kind of an important part of the job, recovery is. 

What about availability/disaster recovery? Considering Mirroring/Clustering? Replication/Log Shipping? Does the hardware support it? Are you installing the components necessary and the editions necessary?

Try It Before You Buy It

Yeah I know… Your timeline is tight, project management is breathing down your management’s neck to get this done. It’s your first time installing this version, this setup or with these options, though. You have a dilemma here – do you bang it out and slug through and have no impact on the timeline for when you can say, “Done! It’s built!”? Or do you spend a little time trying it out and breaking a few things along the way?

I vote for the latter when playing with something new. Even if it means a longer week in the office that week in order to make a date. I choose to pay now instead of later where possible. I am not suggesting you do a trial run for every single installation for the rest of your career. No, at some point you have to take off the training wheels and trust your process, your checklist and your smoke testing abilities. When first trying something major (and installing a production database instance is something major, no?), though, it makes sense.

Read the documentation. Read posts like this series you are reading right now. Look at pre-existing checklists, tailor them to your preferences and standards and try it out. Build up an instance, follow the checklist, take screen shots for your own documentation, tweak your checklist based on input, etc. Try it, test it, experience it.

Ask, “what happens if I press this button?” and then press the button and see. Take note of your findings and research the lessons. It’s called learning and building experiences. Take all of this knowledge and then apply it when building the real deal.

Smoke Test

You went through the process. You checked your checklist. You have an installed instance of SQL Server (congratulations, yes it was almost as easy as next-next-next-finish, but you thought of the big picture and paid attention) now test it out. We’ll go on in Part 3 and talk about some maintenance, settings and defaults post installation.

For now, check everything over. Can you stop and start the instance through SQL Server Configuration Manager? Does the server restart just fine? Can you connect locally and remotely? Is SQL Agent running?

Have you looked over the installation logs? Have you looked at the SQL Server and SQL Server Agent Error Logs? What about the System and Application event logs on the server? Verify the installed files are where you expected them to be?

Verify everything looks good but don’t check the “done” box yet. In Part 3 we’ll take a walk through some final considerations around defaults, maintenance and some other post installation tips. Then I’ll leave you alone.

What about you? What else do you think about pre installation and during SQL Server installation? Have some horror stories about installations gone wrong because they were so simple and quick? We’d love to hear your opinion.