Table of Contents

Chapter 6

Configuring SQL Server

Certification Objectives *

TSQL Method for Server Registration *

Dynamic Memory Allocation *

Fixed Memory Allocation *

TSQL Method for Memory Allocation  *

Manual User Connection Configuration using Enterprise Manager *

Configuring Default Connection Settings *

Installing the Full-Text Search Service *

GUI Method for Enabling Full-Text Search for Specific Columns and Tables *

TSQL Method for Enabling Full-Text Search for Specific Columns and Tables *

Certification Objectives

Now that we have successfully installed SQL Server and an administration workstation, we can begin configuring SQL Server to serve the enterprise. In this chapter, we will begin by discussing the process of registering SQL Servers into the Microsoft Management Console, and creating server groups. Next, we will discuss overriding the default SQL Server memory and user connection configuration settings. This is done in order to preserve system resources for other processes that may be running on the SQL Server. Finally, we will configure SQL Server’s processor affinity mask configuration options.

In the last section of this chapter we will discuss installation of a new feature in Microsoft SQL Server 7.0: The Full-Text Service. This new feature extends the functionality of text searches well beyond anything we have ever been able to use with the "LIKE" keyword. The full-text search function actually searches for words and phrases instead of the characters used by the LIKE keyword. This functionality is ideal for Internet/intranet web development as it provides a quick, easy method for a user to return a result set.

Registering Servers and Groups

In order to perform centralized management of all SQL Server installations, you must first register each server with the SQL Enterprise Manager. Once a connection has been established, you can perform every administrative task possible using either the GUI Tools or TSQL commands from any client machine running the Enterprise Manager.

If you plan to run a stored procedure on a remote server, you must first register that server with the Enterprise Manager on the calling server.

The following exercise will demonstrate registration of a new SQL Server using Enterprise Manager as well as the equivalent TSQL commands.

Exercise 6-x: Using the SQL Server Registration Wizard to Register Servers

In this exercise we will create a registration entry for an existing SQL Server 7.0 installation. Please note that if you are performing this exercise at the server console, you will already see a server registered.

  1. Begin this exercise by launching the SQL Enterprise Manager from your administrative workstation (or the server console). When the Management Console is launched, you will see the screen shown in Figure 6-1.
  2. Figure 1: Beginning the SQL Server Registration Wizard

  3. Using the right mouse button, click on the "Microsoft SQL Servers" entity in the Console root tree. This will allow you to start the SQL Server Registration wizard.
  4. After most users understand each configuration option offered by the SQL Server Registration Wizard, they will most likely prefer to edit the parameters directly instead of using the wizard. If you do not wish to default to the wizard for registration, then implement the following: configure the Enterprise Manager console to use a dialog by checking the "From now on…" checkbox in the dialog presented in Figure 6-2.
  5. Figure 2: SQL Server Registration Wizard Splash Screen

  6. Next, you can either select servers to register, or type the name if the server does not appear in the browse list. From this dialog (shown in Figure 6-3), you can register multiple servers that share a similar configuration. All servers must use same SA password. They must also prompt for credentials, or the NT account must be a valid trusted connection user. If the servers differ in configuration, the wizard will fail to properly register that server.
  7. Figure 3: Selecting a SQL Server to Register

  8. In the screen shown in Figure 6-4, we can configure which authentication method the SQL Server will use. If the server uses Windows NT Authentication, the connection is considered "trusted". This means that the NT user account has been granted the proper rights to the database. If the server was configured to use the SQL Server authentication model, you must enter proper credentials to properly register the server.
  9. Figure 4: Trusted (Windows NT Authentication) or SQL Server authentication

  10. The dialog shown in Figure 6-5 will only be displayed if you chose to connect to the server using the SQL Server Authentication model. You must enter a proper SQL Server user ID and password to register the server. In some scenarios, you may wish to prompt the user for a password in order to use the connection. This can be performed by clicking the "Prompt for the SQL Server account information when connecting" option button.
  11. Figure 5: SQL Server Authentication login credentials dialog

  12. If there are a large number of SQL 7.0 servers that are to be managed, it may be worthwhile to create a Server Group, as in Figure 6-6. The Server Group is simply a container to place server registrations in. This is similar to a directory in the file system.
  13. Figure 6: SQL Server group selection

  14. Once all the configuration options are entered, the SQL Server Registration wizard will display the registration confirmation dialog, as shown in Figure 6-7. When you press "Finish", the system will attempt to connect to the SQL Server. If successful, the dialog box displayed in Figure 6-8 will be presented.
  15. Figure 7: Registration Confirmation Dialog

    Figure 8: Successful Registration Status

  16. Once the SQL Server has been registered into the Enterprise Manager console, you can then configure, administer, and query the SQL Server from the console shown in Figure 6-9.

Figure 9: Viewing the new registration in the Enterprise Manager

There may be a typographical error, or a mismatch between the client and server network libraries. In this instance, the "Register SQL Server Messages" screen will appear and alert you to the failure. A sample of this behavior is shown in Figure 6-10.

Figure 10: Registration Failure

After you become familiar with SQL Server, you will most likely wish to perform the registration tasks without the use of the registration wizard. If you check the "From now on…" checkbox in the wizard startup screen (Figure 2), you can enter all pertinent data in a single screen, as shown in Figure 6-11.

Figure 11: Standard Registration Dialog

Additionally, most users will wish to avoid use of the wizard for registration, as it does not allow you to view the System Databases and Objects in the tree.

On the Job: Many organizations strive to create a secure computing environment throughout every aspect of the enterprise. Perhaps you would like to prevent users who may have access to your computer from administering the SQL Servers registered in Enterprise Manager. If so, then check the "Always prompt for Login name and password" checkbox when registering your servers. This will prevent prying eyes from gaining access to the servers you have registered.

TSQL Method for Server Registration

Using TSQL, we can also register remote and local servers. This functionality is important not only for the exam, but potentially for use in custom developed scripts you may wish to implement. Let’s look at the TSQL syntax to perform a remote server registration:

sp_addlinkedserver [@server =] 'server',[@srvproduct =] 'product_name'
Where:

[@server =] 'server'

is the name of the linked server to create. This value must be specified in order to perform the registration.

[@srvproduct =] 'product_name'

is the product name of the OLE DB data source to add as a linked server. To create links to SQL Server implementations, this value should be ‘SQL Server’. Consult SQL Books Online for more information about linking Access, Sybase, and Oracle database servers using this syntax.

To demonstrate, let’s use the following TSQL syntax to register a version of SQL Server 7.0 named SUSCH753. Please note that these will not show up in the GUI. This is due to the fact that this syntax is used to register remote servers for use with remote stored procedures.

USE master
GO
EXEC sp_addlinkedserver 'SUSCH755', N'SQL Server'
GO
select * from sysservers

Using the SQL Server Registration Wizard, the Enterprise Manager Registration, or TSQL command syntax, we can register both local and remote SQL Servers in the Master database. The registration process allows you to perform administration of remote servers. It also enables SQL Server to run remote stored procedures on another SQL Server installation.

Please note that the sp_addserver command will also register a server in the sysservers table. This feature is retained only for backward-compatibility and should not be used to register SQL Server 7.0 servers.

Memory Allocation

New to this release of SQL Server is the ability to perform dynamic memory allocation. Until now, administrators made semi-educated guesses about how the server should be configured despite the constantly changing activity on the server. Microsoft has left the fixed memory allocation model in place as well, giving administrators a significant number of choices about memory allocation in this release of SQL Server.

Dynamic Memory Allocation

Although SQL Server provides dynamic memory allocation, you can force it to allocate memory within a specified memory range. If the server you are working with has 256MB of memory, you can set SQL to use from 0 – 256 MB, 0-128 MB, 128-256 MB or any other permutation to derive the best overall performance from the server. This operation is done from the server properties memory configuration tab. To view how your server is set, use the following procedure:

  1. If it isn’t already loaded, launch Enterprise Manager.
  2. Right-click on SQL Server 7.0 and select the "Properties" entry from the menu.
  3. Select the Memory tab, as shown in Figure 6-12.

Figure 12: Dynamic Memory Allocation Configuration Options

Using the dialog shown in Figure 6-12, you can allow SQL Server to dynamically allocate memory, but it will be constrained to the range specified by the minimum and maximum values specified. This option is particularly useful if you have multiple applications running on the same server, and you wish to retain the dynamic memory allocation functionality.

Fixed Memory Allocation

For most large enterprises running dedicated hardware for each SQL Server installation, the dynamic memory allocation feature can yield a significant improvement in SQL performance. In previous releases, memory tuning was a pseudo-science, as administrators were constantly adjusting the delicate balance between operating system memory and dedicated SQL Server memory. If you incorrectly set an option, you would render the server unusable, and would have to start SQL Server with the fail-safe configuration flag. Despite the performance advantages of dynamic memory allocation, the fixed memory allocation model still exists in SQL Server 7.0. This functionality may be useful if you wish to run several server applications components on the same server and do not wish to exploit the dynamic memory allocation feature.

If you should have a need (or the desire) to override the dynamic memory allocation feature in SQL 7.0, you can use the following procedure to reconfigure the memory allocation model:

  1. Using Enterprise Manager, select the server to reconfigure. Right-click and select the "Properties" menu entry.
  2. When the tabbed dialog box appears, select the Memory tab (Figure 6-13).

Figure 13: Manual configuration of SQL Server Memory settings

In this dialog you can allocate a fixed amount of memory to SQL Server, allocate physical memory (therefore overriding the operating system’s ability to swap SQL Server pages), and user query memory space allocation.

To select the best values for the fixed memory allocation model, you should use Performance Monitor to monitor the page faults per second counter of the memory object.

TSQL Method for Memory Allocation

The following TSQL command syntax will set the dynamic memory allocation model to use at least 128 MB, and no more than 255 MB:

USE master
GO
EXEC sp_configure 'min server memory (MB)', 128
RECONFIGURE
EXEC sp_configure 'max server memory (MB)', 255
RECONFIGURE
EXEC SP_CONFIGURE

User Connections

Also new to this release of SQL Server is the dynamic allocation of user connection objects. This new feature dynamically assigns system resources as each user establishes a new connection to the SQL Server. By default, SQL Server will dynamically allocate user connection objects for 0 - 32,768 users. According to Microsoft, you should not need to override this option by defining a static number of allowed connections. If you should find a need to do so. however, you can use the following procedures.

Manual User Connection Configuration using Enterprise Manager

To change the Maximum Concurrent User Connections option using Enterprise Manager, implement the following:

  1. If it is not already loaded, launch Enterprise Manager.
  2. Right-click on SQL 7.0 Server to reconfigure.
  3. Click on the Connections tab, shown in Figure 6-14.

Figure 14: Connection Settings Dialog

  1. Specify the maximum number of concurrent user connections.

It is worth noting that with using this method, each user connection object specified uses 40KB of overhead. Therefore, you should set the maximum only to what you believe will address the user community’s needs.

Configuring Default Connection Settings

Using the Connections tab, you can set the default behavior of user connections. You may configure the options in Table 6-1.

Connection Option Behavior
Interim/Deferred Constraint Checking Controls interim or deferred constraint checking.
Implicit Transaction Controls whether a transaction is started implicitly when a statement is executed.
Close cursor on COMMIT Controls behavior of cursors after a commit operation has been performed.
ANSI Warning Controls truncation and NULL in aggregate warnings.
ANSI Padding Controls padding of fixed-length variables.
ANSI Nulls Controls NULL handling when using equality operators.
Arithmetic Abort Terminates a query when an overflow or divide-by-zero error occurs during query execution.
Arithmetic Ignore Returns NULL when an overflow or divide-by-zero error occurs during a query.
Quoted Identifier Differentiates between single and double quotation marks when evaluating an expression.
No Count Turns off the message returned at the end of each statement that states how many rows were affected.
ANSI Null Defined Alters the session’s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow NULLs.
ANSI Null Defined Off Alters the session’s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined not to allow NULLS.

Table 1: Connection Defaults – Source: SQL Server Books Online

Processors

Microsoft SQL Server 7.0 includes a rather robust set of configuration options to control how processes are executed in multi-processor environments. Using the Processors tab in the SQL Server Properties dialog box, we can assign which processors are allowed to be used by SQL Server. This granularity may be useful in quad and eight-way symmetric multi-processing machines, where we can limit SQL Server to half of the available processors. This process leaves the other CPUs dedicated to operating system tasks. Additionally, you may wish to throttle SQL’s access to the CPUs if there are multiple server application components running on the system. Examples of this would include IIS, SMS, or Oracle. Similar to the memory tuning, modification of these parameters is a task best left for the lab environment when you have many hours to collect performance monitor log files.

The following exercise will show how to modify SQL Server’s use of processing resources in a symmetric multiprocessing environment.

Exercise 6-x: Using the SQL Server Registration Wizard to Register Servers

  1. If it is not already running, launch Enterprise Manager.
  2. Right-click on the server for which you wish to modify the processor configuration, and select the "Properties" menu entry.
  3. Click on the Processors tab, shown in Figure 6-15.

Figure 15: Processor Configuration Dialog

Configuring SQL Mail for use with Microsoft Exchange Server

The SQL Mail client adds a significant amount of functionality to the SQL Server environment. In addition to alerting an administrator to potential problems with the server (using alerts), users can also mail queries to the server and receive queries via e-mail. This can be a very powerful feature if it used with an Exchange form or a form-based intranet front-end.

In this section, we will discuss the proper configuration of the SQL Mail client with Microsoft Exchange. If your enterprise is not running Microsoft Exchange, it is still possible to use SQL Mail, as it will allow any MAPI client to be used.

To begin, let’s make sure we address a few prerequisites:

Once you have all the information for the domain account, log on to the server using the credentials for the MSSQLServer service account, as shown in Figure 6-16. For this example, we will be logging in as svcSQLServer.

Figure 16: Verification of the MSSQLServer service account

Install the appropriate MAPI driver software and create a new profile for this user. Once this process has completed (and any necessary reboots have completed), start the Mail client by double-clicking the Inbox icon.

Once the Exchange Client (or Windows Messaging) comes up, you will be presented with the dialog shown in Figure 6-17.

Figure 17: Configuring Microsoft Exchange Client for Microsoft Exchange Server.

For this example (and most installations) only the MAPI drivers (Microsoft Exchange Server) will need to be selected.

Next, we will select the name of the Exchange Server and the alias that was specified when the mailbox was created (see Figure 6-18).

Figure 18: Defining Exchange Server and Mailbox Name

After getting through the off-line settings, personal address book configuration, and startup behavior dialogs, you will be presented with a summary dialog (see Figure 6-19).

Figure 19: Configuration success

Now that we have successfully created a profile, we should test whether or not the profile works correctly. This can be performed by sending an e-mail to the e-mail alias specified in the profile (i.e. send it to and from the same address). See Figure 6-20.

Figure 20: Testing the SQL Mail Profile.

If all went well, you should see the e-mail in the Inbox after a few moments, as in Figure 6-21.

Figure 21: SQL Mail Test Success.

Now that we have validated that the mailbox is configured and working properly, we can begin the process of configuring SQL Server to use this profile. Using the Mail applet in the Control Panel, click the "Choose Profiles" command button. This will bring up a dialog similar to the one in Figure 6-22.

Figure 22: Verify the name of the profile that was created.

Now that we know the name of the profile that was created, we can easily use this profile to configure SQL Server. Log out of the server (remember that you were logged in as the service account). From the server console or an administrative workstation, launch Enterprise Manager. Select the server you wish to enable SQL Mail support on. Right-click the SQL Mail service entry in the tree (as shown in Figure 6-23) and choose "Properties."

Figure 23: SQL Mail Configuration using Enterprise Manager

From the properties sheet, we can configure which profile we wish to use with SQL Mail. Choose the name of the profile that we just created while logged in as the service account (see Figure 6-24).

Figure 24: Choose the proper profile for use with SQL Mail

Once you have selected the profile, you will want to verify that the MAPI session can be started. You will most likely want to start SQL Mail whenever SQL Server is started, so click the appropriate checkbox in the dialog. Press the "Test" button. If all goes well, you should get the dialog shown in Figure 6-25.

Figure 25: Successful MAPI Session Test Confirmation

Once we have successfully configured the SQL Mail service, we can use the SQL Mail service to provide alert notifications (covered later in this book) and process queries. Using TSQL commands, we can actually send the SQL Server a query (via e-mail) and get a result sent back by return mail. There are two important stored procedures that help SQL process mail: xp_sendmail and sp_processmail. Let’s begin by sending the SQL server a test query (see Figure 6-26).

Figure 26: Sending a SQL Query via SQL Mail

Once we have sent the e-mail to the SQL Server, the server will wait indefinitely until the sp_processmail command is executed (see Figure 6-27):

EXEC sp_processmail

fig6-27.gif (10046 bytes)

Figure 27: Executing sp_processmail

In a few moments, SQL Server will reply to the message with the result set (see Figure 6-28).

Figure 28: SQL Mail returns an email with the result set attached as a text file

If you wish to implement the ability for users to process queries via e-mail, you will want to set up a scheduled job that will run ‘EXEC sp_processmail’ at a set interval (perhaps every five minutes during the day).

Using this same functionality, we can also force SQL Server to send e-mail messages to specific users. This functionality can be important to implement in TSQL scripts that we write to perform administrative tasks. It is very reassuring to start your day with an e-mail stating that the script that ran last evening completed successfully. To allow SQL Server to send an e-mail, we can use the xp_sendmail extended stored procedure.

To illustrate this functionality, let’s assume that we are incorporating this line of code into a script that performs a batch of inserts and deletes on a production database. We want to let the DBA (e-mail alias eckth) and his manager (e-mail alias mctagub) know that the script completed successfully. We can use the following syntax to perform this task: 

EXEC xp_sendmail @recipients = ‘OurDBA’,
@message = ‘Delegated administration clean-up task completed successfully on server SUSCH753.’,
@copyrecipients = ‘DBAManager’,
@subject = ‘Delegated Administration clean-up task Completed Successfully on SUSCH753’

Using SQL Mail, we can create some very proactive SQL administration tools, and also develop a SQL query processor that uses e-mail for it’s interface. If you can develop Microsoft Exchange e-mail forms, then this can be an extremely powerful feature for e-mail.

Installing and Configuring the Full-Text Search Service

If you have used the Internet for any period of time, you have most likely called upon the services of a search engine, such as AltaVista, InfoSeek, or Yahoo. These search engines allow the use of Boolean operators to help refine the query and return a smaller, more meaningful result set. In addition, many of these engines support "fuzzy" logic, which will allow you to specify a set of search terms, and the index will return items that are close to or related to the search terms. Using this fuzzy logic, we do not need to enter each iteration of the desired result set. For example, if we search enter "Full-Text" into the search terms form, the engine will return a result set with "Full-Text", "Full Text", "full text", "full! Text" and so on.

The full-text search service is an exciting new feature to the SQL Server product line. This service will search character-based data in SQL Server tables, creating special indexes that will allow linguistic and proximity searches to be used with TSQL queries. Until now, SQL developers and administrators have been limited to the "LIKE" keyword, which would search character based data for words beginning, ending, or containing the search parameters. Microsoft has incorporated the search method we have grown accustomed to with the evolution of the Internet search engines to deliver an incredibly powerful engine for searching text fields in the SQL Server 7.0 database product.

Let’s begin this section by performing the installation of the Full-Text Search service on the SQL 7.0 server.

Installing the Full-Text Search Service

To install the Full-Text Search Service, run the setup routine from the SQL Server 7.0 installation media. Perform a custom installation, and deselect all items except the Full-Text Search Service (it can be found under the Server Components installation heading; see Figure 6-29).

Figure 29: Installing the Full-Text Search Engine

Use the Enterprise Manager console to start the full-text search service. Additionally, you may wish to assign this to a user account and automatically start when the server reboots.

GUI Method for Enabling Full-Text Search for Specific Columns and Tables

In order to use the full-text service, we must first tell SQL Server that the database has the full-text search service enabled. This procedure can be performed two ways. Using the GUI, we can enable the service on the "Northwind" database as follows:

Select the database that contains the table you wish to enable full-text searching within. Right-Click the database name entry in the tree and select Tools, Full-Text Indexing…. This will bring up the full-text indexing wizard, shown in Figure 6-30.

Figure 30: The Full-Text Search Wizard

Once the wizard has started, you must select a table to enable full-text indexing within (see Figure 6-31).

Figure 31: Choosing a table to enable full-text search

Now that we have specified which database and table on which to enable the full-text search service, we must select a unique index for the table (Figure 6-32).

Figure 32: Choosing the unique index for the table

Next, we can select the character-based columns on which we will enable the full-text search service (see Figure 6-33).

Figure 33: Selecting character-based columns to enable full-text search

Now, we must create a new full-text catalog (see Figure 6-34).

Figure 34: Naming a new Full-Text search catalog

Populate the schedule by selecting when the full-text catalog will be indexed. The index is populated asynchronously due to the time it takes to update a full-text index. For this reason, you must schedule a time for the update to take place. (see Figure 6-35).

Figure 35: Scheduling population of the full-text index

Once the wizard has entered your data into the population schedule, you will be presented with the summary dialog shown in Figure 6-36.

Figure 36: Wizard Summary screen

TSQL Method for Enabling Full-Text Search for Specific Columns and Tables

We can perform the above procedure using the following TSQL command syntax instead of the wizard. Following is the commented code to perform this procedure:

USE Northwind

Enable full-text searching in the database:

EXEC sp_fulltext_database 'enable'
GO

Create a new full-text catalog:

EXEC sp_fulltext_catalog 'Northwind_CustomerDesc, 'create'
GO

Register the new table and column within it for full-text querying, then activate the table:

EXEC sp_fulltext_table 'CustomerDemographics', 'create', 'Northwind_CustomerDesc', 'PK_CustomerDemographics’
EXEC sp_fulltext_column 'CustomerDemographics', 'CustomerDesc', 'add'
EXEC sp_fulltext_table 'CustomerDemographics', 'activate'
GO

Start full population of the full-text catalog:

EXEC sp_fulltext_catalog 'Northwind_CustomerDesc', 'start_full'
GO

If you would like to learn more about the TSQL command syntax, please see the TSQL command reference in Appendix E.

Certification Summary

We began this chapter by discussing the process of registering a remote server in the Enterprise manager. We performed this task using the Registration wizard, Enterprise Manager, and TSQL commands. Next, we discussed methods in which we could allow SQL Server to peacefully coexist with another resource intensive application, such as Exchange Server or IIS 4.0. This manual resource allocation was performed by exploring fixed memory allocation configuration options, and processor affinity settings for SMP devices. We also configured default connection settings, and demonstrated how the dynamic user connection manager could be overridden to force a specific number of connections instead.

We then tackled the installation and configuration of the SQL Mail feature. This feature allows a user to use the electronic mail system to process queries (receiving a result set via return e-mail) and also to alert the system administrator/data center operations staff of any potential issues occurring on the SQL Server. While we discussed the installation of SQL Mail for this purpose, it will not be fully exploited until later on in this book.

Lastly, we discussed a new feature in SQL Server 7.0: the Full-Text Search Service. This exciting new feature allows users to search character-based data with much greater efficiency than was previously offered using the "LIKE" keyword. We explored both TSQL and Wizard-based methods of configuration.

Two-Minute Drill