How to manually modify SQL server connections in Systems Management Server 2.0 (886097)


The information in this article applies to:

  • Microsoft Systems Management Server 2.0

SUMMARY

When you install Microsoft Systems Management Server (SMS), you specify the number of concurrent SMS Administrator consoles that you plan to use. SMS allocates five Microsoft SQL Server user connections for each console. However, the actual number of connections that a console uses varies. For example, if you open all the console tree items at the same time, it uses more SQL Server connections than if you open one item at a time. No console uses more than 30 SQL Server user connections at the same time.

If you receive "Out of SQL Connection" error messages when you try to connect to the SMS site database, or if you want to add more consoles after the initial SMS installation, increase the number of SQL Server user connections by using the SQL Enterprise Manager or by using the Licensing Manager. Add five or more user connections for each additional console.

Note If you need additional SQL Server user connections, you might have to purchase more SQL Server client access licenses. Verify the number of licenses that your organization already owns to determine whether this is the case.

If you increase the number of SQL Server connections, also consider increasing the maximum number of SQL Server connections that are used by the SMS Provider. By default, the SMS Provider is configured to use a maximum of 60 simultaneous SQL Server connections. You can change this value by editing the Smsprov.mof file or the Sqlcon.mof file, and then compiling the changed file by using Mofcomp.exe.

Note The Smsprov.mof and the Sqlcon.mof files are included on the Microsoft BackOffice 4.5 Resource Kit CD-ROM.

INTRODUCTION

This article describes how to manually modify SQL server connections in SMS 2.0.

MORE INFORMATION

The maximum number of Microsoft SQL Server connections that are used by Microsoft Systems Management Server (SMS) Provider is specified by the value of the MaxSQLConnections property in the SMS_ConfigData SMS Provider class.

Note After you edit and compile Sqlcon.mof, if you then compile Smsprov.mof by using another set of values, the values in Smsprov.mof will replace the values that you previously compiled.

To change the maximum number of SQL Server connections, use one of following procedures.

Method 1: Modify the maximum number of connections from the SMS Provider to SQL Server by using the Smsprov.mof file

To do this, follow these steps:
  1. Open the Smsprov.mof file in a text editor.
  2. In the following line, replace "MCM" with your three-letter site code:

    #pragma namespace("\\\\.\\root\\SMS\\\site_MCM)

  3. In the following line, replace "60" with a new value:

    uint32 MaxSQLConnections = 60;

  4. Save the file.
  5. At a command prompt, type mofcomp smsprov.mof.
  6. Stop the Windows Management service.
  7. Start the Windows Management service.

Method 2: Modify the maximum number of connections from the SMS Provider to SQL Server by using the Sqlcon.mof file

To do this, follow these steps:
  1. Open the Sqlcon.mof file in a text editor.
  2. In the following line, replace "ABC" with your three-letter site code:

    #pragma namespace("\\\\.\\root\\SMS\\\site_ABC)

  3. In the following line, replace "60" with a new value:

    uint32 MaxSQLConnections = 60;

  4. Save the file.
  5. At a command prompt, type mofcomp sqlcon.mof.
  6. Stop the Windows Management service.
  7. Start the Windows Management service.

Note Do not specify a value for SMSConfigData::MaxSQLConnections that exceeds the maximum number of SQL Server connections, minus 20. For example, if you set SQL Server for 100 SQL Server connections, set the SMS Provider to use a maximum of 80 connections. If you set the SMS Provider value too high, the SMS Provider will not control SQL Server connections correctly and might return "Unable to Get SQL Connection" error messages to the SMS Administrator console.

For information about using either the SQL Enterprise Manager or the SQL Server Licensing Manager, see your SQL Server documentation.

REFERENCES

For more information about how to set SQL server connections in SMS 2.0, visit the following Microsoft Web site:

http://www.microsoft.com/resources/documentation/sms/2/all/reskit/en-us/part3/smc04.mspx

Modification Type:MinorLast Reviewed:6/14/2005
Keywords:kbsmsAdmin kbhowto KB886097 kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.