Getting Started with SQL Server Management Studio

At first glance, SQL Server Management Studio might seem intimidating. So it helps to get an overview of it before you start using it. If you have used SQL Server Management Studio for the Beta or early adopter versions of SQL Server 2005, I suggest you go through this section because there are many new properties to configure, especially with respect to the Windows operating system.

Our work with SQL Server Management Studio here is going to be limited to registering and exposing servers and exploring the various nodes and options. This will help you become familiar with this exhaustive suite of tools so that you know what to grab for when working on SQL Server concurrently with the tough discussions that lie in wait. We will cover starting up, connecting to servers, navigating the environment, and some basic configuration.

Incidentally, Management Studio connects seamlessly to version 8.0 databases, which is SQL Server 2000. You can register version 8.0 databases and access all database objects, such as tables, procs, triggers, views and so on. There is one area I strongly recommend you do not open on SQL Server 2000 with SQL Server Management Studio, and that is Data Transformation Services (DTS) the predecessor to SQL Server Integration Services (SSIS) that now ship with SQL Server 2005.

Management Studio will let you see a list of old DTS packages but it will not let you open a package for editing unless you download a bunch of DTS management parts from Microsoft. I don’t intend to list them here or go into the subject in any depth because I am not the only one that has had grief with this process. I lost one package to corruption and decided that if I needed to work with DTS then the tool to use is the tool that was provided with SQL Server 2000 for the purpose, the very reliable SQL Server Enterprise Manager.

SQL Server Management Studio can be started from the Microsoft SQL Server menu under Programs. When its main window first appears and when you start it for the first time, you need to configure connection to a database engine before you can manage it.

Connecting to Servers

Management Studio manages the components and modules of each server as a hierarchy of services. In turn, each server is part of a collection of servers. Servers can be added to and removed from the server collection in Object Explorer as needed.

The idea behind managing a collection of servers or registering them with Management Studio is that you can simply attach to an instance of SQL Server from a single copy of Management Studio. This means you can manage hundreds of servers or instances from a single copy of Management Studio.

If you are working on a high-bandwidth network, you can hook up each server remotely from a local copy of Management Studio. However, if you are working on a low-bandwidth network, it might make more sense to log on to the server and run a local copy of the Studio on the remote server. Remote access is best via a RDP terminal session under Windows Server.

Getting Around Management Studio

As soon as you have connected to the server, you can drill down to the services you need to manage and configure. To disconnect from a server, you can right-click the connection and select the Disconnect menu item. Management Studio automatically disconnects from all servers when you close the MMC console.

Note 

A thorough understanding of how to set up and customize the Management Studio is well worth acquiring. This book is not the forum for that, but there are several works on the market that cover the basics and Visual Studio.

The program is divided into the following hierarchy of administrative sections:

• Servers

• Databases

• Security

• Server objects

• Replication

• Management

• Notification services

• The SQL Server Agent

The Management Studio is home to a substantial number of wizards. They can be easily accessed from the menus. You can also access the wizards from the Tools menu and in context menus as you drill down into the various folders representing your server’s services.

Configuring the Server Instance

Each instance of SQL Server 2005 can be specifically configured, interactively, in the SQL Server Properties dialog box. None of the properties is set in stone, and you can return to them after this initial walkthrough, as needed. Rather than offer a full explanation in this chapter, we will return to each tab as it relates to a chapter subject in the remaining chapters, where necessary. To access the properties dialog box, you need to perform the following steps:

1. Select the server in the tree, right-click and select Properties. The registered SQL Server Properties dialog box loads to the General tab, as illustrated in Figure 4–2.

   
   Figure 4–2: SQL Server Properties dialog box

2. On the General tab, enter the general server options such as the authentication mode and general security settings.

3. The Memory tab is used for configuring the instance’s management of memory. These options will vary depending on the server’s role, environment, number of connections, and so forth.

4. The Processor tab lets you configure threads, fibers, the number of processors, and so forth. It also lets you manage the query plan threshold for parallel queries.

5. The Security tab, the subject of Chapter 5, lets you manage security. Of special importance is the capability to change the system account.

6. The Connections tab lets you change the number of concurrent connections.

7. The Database Settings tab lets you manage several DBMS-oriented options, such as triggers, data interpretation, and so on. The mail login name of the mail client can also be set from this tab. The Database Settings tab provides the facility to interactively set a number of default database settings. These include settings for the recovery models, the default log directory, and the default index fill factor.

Many of the variables or properties that drive each instance of SQL Server can be accessed via system stored procedures and a number of system functions. And, as discussed earlier, you can tune and lube your server via the management object models. Most experienced DBAs find it easier to work with SQL Server with tried and proven scripts they have developed over the years. Several command-line utilities are another option, especially if the command prompt is more familiar turf for you than “clicky” Windows icons.