SQL Server 2000 Administration in 15 Minutes a Week: Enterprise Manager Introduction

Friday May 24th 2002 by Michael Aubert
Share:

Michael Aubert's fourth article in the SQL Server 2000 Administration in 15 Minutes a Week series takes a look at the tools provided with SQL Server 2000, with topics including navigating Enterprise Manager, SQL Server Groups, adding and modifying SQL Server Registrations, system databases, and Books Online.



Welcome to the fourth article in my series SQL Server Administration in 15 Minutes a Week. Up to this point the series has focused on options, requirements, and the installation process. This week we will start working with the tools provided with SQL Server 2000. The topics for this article include:

- Navigating Enterprise Manager
- SQL Server Groups
- Modifying SQL Server Registrations
- Adding SQL Server Registrations
- System Databases
- Books Online


Navigating Enterprise Manager

The Enterprise Manager is a graphical interface tool used to administer your SQL Servers. You can use the Enterprise Manager to configure SQL Server options, create/edit/view databases, perform maintenance and backups, and do quite a few more tasks that we will look at over the next few weeks.

Back a couple of articles we saw that the SQL Server 2000 setup creates a program group on the Start Menu named "Microsoft SQL Server." To launch the Enterprise Manager Click Start > Programs > Microsoft SQL Server > Enterprise Manager



If you have ever used the Microsoft Management Console (MMC) you will notice that the Enterprise Manager looks familiar. This is because the Enterprise Manager is a MMC snap-in like many of the tools provided with Windows 2000. If you would like more information on using the Microsoft Management Console have a look at the following link:

http://www.microsoft.com/windows2000/techinfo/howitworks/management/mmcover.asp

By expanding "Microsoft SQL Server" and then expanding "SQL Server Group" you can locate the SQL Server you have installed on your local system. Expanding the tree further allows you to manage databases, security, replication, etc.

Right clicking the SQL Servers icon in the tree view will also popup a menu with options that allows you to start/stop the server, view the server messages, access the server's properties, etc.





Enterprise Manager also provides you with a quick look at the status of your SQL Servers. Depending on the icon displayed in the tree view you can tell if SQL Server is running, stopped, paused, or if you are currently connected to the server. Use the following table to check the status of your SQL Servers:

Server Running, You are currently connected
Server Running, You are not currently connected
Server Paused
Server Stopped


SQL Server Groups and Registrations

In many cases you will want to reorganize how your SQL Servers (or Instances) are listed in Enterprise Manager. To make organizing your SQL Servers simpler, Enterprise Manager allows you to create Server Groups. Creating a new group is not complicated; start by right clicking "Microsoft SQL Server" on the Enterprise Manager tree.

Select "New SQL Server Group" from the popup menu.





The Server Groups screen allows you to create new groups as Top level (under "Microsoft SQL Server") or as a Sub-group under an existing group.

Enter a name for the group in the "Name:" textbox and click OK.




 

You can continue to add Groups and Sub-groups until you have the ideal layout for your organization.


Page 2: Server Registrations


 » See All Articles by Columnist Michael Aubert


Modifying Server Registrations

To access an instance of SQL Server in Enterprise Manager you must first register the server. When you install SQL Server, setup automatically registers the instance in Enterprise Manager. If you need to move a SQL Server Registration to another group in Enterprise Manager, or if you need to update the authentication information, you can simply right click the server in the Enterprise Manager tree and select "Edit SQL Server Registration Properties..."



Click to Enlarge


To change the group the server is listed under, select another group name from the "Server Group:" drop-down list.

The "Display SQL Server state in console" checkbox allows you to enable or disable the automatic display of status icons. If you disable this option the server's icon will appear without a status indicator when you open Enterprise Manager:

The "Show system databases and system objects" checkbox will allow you to hide system objects. System objects include system databases, tables, stored procedures, etc... (more on system databases later in this article)

Finally, the "Automatically start SQL Server when connecting" checkbox will automatically start the SQL Server service if the server is stopped when you try connecting.

Once you have made your changes click OK.





Adding SQL Server Registrations

If you need to register a new SQL Server right click any server group and select "New SQL Server Registration..."





Unless you have previously run the wizard and disabled it, you are greeted with the Register SQL Server Wizard welcome screen. Click Next to continue.





Next select the SQL Server(s) you would like to add from the left list-box and click "Add." If your server is not on the list, enter the name of the server in the text-box provided and then click "Add." If your server is already registered in Enterprise Manager the server will not appear in the list of servers. Once you have selected all the servers you would like to add, click Next.





Select the appropriate type of authentication for your server and click Next. Note if you select SQL Server Authentication you will then be prompted for the account and password Information. SQL Server Authentication also gives you the option to prompt you for authentication information each time you connect to the server with Enterprise Manager. Windows Authentication uses the credentials of the user that is currently logged on when you connect to a server using Enterprise Manager.





Next, select the group you would like to add your SQL Server under. You can also create a new top-level group if one does not currently exist. After you select the Server Group, click Next.





Click Finish to add the Server(s) to Enterprise Manager.





The Server(s) is (are) then registered and you are given a status of the registration process. If any registrations fail you can select the server in the status window and click Properties. From the properties screen you can then confirm that your login information is correct. When your servers have been registered, click Close. Your new server(s) now appear(s) in Enterprise Manager under the group name you selected in the wizard.




Page 3: System Databases


 » See All Articles by Columnist Michael Aubert


System Databases

When you install SQL Server several databases are created automatically. Two of the databases (Northwind and pubs) are sample user databases and can be used as examples, or you can safely delete them (but they don't take up a lot of hard disk space). The other four special system databases (master, model, msdb, and tempdb) are used by the system and should not be removed. To view the databases that are on your SQL Server, in Enterprise Manager expand the group your server is located in, then expand the server, and last expand the Databases folder. Remember, if you have the "Show system databases and system objects" checkbox unchecked in the SQL Server's registration properties, you will be unable to see the system databases in Enterprise Manager.



Click to Enlarge


master Database -- Contains information about the server overall and all databases created. The information in this database includes: file allocations, disk space usage, system-wide settings, user accounts, and the existence of other databases and other SQL Servers.

model Database -- This database provides a template for all newly created databases. If you ever need to add a table or stored procedures to all the new databases you create, simply modify the model database.

msdb Database -- Used by the SQL Server Agent service.

tempdb Database -- Is a workspace used for holding temporary information.


Books Online

The last topic I want to cover in this article is the SQL Server Books Online (sometimes called BOL) resource. Books Online, located on the start menu under "Programs" > "Microsoft SQL Server", are an invaluable resource. If you ever have a question the first place I would go are the Books Online -- they provide information on just about every topic dealing with SQL Server. One thing you may notice is that the Books Online are also used as the online "help" inside Enterprise Manager.





That finishes it up for this week's (fairly simple) article. Next week we will take a look at database files and we will also create our first database. As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.

Mike
maubert@databasejournal.com
www.2000trainers.com


» See All Articles by Columnist Michael Aubert


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved