Introduction
A couple of months before I joined my present company they had upgraded from SQL 2000 to SQL 2005. They used SQL 2000 Reporting Services and I was given the job of migrating it to SRS 2005.
From my past experience with SRS 2000 I was hoping it wouldn’t be quite as fiddly and confusing to set up but alas I couldn’t have been further from the truth.
By now I would have thought that thousands of companies would have made the change so, 1) How come I haven’t heard of too many problems? 2) How can Microsoft’s own documentation be wrong after the product has been available to the general public for two years?
To “migrate” rather than upgrade our report server required a MS PSS support call and at least two weeks of trial and error to get it working consistently (and even now it can be unpredictable).
The difference between Upgrade and Migrate
As with most things there are various different ways to skin the SQL cat. Our approach was this: we didn’t want to do an “in-place” upgrade i.e. stick the CD into the production server. We would build some new servers including SQL 2005 SRS, backup the existing database, restore it to the new server and then upgrade the database in its new home and everything would work. If only it was that simple.
The official instructions
The only instructions I was able to find from Microsoft on migrating rather than upgrading were here: http://msdn2.microsoft.com/en-us/library/ms143724.aspx. But as we will see there are mistakes in it so how have other people managed this?
I won’t go through everything I found as my final set up guide that we now use internally runs to some 35 pages. Instead I will just point out the errors in this particular guide.
To be fair I did go through all of this with the PSS support technician and she is going to pass my comments on to Microsoft and the technical authors and no doubt the article will be updated. But in case there is a delay in this happening I thought I would share what I found.
The mistakes I found
These are out of order compared to the steps in the above MSDN article, but will make sense if you continue reading.
- When attempting Configure a Report Server step 6 you may get the following error:
"ReportServicesConfigUI.WMIProvider.WMIProviderException: The resources required to complete this operation could not be found."
Using filemon.exe it reported that the C:\Program Files\Microsoft SQL Server\90\Shared\2057 folder is missing. This relates to a locale folder of some description and applies to the UK only.
This is too far along the install/configuration guide and you may have to start the process again, so I suggest that after you install SRS 2005 using the “files only” option that the next thing you do is this: copy the C:\Program Files \Microsoft SQL Server\90\Shared\1033\ folder, paste it into the same directory and rename it to 2057.
- In Install SQL Server 2005 Reporting Services step 7:
7. If you are applying Service Pack 1, stop all SQL Server 2005 services (including the Report Server Windows service, SQL Server Agent, SQL Server Browser, and SQL Server Fulltext Search).
I’m not sure about Service Pack 1 as I didn’t install it, but the services need to be running when installing Service Pack 2 otherwise you get an error message and it won’t happen.
The next two bullet points, 5 and 9, are related:
- In Install SQL Server 2005 Reporting Services step 5:
5. In Report Server Installation Options, select the Install but do not configure the server [files only] option. Selecting this option allows you to configure the report server after setup is finished.
Then item 9 says:
9. Attach or restore the report server database you backed up from the SQL Server 2000 installation to the new instance.
Well, this is how we got in to trouble on one occasion – unless the databases are created with the Reporting Services Configuration Manager before item 9 is actioned (restoring the databases over the top of them) several things go wrong with the installation including permissions not being applied for the RSExecRole.
- In Configure a Report Server step 6:
6. Click Upgrade. Both the report server database and the temporary database are upgraded to the new schema.
This should also mention that after Upgrade has been pressed, Apply needs to be pressed also or some permission changes are not made.
Once you have a successful migration you may find there are some further issues:
- Datasources are not copied over correctly or are missing entirely and will have to be recreated.
If you use a product like Double Take to replicate data between two servers you may get problems like the next two:
- Reporting Services complains that it cannot decrypt the symmetric key used to access data in the database.
In this case you will need to reimport the symmetric key that you exported from the legacy server (a step earlier in the MS guide).
- The SQL Server Agent service is stopped and when it is started it immediately stops again. When looking at the agent’s icon in SQL Server Management Studio the service is disabled and “(Agent XPs disabled)” is displayed along side of it.
In this case a copy of the database will need to be restored entirely from backup or remirrored from the upstream Double Take replication partner.
Conclusion
The above instructions may make no sense at all to you unless you have attempted a migration of SRS 2005 and have come across the problems I did but I hope they may help if you come unstuck.
As I mentioned at the beginning of this I don’t know why I had so many problems. Googling for solutions showed that one or two people had one or two issues whereas I seemed to get them all. Just my luck I suppose.
IMHO, unless you really have to then don’t bother performing a migration. Find another way to commission your SRS 2005 server or stick with your current solution.
NB Beware, since my colleagues upgraded our databases from SQL 2000 to SQL 2005 they have been hit by many serious performance problems. This has meant that a large number of complex stored procedures have had to be rewritten or very careful attention be made to indices. More information about these issues are gradually being written about in the forums as more and more people have decided to upgrade since the release of SP2.
No comments:
Post a Comment