Using SQL Server Surface Area Configuration

SQL Server Surface Area Configuration is a tool that helps you enable, disable, start, or stop the features and services of your local and remote SQL Server 2005 installations. Surface area refers to the memory and other system resources required to run a program. Stopping and disabling unused services allows you to reduce the surface area and makes your system more secure. The tool uses the WMI provider to access the server configuration. To start SQL Server Surface Area Configuration, from the Start Menu, choose All Programs Microsoft SQL Server 2005 Configuration Tools SQL Server Surface Area Configuration. You will see the initial screen shown in Figure 3-10.

Figure 3-10: Initial screen of SQL Server Surface Area Configuration.

Using Surface Area Configuration For Services And Connections

Surface Area Configuration For Services And Connections allows you to configure the state of SQL Server services. You can stop, pause, resume, and start the SQL Server instance services. Moreover, you are able to configure whether the SQL Server instance will allow remote connections and, if so, which protocols to use. Refer to Figure 3-11.

Figure 3-11: SQL Server Configuration For Services And Connections dialog box.

Using Surface Area Configuration For Features

Surface Area Configuration For Features allows you to enable features on your server. For example, you can enable or disable features such as CLR Integration, Database Mail, Service Broker, or Native XML Web Services, as shown in Figure 3-12.

Figure 3-12: SQL Server Configuration For Features dialog box.

Most of these features can also be configured by using system stored procedures, such as sp_configure. For example, if you want to enable CLR Integration, you could run the following script on the SQL Server instance.

 EXEC sys.sp_configure N'clr enabled', N'1'; RECONFIGURE WITH OVERRIDE; 

Note 

Surface Area Configuration For Features executes the sys.sp_configure system stored procedure behind the scenes to configure most of the features. You could easily verify this by running a SQL Server Profiler trace while running the Surface Area Configuration For Features utility.

Sac Utility

The Sac utility is a command-line tool that helps you configure the surface area in local machines and applies the same setting to remote machines. The tool helps you to easily export the surface area settings in a machine to a configuration file and then use the file for other purposes, such as documenting the servers configuration. In the example below, the following code generates the file config.xml with the surface area setting of the local machine.

 sac out c:\config.xml 

The generated file is an xml-type document that can be used to import the surface area settings into a different machine. For example, to apply the configuration in a server named Server2, you would execute the following command-line batch.

 sac in c:\config.xml S Server2 

The Sac utility is located in the Program Files\Microsoft SQL Server\90\Shared directory, and you customize the execution by using the different command line arguments that can be seen by using the argument ?.