INF: SQL Server Issues Related to SMS

ID: Q155637

The information in this article applies to:

SUMMARY

The System Management Server (SMS) provides a method for centrally managing software and hardware for corporate networks. It is a very useful product that provides an integrated system to maintain inventory of hardware, software, and configurations of computers in a network, distribute and install software, manage network applications and monitor network data flow. SMS incorporates Microsoft SQL Server as a back-end database server. It uses SQL Server to store its inventory database.

SMS collects and maintains hardware and software inventory for an entire enterprise. This inventory information is stored in a SQL Server database. There will be one such inventory database per site. In what follows, a few SMS terms are explained.

The central site is the topmost site or is the root of the site hierarchy. An SMS system is composed of a central site, which stores system-wide information and can have any number of sub-sites beneath it. The Central site requires a SQL Server database.

In addition, any site can possess its own site database. Any site that has its own site database is called a primary site. A site without a database is called a secondary site. A secondary site cannot store its inventory information and hence it forwards all its inventory and status information to a primary site above it in the site hierarchy. Note that a secondary site can have no sites beneath it. On the other hand, a primary site can have one or more primary sites beneath it as well as one or more secondary sites beneath it.


MORE INFORMATION

Having given a brief introduction about the use of SQL Server in SMS sites, we can now enumerate the following configurations in SQL Server that affect the working of SMS in any primary or central site:

Sort Order

SMS will use the same sort order and character set as the SQL Server to execute queries and sort data.

SQL Login ID

You need to have an SMS SQL Login ID to set up a site as a primary or a central site. This login ID is used during the SMS setup program as well as to access the site database on the SQL Server once SMS is set up and is in operation. In many cases the login ID will be 'SA,' because, in general, the SMS administrator will also be the SQL Server administrator, though this is not necessary.

Site Database Devices

SMS requires that each site have its own database and also the transaction log must be on its own device. The site database devices and the database itself can be created in two ways:
  1. The SMS Setup program can create the database and transaction log devices and also create the database, if the site server also has SQL Server installed on it. In order to do this, the SMS login ID must have system administrator privileges on SQL Server.


  2. If the SQL Server is on a remote computer, you will have to create the database devices, before running SMS setup program, which will create the site database on these already existing devices. In this case, the SMS login ID must have Create Database, Dump Database, and Dump Transaction permissions on the Master database. This enables the SMS setup program to create and maintain the site database. SMS will however, delete all objects if any database exists on these devices. SMS requires a database and corresponding transaction log for its own usage and there is no compromise on this. Any existing data will be deleted prior to creating a site database on the specified devices on SQL Server.


Tempdb Usage

Tempdb size is dependent on the number of computers in a particular site and all its sub-sites, from which inventory will be collected and stored in SQL Server. A larger tempdb improves performance for queries that contain sorts. In general, if there are 1,000 computers in a site, a size of 5 to 10 MB is recommended. Default size of tempdb is 2 MB and it resides on the master device. It is better to alter the size of tempdb on other devices rather than increasing its size on the master device itself. If a site uses SMSVIEWS rather heavily, then tempdb size should be increased to facilitate query or view processing appropriately.

User Connections

SQL Server should have at least five user connections configured separately for use by SMS. In practice, it is better however to have at least 10 to 15 user connections set aside for SMS alone.

Memory

The optimum setting depends on how much RAM is installed on SQL Server and what other applications are running on the SQL Server machine. On a dedicated SQL Server, with 32 MB of physical RAM, you can configure 16 MB for SQL Server, for instance. This would enable Windows NT to have enough memory to run its processes and would prevent page thrashing.

Open Objects

For SMS, open objects on SQL Server should be configured for 5,000-10,000.

Locks

For SMS alone, the default configuration of 5,000 locks on SQL Server should suffice. However, if the server has other active databases on it, then this configurations should be appropriately adjusted.

Synchronize time

If SQL server is on a remote machine, the SMS site server and SQL Server should be synchronized with the current time on the site server. On Windows NT, you could use the NET TIME command to do it.

Upgrading

There are some nuances to consider when upgrading SMS and SQL Server to their respective new versions. These can be summarized as follows::
  1. SMS 1.0 is compatible with SQL Server 4.21a servers.


  2. SMS 1.1 is compatible with SQL Server 6.0 and 4.21a servers.


  3. SMS 1.1 is also compatible with SQL Server 6.5 and 6.0.





When upgrading, the order is important; it makes a difference whether SMS or SQL Server is upgraded first. In the case of SMS 1.0 and SQL Server 4.21a, the SMS sites should be first upgraded to SMS 1.1 and thereafter SQL Server must be upgraded to 6.0. This is because the SQL Server 6.0 version is incompatible with SMS 1.0. SQL Server 6.0 can be upgraded to 6.5 as long as the site servers are all running SMS 1.1.

Additional query words: sms 


Keywords          : kbinterop SSrvGen 
Version           : 1.0 1.1 1.2 6.0 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 1, 1999