How to implement SQL 2005 on SMS 2003 and MOM 2005

  • Article

Both SMS and MOM require SQL server as a data store solution. As SQL 2005 is released for several months, you may have request to implement SQL 2005 for SMS and MOM. To prevent all potential upgrade issue and ensure the productive SMS and MOM work well after SQL 2005, I listed the pre-requirement list and the common issues.

Part one: SMS 2003

Pre-requirement:

SMS 2003 SP2

Note: Only SMS 2003 SP2 can work with SQL 2005. This means that there are only two options available to implement SQL 2005 on SMS server:

Option 1: Upgrade existing SMS version to SMS 2003 SP2. Then upgrade SQL version to SQL 2005.

Option 2: Using a slip stream SMS 2003 SP2 installation media to install a new SMS site server with SQL 2005 installed.

 

Common Issues:

Symptom: After upgrade SQL server to SQL 2005, SMS Status manager will be failed to process incoming status messages. There will be a backlog status messages files under the SMS\inboxes\statmgr.box\ folder. In SMS Administrator console and System Status, the component status may not be updated.

Solution/workaround: To fix this issue, we have to manually change the sp_InsStatusMessageAttribute stored procedure. The detailed steps are:

 1) Open SQL Management Studio and navigate to the SMS Site Database.

 2) Open the Programmability node, then the Stored Procedures node.

 3) Find the sp_InsStatusMessageAttribute stored procedure and right click

 4) Choose "Script Stored Procedures > Alter to > New Query Editor Window

 5) Find the following section at the end of the procedure

        IF (@NumRows < 63) GOTO done





       IF (SELECT RecordID FROM StatusMessageAttributes
  WHERE RecordID = @RecordID_63 AND AttributeID = 
  @AttributeID_63 AND AttributeValue = @AttributeValue_63) 
  IS NULL
       INSERT INTO StatusMessageAttributes VALUES 
 (@RecordID_63, @AttributeID_63,  @AttributeValue_63, 
 @Time_63) done: END
       GO
________________________________________

 Add a BEGIN and END statement to the INSERT section, as such: 
________________________________________

    IF (@NumRows < 63) GOTO done
    IF (SELECT RecordID FROM StatusMessageAttributes
WHERE RecordID = @RecordID_63 AND AttributeID = 
@AttributeID_63 AND AttributeValue = @AttributeValue_63) 
IS NULL
    BEGIN INSERT INTO StatusMessageAttributes VALUES 
(@RecordID_63, @AttributeID_63, @AttributeValue_63, @Time_63)  
END done: END
    GO



6) Execute the Alter procedure.

Part two: MOM 2005

Pre-requirement:

MOM 2005 SP1

.Net Framework 2.0 (include ASP.NET)

Hotfix Q913812 https://support.microsoft.com/default.aspx?scid=KB;en-us;q913812

Hotfix Q913801 https://support.microsoft.com/default.aspx?scid=KB;en-us;q913801

Hotfix Q915785 https://support.microsoft.com/default.aspx?scid=KB;en-us;q915785

Note:

Only MOM 2005 SP1 can work with SQL 2005. This means that there is only two option to implement SQL 2005 on MOM server:

Option 1: Upgrade existing MOM version to MOM 2005 SP1. Then upgrade SQL version to SQL 2005.

Option 2: Using a slip stream MOM 2005 SP1 installation media to install a new MOM server with SQL 2005 installed. After the MOM server is installed, we must need to install the above three hotfixes.

 

Common Issues:

1. If you want to install MOM 2005 reporting after SQL 2005 reporting service installed, we need to manually change the SQL reporting service files follow the method in KB article Q899720. https://support.microsoft.com/default.aspx?scid=KB;en-us;q899720

2. After upgrading SQL server to SQL 2005, the SQL reporting service WMI namespace will be changed. It will cause the MOM 2005 reporting installation failed. You may need to follow the steps in KB 918712 to install MOM 2005 reporting. https://support.microsoft.com/default.aspx?scid=KB;en-us;q918712

3. After upgrading SQL server to SQL 2005, the MP importing/upgrading may be failed. This is because SQL 2005 imposes a more rigorous XML validation on the report schema. We are in the progress to upgrade all Management Pack with new report XML file. You can see the upgraded management pack list in the following KB article:

https://support.microsoft.com/kb/919598

Note: if you found that some Microsoft management pack is still failed to import due to the XML format, please contact Microsoft to get the new version reporting file.

4. After upgrading SQL server to SQL 2005, you may be failed to import Availability Report management pack. We have to manually change the compatibility level of SQL 2005 to the level of SQL 2000. The detailed steps can be seen in the following KB article:

https://support.microsoft.com/default.aspx?scid=KB;en-us;q918711

　

-End-

Author: Simon Xin