Installing MOM 2005 and SMS 2003 on SQL 2005 backend [updated!]
INTRODUCTION
I know there have been a lot of questions regarding MOM and SMS running on SQL 2005. This is now a supported configuration for MOM DB and Reporting Services (with 3 hotfixes) and SMS Site database (with SP2).
The steps to perform the installation are not so straightforward. I have carried out both procedures in a VM lab and it works. I assumed Windows Server 2003 SP1 was the OS basis. I want to share with you the steps, so you can go ahead with the install w/o having to figure out why it does not workJ
MOM 2005
1. Install SQL 2005 with those two components (at least:
a. database
b. reporting services
2. Configure reporting services during the installation or later via the configuration wizard).
3. Change permissions on %windir%\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files to grant the Network Service write access (I don’t know why it is not done during install)
4. Install MOM 2005 SP1 server
5. Install hotfixes 913812, 913801
6. Install MOM reporting server. Manually enter both reporting virtual directories.
7. Install hotfix 915785
8. Open IIS manager, open Reports properties page and change ASP.NET version from 1.1. to 2.0 (again, I do not know why the install does not do it automatically)
9. Additionally you may need to follow https://support.microsoft.com/?kbid=909846
10. If you are NOT using SSL on your Reports pages make sure:
a. \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config has the following section <Add Key="SecureConnectionLevel" Value="0"/> (3 would indicate SSL is required, 0 - not)
11. If you are using SSL on your Reports pages then you should follow additionally these steps if you cannot reach the SQL RS website via https:
a. \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config has the following section <Add Key="SecureConnectionLevel" Value="3"/> (3 would indicate SSL is required, 0 - not)
b. Verify the server FQDN and not NETBIOS name is used by the certificate:
i. Open Internet Information Services Manager (IIS)
ii. Open the properties of the Default Web Site. In the Directory Security tab, under "Secure communication" click the "View certificate" button.
iii. Make sure there are no errors/warnings on the General tab or the Certification Path tab.
iv. Get the name from the "Issued to" field on the General tab, or the "Subject" property on the Details tab. This will be the fully-qualified domain name (FQDN).
v. Exit out of IIS Manager
c. Add the server’s FQDN to the SQL Reporting Services Web configuration file if needed:
i. Open the RSWebApplication.config file using Notepad.exe on the Microsoft SQL Server 2000 Reporting Services server. Note The RSWebApplication.config file is typically located in the following path on the SQL Server 2000 Reporting Services server: Drive :\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\
ii. Locate the following line in the RSWebApplication.config file: "<ReportServerURL>https://NetBIOS_Name/ReportServer</ReportServerURL>" (note to the tech writer: this config file is XML, so the <> symbols should appear around ReportServerURL)
iii. Replace this line with the following line: "<ReportServerURL>https://Server FQDN/ReportServer</ReportServerURL>"
d. Save the RSWebApplication.config file.
e. Perform a reset of the IIS service.
f. Verify that you can open the SQL Reporting Services site by connecting to the following URL in Internet Explorer: https://FQDN/Reports.
g. If you successfully connect to the SRS site at https://FQDN/Reports, try again to install MOM 2005 Reporting.
Here are the direct links to the downloads for the fixes:
Couple of gotcha's:
1. Remember to specify the reportserver and report manager virtual directory NAMES not URLs into the prerequisite checker:
2. Make sure your reporting services webpage is running smoothly, i.e. you can open the browser to the reporting manager website (use the Reporting Services configuration wizard). For the application pool I advise to create a new one and use either ASPNET local account or Network service built-in service account to run it. I have had problems running it in LocalSystem account (reporting services claimed that it cannot resolve 'LocalSystem' to a SID).
3. After that the installation will continue and succeed. There is a known issue with importing XML reports to SQL 2005 Reporting Services - it is being worked on by the Product Group in Redmond. Clive posted info on this: https://blogs.technet.com/cliveeastwood/archive/2006/05/12/428162.aspx.
4. A KB article about supportability of MOM 2005 SP1 and SQL 2005 is already out:https://support.microsoft.com/?kbid=917615.
5. SQL 2005 Sp1 is already out, I advise to install it, though it is not a strict requirement, I know there was so minor issue that it fixed.
Note: I installed everything on 1 machine, and in a VM environment. You might want to use different accounts due to security constraints in production environment.
SMS 2003
1. Install SQL 2000 + SP3a or SP4
2. Install SMS 2003 SP1
3. Upgrade SMS 2003 site server to SP2
4. Upgrade SQL 2000 to SQL 2005
Until SMS 2003 comes with a slipstreamed SP2 we will have to follow the above general procedure.
Hope this helpsJ
Comments
- Anonymous
April 03, 2006
I posted an article on this topic if you are interested:
http://blogs.technet.com/alipka/articles/424009.aspx... - Anonymous
April 04, 2006
Great post! This will answer a lot of questions that are posted in the newsgroups and on myitforum. - Anonymous
April 05, 2006
Very concise and precise document among the clutter of confusion we have regarding the installation of MOM 2005 on SQL 2005 backend.
Kudos to you for helping us all out and in turn helping the customers who are eagerly waiting for this upgrade. - Anonymous
April 10, 2006
I noticed a few people have had problems with installing Reporting services on SQL 2005. I will work to update the article to be more specific. The issues are almost always around Reporting services application pools and accounts. - Anonymous
April 13, 2006
Ok, I've updated the article. This should explain the procedure more clearly. - Anonymous
April 13, 2006
For SMS 2003 SP2, It&nbsp;supports SQL Server 2005 already.
For MOM 2005 SP1, Microsoft released 3 hotfixes... - Anonymous
April 14, 2006
Daniel,
You are right. But although for SMS 2003 there has been an official statement from us: http://www.microsoft.com/technet/prodtechnol/sms/smssp2/supconfig/0bdafa4a-71b3-4ed6-94bb-42a9f3986951.mspx?mfr=true
There's not a KB article or technet page on MOM 2005 and SQL 2005 supportability. This according to the PG is coming shortly.
BTW: Nice blog, I especially like your design!:) - Anonymous
April 18, 2006
Why don't you write a KB for it? - Anonymous
April 19, 2006
I have already submitted this as a KB request. However:
1. There is already an article in the works AFAIK. (though not as detailed as mine)
2. The process of triaging KB articles is long, so I am afraid that it will take at least a month to release this as a KB. Therefore I posted it here.
andrew - Anonymous
May 23, 2006
MOM and SQL 2005 - Anonymous
May 31, 2006
So I got it into a KB article finally, but since I made updates during the tech review of this KB article my blog entry is still more uptodate then the KB:
http://support.microsoft.com/?id=918481
Anyway I think that such a process of sharing knowledge is far from ideal, I find wiki much more useful, when the knowledge gets accumulated into its final shape and flavor by inputs from everyone. The concept behind wiki is much more appealing then blogs or even more support pages and kb articles... we'll see. - Anonymous
June 22, 2006
Great summary with all the gotchas.
The install works great if the SQL database is on the default instance. In my lab, I built a SQL 2005 SP1 server with 4 instances of SQL so that I can house all my MOM databases on one physical server.
I then used the momcreatedb.exe to install a new MOM 2005 SP1 database on one of the non-default instance, this went okay.
When I tried to install the management server on another server (a MS virtual server client) the install will fail. The error message is not very helpful.
I had previously done this with MOM 2005 RTM and SQL 2000 and it worked fine but I cannot seem to get it to work with MOM 2005 SP1 and SQL 2005 SP1. - Anonymous
June 22, 2006
Do you have the MSI log file? It should be in %temp%. This should give you more details about the cause of error. I am running mom 2005 sp1 on sql 2005 sp1 with OnePoint on non-default instance w/o problems but i have not installed the db via momcreatedb.