Data Management

Breaking down the Microsoft SQL Server 7.0 installation

Microsoft has made application installations so easy that most of the time you just run Setup, go to lunch, and wait for it to finish. SQL Server is easy too, but there are a few gotchas. Joe Barnes points out these areas.


The benefits of automated and “point-and-click” installations these days are amazing. The speed at which an installation of Microsoft SQL Server can occur is truly astounding! However, with all the newfound ease of use provided by software vendors these days, it is 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 definitions 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 understanding of 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 but you will also understand “why” you have dictated the platform configuration options you have.

In addition, some of the most important configuration details come into play after the initial installation (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.

Documentation tips
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 your selections. By keeping detailed documentation of your environment, you will have a much easier time recovering after a disaster and making future decisions.

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 GUI will look different depending on your mechanism and version of installation. However, the fundamentals remain the same.

Microsoft has made application installations basically fire-and-forget: Simply run Setup and hang on for the ride. However, as with most complicated software installations, there are bound to be a few points where things get confusing. We will look at the main configuration options necessary when installing SQL Server. We will begin by examining the configuration options presented during the GUI portion of the installation.

Talk, talk, talk
The first configuration option you will select is the method of communication the database server is capable of. This determines the ability of the SQL Server to talk to the client and how that process occurs. You are required to select at least one communication protocol, as shown in Figure A.

Figure A
You are required to select at least one communication protocol.


The structure of the conversation between the database server and its clients can be best explained using 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 also is furnished with corresponding libraries, which bind the method for communication.

The new client model
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 I’ve defined what it means 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. To begin, you have your 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. Therefore, if you support Windows 9x clients, you must choose another protocol as well.

Named Pipes is a very specific method for communication that is entrenched in the platform’s architecture. A great number of the utilities and processes that run within and around the SQL Server engines rely on Named Pipes, and using Named Pipes can improve performance.

Next, SQL Server supports a number of protocols that allow communication with an array of different client platforms. The first and probably the most commonly used is TCP/IP Sockets. IP Sockets allow 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 a speedy one. Clients that have access to this common protocol can communicate directly with the SQL Server via a port you specify. SQL Server’s default port is 1433.

SQL Server also supports communication via IPX/SPX, which is the default Novell NetWare protocol. AppleTalk and Banyan Vines are supported as well. These protocols should only be considered 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 already mentioned. Allow Multiple Protocols is intended for use when encryption is necessary, however, and its use is not suggested unless this is a requirement.

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 importantly, do not bog SQL Server down by requiring the server to listen for multiple protocols when it does not have to, as this will impede performance unnecessarily.

Character Sets and Sort Orders
The importance of the Character Set and Sort Order chosen during the installation of the SQL Server can be dramatic. Why, you ask? For starters, if you make a mistake and wish to change the Character Set option, you must either rebuild the master database—dumping all the data that exists in databases located on the server to text files first—or reinstall SQL Server altogether. Beyond that, these options also define how data is stored and sorted when requests for retrieval are made. This option has an obvious effect on performance and the output of the data. In this section, I will describe these options in detail and pay specific attention to why these options should be researched prior to performing the installation.

The Character Set option controls the way SQL Server encodes the data stored in the database. This has great importance when you determine who will be accessing this data.

The default and the most commonly used Character Set is the ANSI standard 1252, as shown in Figure B. If you are setting up a SQL Server for an application that is specific to a certain region of the world and requires a different code page (another name for a Character Set), the others available are well defined and should be part of your requirements definition.

Figure B
You must select the Character Set options during the SQL Server install process.


The next place you must make decisions is the Sort Order screen, shown in Figure C. The Sort Order used by SQL Server determines how data will be delivered for output to the requesting call. To begin this definition, 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 being translated (for example, Dictionary Order, Binary Order, or a number of language-specific orders [like Swedish]). Dictionary Order means the data will be viewed alphabetically. Binary Order means that the binary equivalent of the character takes precedence in the retrieval process. The second part of the Sort Order 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 the SQL Server to respond with the proper data retrieval. Case-Sensitive is the opposite.

Figure C
These are the 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 installation 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.

Choosing which protocols, Character Set, and Sort Order to use can take seconds. However, the purpose of this Daily Drill Down is to demonstrate 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.

Post-install configuration
Now that the GUI configuration is complete and the installation scripts have run, let’s focus on some of the platform configuration you should review after the fact. The items listed here are really environment-dependent and in some cases are specific to the database you’re using. As with the initial configuration options listed previously, you should evaluate these options and establish standards prior to making any choices.

Keep in mind that there are a number of configuration parameters that may be explored but may or may not be good candidates for optimization. This section is meant to expose some of the long-term benefits of making those choices at the same time that the installation is complete.

File locales
By default, SQL Server will place database data files in the c:\mssql7\data directory. This is fine; the database server will function properly without making any modifications to this parameter when creating new databases. However, there are a few reasons why this configuration is not recommended when employing production databases in your environment.

First, the transaction log file (*.ldf file) for a database is necessary when recovering data 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 in one shot. I recommend that you locate your transaction log files on a separate partition from your data, as shown in Figures D and E.

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


Figure E
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 when considering changing any options related to memory allocation. However, 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 your Min and Max Memory options. In this case, it is very important to set a minimum memory allocation for your server. This will ensure that SQL Server gets the slice of memory up front that it needs.

Figure F
These are the different memory settings 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-based tool that allows the user to establish a connection to a database server and run commands (either straight SQL or a Stored Procedure) 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 the SQL Server.

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

The results of running this Stored Procedure through Query Analyzer on our server are as follows:
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
12   MAX_OWNER_NAME_LENGTH
128
13   TABLE_LENGTH
128
14   MAX_QUAL_LENGTH
128
15   COLUMN_LENGTH
128
16   IDENTIFIER_CASE
SENSITIVE
17   TX_ISOLATION
2
18   COLLATION_SEQ
charset=iso_1

sort_order=dictionary_iso charset_num=1 sort_order_num=51

19   SAVEPOINT_SUPPORT
Y
20   MULTI_RESULT_SETS
Y
22   ACCESSIBLE_TABLES
Y
100   USERID_LENGTH
128
101   QUALIFIER_TERM
database
102   NAMED_TRANSACTIONS
Y
103   SPROC_AS_LANGUAGE
Y
104   ACCESSIBLE_SPROC
Y
105   MAX_INDEX_COLS
16
106   RENAME_TABLE
Y
107   RENAME_COLUMN
Y
108   DROP_COLUMN
N
109   INCREASE_COLUMN_LENGTH
N
110   DDL_IN_TRANSACTION
Y
111   DESCENDING_INDEXES
N
112   SP_RENAME
Y
113   REMOTE_SPROC
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
sp_helpsort

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 Wes
tern-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 this 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.

Conclusion
We have gone through the installation of the SQL Server 7 platform, paying close attention to the options selected during installation. 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.

Editor's Picks