SQL Server: The hidden power of Surface Area Configuration Tool

The hidden power of Surface Area Configuration Tool

SQL Server 2005 comes with a Surface Area Configuration tool. This tool allows you to Control services and features installed on the SQL Server Box. You need to have Administrative right in order to work on the SQL Server Configuration tool. The Surface Area Configuration tool can be used to start, stop, enable and disable various services and features associated with SQL Server. The SQL Configuration tool can be run against a local or remote machine.

Problem: This tool works perfectly fine if you want to manage an instance and want to reduce the surface area. But if you are in an environment where you need to manage many SQL Servers you probably don’t want to click 1000 times just to enable\disable a feature on all your servers.

Solution: Just to make your life easier there is a command line version of the tool available. This tool is also called as sac utility & this is very powerful. The tool can be invoked from the command prompt or can be called from a command batch file.

The sac utility imports and exports Microsoft SQL Server 2005 surface area settings. Using this utility, you can configure the surface area on one computer, and then apply the same settings to other computers.

The easiest way to use the sac utility is to use the SQL Server Surface Area Configuration graphical user interface to configure one computer, then use sac to export the settings of that computer to a file. You can then use sac to apply the settings for all SQL Server 2005 components to other SQL Server 2005 instances on the local computer or on remote computers.

You can find the command line version of the tool in the “c:\program files\Microsoft SQL Server\90\shared” folder. Make sure that folder is in the path, or specify the full path to the file when executing it.

The syntax for the command line version of the tool is as follows:

sac {in | out} filename [-S computer_name][-U SQL_login [–P SQL_ password]]   [-I instance_name ]  [-DE][-AS] [-RS] [-IS] [-NS] [-AG] [-BS] [-FT] [-F] [-N] [-T] [-O]  [-H | -?]

To Read full Parameter Detail:

http://msdn.microsoft.com/en-us/library/ms162800(SQL.90).aspx

http://www.SQLServerGeeks.com/Assets/Uploaded-CMS-Files/a49fccf3-3ed2-4cc3-8951-b8289f5e4b3asac-sarabpreet.jpg

Some Examples:

1.       Export All Default Instance Settings

The following example shows how to export all of the feature, connections, and service settings for the default SQL Server instance to the file server1.out. Because the example specifies a user name but no password, sac will prompt for a password.

sac out server1.out –S server1 –U admin –I MSSQLSERVER

Once the information is saved to the file, copy it to another server and import the settings.

2.       Import Feature Settings to Another Server

The following example imports only the feature settings from the file server1.out and applies them to the default SQL Server instance on server 2. Because this example does not provide a user name, sac uses Windows Authentication.

sac in server1.out –S server2 –F

3.       Export All Local Feature and Network Settings

The following example will export all feature and connection settings, but not service settings, for all SQL Server instances and components on the local server to the file server1.out.

sac out server1.out –F –N

The practical application of this would be

1.       If there are a lot of servers to customize, or a lot of servers from which to collect information. The command line version of the tool can save time. By using the command line version of the Surface Area Configuration tool, configuring multiple servers can be done more accurately in less time.

2.       You can also take the backup of your configuration & make use of it if you need to rebuilt your server i.e., in the event of any disaster.

APPLIES TO
MS SQL Server 2005

Regards,

Sarabpreet Singh