Data Management

Breaking down the Microsoft SQL Server installation

Knowing just what is happening during a SQL installation can make your administrative life much easier. Joe Barnes is here to tell you what to look for when making these installations.

The benefits of automated and point-and-click installations today are amazing. Just the speed at which an installation of Microsoft SQL Server can occur is truly astounding! However, with all of the newfound ease of use provided by software vendors these days, it’s important not to forget the reasons we make the choices we do during the install process.

My goal in this Daily Drill Down is to provide some definition and a basic understanding of why the configuration options in the Microsoft SQL Server installation are important. In addition, I want to provide you with some basis as to how to decide which configuration options are best for your organization. By the end of this Daily Drill Down, you will not only know how to install SQL Server, you will understand why you have dictated the platform configuration options you have chosen.

Some of the most important configuration details come into play after the initial install (for example, the positioning of a database’s data and log files). We will review this and other topics in more detail as we work through the setup process.
As with any procedure or standard within an organization, the configuration of your SQL Server platform should be well documented. I recommend that you take detailed notes when installing SQL Server and include the reasons for choosing the options selected. By keeping detailed documentation of your environment, you will have a much easier time recovering after a disaster, as well as making decisions in the future.
Getting started
Although there are many choices for a SQL Server installation in terms of version and platform, I have chosen to use the Microsoft BackOffice Server 4.5 kit, which includes SQL Server 7.0. There will be minor differences between the SQL 7 and 2000 installation procedures—for example, the Graphical User Interface (GUI) will look different depending on your mechanism and version of install—however, the fundamentals remain the same.

To start, we will look at the main configuration options necessary when installing SQL Server. As mentioned previously, this is our main focal point, so we will begin by examining the configuration options presented during the GUI portion of the install.

Talk, talk, talk
The first of the configuration options to select is the method of communication for the database server. What I mean by this is the ability of SQL Server to talk to the client and how that process will occur. As you’ll notice when you perform the installation, you must select at least one communication protocol (see Figure A).

Figure A
Various protocols are available to choose from when you install SQL Server.

To provide a bit more detail about how the conversation between the database server and its clients occurs, we’ll use a traditional network-computing example. The client consists of a set of language-definition protocols and methods for implementing those protocols. These network libraries, or db-libraries, are contained within a series of .dll files (dynamic link libraries) on the client. The server is furnished with corresponding libraries that bind the method for communication.
Remember, what has traditionally been thought of as the “client” is a bit more elusive than in days past. A client may be a Web server that is actually performing the database access in the form of Active Server page requests to the database server. A client may also be an application server brokering requests between the calling application and the database server.
Now that we’ve defined how to choose the method of communication your database server is to employ, let’s talk about our options and why these options are important when installing Microsoft SQL Server. We’ll begin with the default-selected protocol (or native protocol) to SQL Server, which is Named Pipes. Named Pipes is required when installing SQL Server on the NT platform. This protocol is not available to Windows 9x clients, however, so you must take this into consideration while gathering your requirements prior to the install (meaning that if you are going to support 9x clients, you must choose another protocol as well). The benefit of Named Pipes is that it’s a very specific method for communication that’s entrenched in the platform’s architecture (which can improve performance). A great number of the utilities and processes that run within and around the SQL Server engines rely on Named Pipes (thus the requirement during the install).

Next, a number of protocols are supported that allow communication with an array of different client platforms. First (and probably the most commonly used) is TCP/IP Sockets. IP Sockets allows SQL Server to communicate directly with the Windows Inter Process Control layer using the TCP/IP protocol. In addition, TCP/IP is the standard communication protocol of the Internet and is a speedy one as well. Clients having access to this common protocol can communicate directly with SQL Server via the specified port (the default being 1433). SQL Server also supports communication via IPX/SPX, which is the default Novell NetWare protocol, as well as AppleTalk and Banyan Vines. These protocols should be considered only if they are required on your network. The final option for configuring your communication layer is Allow Multiple Protocols. This option supports the standard protocols mentioned above; however, Allow Multiple Protocols is intended for use only when encryption is necessary.

The bottom line for choosing the method of communication that SQL Server will support is defined by the needs of the clients initiating the communication. Most important, though, you shouldn’t bog SQL Server down by requiring that the server listen for multiple protocols when it does not have to, as this will impede performance.

Character set and sort order
The impact of the character set and sort order chosen during the installation of SQL Server can be dramatic. Why? Well, for starters, if you make a mistake and wish to change the Character Set option, you must either rebuild the master database (first dumping all data that exists in databases located on the server into text files) or reinstall SQL Server altogether. Beyond that, these options define how data is stored and sorted when requests for retrieval are made. These options have an obvious effect on performance and the output of the data. In this section, we’ll describe these options in detail and pay specific attention to why you must research these options prior to performing the installation.

The Character Set option (see Figure B) determines the way the data stored in the database is encoded. This is an important factor when considering who will be accessing this data and for future support. The most commonly used character set is the ANSI standard 1252. The benefit of this character set is revealed by its name—the ANSI standard. This character set is also the default. If you’re setting up SQL Server for an application that is specific to a certain region of the world and that requires a different code page (another name for a character set), the other available choices are well defined and should be part of your requirements definition (for example, 1256 is the character set for Arabic).

Figure B
You can choose from various character sets during the SQL Server install process.

The sort order that SQL Server uses is the means by which data will be delivered for output to the requesting call. The Sort Order option is very important for a number of reasons. Let’s start by reviewing some of the Sort Order choices. The first part of any sort order is the means by which the data is translated, for example, Dictionary Order, Binary Order, or a number of language-specific orders, such as Swedish. Dictionary Order means that the data will be viewed as “abcd.” Binary Order means that the numeric translation of the character takes precedence in the retrieval process. The second part of the Sort Order option deals with case preference. The default sort order is Dictionary Order, Case-Insensitive. This means that the calling application does not have to formulate its queries in any specific case designation for SQL Server to respond with the proper data retrieval. Case-Sensitive is the opposite; it requires the caller be very specific as to case in the request. The requirement for specifics during communication does improve performance, however. The Binary sort order is considered the fastest method of sorting (see Figure C for more details).

Figure C
These are the various sort orders available to Microsoft SQL Server.

Why do these configuration options mean so much during the installation of SQL Server? As mentioned previously, the Character Set and Sort Order choices made by the administrator during the configuration phase of the install will make a big difference when communicating with other systems and databases. Moving or replicating a database from one SQL Server to another, for example, can fail based on any inconsistency between these options.

When going through the motions of the install process (the point-and-click portion), choosing which protocols, character set, and sort order to use can take mere seconds. Our purpose in this Daily Drill Down is to demonstrate just how important those selections truly are. The moral of the story is a common one in the field of enterprise computing: Take your time and do your research up front.
Microsoft recently issued Service Pack 3 of SQL Server 7.0. If you’re operating on this platform, you should download and install this Service Pack.
Post-install configuration
Now that the GUI configuration is complete and the installation scripts have been run, let’s focus on some of the platform configuration that should be reviewed after the fact. The items listed below really depend on the environment (in some cases, they’re database specific). I highly recommend that your organization evaluate these options as standards prior to making any choices.

Keep in mind, however, that there are a number of configuration parameters that may be explored, and some of them may or may not be good candidates for optimization. I merely want to point out some of the long-term benefits of making those choices as soon as the installation is complete.

File locales
By default, SQL Server places database data files in the C:\mssql7\data directory. This is fine, and the database server will function properly without your making any modifications to this parameter when creating new databases. There are a few reasons why we don’t recommend this configuration when you employ production databases in your environment, though.

First, the transaction log file, or *.ldf, for a database is necessary when recovering from backup. If your transaction files are in the same location as your data files at the time of a disaster, you may lose both at once. Therefore, you should locate your transaction log files on a separate partition from your data (see Figures D and E for more details).

Figure D
Notice the path for the data file on partition E:.

Figure E
As you can see, the path for the transaction log is on the physical drive C:.

Memory settings
For the most part, SQL 7 and later versions handle memory configuration on the fly. This is very important to consider when changing any options related to memory allocation. But if your server is used for more than just a database server (an IIS server as well, for example), then you will want to consider the minimum and maximum memory options, as shown in Figure F. In this case, it is very important to set a minimum memory allocation for your server. Doing so will ensure that SQL Server gets the slice of memory up front that it needs.

Figure F
Various memory settings are available for SQL Server.

Finished? Let’s review
Now that the SQL Server installation is complete, let’s run a few stored procedures to verify our installation parameters.

Every database platform has a standard tool used to run SQL statements from your client. In the case of Microsoft SQL Server, we have Query Analyzer (QA). QA is an easy-to-use GUI tool that allows the user to establish a connection to a database server and run commands (either straight SQL or stored procedures) against a specific database on that server.

The master database within SQL Server contains all system configuration information. This database also contains a number of stored procedures that can be used for determining the state of SQL Server.

We will first run a procedure used to gather statistics related to SQL Server:

The results of running this stored procedure through QA on our server are shown below:
  attribute_id attribute_name  attribute_value
1  DBMS_NAME      Microsoft SQL Server
2  DBMS_VER      Microsoft SQL Server
7.00 - 7.00.842 (Intel X86) Mar 2 2000 06:49:37 Copyright (c) 1988-1998
Microsoft Corporation Standard Edition on Windows NT 4.0
(Build 1381: Service Pack 5)
10  OWNER_TERM      owner
11  TABLE_TERM      table
13  TABLE_LENGTH     128
14  MAX_QUAL_LENGTH    128
15  COLUMN_LENGTH     128
17  TX_ISOLATION     2
18  COLLATION_SEQ     charset=iso_1
sort_order=dictionary_iso charset_num=1 sort_order_num=51
100 USERID_LENGTH     128
101 QUALIFIER_TERM     database
105 MAX_INDEX_COLS     16
112 SP_RENAME      Y
500 SYS_SPROC_VERSION    7.00.624
(29 row(s) affected)

To gain more specific information about the Character Set and Sort Order options, we will run the procedure

The results of running this stored procedure through Query Analyzer on our server are as follows:
Unicode data sorting
Locale ID = 1033

case sensitive, kana type insensitive, width insensitive

Sort Order Description
Character Set = 1, iso_1
 ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 51, dictionary_iso
 General purpose dictionary sort order for use with several
 Western-European languages including English, French, and German.
 Uses the ISO 8859-1 character set and is case-sensitive.

Characters, in Order
 ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }
 ~ ¡ ¢ £ € ¥ Š § š © ª « ¬ ­ ® ¯ ° ± ² ³ Ž µ ¶ · ž ¹ º » Œ œ Ÿ
 ¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A a À à Á á Â â Ã ã Ä ä Å å Æ æ B b C
 c Ç ç D d E e È è É é Ê ê Ë ë F f G g H h I i Ì ì Í í Î î Ï ï J
 j K k L l M m N n Ñ ñ O o Ò ò Ó ó Ô ô Õ õ Ö ö Ø ø P p Q q R r S
 s ß T t U u Ù ù Ú ú Û û Ü ü V v W w X x Y y Ý ý ÿ Z z Ð ð Þ þ

As you can see from the above output, our SQL Server character set is 1 (or the default 1252 ISO standard code page). Our chosen sort order is 51, also known as the Dictionary Order, Case-Sensitive option.

In this Daily Drill Down, we have run through the installation of the SQL Server 7 platform, paying close attention to the options selected during install. The choices we have made may not be right for your organization, but again, that has been the point of this exercise. Remember, preparation and planning can make for a well-organized, well-documented, and efficient SQL Server environment.
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks