Enterprise SQL Server Manager Installation and Planning Guide

Home

Enterprise SQL Server Manager Installation and Planning Guide

Chapter 2: Planning an Enterprise SQL Server Manager Deployment

Planning the Enterprise SQL Server Manager Deployment

Once you have gathered the information described in the previous sections, begin to plan your Enterprise SQL Server Manager deployment. Plan your deployment in the following phases (worksheets are on following pages):

TMR - TMR Plan
Policy regions - Policy Region Configuration
Profile managers - Profile Manager Configuration
Administrators - Administrator Role Assignment
Event monitoring - Event Monitoring Plan
Task management - Task Management

Planning TMRs

Decide how many TMRs you need and complete a TMR Planning worksheet for each TMR.

You can organize management of more than one business function in a TMR by creating policy regions devoted to each function. Alternatively, create a TMR for each major business function. Base this decision on the business functions you are managing, the number of nodes you plan to manage, and the geographical distribution of the nodes.

Use the high level information in the Business Function Description and the information in your SQL Server Survey to help make these decisions.

Avoid managing a SQL Server installation from more than one TMR. This can create synchronization problems.

Managing SQL Server on Nonsupported Platforms

To maximize performance, Sybase recommends one management host per managed SQL Server. This is particularly important if you are managing a SQL Server installation on a platform that does not have Enterprise SQL Server Manager installed or is not supported by the TMP. If you must manage more than one SQL Server from a management host, try to minimize the number per host.

TMR Plan

Complete one worksheet for each TMR.

TMR server: ___________________

Will this TMR share resources with another TMR? If so, will connections be one-way or two-way? What resource types will you share between the TMRs?

_________________________________________________________

List client machines:

_________________________________________________________

List policy regions to be created:

_________________________________________________________

Planning Policy Regions

For each policy region listed on the TMR Plan, complete a Policy Region Configuration worksheet. Policy Region Configuration asks for the following information for each policy region:

Overall purpose of the region
SQL Server installations to be included in the policy region.
Profile managers to be created in each policy region

For each profile manager, complete Profile Manager Configuration:

Define the profiles that you want to manage
Define the members for each profile
List the policies needed for each profile
List the subscribers

Table 2-1 lists the profiles that you can include in a SQL Server Profile Manager. Table 2-2 lists the profiles that you can include in a Database Profile Manager.

Planning Policies

As mentioned earlier, default policy supplies default values when you create an object. Validation policy ensures that the attributes of newly created objects and modified objects conform to values you specify.

In Enterprise SQL Server Manager, policies are applied to attributes of profiles in Enterprise SQL Server Manager profile managers. Use the list of rules and default values on the SQL Server Inventory to plan the policies that you want to enforce in Enterprise SQL Server Manager.

Table 2-3 lists the objects and attributes that can be subjected to policy. The Enterprise SQL Server Manager User's Guide describe profile managers and policy.

Policy Region Configuration

Complete one worksheet for each policy region.

TMR name: ____________________________

Policy region name: ___________________________

Purpose of policy region: ________________________________________________________

________________________________________________________

Managed SQL Server installations in region:

SQL Server SQL Server Host Management Host

________________________________________________________

Profile managers in region:

Name Purpose

________________________________________________________

Profile Manager Configuration

Complete one worksheet for each profile manager in a policy region.

Profile manager type: SQL Server Database

Name: ______________________________

Source: SQL Server __________________ Database _____________

Profiles to include (available types listed in Table 2-1 and Table 2-2.):

Profile Type
Profile Name
Populate with
Policies for this Profile

Subscribers to this profile manager:

________________________________________________________

Table 2-1: SQL Server Profile Manager profile types
Profile Type
Profile operations
SQLServerProfile
Modify SQL Server configuration (you cannot create or delete SQL Servers using profile management)
SQLDumpDeviceProfile
Create, delete dump devices
SQLDbDeviceProfile
Create, modify, and delete database devices
SQLDatabaseProfile
Create databases, modify database options, delete databases
SQLLoginProfile
Create, modify, and delete logins
SQLRemoteServerProfile
Create, modify, and delete remote servers and remote logins
SQLCacheProfile (SQL Server Release 11.0 only)
Create, modify, and delete named caches

**Table 2-1: SQL Server Profile Manager profile types**
Profile Type	Profile operations
SQLServerProfile	Modify SQL Server configuration (you cannot create or delete SQL Servers using profile management)
SQLDumpDeviceProfile	Create, delete dump devices
SQLDbDeviceProfile	Create, modify, and delete database devices
SQLDatabaseProfile	Create databases, modify database options, delete databases
SQLLoginProfile	Create, modify, and delete logins
SQLRemoteServerProfile	Create, modify, and delete remote servers and remote logins
SQLCacheProfile (SQL Server Release 11.0 only)	Create, modify, and delete named caches

Table 2-2: Database Profile Manager profile types
Profile type
Profile operations
SQLDatabaseProfile
Modify database options
SQLSegmentProfile
Create, modify, and delete segments
SQLGroupProfile
Create, modify, and delete groups
SQLUserProfile
Create, modify, and delete database users
SQLDefaultProfile
Create, modify, and delete defaults
SQLRuleProfile
Create, modify, and delete rules
SQLDataTypeProfile
Create, modify, and delete user datatypes
SQLTableProfile
Create, modify, and delete tables
SQLIndexProfile
Create, modify, and delete indexes
SQLViewProfile
Create, modify, and delete views
SQLProcedureProfile
Create, modify, and delete procedures
SQLTriggerProfile
Create, modify, and delete triggers

**Table 2-2: Database Profile Manager profile types**
Profile type	Profile operations
SQLDatabaseProfile	Modify database options
SQLSegmentProfile	Create, modify, and delete segments
SQLGroupProfile	Create, modify, and delete groups
SQLUserProfile	Create, modify, and delete database users
SQLDefaultProfile	Create, modify, and delete defaults
SQLRuleProfile	Create, modify, and delete rules
SQLDataTypeProfile	Create, modify, and delete user datatypes
SQLTableProfile	Create, modify, and delete tables
SQLIndexProfile	Create, modify, and delete indexes
SQLViewProfile	Create, modify, and delete views
SQLProcedureProfile	Create, modify, and delete procedures
SQLTriggerProfile	Create, modify, and delete triggers

Table 2-3: Object attributes subject to policy
Profile
Attributes
Type
SQLLoginProfile
Default Database
Default Language
Login
Roles
Password (validation policy only)
string
string
string
list
string
SQLDbDeviceProfile
Controller Number
Logical Name
Physical Name
Size
VDevNo
number
string
string
number
number
SQLDumpDeviceProfile
Device
Physical Name
Size
string
string
number
SQLRemoteServerProfile
Default Login Map
Local Login
Remote Server
Timeout
Encrypt
Boolean
string
string
Boolean
Boolean
SQLDatabaseProfile
Database
Owner
string
string
SQLCacheProfile
Cache Name
string
SQLUserProfile
Aliases
Group
Login Name
User
list
string
string
string
SQLGroupProfile
Name
string
SQLSegmentProfile
Device Names
Name
list
string
SQLTableProfile
Table
Owner
Segment
string
string
string
SQLViewProfile
Name
Owner Name
string
string
SQLIndexProfile
Index Name
Owner Name
Segment Name
string
string
string
SQLTriggerProfile
Name
Owner Name
string
string
SQLProcedureProfile
Name
Owner Name
string
string
SQLRuleProfile
Name
Owner Name
string
string
SQLDefaultProfile
Name
Owner Name
string
string
SQLDataTypeProfile
User Datatype Name
User
Phystype
string
string
string

**Table 2-3: Object attributes subject to policy**
Profile	Attributes	Type
SQLLoginProfile	Default Database Default Language Login Roles Password (validation policy only)	string string string list string
SQLDbDeviceProfile	Controller Number Logical Name Physical Name Size VDevNo	number string string number number
SQLDumpDeviceProfile	Device Physical Name Size	string string number
SQLRemoteServerProfile	Default Login Map Local Login Remote Server Timeout Encrypt	Boolean string string Boolean Boolean
SQLDatabaseProfile	Database Owner	string string
SQLCacheProfile	Cache Name	string
SQLUserProfile	Aliases Group Login Name User	list string string string
SQLGroupProfile	Name	string
SQLSegmentProfile	Device Names Name	list string
SQLTableProfile	Table Owner Segment	string string string
SQLViewProfile	Name Owner Name	string string
SQLIndexProfile	Index Name Owner Name Segment Name	string string string
SQLTriggerProfile	Name Owner Name	string string
SQLProcedureProfile	Name Owner Name	string string
SQLRuleProfile	Name Owner Name	string string
SQLDefaultProfile	Name Owner Name	string string
SQLDataTypeProfile	User Datatype Name User Phystype	string string string

Planning Administrator Assignments

Once you decide how to deploy your SQL Server installations, you must decide who will manage them. Use the information on the Administrator Survey worksheet as your starting point for this section. Using the Administrator Role Assignment worksheet:

Assign the administrators to policy regions or the TMR.
Assign administrators their responsibilities.
Add role information to the Administrator Role Assignment worksheet.

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.

Use the tables in Appendix A of the Tivoli Management Platform Planning and Installation Guide to determine the roles required for TMP management tasks.

Enterprise SQL Server Manager roles and their associated tasks are listed in Table 2-4. The Enterprise SQL Server Manager Reference Manual and the Enterprise SQL Server Manager User's Guide list the role requirements of individual commands and tasks.

Table 2-4: Enterprise SQL Server Manager administrator roles
Role
Authorizes an administrator to perform...
dump
Database backup in managed SQL Servers.
load
Database restore in managed SQL Servers.
server
SQL Server configuration. An administrator can 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. An administrator can 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. An administrator can create, modify, and delete devices, dump devices, databases, device mirrors, segments, and thresholds.
schema
Schema management. 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.

**Table 2-4: Enterprise SQL Server Manager administrator roles**
Role	Authorizes an administrator to perform...
dump	Database backup in managed SQL Servers.
load	Database restore in managed SQL Servers.
server	SQL Server configuration. An administrator can 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. An administrator can 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. An administrator can create, modify, and delete devices, dump devices, databases, device mirrors, segments, and thresholds.
schema	Schema management. 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.

Administrator
Policy Region
Task
TME/
ESSM Role
SQL Server Role


	Expand Search