As you can see, Query Analyzer is a very powerful tool. It has been greatly enhanced in SQL Server 2000 to be the programmer's and the database administrator's best friend. However, you still have SQL Server Enterprise Manager, which is meant to be SQL Server's primary graphical administrative and development interface. There's very little SQL Server Enterprise Manager can do that you can't accomplish by using a Transact-SQL command in Query Analyzer. However, using the Enterprise Manager is sometimes more convenient, especially when you are new to SQL Server.

SQL Server Enterprise Manager is an MMC snap-in - a common utility that Microsoft and third-party vendors can use as the common administrative interface to their respective products. All Microsoft BackOffice products, as well as system components within Windows 2000 and Windows XP, use the MMC as their primary administrative interfaces.

Registering a Server

When you start SQL Server Enterprise Manager, you might need to register a server. If you are sitting at a computer with SQL Server installed, your local SQL Server is registered for you during setup. If you have an earlier release of SQL Server installed on the Windows 2000 computer on which you installed SQL Server 2000, you might also see a SQL Server 6.x group. To register a server, expand the Microsoft SQL Servers option, and you should see the default group, SQL Server Group. Highlight the SQL Server Group option, and then from the Action menu, select New SQL Server Registration.

I recommend that you check the box to not use the wizard in the future because registering a SQL Server is one of the easier tasks you can perform.

Enter your computer name and then select the security mode you want to use. You can use Windows NT security mode (if the SQL Server is running on Windows NT), or you can specify a SQL Server security login (which works only on Windows 9x by default). Select the SQL Server login option, and complete your SQL Server login credentials if you select to use SQL Server Authentication. Because you previously installed your default instance in Windows Integrated Mode, you must select Use Windows NT Authentication.

Notice that you can also choose to be prompted for your login and password every time you try to connect (if you choose to use SQL Server authentication). Use this option on a Windows 9x computer to protect your security. If you are concerned about security, you should probably be using Windows 2000, which is much more secure than the Windows 9x operating systems.

You can select to add this server under the default grouping of servers or to create a new grouping in the Server Group text box near the bottom of the dialog. This grouping is used strictly as an organizational tool for your desktop. The SQL Server computers on your network have no knowledge of this grouping. You can also change several options such as to automatically start SQL Server when you use SQL Server Enterprise Manager and try to connect to the server and view the system databases. For the purposes of this book, make sure that you check all available options.

Now click OK—that's it! You've configured your first registration for Enterprise Manager.

Note: Of course, you just got an error that says A Server with this name already exists, didn't you? When you install SQL Server, the setup program automatically registers your local installations in your copy of Enterprise Manager. So, for your local copies, you don't need to register them; they are already there. For any remote copies, however, you need to go through this registration process.

Examining How Enterprise Manager Works

Close the registration dialog by clicking the Cancel button. You see that your servers are already registered in the left pane of Enterprise Manager when you expand the SQL Server Group and then expand each server. You can tell when you are connected by the presence of the red lightning bolt through your server icon.

Within each server, the Databases folder lists the databases installed on your server. The Data Transformation Services folder shows any DTS "packages" that you've set up, as well as Meta Data Services packages. The Management folder shows administrative management features of SQL Server. The Replication folder shows any replication configuration changes that you've made to your server. The Security folder allows you to configure security in SQL Server. The Support Services folder enables you to control other SQL Server-related services on your computer, such as the Distributed Transaction Coordinator and full-text search.

Now expand the pubs database folder, and then highlight the pubs database in the left pane. Notice how a new Web page is then displayed on the right. This is how Enterprise Manager works: Container objects are typically shown on the left, and the contents of the container objects, or other information, are displayed on the right.

Some dialogs also open separate windows or dialogs for you to work with. For example, right-click the pubs database folder on the left, and select Properties from the pop-up menu. Notice that a new dialog opens on top of Enterprise Manager. Property sheets (dialogs with the descriptions of properties of an object) often appear in these separate dialogs. Click Cancel to dismiss the pubs database's property sheet without making any changes.

Import and Export Data

The Import and Export Data choice in the Microsoft SQL Server program group starts the DTS Wizard.

Configure SQL XML Support in IIS

By using the Configure SQL XML Support in IIS option, you can set up your Web server to allow direct queries into SQL Server using Extensible Markup Language (XML).

Profiler

The SQL Server Profiler utility enables you to monitor all activity on your SQL Server. You can also use the Profiler to perform performance tuning activities, such as examining the execution plan that SQL Server will use to run your queries. The Profiler has sophisticated filtering mechanisms to let you keep track only of specific users, applications, or types of commands. You can monitor more than 100 different events from SQL Server 2000.