SQL Server Snap-In Management

Using Enterprise Manager and the Microsoft Management Console is like plugging in an old favorite

FOR YEARS, systems managers have waited for a single interface to manage applications on a server. We've grown accustomed to the idea from using Microsoft Office and other applications that have a common interface, which makes application management easier because you can use a single command set to manage multiple applications. SQL Server 7.0 needs such an interface because it runs on Windows 9x, Windows NT Server, and NT Workstation. This interface will let more users run SQL Server on their desktops for development purposes or end-user applications.

The Microsoft Management Console (MMC), which debuted with the NT Option Pack, provides this interface. SQL Server 7.0 uses MMC 1.1 for its Enterprise Manager and OLAP Services. MMC is a handy tool that lets SQL Server and Internet Information Server (IIS), for example, use snap-ins that provide management functionality for applications.

Screen 1, page 52, shows MMC with the SQL Server Enterprise Manager just after initial loading. The interface resembles Windows Explorer in look and feel, with folders for various items displayed in the tree.

You can create server groups in Enterprise Manager. Each server group can contain numerous servers, and SQL Server Group is the default. You can't rename this group, but you can create other groups and delete the default group if it doesn't match your naming convention. Screen 2, page 52, shows the Enterprise Manager with two new groups—CustomerService and Manufacturing. I created these groups, unregistered the server ABACO from the default group, and reregistered it in the CustomerService group.

To create a new server group, right-click the Microsoft SQL Servers folder and select New SQL Server Group from the menu. You will see the Server Groups dialog box, from which you assign group names. Choose Top-level or Sub-Group in the SQL Server Groups dialog box to determine folder groups. Screen 2 also shows two new top-level folders. After you create the group, right-click the group name and select Register SQL Server to assign the server to that group. A server can exist in only one group at a time. By selecting the Create a new top-level SQL Server group option in the registration wizard, you can automatically create a group when you register a server. The ability to group servers is a convenient way to think about servers, and grouping servers doesn't impose any security constraints or overhead on the servers in the group.

You can add other snap-ins to MMC and use them with the Enterprise Manager. Select Add/Remove Snap-in from the Console menu. Set the MMC to Author mode to enable this option. You can change the setting by selecting Options from the console menu, then checking the Always open console files in Author mode check box. Next, select and load one of the available snap-ins on the system. This approach lets you customize MMC settings for use with IIS, Microsoft Transaction Server (MTS), Microsoft Message Queue Server (MSMQ), and future snap-ins. Also, you can place MMC files on the network or send them to other users to share the configuration.

Using the Enterprise Manager to add snap-ins is simple. Use the folders in the tree on the left to hide or display elements you need to work with. For example, to get to databases, expand the Databases folder by clicking the + sign to the left of the name. When the folder is open, you can work with its elements, which display in the right window.

Also, you can use the shortcut menus to perform numerous object tasks. For example, to start, pause, or stop SQL Server, right-click the server's name in the tree view and select the appropriate command from the shortcut menu. The Action menu contains the context-sensitive items for the selected folder. In addition, you can access various tools from a folder's shortcut menu by clicking the Tools menu.

You can open a new window to display a folder's elements by right-clicking the folder and selecting New Window from the shortcut menu. You can use the Windows menu or the standard Windows controls to arrange and size the MMC windows.

When you double-click an item in the right pane, the resulting action depends on the item. For instance, double-clicking a folder opens it and drills down, but double-clicking an item such as a table displays its properties. You also can right-click items in the right pane and perform actions on them from the shortcut menu.

Visual Database Tools

Now let's look at new Enterprise Manager features. SQL Server 7.0 includes Microsoft's Visual Database Tools, a tool package that Microsoft first introduced with Visual InterDev 1.0. The package includes Query Designer and Database Designer. The Visual Database Tools let you create tables in SQL Server with the table designer; use database diagrams to modify SQL Server databases; design, execute, and save complex queries with Query Designer; use Query Designer to add, update, and delete data stored in database tables; and design objects such as tables, triggers, and stored procedures.

Enterprise Manager's Visual Database Tools let you easily access the tools without having to hunt for them. The exception is Query Designer, which hides behind Enterprise Manager's tables.

How do you use the Visual Database Tools in daily SQL Server management operations? They show up automatically in numerous places. For example, you can create a table by expanding the Databases folder for your server and right-clicking the database that you want to add a table to. Then, select New Table from the shortcut menu.

These steps will prompt you for a table name. When you enter a name and click OK, you'll see the New Table window, as Screen 3 shows. To fill in the table, enter a name for each column and specify column properties. Repeat these steps for each column, and save the table by clicking the Save button on the windows toolbar. These steps will generate the SQL script required to create the table and execute it.

You can save the SQL script at any time by clicking the Save Changes script button on the toolbar. When you click this button, it displays the Save Change Script dialog, which contains the script required to perform the current action. If you click Yes, the script will be saved to a file. Pay attention to the next message box, which contains the full path to the file containing the change script, to confirm the save action. You'll need this information to find the resulting file. After you've saved the script, you can execute it later by loading it into Query Analyzer or any other SQL tool.

Now, let's return to the table. You can use Enterprise Manager to modify tables. To do so, right-click the table and select Design Table from the shortcut menu. This action will open the Design Table window with the table settings. You can change the settings the same way you create a new table. For example, I created a simple table named Kens, then populated it with a few data rows. Then, I opened the Design Table window, modified a column by changing its length, and clicked the Save Change Script button on the toolbar. The Save Change Script dialog contained the SQL script in Listing 1, page 53. You can also use Transact SQL (T-SQL) to make these changes. Similar to any other automated tools, the way the Visual Database Tools modify the table structure might not be the most efficient. Thus, you have the option of saving the script and later modifying and executing it.

Finally, the change drops the existing table and renames the new table. Note that a simple change to one table generated all these actions. The more work you do before a save, the more work the server must do to write the changes into the change script. The server's workload also depends on how much data is in a table. For a simple table with a few rows, the script will execute rapidly and not place a heavy burden on the server. However, a table with 100,000 rows will take much longer to execute and place a heavy load on the server. Also, consider the number of users accessing the database. You don't want to execute SQL scripts that change the database's structure while others are using it.

An interesting feature of the Enterprise Manager is database diagrams. A graphical representation of portions of a database, database diagrams represent the database's physical structure and reveal table definitions and relations. Database diagrams are stored in the database, letting users with appropriate permission access the database and work with the diagrams.

You can create a new diagram by right-clicking the database diagrams folders—a subfolder of the Databases folder—and selecting New Database Diagram. After you select this command, the new diagram will open and the Create Diagram Wizard will start.

This wizard is handy for creating a new diagram and populating it with tables. On the wizard's first step, click Next, select the tables to include in the diagram, and click Add. If the Add related tables check box is checked, related tables will be added automatically each time you add a table. You can control the added level-related tables by changing the counter at the bottom of the dialog box. When you're finished adding tables, click Next. Finally, the wizard can automatically arrange the tables in the new diagram, or you can arrange them yourself. When I create a new diagram, I always let the wizard arrange the tables automatically. The last step, which Screen 4 shows, confirms the tables to add and gives you a chance to change the diagram.

You can use the toolbar on the database diagram window to access commands quickly. Database diagrams generate SQL script just as the Design Table window does. In fact, you can display a table's detailed properties by right-clicking the table and selecting Column Properties from the shortcut menu. Also, you can access the display properties by using the icon to the left of the Set Primary Key icon on the toolbar.

When you display the table's column properties, you can modify the table the same way you modified the Design Table window. In fact, when you view the tables in a database diagram, you're really using the Design Table window. In addition, you can use the New Table icon on the toolbar to open the Design Table window and create new tables. When you save the diagram, the new table will be added to the database diagram automatically.

Subtle Management

In one tool, the new MMC version of Enterprise Manager in SQL Server 7.0 provides enterprise management of numerous technologies. MMC is flexible and lets users configure it in any manner they choose. The new SQL Server and MMC 1.1 features are subtle and make management easy. And Windows 2000 and other products will use MMC to make management even easier. For example, you'll be able to snap in ActiveX components to monitor and manage your systems.