SSRS 2005 Migration

My company is working on a DR Plan that includes VM for recovery while new hardware can be ordered and systems built. Last week I had the task of moving our SSRS 2005 installation to new VM that in the end would have the same name as the original physical server.

Lets name the original server ServerA and the VM ServerB.

I found a couple of articles through Google which covered the process with good detail, the links can be found at the below.

After installing SQL2005 and SSRS on ServerB to match the versions on ServerA, I took a backup of the Source Databases, reportserver and reportservertempdb and restored those back to ServerB.
Ensured the configuration for both servers matched and then imported the Encryption Keys ServerB came up and worked, it would serve up the Reports, ServerB\Reports.

After shutting down ServerA we renamed ServerB to ServerA, performed the required updates on new ServerA for SSRS to be able to connect, link below and everything was good to go. So we thought.

Two issues arose after the migration, neither of which I could find a fix for through Google or other searches.

The first issue is that when IIS is installed two accounts are created IUSR_(servername) and IWAM_(servername), both of these retain their original name after the server rename. Our issue was that IUSR_ServerA owned subscriptions, these failed because the user did not exist. The system team renamed the account, IUSR_ServerB and IWAM_ServerB to IUSR_ServerA and IWAM_ServerA, this resolved the problem of the account not existing but it was missing some privileges so it was still unable to successfully execute the subscriptions, I ended up changing the owner of the subscription to the SQL Service Account and the subscriptions would generate. Reinstalling IIS may resolve this issue, but not sure what would happen to the SSRS configuration information.

The second issue arose when the subscriptions emailed links to the reports, the links included ServerB in the path, \\ServerB\Daily Reports...., resulting in a 404 error when following. Inside the config file referenced below there is a key named URLRoot, this required updating to the correct server name, C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Report Server\rsreportserver.cfg.
I didn't need to change the user/password as detailed in the first article.

Overall this was a fairly smooth process, the two issues were easy to resolve once found but caused unplanned report failures and production issues for users relying on the reports.

Migration Assistance:
http://blogs.egroup-us.com/?p=1600
http://support.microsoft.com/Default.aspx?id=842425

Renaming a SSRS Computer:
http://msdn.microsoft.com/en-us/library/ms345235.aspx

SQL2000

I work for an insurance company in North Texas that is running still running a majority of its SQL serves on SQL2000. We have requested budget for licensing of SQL2008 and have just received our first SQL2008 test server from the network group. It is in a segmented VLAN so only one user at a time will be able to test but it is a start.

I created this blog as a place where issues from SQL2000 to SQL2008 can be recorded and solutions posted. I'm not sure but I would think there are still quite a few SQL2000 installations out there.

As I find items to help out our developers be more effecient, they are a good bunch but very few have any database knowledge. With only 2 dbas at the company and 42 production servers, we serve in a consulting role.

xp_sendmail issues

After several weeks of wrestling with xp_sendmail failing with the error 'No such user or group (sqlusername)' it has been resolved. The error only showed up when the @query parameter was also in use. The solution was easier than I thought, each of the xp_sendmail commands that used the @query also needed the @dbuse parameter added.
This all started happening after I moved the system databases off of a mountpoint to a drive letter.
The company I work for runs on SQL2000 mainly, in the process of upgrading to SQL2008. Not sure what the actual cause of the change in behavior was but it has been resolved.