Surface Area Configuration Tool (Part 1 of 3)
By: Sarah Barela
Getting Started
Introduction
Welcome to using and understanding the surface area configuration tool. I am Sarah Barela and I will be leading the session. Let’s get started.
In this session we will be covering the surface area configuration tool, what it is, and how you would use it to set up your servers. We will also talk about all the new services and features in SQL Server 2005 and the implication of enabling those features. As we move through each of these components I will demo the corresponding part of the surface area configuration tool for you. Let’s get started.
What Is SAC And How Do You Use It?
What is the surface area configuration tool? The surface area configuration tool, or SAC – I’m not sure if anybody else calls it SAC, but I do and I will throughout the presentation – is a new configuration tool that ships with SQL Server 2005, and it can be used to enable, disable, start, or stop features, services, and network protocols. The reason this tool is needed was that in SQL Server 2005 there are so many new features and services that SQL Server has a large surface area that is vulnerable to attack from people who might be trying to compromise your server.
For example, XP command shell is a feature that can be used by a malicious user to attack your server. Now in SQL Server 2005 XP command shell is disabled by default. In SQL 2000 you had to go in and disable it if you didn’t want to use it, now you can use the surface area configuration tool to enable it only if you need to use it. The other really nice feature of the surface area configuration tool is that once you have created a policy for enabling the services and features that you need, you can export those configurations for disaster recovery purposes or to replicate on other servers in your environment.
Okay, let’s go ahead and open up the tool. You can open the tool from the start menu, all programs, SQL Server 2005, configuration tools, and down here at the last option is the SQL Server surface configuration tool. When it opens up it automatically connects to your local host. Now the thing to keep in mind about the configuration tool is that it connects to servers, not instances, so you can configuration all the instances on one server from this tool.
Now the surface area configuration tool has two distinct parts: there is the surface area configuration for services and connections and the surface area configuration tool for features. Now the SAC for services and connections is where you can enable or disable whole services like full text services or analysis services. It also is where you can configure whether you want to allow remote connectivity for each of those services.
The SAC for features is where you can enable or disable component features of those services. For instance, database mail is a component of the database engine so you would use the surface area configuration for features to enable database mail. Okay, we’re going to go ahead and get started with the surface area configuration for services and connections. When it loads it goes through and looks at all of your instances.
If you have a lot of instances this could take up to a minute, but since I only have one it was pretty quick. You have two choices on how you want to view your services here: you can view them by instance or you can view by component. Viewing by component is handy if you have a lot of instances on your server, so you could go and set all the instances at one time. I’m going to view by instance here since we only have the one.
Stop! Start! Pause! Resume..
Now, one of the things, what you can do here for all of these services is you can change the startup type, and you have automatic, manual, or disabled. You also have the option of starting, stopping, pausing, or resuming each of these services. Now I wanted to take a moment and discuss the pause button. Start and stop are pretty clear as to what they do, but pausing is a little less so.
Pausing is – or if you decide to select the pause button what that does is it allows people who are currently connected to the server to finish up their processes, but it won’t allow any new connections to the server. This is basically a really nice way of shutting down your server. You can pause it, wait for all the users who are currently connected to finish up, you could send them a little message, and then you could go ahead and shut down your server.
Now, I’ve been a DBA for more than ten years and I have never once done this. But you could be a much nicer DBA than I am and like to give your users warning, this is where you would come to pause your SQL Server. Now, you can then resume it or go ahead and stop it to go on with your maintenance. We’re going to go ahead and just turn it back on.
The Core Of SQL Server
Let’s go ahead and get started with the database engine. Now the database engine is first and that’s because it’s the core of SQL Server. It is what most people consider to be SQL Server, the database engine is what stores the data, it processes it, it secures it. I would leave this set to automatic and running unless you’re in a development environment where you would maybe not use your SQL server all the time. But the last thing you would want in a production environment is your SQL Server not to be running for some reason.
Underneath the services you have remote connections. Now, for SQL Server Express and Developer and Evaluation editions remote connections is disabled by default. What this means is that no one else outside of the connections from the local server can connect to the instance of SQL Server. If you have Studio Manager running on your local machine and you want to connect to an instance of SQL Express on another machine you won’t be able to do that, and the reason is that the remote connections are disabled. I see this error all the time: an error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005 this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Now you also will get this error if you are connecting to the wrong server. It is like a general connection error, but it is the most common error that I see when new users are using SQL Server. What you would need to do is if you were using SQL Express is you would need to come in here to the surface area configuration tool and select the remote connections. Then you would want to enable local and remote connections.
Here we’re going to enable using TCP/IP only. Now, Microsoft recommends that TCP/IP only be enabled as it requires the fewest firewall ports to be opened. And truthfully TCP/IP works for most people. If you do have a legacy application you might choose to enable both TCP/IP and named pipes.
Now, the other thing to keep in mind is this only applies to incoming remote connections. Your outbound connections for if you were using a linked server or something like that, there are no limitations placed on it so you wouldn’t have to make a change. And the other thing to note is if you do change the remote connections you have to restart the SQL Server engine to apply the change.
Is Analysis Services For Me?
Now, what we have here is now all of these services that can be configured within SQL Server, and let’s start with analysis services. Analysis services is not something that the average DBA works with, but if you are an expert in business intelligence or data warehousing, analysis server is the tool that allows you to create and manage and design OLAP and data warehouse solutions. When you install analysis services you have the option to set whether the service is automatic or manual.
Using the surface area configuration tool is where you could come if you later decide analysis services is not for me, you can come in here and set it to disabled. Analysis services also has remote connections and it works the same way as the database engine. You can either choose to enable or disable it there.
Reporting server. Reporting server was introduced as an addition to SQL 2000, but in SQL 2005 reporting services is fully integrated into the SQL Server suite. It is a reporting tool that you can use to create reports from not only SQL Server, but other data sources. Nearly anything you could connect to using an OLDB or an ODBC connection you can create a report from. You can create reports in a variety of formats like HTML or PDF, Xcel, Word. And it is set to automatic by default.
The SQL Server Agent, now this is my biggest pet peeve in SQL Server. The SQL Server Agent is the scheduling application in SQL Server, you use it to schedule your backups and your maintenance plans. And by default the SQL Server Agent is set to manual and stopped. Now this can have disastrous results if you’re not aware of it. A lot of people will log in to Studio Manager and say, “Oh, the SQL Server Agent isn’t running,” and they will go ahead and start it up. But not realizing that the next time their SQL Server is rebooted that it won’t start up because the start up type is still set at manual.
What will happen is somebody will go and create a backup plan or a maintenance plan and think that their backups are running just fine, and they don’t find out until here’s a disaster and need to restore that they have no backups. This is the very first thing that I change after I install SQL Server, and let’s go ahead and make that change here. So we go to the SQL Server Agent and we’re going to go ahead and change this to automatic and then start this up. And it will take a second to start up.
-----------
This article was taken from the session presentation at the SSWUG.ORG virtual conference. Please be sure to see the other parts of this article, if any, linked at the top of this article. For additional information about the SSWUG.ORG virtual conference and expos, please see http://www.sswug.org/uvc
