Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 2

Installing SQL Server 6.5


On Day 1, "Introduction to SQL Server 6.5 and Relational Databases," you were introduced to the world of relational databases and Microsoft SQL Server. Today you focus on the requirements and process for the installation of SQL Server 6.5, as well as the initial configuration. You learn what components were installed by default. You also learn some installation troubleshooting. You will finally be presented with some questions and answers that reflect real-world situations. Please read the entire chapter before you begin your installation process, as there are many pieces of the installation puzzle that need to be explained before you begin your installation.

SQL Server Requirements

Before you install SQL Server, you should be aware of the licensing options, as well as the security, hardware, and software requirements. This section describes these options and requirements. Knowledge of the available file systems in the Windows NT environment can add fault-tolerance to your SQL Server and will be covered here as well.

Licensing Option

SQL Server ships in two different package configurations. Currently there is an enterprise and a workstation version. The workstation edition includes the PDK (Program Developers Kit). Both configurations come on CD-ROM and include all of the tools and utilities as well as the SQL Server Books Online. Day 3, "SQL Server Tools and Utilities," covers what utilities and tools are installed with SQL Server.

The only significant difference between the two packages is the licensing option available. In the workstation version, you are automatically allocated 15 connections and cannot change this. With the enterprise version, you can install as many connections as you have paid for.

Security Options

In this section, you learn a little bit about SQL Server security as your security choice may affect the machine on which you install SQL Server. SQL Server security will be covered in much more detail in Day 6, "SQL Server Login and User Security."

SQL Server has three different security options to choose from. The options are Integrated, Standard, and Mixed. Based on your enterprise environment, you need to choose the appropriate security option. To determine which security option is right for your enterprise, you need to ask the question, "Where does SQL Server get information about user accounts?" There are three answers: from the Windows NT SAM database, from SQL Server's own internal database, or from both.

In an integrated security environment, SQL Server uses Windows NT's SAM (Security Accounts Manager) database for validation of SQL Server logins. This means that user accounts must be accessible to SQL Server. To implement this, you must be using either the named-pipes or multiprotocol network protocols. SQL Server will also need access to the Windows NT SAM database, either locally or through a trust relationship. This type of security simplifies access for users because they only need to log on to Windows NT in order to gain access to the SQL Server.


NOTE: The Windows NT Security Accounts Manager database is often referred to as the directory database.

In a standard security environment, SQL Server will use its own database table (syslogins) to verify logins. There are no special network protocols needed for users to access the SQL Server, just the appropriate SQL Server login ID and password. This type of security can be useful in an environment where SQL Server does not have access to a Windows NT SAM database, or where users are getting their login credentials from a different security provider, such as Novell NetWare.

In a mixed security environment, SQL Server will first check for a mapped login ID from the NT SAM database; if it finds one, you will be logged in with that ID. If there is no match, SQL Server will validate the login ID and password that you specify. This type of security is helpful in environments where there are several different security providers.

Hardware and Software Options

As with most Microsoft products, the newer and bigger the hardware and software you have, the better your system will perform. SQL Server can be installed on any platform that is supported by Windows NT. At the time this book was written, the current platforms included Intel, DEC Alpha, MIPS, and the Power PC. This book will focus on the Intel platform, but most of the information covered here is transferable to the other platforms.


WARNING: As of this writing, Microsoft has announced that it will no longer support MIPS- and PPC-based computer systems.

Hardware plays a major role in your SQL Server environment. SQL Server requires at least a 486 CPU or later, 16MB of RAM on the Windows NT machine, and 60MB of hard disk space. If you will be using SQL Server replication (see Day 19, "Data Distribution (Replication)," for more information) you must have 32MB of RAM installed on the Windows NT machine with 16MB designated for use by the SQL Server itself. It's strongly recommended that you install the SQL Server Books Online somewhere in your enterprise. Books Online requires an additional 15MB of hard disk space for a complete installation. If you prefer, you can install Books Online to run off of the CD-ROM. While this option is a little less convenient, it can save some hard disk space. If you run from the CD-ROM, only 1MB of additional hard disk space is required.

SQL Server requires the Windows NT operating system (either Workstation or Server) version 3.51 or later. All of the screen shots from this chapter were taken on a Windows NT Server 4.0 (with Service Pack 3 installed) running on a Pentium 166MHz with 128MB RAM and 4GB of hard disk space.

SQL Server can be installed on FAT (Microsoft DOS's File Allocation Table) or NTFS (Windows NT File System) partitions. You should use the NTFS file system for both fault tolerance and the ability to implement file level security. Another advantage of NTFS is that you can set up a software implementation of a RAID 5 system, which includes disk striping with parity.

Table 2.1 summarizes the hardware and software requirements for SQL Server 6.5.

Table 2.1. SQL Server 6.5 hardware and software requirements.

Components Minimum Requirements
Platform Intel 486 or higher, PowerPC, MIPS, DEC Alpha
RAM 16MB, 32MB for replication
Operating System Windows NT 3.51 or later
File System FAT or NTFS
Hard Disk 60MB, 75MB with SQL Server Books Online

File System Options

SQL Server can be run on either a FAT or an NTFS partition. In choosing a file system, performance is probably not the primary issue as there is no significant performance difference between FAT and NTFS. You can have a greater impact on performance in other areas. Typically, NTFS is chosen as the file system for SQL Server for its increased fault tolerance, transaction tracking, hot fixing, and file-level security.

More important than the file system itself is the disk subsystem that you choose. A hardware implementation of a RAID 5 disk subsystem will give you great fault tolerance.

RAID 5

New Term: RAID 5 subsystems are created using disk striping with parity across multiple hard disks. This implementation can be accomplished using specially designed RAID 5 hardware, or through the use of software. Striping with parity means that information is written across multiple disk partitions (and/or hard disks) in a stripe set. As each partition is crossed, a unique parity checksum is created from the information stored on that particular section of the stripe. The checksums are accurate to 1 in 4 billion. In other words, if you have to rebuild information that was contained on that stripe, you have 1 chance in 4 billion that it is wrong. Striping with parity is covered in more detail in Day 8, "Backing up and Restoring."

A RAID 5 system can monitor the disks for failure and, if the failure of a hard disk is found, the information on the hard disk can be rebuilt using the parity checksums. Once you have isolated the disk that is no longer working, you simply replace the bad disk with a new one and the information that was stored on the disk can be rebuilt.

RAID 10 subsystems can be built also. A RAID 10 subsystem is the same as a RAID 5 subsystem, but in addition to the disk striping with parity, you mirror your stripe sets on another set of hard disks. This is an expensive option, but it virtually guarantees no down time for your SQL Server.

Both of these systems are expensive as they require new hardware, but there is a cheaper alternative. Windows NT allows you to create your own software implementation of RAID 5. To create a RAID 5 subsystem in Windows NT, you create stripe sets on NTFS partitions. These NTFS partitions can be on a single hard disk, or across multiple hard disks. You should create your stripe sets before you install and run SQL Server.



NOTE: You cannot boot Windows NT from a software implementation of RAID 5. If you want your boot partition to be fault tolerant, you should mirror that partition.

Using Windows NT's striping with parity will place increased demands on your CPU and memory.


NT Server Options

With all of these options in mind (security, fault-tolerance, licensing, and so on), which NT Server should you install SQL Server onto? In a single-domain environment, the PDC and BDC (Primary/Backup Domain Controller) are busy with authentication and replication tasks and are not the best choices for SQL Server. In this situation, your best bet is to install SQL Server on a machine that is not busy running other services. The more memory and CPU time you give SQL Server, the better it will perform.

In you are planning on running SQL Server in a multi-domain environment, you should install SQL Server on an NT Server that is not a domain controller, but a machine that does have access to trusted domain accounts. If you do not, you will only be able to use standard security (or the standard portion of mixed security) for authentication. This means that your users will not only have to log in to Windows NT, but they will also have to supply another set of credentials to log in to SQL Server.

Installing SQL Server 6.5

Now that you have decided on your Windows NT operating system and its role in your environment, hardware, file system, disk subsystem, and security, what do you do next? You need to run the Setup program. If you are using the SQL Server 6.5 CD-ROM, navigate to the appropriate directory and double-click the SETUP.EXE program. For Intel users, this is the \i386 directory. For Alpha-AXP users this is the \Alpha directory. MIPS and PowerPC users will find their setup programs in the \MIPS and \PPC directories respectively.

On the BackOffice CD-ROMs, use CD-ROM number 2 and navigate to the SQL65 directory. The SETUP.EXE program on the BackOffice CD is hidden. To view this file, from Windows Explorer, select View | Options. Select the View tab and then select the Show all Files option as shown in Figure 2.1. When you are finished, click OK. You should now be able to view the hidden files.

Figure 2.1. The Options dialog box can be used to view hidden files.

Double-click SETUP.EXE icon to initiate the setup process. You are first greeted by a welcome screen. Click Continue to proceed with the installation. You will now be asked for your Name, Company Name, and Product ID number. Only the Name is required, but you should provide the Company Name and Product ID for future reference. (See Figures 2.2 and 2.3.)

Figure 2.2. SQL Server Welcome screen.

You are presented with the SQL Server 6.5 Options dialog box (see Figure 2.4). If SQL Server 6.5 is not currently installed, this dialog box will allow you to choose one of three options: Install SQL Server and Utilities, Upgrade SQL Server, or Install Utilities Only. You should install the complete SQL Server package including the SQL Server utilities, so choose the Install SQL Server and Utilities option. Click Continue.

Figure 2.3. Name, Company Name, and Product ID.

Figure 2.4. SQL Server options.

You should now see the Licensing dialog box (see Figure 2.5). SQL Server licensing is the same as that used by Windows NT. Your choices are Per Server and Per Seat. With the Per Server licensing option, you must purchase a separate client access license for each client who wishes to log into a particular SQL Server. If you add another SQL Server in your enterprise, you must purchase a separate client license for each client who wants to access the new SQL Server. The Per Seat configuration, on the other hand, allows you to purchase a single client access license that gives you access to any SQL Server in your enterprise that has a licensing mode of Per Seat. Make your selection and click Continue.

Figure 2.5. SQL Server licensing.


NOTE: Microsoft SQL Server allows a one-time, one-way upgrade from a Per Server to a Per Seat licensing option.

Depending on what platform and operating system you are installing SQL Server onto, Figure 2.5 may look a little different. In other words, the same information will be displayed to you, but the dialog box itself may look different than what is shown in Figure 2.5.


You should now be looking at the SQL Server Installation Path (see Figure 2.6). This is where you can specify on which drive and directory to install SQL Server. SQL Server will install on the first drive off the root that has sufficient hard disk space. SQL Server will also install into the \MSSQL folder by default. Click Continue.

Figure 2.6. Installation drive and directory.

You are now presented with the Master Device Creation dialog box (see Figure 2.7). The master device is often referred to as the master database device. A device is simply a file in the SQL Server directory structure that is used to pre-allocate disk space. Devices are used to store databases, transaction logs, and backups of databases and transaction logs. You will learn more about databases and devices on Day 4, "Devices and Databases."

By default, the master device is 25MB and is created in the \MSSQL\Data folder with a filename of MASTER.DAT. 25MB is usually enough space for most SQL Server installations. I recommend that you make the device a bit larger if you plan on having a large number of users and databases in your SQL Server. 30-35MB should be sufficient. Click Continue when you are ready.

You are now presented with the Books Online screen (see Figure 2.8). This dialog box has options that include: Install on Hard Disk, Install to Run from CD, and Do not Install. The Books Online are an invaluable resource and should be installed somewhere in your enterprise. Day 3 provides more details about the Books Online. In Figure 2.8, the Books Online will be installed and copied to hard disk. Click Continue.

Figure 2.7. Master Database Device options.

Figure 2.8. SQL Server Books Online.

The Installation Options dialog box is now presented (see Figure 2.9). You can select a character set, sort order, network support options, and whether or not you want the SQL Server and the SQL Executive services to start automatically. Click Sets to view the Select Character Set dialog box.

Figure 2.9. Character Set, Sort Order, and Additional Network Support options.

You are now presented with the Select Character Set dialog box (see Figure 2.10). The character set, sometimes called a code page, and the sort order are the most important decisions you will make during your installation. Both of these choices impact the compatibility and performance of your SQL Server.

Figure 2.10. Character sets.



WARNING: If you change your character set or sort order after you have installed SQL Server, you will have to rebuild all of your indexes and reload all of your data. In essence, you will have to bulk copy all of your data out of SQL Server, change your sort order or character sets, rebuild all of your tables, stored procedures, and so on, and then reload your data.

When you select a character set, you are telling SQL Server which characters to recognize. Each character set is made up of 256 characters. The first 128 characters are the same throughout the various code pages. The last 128 characters, referred to as the extended characters, are different for each code page. The top three choices are ISO Character Set (Default), 850 Multilingual, and 437 U.S. English.

The ISO Character Set is the default character set and is the ANSI standard. This character set is useful for companies that do business in the Americas as it adds support for the most commonly used characters for English, Spanish, French, Portuguese, and German. It is also compatible with other platforms such as UNIX.

The 850 Multilingual character set is also a good choice for those doing business in the Americas. This was the default character set used in SQL Server 4.2x.

Code page 437 U.S. English is useful in the United States and is fairly popular.

The ISO Character Set has been chosen. Click OK to return to the Installation Options dialog box and make choices about sort orders and network libraries. Click Orders (see Figure 2.9) to make a sort order selection. You are now presented with the sort order choices (see Figure 2.11).

Your sort orders are dependent upon which character set you chose. Choosing the appropriate sort order can impact the performance of your SQL Server. For example, if you choose a case-sensitive sort order, all of your table names must be referred from your queries using proper case. This would include any front-end tools that access SQL Server as well. Data must be compared and sorted based on the binary value of the information when you use a case-sensitive sort order. This means that a capital "A" has a different value than a lowercase "a" and will be sorted appropriately. A case-sensitive sort order can be useful with foreign languages that use special characters like the French letter â or the German letter ö.

Figure 2.11. Sort orders.

For most English speaking installations, you should use Dictionary Order, Case-Insensitive. This sorts your information in the same manner as you would expect to find in a dictionary--punctuation-type characters first (!,@,#,$,%,^, and so on), numbers, and then the alphabet. This simple alphabetizing can save a lot of time and CPU cycles.

For more information on sort orders, press F1 while in this dialog box.

I will select the Dictionary Order, Case-Insensitive. Click OK to return to the Installation Options dialog box. Click Networks. You see a dialog box similar to Figure 2.12. As you can see, SQL Server supports many protocols for client/server communications.

Figure 2.12. Network protocols.

Selections you make here dictate what type of protocols your SQL Server clients may use to access SQL Server. Remember that to support integrated security, you must choose Named Pipes (the default) and/or Multi-Protocol. Although Named Pipes is listed here as an option, you cannot deselect it because SQL Server uses Named Pipes for installation. If you try to remove it, you get an error message.

When using your Named Pipes, SQL Server will be accessed through \\computer_name\pipe\sql\query. Multi-Protocol, as its name suggests, supports communications over any or all open IPC mechanisms simultaneously. The multi-protocol takes advantage of Remote Procedure Calls (RPCs) to pass information between client and server and is therefore a bit slower than through a Named Pipe.

There are also options to support NWLink for Novell's IPX/SPX support, TCP/IP using Windows Sockets, Banyan VINES SPP for use with Banyan's StreeTalk, and AppleTalk ADSP for use with Apple computers. There is also support for Digital's DECNet Sockets. Click OK to return to the Installation Options dialog box.

The last two options in the Installation Options screen are Auto Start SQL Server and Auto Start SQL Executive at boot time. When you select these options, you are telling Windows NT to start these services every time the NT Server is booted. Select these two checkboxes and click Continue.

You are now presented with the SQL Executive Log On Account dialog box (see Figure 2.13). In this dialog box you can configure the logon account that the SQL Executive service should use. By default, it is the same as your current Windows NT logon. As you can see in Figure 2.13, my account is trying to log on to the GizInst computer with the account of SQLExecAcct.

Figure 2.13. Specify a SQL Executive logon account.

You should create a special service account for the SQL Executive to log on with. Create this account in the User Manager for Domains. This account should be used exclusively by the SQL Executive for authentication. This will help ensure that the permissions and rights for this account will not be altered, and that the account will always be available to the SQL Executive service.

When you create the SQL Executive account, you should specify the following character-istics.

Click Continue. The file copy process will now proceed. Following the copy process, the SQL Server setup will create the master device as well as the other required devices and databases, and entries will be made in the Windows NT Registry. The copy process takes anywhere from 5 minutes to as long as 45 minutes, depending on the hardware you are using.

Once the setup process has completed, you will receive a final dialog box with a Return to Windows NT choice. Click Return to Windows NT and begin using your SQL Server.

Starting and Stopping the SQL Server and SQL Executive Services

SQL Server has been tightly integrated with Windows NT. The MSSQLServer (the database engine) and SQLExecutive (a suite of management utilities) run as Windows NT services. With this integration, you can start and stop the SQL services from a variety of locations including the SQL Service Manager, SQL Enterprise Manager, the Control Panel/Services window, and the COMMAND prompt.

SQL Service Manager

Using the SQL Service Manager is one of the simplest methods of starting and stopping the SQL Server (MSSQLServer) and the SQL Executive (SQLExecutive) services.

Start the SQL Service Manager by selecting Start | Programs | Microsoft SQL Server 6.5 | SQL Service Manager. You will be presented with a dialog box similar to Figure 2.14. To start, stop, or pause a service, select the service from the Services listbox, and then double-click the appropriate light. To start the MSSQLServer, double-click the green light. To stop the MSSQLServer, double-click the red light.

You can start, stop, and pause the MSSQLServer, but you can only start and stop the MSDTC (Microsoft Distributed Transaction Coordinator) and the SQLExecutive service. If you pause the MSSQLServer, you are essentially allowing the server to continue processing for all logged-in users. New connections to SQL Server, however, are denied. When a user logs out of SQL Server, he is not allowed to log back in.

Figure 2.14. The SQL Server Service Manager.



NOTE: When you exit SQL Server by closing the Enterprise Manager, closing an ISQL or ISQL/w window, or otherwise, your connection is automatically terminated and you are logged out. There is no "logoff" procedure.

SQL Enterprise Manager

You can use the SQL Enterprise Manager to start and stop the SQL Server services as well. Start the SQL Enterprise Manager by selecting Start | Programs | Microsoft SQL Server 6.5 | SQL Enterprise Manager.

You must have a SQL Server registered in Enterprise Manager in order to administrate it with this tool. You learn how to do this a little later in the chapter.

To start the MSSQLServer service, choose a registered server and right-click it. From the context menu, you will be able to start, stop, or pause the service. In Figure 2.15, the SQL Server is paused.

Figure 2.15. Pausing SQL Server from the SQL Enterprise Manager.

If you right-click the SQL Executive icon, you can choose to start or stop this service as well.

Control Panel/Services

You can use the Control Panel/Services dialog box (see Figure 2.16) to start and stop the SQL Server services. To use the this tool, select Start | Settings | Control Panel. From the control panel window, double-click the Services icon. From here, you simply click the service you want to work with and then click the appropriate button.

There is an option box at the bottom of the dialog box where you can specify startup options. If you click the Startup button, you will be presented with the Service dialog box (see Figure 2.17). From here you can specify how the service starts up, whether it is automatic or manual. You can also specify the logon account the service will use. This is especially useful if you want to change the account that you used in the Setup procedure. Click OK to dismiss the Service dialog box. Click Close to close the Services dialog box.

Figure 2.16. The Control Panel/Services dialog box.

Figure 2.17. The Service dialog box.

Command Prompt

You can use the NET START and NET STOP commands to start and stop the SQL Server services from a command prompt. To do this, click Start | Run. In the Run dialog box enter CMD, and then click OK. You will now be at a command prompt.

To start the MSSQLServer service, enter the following:

NET START MSSQLServer

To start the SQLExecutive service, enter the following:

NET START SQLExecutive

To stop either of these services, use the NET STOP <service name> syntax.


TIP: You can view what services are currently running, and which computers are visible on the network, by running the NET START command from the command prompt.

You can find out more about the NET XXXX statements by running a NET USE /? statement.


SQL Server as an Application

You can run SQL Server as an application rather than a Windows NT service. When you do this, you limit SQL Server's integration with Windows NT. For example, error messages that would normally be sent to the Windows NT Event log will now be shown in message boxes on the dialog box.

To start SQL Server as an application, you run SQLSERVR from the command prompt. This can be useful if you are having problems with SQL Server running as a Windows NT service.

SQL Server Is Installed--What Did You Get?

If you did not receive error messages during your installation, SQL Server has most likely been installed properly. If you did get error messages, you should take a look at the troubleshooting section later in this chapter.

Now that SQL Server is installed, what did you get? Today, you look at the following components:

On Day 3 you look at the rest of the installed components and utilities.

Installation Folders

Table 2.2 is a listing of the following components that are created and the folders in which they are stored.

Table 2.2. SQL Server folders.

Components Folder
Default folder to store database and transaction log backups \MSSQL\Backup
16-bit client executables, utilities, and .DLL files \MSSQL\Bin
32-bit client executables, utilities, and .DLL files \MSSQL\Binn
Files used to handle character sets and sort orders \MSSQL\Charsets
Default folder used to store database devices \MSSQL\Data
Installation scripts used to install portions of SQL Server; this folder is also used to store output files created during installation \MSSQL\Install
Folder used to store error logs \MSSQL\Log
Folder used by SQL Server replication \MSSQL\ReplData
Folder containing files used by the Simple Network Management Protocol (SNMP) agent \MSSQL\SNMP
Folder containing symbol files that are useful for debugging \MSSQL\SQL60\Symbols
Folder that may or may not contain sample files used with SQLOLE \MSSQL\SQLOLE\Sample

Windows NT Registry Entries

When SQL Server is installed, entries are added to the Windows NT Registry database. There will be entries added to the Software key as well as to the Services key.

To view these entries, select Start | Run. From the Run window, enter REGEDT32 and click OK. This will start the Windows NT Registry Editor. In the Registry Editor, select the window titled HKEY_LOCAL_MACHINE. Double-click the folders to drill down to the key HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer as shown in Figure 2.18.

Figure 2.18. The Windows NT Registry.

Because the SQL Server and SQL Executive run as Windows NT Services, you can find out more information about these keys in the following folders:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLExecutive

SQL Enterprise Manager: A First Glimpse

In this section, you learn a little bit about the SQL Enterprise Manager utility. This tool is used to graphically manage SQL Servers throughout your enterprise. The SQL Enterprise Manager is a large and robust application and, as such, different components of the Enterprise Manager will be discussed throughout the rest of this book.

Before you can graphically manage a server, it must first be registered in the Enterprise Manager. In this section you concentrate on registering a server and creating server groups.

Registering a SQL Server

To register a SQL Server in the Enterprise Manager means that you are going to supply the MSSQLServer engine a login ID and a valid password. Further, you are telling the Enterprise Manager to maintain this information for you so that each time you use the Enterprise Manager and connect to a SQL Server, you do not need to specify these login credentials.

Start the SQL Enterprise Manager by selecting Start | Programs | Microsoft SQL Server 6.5 | SQL Enterprise Manager. The first time you start the Enterprise Manager, you will be asked to register your server as shown in Figure 2.19. You need to enter the Server Name (computer name) of the server where SQL Server is installed. In this implementation, SQL Server is installed on the Gizmo computer. If you are unsure what your computer name is, you can check it in the Control Panel/Networks.

You should specify either a trusted connection or a standard connection. Since we are using Standard security (the default security for a new SQL Server installation), you should select the Standard security option. You also need to specify a login ID and password. Use the SA login ID. This is for the SQL Administrator. This ID is the superuser in your SQL Server environment. There are no restrictions on what the SA can do in your SQL Server. This is also one of the default IDs that are installed with SQL Server. There is no password for the SA account yet. You will add a password to the SA account a little later in this chapter.

Once you have filled in the appropriate information, click Register to register your SQL Server.

Figure 2.19. Register a SQL Server.


WARNING: If your SQL Server is not running, you will receive a warning message when you attempt to register it. The error message specifies that this SQL Server cannot be located on the network. Register anyway? You can still register the SQL Server by clicking Yes from the warning box. Once registered, you can then start your SQL Server using the Enterprise Manager as discussed earlier in this chapter.

You will also receive a warning if you supply invalid login credentials. In this case, you should not register that SQL Server, but correct your login credentials and then try to register the SQL Server again.


Server Groups

Every server you register will fall into the default server group of SQL 6.5. Server groups are used to logically organize SQL Servers into user-defined groups. These groups can mirror the business relationships in your company. For example, you might create a server group for Accounting and then place all of the Accounting SQL Servers in that group.

To create a new server group, right-click the SQL 6.5 icon in your Enterprise Manager and select New Server Group from the context menu. You can now add a new server group as shown in Figure 2.20. You can specify whether or not this server group is a top-level group (like the SQL 6.5 group) or a subgroup. In Figure 2.20, I have added Widget as a top-level group.

Figure 2.20. Create a server group.

Now that the server group has been added, you can register new servers in this group by following the steps outlined previously in "Registering a SQL Server." You will note that at the bottom of the Register Server dialog box, there is a group option. Simply select the group you want to place your new server in, fill out the rest of the necessary information, and click Register.

Once you have servers registered in the Enterprise Manager, you can use drag-and-drop to move servers from one group to another. Simply select the server you wish to move and drag it to a new server group.


TIP: The server groups you create are specific to an individual machine. If you would like to have the same groups show up on different machines in your enterprise, you must set them up in the same fashion at each machine.

SQL Server Default Devices

As you can see in Figure 2.21, SQL Server has three default devices: master, MSDBData, and MSDBLog. Devices are simply files the on hard disk that are used to pre-allocate space for databases and transaction logs. Backup devices serve the same purpose, but are used to store dumps (backups) of transaction logs and databases. Note that dump devices are 0 bytes long when they are first created and will dynamically resize themselves as you add data to them. Database devices, on the other hand, need to have a size specified when you create them. You will learn more about devices on Day 4.

Figure 2.21. Default databases, devices, and login IDs.

The master database device stores the master, model, tempdb, and pubs databases that are shown in the databases folder in Figure 2.21. MSDBData and MSDBLog are used to store the msdb data database and its corresponding transaction log.

You might have noticed that there is a backup device called diskdump. This is a special type of backup device that is used to perform NULL backups. You learn more about performing backups on Day 8.

SQL Server Default Databases

The default databases installed in SQL Server are shown in Figure 2.21. These are master, model, msdb, pubs, and tempdb.

master Database

The master database is used by SQL Server to handle the operation of the SQL Server database. The master database is used to track login IDs and passwords, databases that are created, and which portion of each database resides on a particular device. It is also used to track devices, their size and location, system messages, system-stored procedures, and active locks, as well as the location of transaction logs.

The master database resides on the master database device that is roughly 17MB in size. Following is a list of a few of the more useful tables in the master database.

model Database

The model database is used as a template for creating all new user databases in SQL Server. It has default tables preloaded into it and is roughly 500KB in size and resides on the master database device. Once you have added new tables or other information to this database, new databases that you create will have this information in them as well. For example, if you added a guest account to the model database, every new database you create will automatically have that guest account in it.

Here are a few of the more important tables in the model database.

The msdb Database

The msdb database is used by the SQL Executive suite of utilities. These utilities are used to run and execute tasks, alerts, and automatic backups, as well as database replication.

The msdb database has a database that resides on the MSDBData device and a transaction log that resides on its own MSDBLog device. Having your transaction log separated from your database is very useful and can make your database more fault-tolerant and perform more quickly. Creating and manipulating databases and transaction logs will be discussed on Day 4.

The pubs Database

The pubs database is a sample database that Microsoft provides to you as a learning tool. Throughout this book you will be presented with exercises and sample code that you can run against the pubs database. If you wish to restore your pubs database to its original pristine condition, you can run the INSTPUBS.SQL script located in the \MSSQL\Install folder. You will learn how to run scripts in SQL Server on Day 3 in the ISQL/w section.

The pubs database is 3MB in size and resides on the master database device. The database itself simulates a book publishing company's day-to-day activities and has the following user-defined tables.
authors discounts employee
jobs pub_info publishers
roysched sales stores
titleauthor titles

The tempdb Database

The tempdb database is 2MB by default and resides on the master database device. It is used for ad hoc queries and as a temporary working storage locations for things like sorting. This is a shared workspace used by all of the databases and users within the SQL Server.

This database is destroyed and re-created every time SQL Server is stopped and restarted.

The tempdb database can be placed in RAM, which may improve the performance of SQL Server. This topic is discussed on Day 20, "Performance Tuning and Optimization."

SQL Server Default Login IDs

The default login IDs for SQL Server are in the Logins folder as shown in Figure 2.21.

The sa Password

For security reasons, you should modify the sa password as soon as possible. The sa ID has no restrictions on what it can do in your SQL Server. To change the password, start the SQL Enterprise Manager, if you have not already. Expand your server and the Logins folder as shown in Figure 2.21. Double-click the sa ID. This will bring up the Manage Logins window shown in Figure 2.22.

Figure 2.22. Manage logins.

Type your new password in the Password field and then click Modify. You will be presented with another dialog box to confirm your new password. Enter the same password in the Confirm New Password field and click OK. The password has now been changed. Click Close to dismiss the Manage Logins window.


NOTE: Depending on what sort order you chose during your installation, your passwords may be case-sensitive.

Troubleshooting Your Installation

Troubleshooting SQL Server installations is a relatively straight forward task as there is not a whole lot that can go wrong. In this section, you will learn how to find the *.OUT files, view the NT Application Log, view the SQL Server Error Log, and validate the Named Pipes protocol.

*.OUT Files

You can search for *.OUT files in the \MSSQL\Install folder. *.OUT files will have information pertaining to a particular event that the SQL Server was trying to carry out. These events are not necessarily errors. For example, I have looked through the \MSSQL\Install folder on my system and have found a file called InstMSDB.OUT. When I open it with Notepad (see Figure 2.23), I can see that this is the output from the INSTMSDB.SQL script that creates the MSDB database during setup.

Figure 2.23. InstMSDB.OUT.

NT Application Log

SQL Server will log errors as well as events (startup and shutdown of SQL Server) to the Windows NT Application Log. To use the Windows NT Event Viewer, click Start | Programs | Administrative Tools | Event Viewer. This starts the NT Event Viewer (see Figure 2.24). The Event Viewer displays information from three different logs. To change to the Application Log where SQL Server events are found, select Application Log from the Log menu.

Figure 2.24. Windows NT Event Viewer.

If you double-click an item in the list, the Event Detail window will appear (see Figure 2.24) giving you more detailed information about an event.

You may see different colored icons in the Event Viewer. Blue icons are informational messages. Yellow icons are warnings. Red icons are errors that have occurred. You can do some troubleshooting by looking for red icons with a source of MSSQLServer or SQLExecutive.

SQL Server Error Log

The SQL Server Error Log receives audit and error information from the SQL Server. This information is stored in a file called ERRORLOG.x where x is a number from blank to 6. The Error Logs are stored in the \MSSQL\Log folder and use a FIFO (First In, First Out) system. For example, when you start SQL Server, a new ERRORLOG. is created. The old ERRORLOG. is renamed to ERRORLOG.1 and ERRORLOG.1 is renamed to ERRORLOG.2 and so forth in a six log rotation.

You can view the Error Logs with a text editor, such as Notepad or Microsoft Word, or you can use the SQL Server Error Log utility. To use the SQL Server Error Log utility, start SQL Enterprise Manager. Attach to the SQL Server you wish to view error logs for. You can do this by double-clicking that server (a red zigzag will appear next to the server icon when you are connected). Select Error Log from the Server menu. You should see something similar to Figure 2.25.

Figure 2.25. SQL Server Error Log.

You can select the different Error Logs available from the Error Log listbox. The button to the right of the listbox has two arrows chasing each other in a circle is used to "refresh" the data in the Error Log.

Named Pipes

Named Pipes is the default protocol for SQL Server. It is also used for the installation of SQL Server. If you are having problems connecting a client to SQL Server, you can try testing the Named Pipes protocol with the MAKEPIPE and READPIPE utilities.

To use these utilities, follow these steps:

1. Click Start | Run.

2. From the Run dialog box, enter CMD and click OK. This opens a command prompt.

3. From the command prompt, enter
MAKEPIPE

You should now see:

Making PIPE: \\.\pipe\abc
read to write delay <seconds>:0
Waiting for Client to Connect. . .

On a client machine, or in a different command window on the same server, send information through the pipe using READPIPE /S<servername> /D<text string>. In the following example, I will be connecting to the Gizmo server and sending "Hello World" through the pipe.

READPIPE /Sgizmo /D"Hello World"

Output on the client machine looks like this:

SvrName: \\Gizmo
PIPE: :\\Gizmo\pipe\abc
DATA: :Hello World
Data Sent: 1 Hello World
Data Read: 1 Hello World

Output on the server machine looks like this:

Waiting for Client to Send. . . 1
Data Read:
Hello World
Waiting for Client to Send. . . 2
Pipe closed
Waiting for Client to Connect. . .

Client Software

The type of client you are using determines which tools and utilities are installed when you run the SQL Server setup program, as well as where information about those tools and utilities are going to be stored. There is support for MS-DOS, 16-bit Windows, and 32-bit Windows clients.

MS-DOS Clients

When you install software for MS-DOS-based clients, you must do all of the work by hand. This means that you must create your own folder on the DOS machine to store your SQL Server information. For example, you might choose to create a directory called c:\MSSQL\DOS.

Once you have done this, you need to manually copy all of the files from the \Clients\MSDOS folder on your SQL Server CD-ROM.

Since MS-DOS can only have one network protocol loaded at a time, you should disable any network protocols you have running and install the appropriate SQL Server protocol. To do this you need to change the comments the appropriate lines in your AUTOEXEC.BAT file on your MS-DOS machine. You would then add your protocol. If you are running Named Pipes, you can use the DBMSPIPE.EXE. For Novell's IPX/SPX protocol, you would load the DBMSSPX.EXE protocol, and for Banyan VINES's StreeTalk, you would load the DBMSVINE.EXE protocol.

When you are finished, reboot the MS-DOS machine so that the new protocols are loaded.

MS-DOS clients get only the following tools and utilities.


NOTE: The tools and utilities mentioned here are discussed in more detail on Day 3.

16-Bit Windows Clients

To install the client utilities for a 16-bit Windows client, you must browse to the \Clients\Win16 folder and run the SETUP.EXE program. The setup will create new folders on the client called \MSSQL\BIN and \MSSQL\Install. You can also add Books Online to a 16-bit Windows client. The setup program will also create the SQL Server Tools program group and add icons for the following 16-bit utilities. Client configuration options will also be added to your WIN.INI file.

If you are going to be using ODBC, you should also install the latest ODBC drivers on both the client and the server. Older ODBC drivers are located on the CD-ROM in the \Clients\Win16\ODBC folder. These files are dated and may not work properly with newer installations of SQL Server or new patches that have been applied for SQL Server and Windows NT. You can download the latest and greatest drivers from the Microsoft Web site at

http://www.microsoft.com/ODBC/

32-Bit Windows Clients

With a 32-bit Windows client, you can install all of the SQL Server utilities. To do this, use the same setup program you used to install SQL Server. From the options list, choose Install Utilities Only option (Figure 2.4). The setup will create a new \MSSQL\BINN folder and the \MSSQL\Install folder on your system, transfer the appropriate files, add entries to your Registry database, and install the DB-Library and Network library files into the appropriate system folder. All of the SQL Server utilities will be installed.

Summary

Whew! That was a lot of information to cover in one day. Here is a chance for you to refresh your memory on what material was covered today. You started out with a discussion on the SQL Server requirements. This included the available licensing options in SQL Server (Per Server or Per Seat). You then had a brief overview of how SQL Server handles login security and how it may affect your choice of installation machines. You were then presented with the hardware and software requirements for SQL Server. File systems are important in SQL Server, as they can add fault-tolerance and security. It is recommended that you use an NTFS partition for those reasons. More important than the file system itself is the disk subsystem. With the right disk subsystem, you can prevent virtually any disaster from corrupting your databases. This can be accomplished with RAID 5 and RAID 10 systems. The next section covered some of the NT Server types that are out there and how they might allow SQL Server to integrate into their environments.

The second major section of this chapter covered the installation process itself. This included the initial installation options as well as default directories and whether or not to install the SQL Server Books Online. You then got the most important dialog box in the installation process, the Installation Options dialog box. This is where you chose your character sets, sort orders, and network libraries. Character sets and sort orders were important in that a bad choice made here is not easily corrected. To change a character set or sort order after your databases have been created, you must drop all of your databases, re-create them, and reload your data. The network libraries are also important. Specifying a network library tells SQL Server what "network languages" to speak. If a client tries to use TCP/IP and SQL Server doesn't recognize it, the user will not have access to SQL Server. You also learned a little bit about the SQL Executive logon account. This account is used by the SQL Executive utilities to log in to SQL Server and perform tasks, alerts processing, backups, and database replication.

The third section of this chapter concentrated on starting and stopping SQL Server. As you learned, you can start and stop the SQL Server from the SQL Service Manager (the utility with the traffic signal), the SQL Enterprise Manager, Control Panel/Services as well as from the command line using the NET START and NET STOP statements.

The fourth section of this chapter dealt with describing what was installed onto your machine during the setup process. You learned about the default folders in \MSSQL as well as the Registry entries made in the HKEY_LOCAL_MACHINE hive. You also learned how to register a server and create server groups in the SQL Enterprise Manager. You finished that section off with a look at the default database devices, dump devices, databases, and login IDs that were created. You also learned how to alter the SA's password in the SQL Enterprise Manager.

The fifth section was devoted to some light troubleshooting of your installation process. You learned how to look for the *.OUT files stored in the \MSSQL\Install folder. You walked through a demonstration of the NT Event Viewer utility to examine the NT Application Log and look for MSSQLServer and SQLExecutive events. In the SQL Enterprise Manager, you learned how to look at the SQL Error logs. Remember that a new log is created every time SQL Server is stopped and re-started. The newer logs will replace the older logs in a six-log rotation. To finish that section, you learned how to make sure that you can establish communications across the network using the Named Pipe utilities makepipe and readpipe.

In the last section you learned a little bit about what utilities and tools are installed and how to install those client utilities on a variety of platforms. Remember that in MS-DOS, you must do all of the work yourself and that client information will be stored in the AUTOEXEC.BAT file. The 16-bit Windows environment is a little friendlier as it has its own setup program that installed the software, created program groups and icons, and added entries to the WIN.INI file. The 32-bit clients, as you recall, get all of the utilities installed and entries are made to the Windows Registry database for you.

Here are a few real-world questions and answers. Following that is the workshop where you will have some quiz questions to test your understanding of the material presented in this chapter. You will finish today with some exercises that can be used to reinforce what you have learned today.

Q&A

Q Can I install more than one SQL Server on a single machine?

A
The answer is yes, but it is not easy and it is not well documented. Microsoft would rather you didn't do this. Remember that SQL Server uses as many of the resources as you give it. The more resources SQL Server has, the better it performs. Dividing the resources between two SQL Servers, as well as Windows NT, is not recommended.

Q How do most people interact with SQL Server?


A
Most people? Let me reword that a little bit, most users will interact with SQL Server through some type of front-end program developed in Visual Basic, PowerBuilder, or even Access. Most database administrators will interact with SQL Server through the SQL Enterprise Manager.

Q How long does it take to become proficient with SQL Server?


A
Hmmmm. How about 21 days? Just kidding. It depends on how much time you spend working with the product. For me it took roughly 4-6 months of heavy use with SQL Server to gain a solid understanding of Transact-SQL as well as the capabilities and configuration of SQL Server. We, the authors, sincerely hope that this book will cut your learning curve down from 6 months to 3 months (or even less).

Workshop

This Workshop is designed to test your understanding of the materials presented in this chapter. If you do not do well on the quiz, you might want to reread the chapter, or play with the exercises a bit more. The exercises are designed to give you some hands-on experience installing and troubleshooting SQL Server.

Quiz

1. What two installation options, which if changed at a later date, would require you to drop your databases, re-create them, and reload data?

2. The best place to install SQL Server is on a member server with access to domain accounts through a trust relationship.

3. What protocols support integrated security on SQL Server?

4. What three different ways can you start and stop SQL Server?

5. Where can you find information about SQL Server events and errors?

6. What command-line utilities can you use to test Named Pipes?

7. What type of disk partition is recommended for use by SQL Server?

Exercises

1. Install SQL Server.

2. Test the Named Pipes protocol on your local machine and across a network.

3. Use the NT Event Viewer and look at an informational message from the NT Application Log.

4. Register a server in the SQL Enterprise Manager.

5. Create a new server group in Enterprise Manager named Widget.

6. Check the Windows NT Registry and verify the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer key.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.