Microsoft Reduce Customer Effort Center

Our team drives product feedback based on solid data, it drives proactive issue prevention and ultimately, drives improvements around products based on customer feedback.

Blogs

How to implement SQL 2005 on SMS 2003 and MOM 2005

  • Comments 2
  • Likes

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 http://support.microsoft.com/default.aspx?scid=KB;en-us;q913812

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

Hotfix Q915785 http://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. http://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. http://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:

http://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:

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

 

-End-

Author: Simon Xin

Comments
  • myITforum Daily Newsletter Daily Newsletter September 6, 2006 The myITforum.com newsletter is delivered

  • I have already server with sql 2005 installed in it , and i have a SMS 2003 CD but without SP2 . now when i tried to install SMS 2003 ( and then install SP2  ) i cant .

    what i can do ? i need SMS 2003 SP2 but my license is to SMS 2003 .

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment