Home

	Expand Search

Enterprise SQL Server Manager Installation and Planning Guide

Chapter 7: Getting Started with Enterprise SQL Server Manager

Chapter 7

This chapter describes how to begin using Enterprise SQL Server Manager.

Back Up the TMR

After you install Enterprise SQL Server Manager and any Sybase-supplied patches, back up the TMR in which you installed Enterprise SQL Server Manager. For information about how to back up a TMR, see Chapter 6, "TME Maintenance and Troubleshooting," in Tivoli Management Platform Planning and Installation Guide.

Preparing the TME to Manage SQL Servers

Before you can use Enterprise SQL Server Manager, you must prepare the TME to manage SQL Server by:

• Assigning the appropriate Enterprise SQL Server Manager roles to administrators to allow them to perform management tasks on managed SQL Servers
• Assigning SQL Server logins to administrators to allow connections to SQL Server installations
• Configuring one or more policy regions with Enterprise SQL Server Manager resources
• Adding managed SQL Server resources to the policy regions
• Adding profile managers to the policy regions

All of these Enterprise SQL Server Manager activities require at least one existing TME administrator and one policy region. If your TMR does not have a policy region, create one now.

• For information about creating and configuring Tivoli administrators, see Chapter 3, "Tivoli Administrators," in Tivoli Management Platform User's Guide.
• For information about creating and configuring policy regions, see Chapter 5, "Policy and Policy Regions," in Tivoli Management Platform User's Guide.

Creating Enterprise SQL Server Manager Administrators

When you install Tivoli, a TME administrator is created. This administrator has the name "root@policy_region". After Enterprise SQL Server Manager is installed, you should create additional Tivoli administrators and associate them with the UNIX logins of the people who will administer Enterprise SQL Server Manager. Initially, only the root administrator can create new administrators and assign TME and Enterprise SQL Server Manager roles to them. Once the new administrators are created, they can start the TME under their names and begin the tasks involved in configuring your Enterprise SQL Server Manager installation.

To give a TME administrator access to SQL Server management functions, you must assign Enterprise SQL Server Manager roles to the administrator and assign one or more SQL Server logins for the administrator to use when connecting to managed SQL Servers.

Enterprise SQL Server Manager Administrator Roles

Enterprise SQL Server Manager extends the standard set of TME administrator roles with the following roles:

Table 7-1: Enterprise SQL Server Manager administrator roles

Role name	Authorizes an administrator to perform...
dump	Database backup operations. Lets an administrator back up databases within managed SQL Servers.
load	Database restore operations. Lets an administrator restore database backups within managed SQL Servers.
server	SQL Server configuration operations. Lets an administrator modify a server configuration, manage SQL Servers, display and stop SQL Server processes, and start and stop managed SQL Servers.
security	Authentication, authorization, and auditing operations. Lets an administrator create, modify, and delete logins, users, groups, remote servers, and remote logins. Lets an administrator modify object permissions, command permissions, server auditing, and object auditing.
space	SQL Server device configuration and allocation operations. Lets an administrator create, modify, and delete devices, dump devices, databases, device mirrors, segments, and thresholds.
schema	Schema management operations. An administrator can create, modify, and delete defaults, views, user datatypes, triggers, stored procedures, tables, rules.
cache	Cache operations. An administrator can create, modify, and delete named caches.
monitor	Launch of SQL Server Monitor.

Ideally, a hierarchy of administrator tasks is established for the enterprise. Within each TMR, a supervisory administrator assigns other administrators the roles and SQL Server logins they need to accomplish their assigned tasks. This process ensures that SQL Server configurations created and distributed using Enterprise SQL Server Manager cannot be compromised by administrator actions at the local SQL Server level.

To maintain security, an administrator must have the super role for the TMR or policy region to change any SQL Server login and password.

An administrator must have the appropriate combination of Tivoli, Enterprise SQL Server Manager, and SQL Server roles to perform an Enterprise SQL Server Manager administration task. The Tivoli roles allow an administrator to perform standard TMP operations. The Enterprise SQL Server Manager roles allow an administrator to perform Enterprise SQL Server Manager tasks within the TMP and control access to SQL Server. The SQL Server roles allow the administrator to perform tasks that directly act on SQL Server.

For example, to distribute a SQL Server profile, an administrator must have the TME role admin, senior, or super, the ESSM role server, and the SQL Server role sa_role. The combination of roles varies according to the task. For the roles required for each Enterprise SQL Server Manager administration task, see the Enterprise SQL Server Manager User's Guide.

Note: When assigned using Enterprise SQL Server Manager commands, the SQL Server roles are specified as sa_role, sso_role, and oper_role. When assigned through the graphic interface, SQL Server roles are specified as System Administrator, System Security Officer, and Operator.

Assigning Enterprise SQL Server Manager Roles to Administrators

You can assign Enterprise SQL Server Manager roles at both the TMR and the resource level. Roles assigned at the TMR level apply to all resources within a TMR. Roles assigned at the resource level apply only to a specific resource on the administrator's desktop. The following steps describe how to assign TMR roles to an administrator. For more information about assigning administrator authorization roles, see Chapter 3, "Tivoli Administrators," in Tivoli Management Platform User's Guide.

Note: To assign a TMR role to an administrator, you must have the Administrators collection icon on your TME desktop and you must have super or senior role over the Administrators resource.

1. Open the Administrators window to see the icons representing the defined TME administrators.

2. Locate the administrator you want to modify, and choose Edit TMR Roles from that administrator icon's pop-up menu:

   Figure 7-1: Administrator's popup menu
   The Set TMR Roles dialog box opens:

   Figure 7-2: Set TMR Roles dialog box
   

3. In the Available Roles list box, select the roles you want to assign to the administrator. The Enterprise SQL Server Manager roles are:

4. server

5. space

6. security

7. dump

8. load

9. schema

10. cache

11. monitor

    For example, if you want the administrator to be able to create managed SQL Server resources, the administrator needs the server role.

12. Click the left-pointing arrow to move the selected roles to the Current Roles list box.

13. Choose Change & Close to set the roles for the administrator and return to the Administrators window.

14. Repeat these steps, beginning with step 2, for each Enterprise SQL Server Manager administrator you want to configure.

Assigning SQL Server Logins to Administrators

For each SQL Server on which an administrator is to perform management tasks, the administrator must have a valid SQL Server login assigned. The login must already exist on the SQL Server installation the administrator is to access.

SQL Server Logins for Multiple UNIX Logins

A single TME administrator can have more than one UNIX login associated with it. For example, the administrator "Northeast_Region" could be mapped to the UNIX logins of several administrators who have responsibilities in the Northeast policy region. Enterprise SQL Server Manager allows you to specify a different SQL Server login for each UNIX login associated with the administrator.

SQL Server Logins for Multiple Management Levels

You can configure SQL Servers logins at the following levels:

• For all managed SQL Servers within a TMR (enterprise-wide)
• For all managed SQL Servers within a policy region (supersedes any enterprise-wide SQL Server login assignment)
• For a specific managed SQL Server (supersedes any enterprise-wide or policy region SQL Server login assignment)

  Note: Event Monitoring Services allows you to monitor the relationship between the Enterprise SQL Server Manager Administrator login and the administrator's SQL Server login and password. If the SQL Server password changes, the Enterprise SQL Server Manager Administrator cannot log in to SQL Server. Therefore, you may want to use the Sybase Login Status monitor to contact you if this relationship changes.

Be sure that the SQL Server login you assign to an administrator has the correct SQL Server roles for the Enterprise SQL Server Manager tasks that the administrator is to perform. Set SQL Server roles from the command line or in the graphic interface. See Enterprise SQL Server Manager User's Guide for SQL Server role requirements.

Note: Assigning SQL Server logins to Enterprise SQL Server Manager administrators requires the super role.

Assigning ESSM Administrator SQL Server Logins for the Enterprise

To set an enterprise-wide SQL Server login for an administrator:

1. Open the Administrators window.

2. From the administrator's pop-up menu, choose Edit Logins.

   The Set Login Names dialog box opens.

   Figure 7-3: Set Login Names dialog box
   

3. In the Current Login Names list box, select the UNIX login name for the administrator.

4. Choose Set SQL Server Logins. The SQL Server Logins for Administrator dialog box opens, displaying the Enterprise Level tab. The options on this tab let you set the enterprise-wide SQL Server login for the selected administrator UNIX login.

   Figure 7-4: SQL Server Logins for Administrators: Enterprise Level
   

5. In the SQL Server Login box, enter the SQL Server login name that the administrator will use when accessing any managed SQL Server in the TMR. In Figure 7-4, the Tivoli administrator "jhodges" is associated with the SQL Server login "sa".

6. In the SQL Server Password box, enter the existing password in SQL Server for this SQL Server login name.

7. In the Confirm Password box, retype the password to confirm that it is correct.

8. Choose Apply to record the login assignment in the TMR database.

9. Choose Done to close the dialog box.

10. Repeat these steps, beginning with step 3, for each UNIX login you want to configure for the selected Enterprise SQL Server Manager administrator icon, selecting a new UNIX login each time.

Assigning ESSM Administrator SQL Server Logins for Policy Regions

1. Open the SQL Server Logins for Administrators dialog box as described in the procedure on .

2. Select the Policy Region Level tab. The Policy Region Level tab shows a list of all current policy region SQL Server login assignments for the administrator.

   Figure 7-5: SQL Server Logins for Administrators: Policy Region Level
   

3. In the Policy Region Level group box, specify the list of policy regions and associated SQL Server logins you want to assign to the administrator, one pair at a time. You can add, remove, or change policy region SQL Server login assignments until you are satisfied with the list. In Figure 7-5, the Tivoli administrator "jhodges" is associated with the SQL Server login "sa" in the Northeast policy region. Data has been entered to associate "jhodges" with the SQL Server login "sso" in the Inventory policy region.

4. After you finish making SQL Server login assignments for the administrator, click Done to close the dialog box.

Assigning ESSM Administrator SQL Server Logins for Managed SQL Servers

1. Open the SQL Server Logins for Administrators dialog box as described in the procedure beginning on .

2. Select the SQL Server Level tab. The SQL Server Level tab shows a list of all current policy region SQL Server login assignments for the administrator.

   Figure 7-6: SQL Server Logins for Administrators: SQL Server Level
   

3. In the SQL Server Level group box, specify the list of managed SQL Servers and associated SQL Server logins you want to assign to the administrator, one pair at a time. You may add, remove, or change managed SQL Server login assignments until you are satisfied with the list. In Figure 7-6, Tivoli administrator "jhodges" is associated with the SQL Server login "sa" in SQL Server OSLO_DEV and data has been entered to associate "jhodges" with the SQL Server login "oper" in SQL Server BONN_DEV.

4. After you finish making SQL Server login assignments for the administrator, click Done to close the dialog box.

Configuring Policy Regions for Managed SQL Server Resources

Administrators manage SQL Server by creating managed SQL Server resources within policy regions. Managed SQL Servers can reside in a policy region along with other types of resources (managed nodes, task libraries, and so forth), which allows you to group and organize resources to suit your needs.

However, before you can create a managed SQL Server resource within a policy region, you must add the resource type (ManagedSQLServer) to the policy region's list of valid resources. Doing this adds ManagedSQLServer to the policy region's Create menu.

In addition to the ManagedSQLServer resource type, add all other Enterprise SQL Server Manager resources. Doing so enables all Enterprise SQL Server Manager management functions for managing SQL Servers in the policy region.

The list of available Enterprise SQL Server Manager resource types includes:

• ManagedSQLServer
• SQLCacheProfile
• SQLDatabase
• SQLDatabaseProfile
• SQLDatabaseProfileMgr
• SQLDataTypeProfile
• SQLDbDeviceProfile
• SQLDefaultProfile
• SQLDumpDeviceProfile
• SQLGroupProfile
• SQLIndexProfile
• SQLLoginProfile
• SQLProcedureProfile
• SQLRemoteServerProfile
• SQLRuleProfile
• SQLSegmentProfile
• SQLServerProfile
• SQLServerProfileMgr
• SQLTableProfile
• SQLTriggerProfile
• SQLUserProfile
• SQLViewProfile

  Note: Adding a resource type to a policy region's list of valid resources requires the senior role.

To add all the Enterprise SQL Server Manager resource types to a policy region's list of valid resources, follow these steps:

1. Open the policy region you want to modify.

2. From the policy region's Properties menu, choose Managed Resources. The Set Managed Resources dialog box opens:

   Figure 7-7: Set Managed Resources dialog box
   

3. In the Available Resources list box, select the Enterprise SQL Server Manager resource types (listed on ).

4. Select the left-pointing arrow to move the selected resource types to the Current Resources list box.

5. Choose Set & Close to add the resource type to the policy region's list of valid resources and close the Set Managed Resources dialog box.

6. Repeat these steps for each policy region in which you want to create managed SQL Server resources.

Creating a Managed SQL Server Resource in a Policy Region

To perform Enterprise SQL Server Manager administration tasks on SQL Server, you must create a corresponding managed SQL Server resource in a policy region. Doing this registers the managed resource in the TMR database. Creating a managed SQL Server resource also establishes which machine will serve as the management host for the managed SQL Server.

Enterprise SQL Server Manager Management Hosts

A management host is the TME client on which the Enterprise SQL Server Manager processing is to occur on behalf of the managed SQL Server. Creating a managed SQL Server resource also establishes a logical connection between the managed SQL Server and its management host. The management host may be any TME client on which Enterprise SQL Server Manager is installed.

The following figures show different configurations to consider when choosing a management host.

Figure 7-8: Managed SQL Server resides on management host

In Figure 7-8, the managed server's host and its management host are the same machine. SQL Server is running on a TME client with Enterprise SQL Server Manager installed. This is the most common configuration.

Figure 7-9: Managed SQL Server and remote management host

In Figure 7-9, the managed server's host and its management host are two different machines. SQL Server is running on a computer without the TME, and its management host is a TME client running Enterprise SQL Server Manager.

The advantage to this configuration is that you can use Enterprise SQL Server Manager to manage SQL Servers running on platforms not supported by Enterprise SQL Server Manager. This is possible because communication between Enterprise SQL Server Manager and the managed SQL Server occurs using Sybase connectivity.

The disadvantage is that when you manage SQL Server on an unsupported host, you lose some features. See "Managing SQL Server on Unsupported Platforms" in Chapter 2, "Planning an Enterprise SQL Server Manager Deployment" for more information about managing SQL Server on unsupported platforms.

Creating a Managed SQL Server Resource

	TME	ESSM	SQL Server
Required roles	user	server	none

To create a managed SQL Server resource in a policy region, follow these steps:

1. Open the policy region window in which you want to create the managed SQL Server resource. The policy region must be configured to allow this resource type (see "Configuring Policy Regions for Managed SQL Server Resources").

2. From the Create menu, choose ManagedSQLServer. The Manage SQL Server dialog box opens:

   Figure 7-10: Manage SQL Server dialog box
   

3. In the Name box, enter the name of the SQL Server you want to add to the policy region as a managed resource.

4. In the SQL Server Host box, enter the name of the machine on which SQL Server is running.

5. In the Management Host box, select the TME client that is to be the server's management host (see "Enterprise SQL Server Manager Management Hosts").

6. Choose OK to create the new managed SQL Server resource in the policy region.

7. Repeat these steps, beginning with step 2, for each SQL Server resource you want to add to the policy region.

After you create a managed SQL Server, the TMP adds a SQL Server icon to the policy region. Double-clicking this icon opens a SQL Server window, the primary window in the Enterprise SQL Server Manager GUI. For information about using the SQL Server window, as well as how to create and use Profile Manager windows, see the Enterprise SQL Server Manager User's Guide.

Note: Creating a managed SQL Server takes several minutes.

Creating Profile Managers

There are two types of profile managers in Enterprise SQL Server Manager: SQL Server Profile Managers and Database Profile Managers.

Creating a SQL Server Profile Manager

	TME	ESSM	SQL Server
Required roles	senior	any	none

To create a SQL Server Profile Manager:

1. From the Create menu of a policy region window, choose SQLServerProfileMgr. The Create SQL Server Profile Manager dialog box opens.

   Figure 7-11: Create SQL Server Profile Manager dialog box
   

2. In the Name box, type a name for the profile manager.

   Note: Once you create the profile manager, you cannot change its name, so be sure you have entered the name you want before you complete this procedure.

3. In the SQL Server list box select a name from the list.

4. Click OK.

Enterprise SQL Server Manager creates the SQL Server Profile Manager, and its icon appears in the policy region window.

Note: Only one SQL Server Profile Manager can be associated with a given SQL Server. (Multiple Database Profile Managers can be associated with a SQL Server; each Database Profile Manager is associated with a different database in the SQL Server.)

Creating a Database Profile Manager

	TME	ESSM	SQL Server
Required roles	senior	any	none

To create a Database Profile Manager:

1. From the Create menu of a policy region window, choose SQLDatabaseProfileMgr. The Create Database Profile Manager dialog box opens.

   Figure 7-12: Create Database Profile Manager dialog box
   

2. In the Name box, type a name for the profile manager.

3. In the SQL Server box select a SQL Server from the list.

   Note: Once you create the profile manager, you cannot change its name, so be sure you have entered the name you want before you complete this procedure.

4. Select a name from the Database drop-down list.

5. Click OK.

Enterprise SQL Server Manager creates the database profile manager, and its icon appears in the policy region window.

Note: Only one Database Profile Manager can be associated with a given database.

For more information about creating profile managers, see "Enterprise SQL Server Manager Profile Managers" in the Enterprise SQL Server Manager User's Guide.