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 *
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 Servers 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.
Figure 1: Beginning the SQL Server Registration Wizard
Figure 2: SQL Server Registration Wizard Splash Screen
Figure 3: Selecting a SQL Server to Register
Figure 4: Trusted (Windows NT Authentication) or SQL Server authentication
Figure 5: SQL Server Authentication login credentials dialog
Figure 6: SQL Server group selection
Figure 7: Registration Confirmation Dialog
Figure 8: Successful Registration Status
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. Lets 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, lets 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.
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:
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.
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:
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 systems 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:
Figure 14: Connection Settings Dialog
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 communitys 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 sessions behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow NULLs. |
ANSI Null Defined Off | Alters the sessions 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 SQLs 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 Servers use of processing resources in a symmetric multiprocessing environment.
Exercise 6-x: Using the SQL Server Registration Wizard to Register Servers
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, lets 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. Lets 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
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, lets 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 its 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.
Lets 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