SQL Server 2000 Administration in 15 Minutes a Week: Basic Installation

Friday May 10th 2002 by Michael Aubert

Michael Aubert's second article in the SQL Server 2000 Administration in 15 Minutes a Week series covers performing a basic installation of SQL Server 2000, with topics including pre-installation procedures and an installation overview.

Welcome to the second article in my series SQL Server Administration in 15 Minutes a Week. In last week's article, we took a look at an overview of the options and requirements when installing SQL Server 2000. This week we will look at how to perform a basic installation of SQL Server 2000. The topics for this article include:

- Before the Installation
- Installation Overview

Before the Installation

Use the following as a checklist before you install SQL Server 2000:

- Have your SQL Server 2000 CD, Installation Key, and operating system CD handy
- You should be logged on using an account that has Administrative rights.
- Disable any Antivirus software that is running as it can sometimes effect the installation.
- Shut down any services or applications that depend on SQL Server, Data Source Names (DSN), or Open Database Connectivity (ODBC).
- Close any unnecessary applications that access the registry
- Close the Event Viewer (Microsoft says so!)

Installation Overview

1. When you place the SQL Server 2000 CD-ROM in your drive, you are greeted with the screen shown below. If you don't see the SQL Server menu, you most likely have autorun disabled. To start the SQL Server menu manually use the Windows Explorer and run autorun.exe from your CD-ROM drive. You can also use "Run..." from the start menu and enter d:\autorun.exe where d: is the letter of your CD-ROM drive.

Click on "SQL Server 2000 Components" to view the available components you can install.

2. Now we are presented with three options on the components screen. Lets continue our installation by installing the Database Server. We will look at the Analysis Service and English Query components in a later article.

Click on "Install Database Server" to begin the SQL Server 2000 Installation.

3. The installation of the Database Server now begins.

Click Next to continue the installation.

4. We are now prompted to select which computer we would like to install SQL Server on. There are three options:

Local Computer -- Installs SQL Server onto the local computer
Remote Computer -- Installs SQL Server onto a remote computer on the network
Virtual Server -- Used for installing SQL Server onto a Server Cluster

In this article we will look at an installation of SQL Server 2000 on a local computer only. In the next article of this series I will discuss Remote and Clustered installations.

Select "Local Computer" and click Next.

5. Now that we have selected where we would like to install SQL Server we have to choose what we would like to do in this installation.

Create a new instance -- Allows you to install a new instance of SQL Server or the Client Tools
Upgrade, remove, or add components -- Allows you to work with an existing instance that is already installed
Advanced options -- Allows you to create custom installation files, rebuild registry keys, and maintain Virtual Servers for Failover Clustering

For now we will look at how to create a new instance. In my next article I will cover how to upgrade an existing instance, perform unattended installations, and configure a clustered server.

Select "Create a new instance of SQL Server, or install Client Tools" and click Next.

Page 2: The Installation Continues...

 » See All Articles by Columnist Michael Aubert

6. The next few screens are common and fairly simple so I will cover them quickly.

Click to Enlarge

Enter your name and company information and click Next.

7. To continue the installation we are asked to read the licensing agreement.

Click to Enlarge

Read the agreement and click "Yes" if you agree or click "No" if you do not agree with the licensing terms. Note if you click "No" the installation will be terminated.

8. Next we need to choose what type of Installation we would like to perform.

Client Tools Only -- Installs the Client Tools that allow you to manage a remote SQL Server
Server and Client Tools -- Installs an instance of SQL server and the Client Tools
Connectivity Only -- Installs the Data Access Components (DAC) and Network Libraries so applications can access a SQL Server instance

Select "Server and Client Tools" and click Next.

9. The Instance Name is the next option we are presented with. If you are installing SQL Server as a Default Instance you leave the "Default" checkbox checked. If you need to install a Named Instance you must uncheck "Default" and you will then be required to enter a valid name for the instance in the "Instance Name" textbox. For information on what is and is not a valid Named Instance name, have a look at my first article in this series: SQL Server 2000 Administration Fundamentals.

Leave the "Default" checkbox checked and click Next.

10. We must now select the setup type.

Typical -- Installs required files and the most commonly used optional files
Minimum -- Installs only required files
Custom -- Installs required files and then you will be prompted to select which optional files you would like installed. Custom setup also allows you to set the collation and network libraries that are used.
Type System Files Program Files Database Files
Typical/Custom* 182,917K 38,205K 34,432K
Minimum 75,116K 12,127K 25,600K

*By adding and removing components the Custom installation size will change.

On this screen we are also prompted to select the destination folders for the Program Files and Data Files.

Program Files -- The files that make up the SQL Server engine and tools
Data Files -- The SQL Server databases

The default drive selected to install SQL Server 2000 is the System Drive (the same drive that contains your Windows installation). Finding the default directories is a little different and depends on whether you are installing a default or named instance. Use the table below to locate the default directories.

Instance Type Program Files Directory Data Files Directory*
Default Instance \Program Files\Microsoft SQL Server\Mssql\Binn \Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn
Named Instance \Program Files\Microsoft SQL Server\Mssql\Data \Program Files\Microsoft SQL Server\MSSQL$InstanceName\Data

* Where "InstanceName" is the name of your SQL Server Instance.

Select "Custom" and unless you have a specific reason to change the destination folders (such as not having the disk space needed) leave them as default and click Next.

Select "Custom" and unless you have a specific reason to change the destination folders (such as not having the disk space needed) leave them as default and click Next.

Page 3: ...and Continues...

 » See All Articles by Columnist Michael Aubert

11. At this point the installation gives you the opportunity to select optional components.

Click to Enlarge

Click Next to continue the installation.

12. Choosing the proper service accounts (the accounts that SQL Server will use to authenticate to Windows when it needs to access resources such as NTFS hard drives or other computers) is an important one. SQL Server 2000 uses two accounts: one for SQL Server and one for the SQL Server Agent. The SQL Server account is used by the SQL database engine which provides all the database related services. The SQL Server Agent account is used by the SQL Server Agent which provides alerts and job scheduling for your SQL Server. For now just understand there are two different accounts; the differences will become apparent when we discuss the SQL Server Agent in more detail.

The first option you must select is whether you want the two services to use the same account and have the SQL Server agent start automatically or if you want to customize the accounts for each service. If you select to use the same account, you then have the option to use the Local System account or use a Domain User account.

If this is your only SQL Server on the network and you don't plan on having this server interact with any other SQL servers or remote storage servers, you can use the Local System account. If you want to interact with other SQL servers or pull information off of another server you are going to need to create a user account in your domain. If you do use a domain account for SQL Server, it is recommended that you create an account for just SQL server and not use an existing user account. This is important because if your network's account policy is to have the password expire every 30-90 days you would have to change the account passwords on all your SQL Servers every 30-90 days. To overcome this problem, set the "Password Never Expires" on the account you use for your SQL Servers.

Customizing each service's account gives you the same options as using the same account for each service, but allows you to set the account each service uses independently. It also gives you the option to start the SQL Server Agent at startup or not. We will look at services in more detail in the next article.

Select "Use the same account for both services. Autostart SQL Server Agent Service," "Use the Local System account," and click Next.

13. Selecting the Authentication Mode is the next option we are presented with. The process of authenticating (using your User Name and Password) to a SQL Server for access to the databases can be accomplished by the SQL Server or by the operating system.

If you select "Windows Authentication Mode" all authentication will be handled by the Windows Security Users and Groups. In other words the accounts that you use to login to a Windows computer are also used to access the SQL Server databases. Having the ability to use Windows' Security provides benefits such as streamlined authentication and much simpler user management. It is recommended to use only Windows Authentication Mode unless you have a spasific reason not to.

"Mixed Mode" authentication provides both Windows Authentication and SQL Server Authentication. Unlike Windows Authentication, SQL Server Authentication is handled by SQL Server and does not rely on the operating system. Because SQL Server Authentication is independent, authentication can be provided for platforms other than Windows (ex: Unix). When you choose Mixed Mode authentication you are asked to provide a password for the system administrator (sa) account. This account is available so you can authenticate as a "sysadmin" via SQL Server Authentication. When you use only the Windows Authentication Mode the sa account is disabled.

There are two final notes about Windows Authentication (for either of the two modes): The Windows Security groups BUILTIN\Administrators on the local computer (the computer SQL Server is installed on) and the group Domain Admins (if the SQL Server computer is part of a domain) are automatically granted "sysadmin" rights to the SQL server. Also remember you must use Windows NT or Windows 2000 to provide Windows Authentication. If you are using Windows 98, for example, you can only use SQL Server Authentication.

Security is a big issue and will take one or two articles to examine in more detail. For now it is only important to understand the two modes of authentication.

Select "Windows Authentication Mode" and click Next.

14. Another important decision you have to make is the default collation. There are three parts to the collation setting:

- Character Set
- Sort Order
- Case Sensitivity

The character set specifies what ASCII code page will be used. ASCII (pronounced "ask-ee") code pages contain the codes (numbers 0 through 255) that correspond to the appropriate characters. For example, the ASCII code for the letter A is 65. Characters 0 through 127 are called the "Standard ASCII Set" and are the same from one code page to another. The character codes 128 through 255 are in the "Extended ASCII Set" and will varied between different code pages. Take a look at ASCII code pages 1252 and 437.

There are two major types of sort orders: Dictionary and Binary. You have seen a Dictionary sort order before if you have ever used a dictionary. Just like in a dictionary, Dictionary sort order arranges the data in alphabetical order. When you use a dictionary sort it's simple to predict in what order your data will be returned to you.

Binary sorts, on the other hand, are a little more complicated to understand and predict. A binary sort is preformed on the numbers that make up the ASCII letters. Binary sorts can return results that are not always expected because the numbers that make up ASCII codes are not in dictionary order. However the benefit of a binary sort order is its fast speed.

Case sensitivity comes into play when you start to make comparisons. For example, if you had a statement that was looking for the word "trainers" and you used a collation that was case-insensitive; "Trainers", "TRAINERS", and "TrAiNeRs" would all be returned. If you used a case-sensitive collation only "trainers" would be returned.

Now that you understand what makes up a collation lets look at the settings available on the collation setup screen. You can choose to use Windows' Locale settings to set the collation or use a SQL Server Collation. By using Windows' Locales you use the Collation information that is provided by the operating system. SQL Collations are provided for compatibility when you need to interact with older SQL Servers or upgrade a database.

Just remember that you are only setting the default for the server. You can select another collation for each database, or even each table, individually.

Select "SQL Server Collations", "Dictionary order, case-insensitive, for use with 1252 Character Set.", and click Next.

Page 4: ...and Continues Some More...

 » See All Articles by Columnist Michael Aubert

15. Selecting the Network Libraries is one of the last decisions you need to make. In order for clients to access the SQL Server, the Server and Clients must be using a compatible protocol. The decision on which protocols you will use will depend on what type of clients you are serving and what network protocols you use on your network. Note that Named Pipes are only available on Windows NT 4.0 or Windows 2000. We will discuss the Named Pipes and TCP/IP Network Libraries in more depth later in this series.

Click to Enlarge

Keep the default settings and click Next.

16. The installation now informs us that it has enough information and can continue. Note that if you need to go back and change anything you must do so now. Once you click Next you will be unable to go back.

Click to Enlarge

Click Next to continue the installation.

17. We must now choose from one of the two licensing modes:

Per Seat -- Requires a SQL license and a Client Access License (CAL) for each device that accesses the SQL Server.
Processor License -- Requires a license for each processor SQL Server will use.

It is best to buy Per Seat licenses when you have a known number of clients that access many servers. On the other hand, processor licenses are best used when you have an unknown number of clients (that normally vary widely from hour to hour) such as an e-commerce website.

Enter the type and number of licenses you have purchased and click Continue.

18. The installation copies the files to your hard drive and then informs you when it has completed.

19. You can now locate the tools used to work with your SQL Server by clicking "Start" on the taskbar, selecting "Programs", and then selecting "Microsoft SQL Server."

That finishes up our look at a basic installation of SQL Server 2000. Keep in mind if you are not using the same exact version of SQL server I am, your installation may vary slightly (ex: I used a copy of SQL server that does not require an installation key). Next week I will explain some of the more advanced topics when installing SQL Server 2000.

If you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.


» See All Articles by Columnist Michael Aubert

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved