Setting SQL Server Connections

When you installed SMS, you specified the number of concurrent SMS Administrator consoles that you planned to use. SMS allocates five SQL Server user connections for each console, although the actual number of connections used by a console will vary. For example, opening all the console tree items at once uses more SQL Server connections than opening one item at a time. No console will use more than 30 SQL Server user connections at one time.

If you receive “Out of SQL Connection” error messages when attempting to connect to the SMS site database, or want to add additional consoles after the initial SMS installation, you should increase the number of SQL Server user connections by using either the SQL Enterprise Manager or the Licensing Manager and adding five (or more) user connections for each additional console.

Note   If you need additional SQL Server user connections, you might need to purchase more SQL Server client access licenses. Check the number of licenses 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 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 either SMS\Bin\Smsprov.mof or Sqlcon.mof (included on the Microsoft BackOffice 4.5 Resource Kit CD-ROM) and then compiling the changed file by using Mofcomp.exe.

The maximum number of SQL Server connections used by SMS Provider is specified by the value of the MaxSQLConnections property in the SMS_ConfigData SMS Provider class.

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

To change the maximum number of SQL Server connections, use the following procedure. If you prefer to edit Smsprov.mof, find the equivalent section in that file and make the same changes, as described in the following procedure.

Procedure Bullet  To modify the maximum number of connections from the SMS Provider to SQL Server by using Sqlcon.mof

  1. Open Sqlcon.mof in a text editor.
  2. Change the following line:
    3. #pragma namespace(“\\\\.\\root\\SMS\\\site_ABC)

    Replace “ABC” with your three-letter site code.

  3. Change the following line:
    4. uint32 MaxSQLConnections = 60;

    Replace “60” with a new value.

  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 regulate 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.