Data Management

Tweak SQL Server options to gain functionality


Administering SQL Server 2005 is no easy task. Here are examples of a number of SQL Server options that DBAs can set to enable different functionality by the database engine. Even though you may rarely (or ever) need to change these options, it's useful to know how if the occasion arises.

Server configuration

There are two different ways in TSQL to view the database server configuration options: the sys.configurations system view and the sp_configure system stored procedure. The sys.configurations system view provides an easy way to view the system configurations, yet I prefer using the sp_configure system stored procedure because it allows you to enable and disable the different options. For the rest of this post, I will only refer to the sp_configure system stored procedure.

sp_configure When you run this with no parameter list, it will return a listing of the system configurations that are able to be set. However, it may be necessary on your system to enable the advanced options. To make this change, you can use the same sp_configure procedure, along with some additional parameters.

EXECUTE sp_configure 'show advanced options', 1

reconfigure with override

To see the listing of all available server options, you can run the sp_configure procedure again with any parameters.

EXECUTE sp_configure

Here's a look at three other useful settings that you can set on your server. (Note: You can enable all of these server options with the value 1, as shown in the example above, or disable all the options with the value 0 unless otherwise noted. allow updates This option has been removed from SQL Server 2005, but it is still useful in SQL Server 2000. The option lets the user make changes to system tables. Once you enable this option, you can alter system table structures, system procedures, etc. although it is not recommended in most instances. c2 audit mode This is an advanced auditing option that keeps track of successful and failed attempts to statements and objects in the database. It is of great help for determining a security policy and investigating possible security violations. It is worth noting that this option saves a lot of information to the Windows Event Log, and it takes additional processing to record such events. Turn this on in a production environment only if it is absolutely necessary. clr enabled New to SQL Server 2005 is the ability to write stored procedures, functions, triggers, etc. in managed .NET code. This type of functionality is known as Common Language Runtime, and it must be enabled in SQL Server 2005 in order to use managed code in the database.

Database Mail XPs

Database Mail, a new feature in SQL Server 2005, allows you to send custom e-mail messages and alerts from the database engine. The best part about Database Mail is that it relies on an SMTP server to send messages unlike SQL Mail, which requires a MAPI client to be installed.

xp_cmdshell This is a SQL Server system stored procedure that will spawn a Windows command shell and will execute the string statement passed into the procedure. This server option is new to SQL Server 2005, and it allows you to enable or disable the execution of this stored procedure. By default, this is disabled due to Microsoft's "Secure By Default" initiative. Allowing this stored procedure to be executed opens up potential security vulnerabilities. default trace enabled A new feature in SQL Server 2005 is that a default trace is always running in the background. This option, which is enabled by default, gives you an easy option for determining errors.

The default trace file is typically stored on the server machine, and you have two options for viewing the data in the trace. If you know the path to the trace file, you can use the fn_trace_gettable() table function, which takes the path to the file as a parameter, along with the file number. This allows you to view the data inside SQL Server that is useful for queries. The other option is to navigate to the location of the trace file and open it with SQL Profiler, which is a useful tool for diagnosing performance issues.

nested triggers SQL Server allows the nesting of triggers, which means that one trigger can modify data in a table that invokes another trigger, then another, etc. This is limited to only 32 nesting levels. After 32 levels of nesting, an error will occur. If you are unfortunate enough to run into this situation, it might be a good idea to sit down and look at your design to determine if you can make changes to not rely so heavily on triggers. max server memory (MB) This option determines the maximum amount of memory, in megabytes, that is available to the SQL Server Database Engine buffer pool. It is sometimes necessary to change this setting from the default value to prevent SQL Server from using more than a specified amount of memory, freeing up memory to be used by other applications on the server. min server memory (MB) This option determines the minimum amount of memory, in megabytes, that is available to the SQL Server Database Engine buffer pool. This option is typically used to guarantee that the SQL Server Database Engine will have at least a certain amount of memory available to it to perform work. This is useful so that, if you are running your SQL Server database on a server with other applications, another application will not consume all of the memory available on the server. user connections This option allows you to limit the number of simultaneous connections on the SQL Server (32,767 simultaneous user connections is the maximum that SQL Server allows). You usually will not need to alter this option in a production environment. However, it can be very handy if you want to limit the number of users on your database in a testing environment for certain scenarios.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

4 comments
Tony Hopkinson
Tony Hopkinson

You could become an honorary northerner if you keep that up.

etc-9909
etc-9909

Sorry. This was to have been posted to a different query. My appologies to all.

Chris.Conner
Chris.Conner

SQL Server has offered xp_cmdshell since SQL Server 7.0 (when I started using it - it could have existed before that) and is not new to SQL 2005. I thought I would clarify that, since this post says it is new to SQL 2005.

chapman.tim
chapman.tim

This article is talking about server configurations. The xp_cmdshell server configuration is new to 2005. As the article discusses, this option allows you to enable or disable the execution of the stored procedure xp_cmdshell (which is what you are talking about)...a procedure that has been around for a long time.

Editor's Picks