In the news, Microsoft has updated the community links for SQL Server. In this week’s feature, I’ll explain the “big deal” about preparing for an installation, the web link shows you how to corrupt a database – on purpose, and the tip of the week is on upgrade options.
News this week:
The Community Links on the MSDN Website have been updated to include many of the new Most Valuable Professionals, or MVP’s. Check it out here: tinyurl.com/yvm8d4
How hard could it be to install some software? Drop in the DVD, and hit Next, Next, Finish, right? As a database consultant, l gained more business from statements like these than I could keep up with.
Installing a database server is far more complex than installing an application like Microsoft office. Sure, you can install SQL Server quickly and easily, but you don’t really want to, especially on any system that you will use in production. The reason is that SQL Server is a foundation, one that supports your most valuable asset - your company’s data. Like any foundation, what you build on it is only as stable as the foundation itself.
So if you follow that logic through, you can see that proper design of the database is essential to your applications, the proper database configuration is essential to your database design, the proper server configuration is essential for the database settings, and the proper installation configuration is essential to the instance itself. So you can see that your applications can be drastically affected by the decisions you make during installation.
I spoke recently with Louis Berner, who works in the SQL Server team here at Microsoft, to find out more about the resources the DBA has for learning to plan an installation, from The hardware to the software and even the settings in the operating system that are important. By the way, I’m only covering installations today – in next week’s Podcast I’ll explain upgrade decisions and processes.
Microsoft recommends that you follow a “Prepare, Perform and Verify” method. Before we start, it’s important to understand that on any installation other than a test system, you should involve others in your designs. Everyone from management to the development team needs to be in on your planning, for budget reasons to the drive layouts. In larger organizations, you might even have the hardware and operating system decisions made for you by other teams. Even so, you’re the trained professional everyone is looking to for the voice of reason on all the decisions.
Let’s start with the “Prepare” stage:
SQL Server comes with literally hundreds of pages of installation and upgrade instructions. You can find the documentation on your DVD or CD, and it’s also on the web at http://tinyurl.com/23dq9v for SQL Server 2005. If you’re installing another version you can get to it from there by just looking at the table of contents on the left of the site.
You don’t have to read all of that documentation of course; because it covers just about every possible configuration you can set up. To narrow down what you need to read, the documentation is based on the choices and decisions you make about your installation.
The first thing you need to decide is whether you’re installing on a cluster or not. Right away you have licensing choices you need to make. In another Podcast I explained the various editions that SQL Server comes in. Only three of those - Standard, Enterprise and in version 2000, Datacenter - support clustered systems. You’ll normally want to cluster when you have 5x9 needs (meaning only a few minutes a year in downtime), want to guard data against machine failures and so on.
Once you’ve decided on your clustering, you’ll need to pick out the hardware. For clustering, you need to make sure it is on the Hardware Compatibility List (HCL) that is referenced in the setup documentation, otherwise it won’t be supported, and you’re gambling with whether it will even work or not. For non-clustered systems, you have a lot more choices. In any case you want server-class hardware, which normally have beefier power supplies and thicker electronic components that can stand the heat generated from constant use. Also on the hardware decision is memory and storage sizes – but that’s another podcast.
(As you progress in your decision making, you’ll find that the documentation has a lot of checklists to follow, and links that point to more information. I can’t emphasize enough that you should review this documentation as you go through your install.)
Next, you’ll want to think about your security. As you set up the operating system that will run the server, make sure you install all patches and set up your security software such as firewalls and the like and note down all of your settings, especially your network configuration. In a clustered situation, you’ll need to have this handy. Then create several accounts that you will use for your services. I recommend a different account for each SQL Server service, for each instance. A common mistake is to make these accounts administrators on the system or even worse, on the domain. You don’t need to do that! Once again, Books Online has some recommendations for that, and I’ll explain more about those later in another podcast.
On my production systems I normally review and archive all of the event logs, so they are “clean” before I start. SQL Server writes information to these logs and into a series of text files that record the installation process.
Now on to the “Perform” stage:
Now you’re ready to start your installation of the SQL Server software. Run “Setup.EXE” or pop the media in the drive, and carefully evaluate the choices you’re offered. For some options, such as the samples, you’ll need to check a box to install them and also set their locations. Don’t take anything for granted, and take your time with the process. When you’re asked about the service accounts, select the ones you created earlier, and the installation process will automatically grant them all of the rights and privileges they need.
Finally, we’re in the “Verify” stage:
Once the installation completes, you don’t have to restart the server. Some people do as a matter of course, but it isn’t required in SQL Server 2005 and higher. Check and make sure all of the services have started, review the event logs, and try creating a database and some users. Remember that depending on the edition you install, you may have to configure the TCP/IP settings to allow connections as well as running the Surface Area Configuration Tools to let people in.
If you should have any issues with the installation, review the error message and setup logs to see if you can restart the installation or if you need to back it out. There’s a link in the setup docs for cleaning up a failed installation. If you still can’t make it work and want to contact support, make sure you collect all of the installation logs into a zip file before you make the call. They will ask you for these logs, so you may as well have them ready.
This week I found a link from Tony Rogerson that will show you how to corrupt a database. That’s right, a script that will trash a database. Why would you ever want to do that? I do this all the time, either for demonstrations, and most importantly, to test how I can correct things in a crisis. Always do this on a test server, of course, and never on production! http://tinyurl.com/2gacos
Tip of the Week:
As a preview to next week’s topic, did you know that English-language versions of SQL Server can be upgraded to any localized version of SQL Server 2005? Localized versions of SQL Server can be upgraded to localized versions of SQL Server 2005 of the same language.