[Previous] [Next]

Lesson 1: SQL Server Support and SMS

SQL Server is an integral part of SMS. An SMS installation cannot be completed without it. The SQL Server database is used as the repository for most data collected by SMS.


After this lesson, you will be able to Estimated Completion Time: 40 minutes

How SMS Uses SQL Server

Database Requirements

Microsoft SQL Server running on Windows NT/2000 Server is the only database engine that can store SMS data. SMS 2.0 supports SQL Server version 6.5 (SP4) or SQL Server version 7.0. SQL Server is installed before or during the installation of SMS. During the installation, SMS creates the databases it needs for operation. After the installation, SMS services store systems management data in the databases, and administrators access the database via the WBEM/SMS Provider interface.

Database Architecture

The SMS installation routine builds database device files in SQL Server. A device file, also called a SQL device, is pre-allocated hard disk space, like a page file. A SQL device contains tables, stored procedures, indexes, and views. Tables are used to store data collected by SMS services. Stored procedures are precompiled collections of SQL statements that allow SQL Server to manage SMS data. Indexes provide a mechanism for finding data rapidly. Views provide an alternative way of presenting data from one or more tables in the database.

Two databases, the SMS site database and the SMS software metering database, can be created during the installation of SMS. The site database is always created during the installation of a primary site server. The software metering database is only created if the software metering feature is installed.

IMPORTANT
When the site database and the software metering database are discussed together in this study guide, they are referred to collectively as "SMS databases."

Each database requires two device files: the primary data file and the transaction log file. The primary data file stores SMS data. The transaction log file stores the changes made to the database; the transaction log is used to recover the database in case of a system failure. Figure 2-1 shows the various ways that SMS interacts with the SMS databases in SQL Server.

Click to view at full size

Figure 2-1. An architectural view of how SMS interacts with the SMS databases in SQL Server.

User Account Database Access

The SMS installation routine must log on to SQL Server in order to create the databases. After installation, the same logon account is used to alter the databases on behalf of the SMS Service account. The accounts used to create the databases during installation and to alter the databases in production are called the SQL Server accounts.

How SMS logs on to SQL Server depends on how security is configured in SQL Server. There are several SQL Server logon methods, as shown in the first and second columns of Table 1.

Table 1. Security Configuration Options in SQL Server

SQL Server version 6.5 logon SQL Server version 7.0 logon Account Type SMS Installation Method
Standard SQL Server Authentication* A SQL account contained in SQL Server SMS is installed using SQL Server Authentication and the sa account or another account with the necessary rights to create and manipulate the SMS database.
Integrated Windows NT Authentication A Windows NT/2000 user or group SMS is installed using a Windows NT/2000 user account with the necessary rights to create the SMS database.
Mixed SQL Server and Windows NT Authentication Either a SQL account or a Windows NT/2000 user account SMS can be installed to use SQL Server Authentication and Windows NT/2000 Authentication.

* In SQL Server version 7.0, SQL Server Authentication is not available except with Windows NT/2000 Authentication.

In SQL Server version 6.5, you can configure the database engine to use either or both of the logon methods. Supporting both logon methods is called mixed security. While the security terminology is different in SQL Server version 6.5 and 7.0, the security functions are the same in both versions. SQL Server version 7.0 terminology will be used throughout this guide, unless otherwise noted.

Creating Databases for SMS

SMS and SQL Server can be installed on the same computer or on different computers. The database engine installed on the computer that will run SMS is called a local SQL Server installation. If the database engine is installed on a separate computer, it is called a remote SQL Server installation. The SMS installation routine creates the SMS device files for a local installation of SQL Server. It also tunes SQL Server for SMS operations if SQL Server is installed through the SMS installation routine.

If a remote SQL Server installation is to be used for SMS operations, the SMS device files must first be created on the SQL Server. Also, a remote SQL Server installation will not be tuned by the SMS installation routine. In SQL Server version 6.5, the SQL Server administrator creates the necessary devices in SQL Server Enterprise Manager and the installation routine creates the databases. In SQL Server version 7.0, the SQL Administrator creates the database and devices simultaneously, and the SMS installation routine is instructed to use the database.

CAUTION
Do not use existing devices that are in use by other database applications, because SMS will delete all data structures within these devices and then use the purged devices to generate the SMS databases.

To create the databases, the user account or accounts specified during the installation routine must have the SQL Server version 7.0 Create Database, Backup Database, and Backup Log privileges to the master database. The master database is a system database used by SQL Server. When the SMS databases are created, the user account specified during the installation routine becomes the SMS database owner. Ownership provides the user account with administrative privileges to the SMS databases. The user account specified during the installation of SMS is called the SQL Server account.

NOTE
In SQL Server version 6.5, the Dump Database and Dump Transaction privileges are synonymous with the Backup Database and Backup Log privileges used in SQL Server version 7.0.

Using SQL Server Authentication to Create the Databases

If the database engine is configured to allow SQL Server Authentication, you can use SQL Server login IDs to build the SMS databases. The sa login ID can be used to create the database, or a new SQL account can be created in SQL Server. By default, the sa account has database operator (dbo) privilege to all databases, so it is not necessary to grant individual privileges to this account. If you create a SQL Server logon ID instead of using the sa logon ID, make sure this account has dbo privileges to the database or that it has been granted the necessary privileges to the master database.

Using Windows NT Authentication to Create the Databases

If SQL Server is configured for Windows NT Authentication, the SMS installation routine can create the databases using the Windows NT/2000 user account for the currently logged on user. This user must have system administrator access to the database or be granted the Create Database, Backup Database, and Backup Log privileges to the master database. In Exercise 4, you will verify that the Administrators local group has system administrator access to the SQL Server.

Sizing the Databases

The SMS databases may take up a significant amount of disk space. Database disk space requirements are based on a number of factors, including site activity, discovered resources, and client computers within the site boundaries. The SMS installation routine sizes the databases based on estimates provided during installation. Properly sizing the database is essential in SQL Server version 6.5, but is less important in SQL Server version 7.0. SQL Server version 7.0 can be configured to automatically grow the database devices as needed. In all cases, the disk partitions used for the SMS databases must provide enough storage capacity to support the SMS device files. Details on sizing the SMS databases for a production SMS system are explored later in this lesson and in Chapter 9, "Planning for SMS."

Preparing SQL Server for SMS Installation

Before completing this exercise, you must install Windows NT Server, Microsoft SQL Server version 7.0, and any required service packs on your computer. For information on installing the 120-day Evaluation Edition of Microsoft Windows NT Server version 4.0, the service packs, and the 120-day Evaluation Edition of Microsoft SQL Server version 7.0, see "About This Book."

In the following exercises, you will configure two accounts in preparation for installing SMS. One account will be used to create and access the site database, and the other account will be used to create and access the software metering database. Rather than use two of the same account types, you will create a SQL Server Authentication account for the creation of one database, and use the Windows NT Administrator account to create the other database.

Exercise 1: Configuring SQL Server Version 7.0 for Both Authentication Methods

In this exercise, you will use the SQL Server Enterprise Manager to configure or verify that both security methods are allowed.

  1. Click the Start button, then point to Programs. A menu of choices appears.
  2. Point to Microsoft SQL Server version 7.0, then click Enterprise Manager.
  3. The Microsoft SQL Server Enterprise Manager MMC appears.

  4. Expand the SQL Server console tree and select the Server 1 (Windows NT) node.
  5. SERVER1 appears. If SERVER1 does not appear, you must first register SERVER1. Select the SQL Server Group node, then from the Action menu choose New SQL Server Registration. Follow the registration wizard to register SERVER1 in the Enterprise Manager MMC.

  6. From the Action menu choose Properties.
  7. The SQL Server Properties - Server 1 dialog box appears.

  8. Select the Security tab.
  9. Select the SQL Server and Windows NT radio button, or verify that it is selected.
  10. Click OK to close the SQL Server Properties - Server 1 dialog box.

Do not close SQL Enterprise Manager, as it will be used in the next exercise.

Exercise 2: Assigning a New SQL Authentication Account in the SQL Enterprise Manager

In this exercise, you will create a SQL Authentication Account that will be used during the installation of the site database (Exercise 5).

  1. From the SQL Server console tree, expand the Server 1(Windows NT) node. Open the Security folder and select the Logins node.
  2. The currently configured accounts appear in the details pane.

  3. From the Action menu choose New Login.
  4. The SQL Server Login Properties - New Login dialog box appears.

  5. In the Name field, enter sms_serv and select the SQL Server Authentication radio button.
  6. Leave the Password field blank and click OK.

The new SQL Authentication Account, sms_serv will appear in the details pane. Do not close SQL Enterprise Manager, as it will be used in the next exercise.

Exercise 3: Assigning the SQL Authentication Account Required Permissions to the Master Database

In this exercise, you will grant sms_serv the Create Database, Backup Database, and Backup Log permissions to the master database.

  1. To see the master database, select the Server 1 (Windows NT) node.
  2. From the Action menu, choose Edit SQL Server Registration Properties.
  3. The Registered SQL Server Properties dialog box appears.

  4. From the General tab, verify that the `Show system databases and system objects' checkbox is selected.
  5. Click OK to close the Registered SQL Server Properties dialog box.
  6. The master node appears under the Databases node.

  7. Expand the master node and select the Users node.
  8. Currently assigned master database accounts appear in the details pane.

  9. From the Action menu, choose New Database User.
  10. The Database User Properties - New User dialog box appears.

  11. From the `Logon Name' list box, choose sms_serv and click OK.
  12. sms_serv appears in the details pane.

  13. Select the master node in the SQL Server console tree.
  14. From the Action menu, choose Properties.
  15. The master Properties dialog box appears.

  16. Select the Permissions tab and expand the User/Role column so that you can see the full account names.
  17. In the row for the sms_serv account, select the checkboxes for the Create DB, Backup DB and Backup Log columns.
  18. Click OK to close the master Properties dialog box. The sms_serv SQL Server account is now prepared to create the site database.

Do not close SQL Enterprise Manager, as it will be used in the next exercise.

Exercise 4: Verifying Windows NT Account Permissions to the Master Database

The Windows NT Administrators local group appears in SQL Server by default; it does not need to be added through the Logins node in Enterprise Manager (Exercise 2). When you are logged on as a member of the Administrators local group, SQL Server rights are inherited. The Administrators local group is automatically assigned the sysadmin fixed SQL Server role, so it is not necessary to explicitly assign privileges to this group for the master database (Exercise 3).

In this exercise, you will verify that the Administrators local group appears in SQL Enterprise Manager and that the Administrators local group has been granted the System Administrators SQL Server role. Then you will verify that the Administrator account is a member of the Administrators local group.

  1. From the Security folder, select the Logins node and verify that the BUILTIN\Administrators local group appears in the details pane.
  2. Select the BUILTIN\Administrators group from the details pane. Then from the Action menu, choose Properties.
  3. The SQL Server Login Properties - BUILTIN\Administrators dialog box appears.

  4. Select the Server Roles tab in the SQL Server Login Properties - BUILTIN\Administrators dialog box and verify that the `System Administrators' checkbox is selected.
  5. Click Cancel to close the SQL Server Login Properties - BUILTIN\Administrators dialog box.

  6. Click the Start button, go to Programs, point to Administrative Tools (Common) group, and start User Manager for Domains.
  7. User Manager - Domain 1 appears.

  8. Double-click the Administrators local group in the bottom pane of User Manager for Domains.
  9. The Local Group Properties dialog box appears.

  10. Verify that Administrator is a member of the Administrators local group.
  11. You have verified that the Administrator user can be used to create the software metering database during the installation of SMS.

Create a Database Using SQL Server Version 7.0

During the installation of SMS, you will allow SMS to create the databases on your locally installed copy of SQL Server version 7.0. To run a demonstration of how the database is created using SQL Server version 7.0, run the DBCREATE.AVI in the \CHAPT02\DEMOS folder on the Supplemental Course Materials compact disc.

IMPORTANT
This demonstration requires 800 x 600 resolution with 256 colors. Run it at a zoom setting of 100%.

Configuring SQL Server for SMS

In addition to configuring user accounts and SQL Server databases for SMS, you must verify that several SQL Server parameters are tuned for SMS operations. Configuration parameters can be set from SQL Server's Enterprise Manager, or by using the sp_configure stored procedure. For details on using sp_configure, see the SQL Server documentation.

All the SQL Server configuration parameters that affect SMS operations are self-configuring, by default, in SQL Server version 7.0. However, you should know these configuration settings, since they are not self-configuring in SQL Server version 6.5. Figure 2-2 shows the SQL Server Properties dialog box, which is used to set the most common configuration parameters in SQL Server version 7.0.

SQL Server Configuration Parameters

SQL Server configuration parameters contain both minimum and maximum values. Minimum values allocate system resources without regard to SQL Server resource requirements. Thus, assigning minimum SQL Server configuration parameters takes careful planning, since setting these values too high is an inefficient use of computer resources. For example, if the SQL Server version 7.0 Minimum (MB) value is greater than zero (Figure 2-2), this memory is only available to SQL Server system processes. Set minimum configuration values with care and use maximum values to control how much of the system resources should be assigned to SQL Server operations.

In SQL Server version 7.0, the use of system resources based on maximum configuration values is more dynamic than in SQL Server version 6.5. SQL Server version 7.0 increases allocated resources when necessary without overcommitting them, by decreasing the resources used when they are no longer needed. So assigning maximum rather than minimum values to configuration parameters is the most efficient configuration method. In SQL Server version 6.5, allocated resources are not returned to the pool of available resources until SQL Server is stopped and restarted.

User Connections

User connections provide application access to SQL Server. SMS uses these connections for installation, SMS service operations, and SMS Administrator console access. A primary site server uses a minimum of 40 user connections for installation and operation. Above the minimum of 40 connections, the first five concurrently operating SMS Administrator consoles require two user connections each. If more than five SMS Administrator consoles will be running on your site concurrently, grant an additional five user connections in SQL Server for each console.

Each user connection requires approximately 40 KB of RAM on the computer running SQL Server. Configuring maximum user connections does not commit memory to support user connection allocation unless the user connections are established. Further, SQL Server version 7.0 will return RAM to the pool of system resources for inactive user connections.

NOTE
Minimum user connections can be configured for SQL Server version 6.5 and 7.0 using the sp_configure stored procedure and the reconfigure command. Minimum user connections can also be configured from SQL Server version 6.5's Enterprise Manager.

Open Objects

The number of open objects determines how many tables, views, rules, default settings, triggers, and stored procedures can be open at any one time. SQL Server version 7.0 handles open object allocation dynamically, so there is no need to adjust this parameter manually. A large implementation of SMS can create thousands of open objects.

Memory

All SQL Server operations run in RAM. SQL Server can be configured to pre-allocate RAM to its functions. RAM is allocated in memory units. Each memory unit is 2 KB of RAM. The default minimum value of RAM allocated in SQL Server version 7.0 is 0, while the maximum allocated RAM is 2GB (2,147,483,647 bytes). When the `Dynamically configure SQL Server memory' option is selected as shown in Figure 2-2, memory allocation is self-configuring. The value for Maximum (MB) shown in Figure 2-2 is the total physical RAM in the computer running SQL Server. The default max server memory (MB) value is 2GB, which is seen by running the sp_configure stored procedure. 2GB is the maximum amount of virtual memory that can be assigned by Windows NT for SQL Server operation.

Click to view at full size

Figure 2-2. Configuring the server memory options in SQL Server version 7.0.

If SQL Server version 7.0 is configured to use memory dynamically, it checks the system periodically to determine how much free RAM is available. SQL Server grows or shrinks its use of memory to keep free physical memory at 5 MB plus or minus 200 KB to prevent excessive paging in Windows NT/2000.

Locks

SQL Server uses a mechanism called locking to allow for controlled multi-process database access. To minimize the computer resource cost of locking, SQL Server version 7.0 automatically locks resources at a level appropriate to the task. The levels of locking are table level, page level, and row level. Locks are objects maintained in memory and managed by SQL Server. SMS needs a minimum of 1,500 locks for normal operation. Locks are self-configuring in SQL Server version 7.0; there is no need to set this parameter manually.

Primary Data File and Transaction Log File Sizes

The primary data file used for the SMS site database should always be greater than 50 MB for a production SMS environment. Above the 50 MB minimum requirement, SMS uses approximately 100 KB of storage space in the primary data file for each client computer. The transaction log device file size should be at least 20 percent of the primary data file size. If data for 1,000 client computers will be stored in the site database, for example, then 100 MB (1,000 client computers times 100 KB per client data) total for the primary data file and at least 20 MB for the transaction log file are required.

The primary data file used for the software metering database should always be more than 10 MB for a production SMS environment. Above the 10MB minimum requirement, SMS requires approximately 200 KB of storage space in the primary data file for each client computer running the Software Metering Client Agent. The transaction log file for this database should also be 15 percent of its primary data file. So, to support 1,000 client computers running the Software Metering Client Agent, the primary data file should be 200 MB and the transaction log file should be 30 MB.

Temporary Database (tempdb) Size

Tempdb is a system database that holds all temporary tables and temporary stored procedures, and fills any other temporary storage needs for SQL Server. The primary data file size for tempdb should be approximately 20 percent of the sum of all device files on the system excluding the master database device files. Tempdb is contained in the SQL Server version 7.0 master database by default, so the master database devices should not be included in the tempdb size calculation. If SQL Server is running the site database and the software metering database exclusively, and if the combined size of these databases is 350 MB, then tempdb should be at least 70 MB.

Database Size Calculations

These recommendations for minimum data file size are to ensure their safe use with SMS. The actual space needed for the data and log devices depends on a variety of factors, including the number of databases on the computer, the number of instances of the SMS Administrator console accessing the database, the number of queries being run, the size of the tables being queried, and so on.

By default, SQL Server version 7.0 dynamically sizes all its databases based on need. You must use the size calculations provided in the previous sections of this lesson to determine the size of the disk partition used to store the database device files. Then, allow SQL Server version 7.0 to manage the growth of these files. Figure 2-3 shows the dynamic configuration settings for databases in SQL Server version 7.0.

Click to view at full size

Figure 2-3. Database properties settings showing how SQL Server version 7.0 dynamically sizes device files.

Time Synchronization

Because SMS uses the SQL Server's time when scheduling its tasks, synchronize the time settings of the SQL Server and SMS site server. The simplest way to do this is through the following Windows NT command line:

NET TIME \\computername /SET /Y

Replace the computername parameter with the NetBIOS name of the SQL Server where the SMS database resides. The Windows NT AT Command Scheduler can be used to schedule time update synchronization with the SQL Server. For more information on the AT command scheduler, go to the Windows NT command prompt and type the following command:

AT /?

The Windows NT Resource Kit contains a graphical version of the command scheduler called WINAT.EXE. Consult the Windows NT Resource Kit Tools Help file for more information on the graphical command scheduler. The Time Synchronizing Service utility on the Windows NT Server Resource Kit can also be used in place of the NET TIME command and the AT service. The Time Synchronizing Service synchronizes all site system times with a primary or secondary time server.

Accessing the Database

It is important to understand the required database access used to complete an SMS installation, but just as important is understanding how SMS accesses the SQL Server database after installation. Three common access methods (Figure 2-4) that you will learn about before the installation of SMS are:

Click to view at full size

Figure 2-4. How user accounts access the SMS databases.

Many other mandatory user accounts are automatically created based on the features that are enabled during or after the installation of SMS. You will learn about the various mandatory and optional user accounts that are created and configured to perform SMS functions in Chapter 12, "Configuring SMS Security." Site security is enhanced by granting only the required privileges for the SMS functions that various user accounts serve.

TIP
Use Windows NT Authentication (integrated security) for the SQL Server account and use different Windows NT user accounts for each function in SMS.