DBA Tips Archive for SQL Server
No Title[an error occurred while processing this directive]
by Jeff Hunter, Sr. Database Administrator
This article details the steps for installing Microsoft SQL Server on to the Windows XP Professional operating environment. Installing SQL Server is not a difficult task by any means. There are, however, critical decisions that have to be made during the setup that will have a great impact on your installation.
For the purpose of this article, I will be installing Microsoft SQL Server 2000 (Developers Edition) on the Windows XP operating environment.
Before going into the installation details, let's first talk about the different versions (or editions) of Microsoft SQL Server.
Microsoft released six different editions of their SQL Server product. In most cases, it will be apparent which edition of the SQL Server you will need to install. Do keep in mind, however, that all editions of SQL Server are built from a comman code base (with the exception of Windows CE).
If you are looking for it all features and functionality in SQL Server, this is the edition for you. This edition is for high-end installation or those installations that require the best performance. Enterprise edition will run on Windows NT 4.0 Server (SP5 or later), Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Data Center Server. One of the major features contained in the Enterprise Edition is its support for up to 64GB of RAM. Also available is is support for Microsoft Clustering (up to four nodes) as well as support for up to 32 processors. It is important to keep in mind that the underlying operating system must provide support for these more advanced features as well.
This is the most popular edition of SQL Server 2000. Standard Edition is includes all of the features and functionality offered in SQL Server and is meant to be run on Window NT Server 4.0 (SP5 or later), Windows 2000 Server, Windows 2000 Advanced Server, or Windows 2000 Data Center Server. It provides support for up to four processors and up to 2GB of RAM. Keep in mind that this edition will not run (or install) on Windows 2000 Professional or Windows XP.
If you are looking for a SQL Server 2000 Edition that can be run on Windows 98, Windows ME, Windows NT Workstation, or Windows 2000 Professional, then this is the edition for you. For the most part, this edition was designed as a development platform. It allows for several simultaneous user sessions (although Microsoft does not hard code a limit within the code). Also, limits are not placed on database size. Those limits, however, are placed by the underlying operating systems. Windows NT Workstation and Windows 2000 Professional support up to two processors while Windows 9x only supports one processor.
While the Personal Edition does contain many of the features found in the Enterprise Edition, it does not provide support to publish in transactional replication. While Windows 9x includes even more restrictions, many of them will not be visible to a developer. This allows developers to do their development on the Personal Edition (or even the Developer or Desktop Edition) and rest assured that deployment to Standard or Enterprise Edition will be successful.
Developer Edition is the same as Enterprise Edition.
Many books (and the Microsoft website) offer another edition named the Evaluation Edition. Just like the Developer Edition, Evaluation Edition is the same as Enterprise Edition.
Desktop Edition - (MSDE)
This edition, called MSDE, is a bit unique. It is a stripped down version of the full product. Even though it is built from the same code base, it inserts several restrictions like no support for replication and no user interface (tools like Enterprise Manager). For the most part, this edition was meant as a back-end storage (or repository) for applications. You do get a version of MSDE when purchasing Microsoft Visual Studio or Microsoft Office 2000 Developer Edition. This allows you to develop applications and redistribute MSDE as an embedded part of your application.
If you do have a full version of SQL Server, it is possible to administer an MSDE database using those tools.
Although I will not go into great detail about pre-requisites for installing SQL Server 2000, I will mention several considerations that should be made at the operating system level.
Obviously, the first choice to be made is which operating system you will be using. As mentioned in the previous section, SQL Server 2000 has strict requirements for which operating system it will run on. You will not be able to utilize Windows 98 if your needs require SQL Server Enterprise Edition.
The second issue to consider is security. For this reason, it is probably best to stick with an operating system that supports a high degree of security like found in Windows NT Server 4.0 and Windows 2000. Both of these are considered secure operating environments and include features like access control as well as file restrictions. SQL Server 2000 includes a feature known as Windows Authentication Mode that allows database users to be authenticated at the operating system level (by their Windows user id or group). After being authenticated with the Windows operating system, users can obtain direct access to the SQL Server database through their operating system account.
Another consideration is the file system you will be using. SQL Server 2000 can use the File Allocation Table (FAT), FAT32 (Windows 2000 only), and the New Technology File System (NTFS). It is strongly recommended to use NTFS. This allows SQL Server to secure your installation files, as well as your system database files. It is also a much faster file system. Included with Windows 2000 is a feature named Encrypted File System (EFS) that allows you to encrypt database files. Users will be unable to copy or view these files without the username and password for the SQL Server service account.
As mentioned at the beginning of this article, we will be installing Microsoft SQL Server 2000, Developer Edition on to Windows XP Professional.
The following table lists the hardware and software I will be using for this installation:
|SQL Server Edition||Developer Edition|
|Machine Name||melody.idevelopment.info - (192.168.1.106)|
|Operating Environment||Windows XP Professional|
|Machine||Dell Inspiron 8600 Laptop|
|Hard Drive||60 GB|
Now let's get started with the installation!
To start the installation, insert the SQL Server 2000 CD into your CD-ROM drive. You will then be presented with the autoplay dialog box listing all installation options.
It is recommended to first read over the readme.txt file. This file contains information that may not have been available at the time the manuals were being published. After reading over this file, click the SQL Server 2000 Components option.
Figure 1: Autoplay Dialog
On the Install Components screen, click the Install Database Server option.
Figure 2: Install Components
You are now brought to the Welcome Screen. Click Next to start the installation.
Figure 3: Welcome
You are now asked if you would like to install SQL Server 2000 on this computer (Local Computer) or to a Remote Computer. After verifying that the installation process determined the correct name for your machine, ensure that the Local Computer option is selected and click Next to continue.
Figure 4: Computer Name
SQL Server 2000 is now asking for the type of installation to perform. By default, the option is to "Create a new instance of SQL Server or the Client Tools". This is the option we will be using for this installation. Before displaying this window, the installation process determines if there is a previous installation of SQL Server on the computer you are installing to. If there were a previous installation, the second radio button, "Upgrade, remove, or add components to an existing instance of SQL Server", would be enabled. This is a new installation and we will be using the Create a new instance of SQL Server or the Client Tools option. Click Next to continue.
Figure 5: Installation Selection
You are now being asked for your user and company information. After entering this information, click Next to continue.
Figure 6: User Information
Read over the license agreement. If you agree with the license agreement, click Yes to continue.
Figure 7: Software License Agreement
Enter the 25-character CD license key. You can find the this key on the yellow sticker on the CD. Click Next to continue.
Figure 8: CD-Key
We will be accepting the default selection, "Server and Client Tools". If you were preparing a workstation for the sole purpose of administrating a SQL Server database on a remote machine, you select the "Client Tools Only" option. Also, if all you need is the updated data access components (commonly known as MDAC 2.6) that comes with SQL Server 2000, you can use the "Connectivity Option" option. Keep the default selection of Server and Client Tools and click Next to continue.
Figure 9: Installation Definition
This screen allows you to select a "Default Instance" or to manually define a "Named Instance". For this installation, we will be accepting the Default Instance. Click Next to continue.
Figure 10: Instance Name
This screen allows you to select which SQL Server components (options) should be installed. The default is a "Typical" installation. With a typical installation, all default options are taken and the installation process jumps to the dialog seen in Screen 13 - (Services Accounts). All components will be selected in a typical installation with the exception of development tools (header files, APIs) and code examples. With a "Minimal' installation, only client access components (ODBC, etc.) and the SQL Server core services are selected. None of the management tools (or even Books Online) is selected.
When I install SQL Server for development purposes, I typically want everything. For this reason, I choose the Custom option. Click Next to continue.
Figure 11: Setup Type
If you selected the "Custom" installation type in the previous screen, the installation process will present you with the "Select Components" dialog. There are three Component options you should select to ensure you are getting all of the code examples, development libraries and header files. The three components that include sub-components NOT selected by default are:
After selecting all components (and sub-components), click Next to continue.
Figure 12: Select Components
The following dialog will only appear on Windows NT 4.0 and Windows 2000 systems. Windows9x does not support the concept of operating system services and will only run SQL Server in the context of the logged-in user. With Windows NT 4.0 and Windows 2000, however, SQL Server is run as a background service. No user has to actually be logged in to enable SQL Server services to be running. Even services, however, need to log in to the O/S and that is what this screen is asking for.
For Windows NT and 2000, the "Local System" account is understood to mean the actual operating system itself. Using the local system account will allow SQL Server to always work. (No log in issues should ever be encountered.) The local system account, however, does not have access rights to networking services.
This will be a problem if you want to access with this SQL Server instance on your network. If you will require access to this SQL Server instance from a network, you will need to supply a user account (and password) for the SQL Services to run under. By default, the installation process chooses the user who is performing the install. That is the selection I will use for this installation. If this were a production installation, however, you will want to ensure that the services are run under a special account for the sole purpose of running SQL Server. If you need to, you can use User Manager to create this special user. Here are the requirements to keep in mind when creating this special user:
Choose a name that reflects what the account will be used for. I like to use SQLService
The user should be a member of the local administrator's group on the computer. Although this is not a strict requirement, it does make things much easier. If this will be a problem with security in your environment, Books Online contains information about which privileges need to be granted to the service account. Search for "Setting up Windows Services Accounts".
The following options should be configured when creating the new service user account:
Click Next to continue.
Figure 13: Services Accounts
After entering a valid login option for the services account, you are now asked on how SQL Server will authenticate database users. Although this dialog will appear on all Windows platforms, Windows9x does not support "Windows Authentication". For Windows NT 4.0 and Windows 2000, the "Windows Authentication Mode" is selected by default.
There are two modes for SQL Server authentication. In "Windows Authentication Mode", SQL Server depends on the Windows operating system to create and manage database users. You can also configure SQL Server to use "Mixed Mode". In this mode, SQL Server can use both Windows operating system accounts as well as accounts created directly within SQL Server.
Select Mixed Mode and supply a password for the sa account. When finished, click Next to continue.
Figure 14: Authentication Mode
This is an extremely important screen as it defines the character set and sort order by default for databases. For the purpose of this example, I will simply select the default option. Click Next to continue.
Figure 15: Authentication Mode
The "Network Libraries" screen allows you to select which networking protocols you want to support for this SQL Server instance. Unless you have specific reasons, there should be no reason to change anything on this screen. By default, the installation process selects Named Pipes and TCP/IP as the only networking libraries that clients can use to talk to this instance. Under Windows9x, only TCP/IP is selected by default. Click Next to continue.
Figure 16: Network Libraries
Click Next to continue to start the installation process.
Figure 17: Start Copying Files
Figure 18: Installation Process
Click Finish to complete the installation.
Figure 19: Setup Complete
When the installation process is complete, SQL Server will have installed all necessary files, created several new services, and updated your registry. Within the new SQL Server instance, you will also have several pre-created databases.
By default, SQL Server creates six databases:
Using Enterprise Manager, open the master or Northwind database and look around at the database objects. For a detailed look at what these database are used for, see my article entitled Default SQL Server Databases.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Tuesday, 12-Mar-2013 00:54:39 EDT
Page Count: 7703