Using SQL 2000 for SSRS 2005 metadata storage

When setting up SQL Server Reporting Services 2005, can you use SQL 2000 as the host for the reportserver database? Yes, you can (with an obvious caveat in one scenario).

 

How?

 

Scenario 1:

 

 

SQL Server 2000 currently is not running SSRS, so it does not host the ReportServer database.

 

1. Run SQL Server 2005 setup, including the Reporting Services component.

 

2. Don't choose to go with the "install default configuration" option of SSRS. Instead,

    choose the second option to manually configure SSRS after setup has completed.

 

3. Finish setup

 

4. Launch the Reporting Services Configuration tool, and choose the Databases Setup task.

 

5. Provide the name of your 2000 server and click the New button. When prompted, provide

   credentials necessary to create a database on SQL 2000...you'll also need to provide the

   name of the database (ReportServer).

 

6. After the database has been created, use the lower portion of hte Database Setup screen to

   specify the credentials SSRS should use when connecting to the database in the future.

 

I used these steps with SQL Server 2000 SP3, and had no problems.

 

Scenario 2:

 

SQL Server 2000 is already hosting SSRS 2000 and/or is managing a ReportServer database created for SSRS 2000

 

Warning: Following these steps will "break" your SSRS 2000 report servers as we'll upgrade the ReportServer database to a schema 2000 doesn't undersand.

 

1. Repeat steps 1-4 as above.

 

2. Type in the name of the 2000 server and click Connect. Choose the ReportServer database

   from the drop-down list (or just click the Upgrade button).

 

3. When prompted whether you want to upgrade, choose Yes.

 

4. After the upgrade process has finished, complete step 6 as above. If you have upgraded a

   database that another SSRS server used to use, you may get warning messages around the

   fact that other encryption keys were detected in the database. Since our 2000 report servers

   can't use this database now anyway, feel free to delete the existing keys.

 

Reminder: If/when you launch Report Manager from an SSRS 2000 server which used to use this database, you'll get an exception which looks like this:

 

"The version of the report server database is either in a format that is not vaid, or it cannot be read. The found version is 'C.0.8.40'. The expected version is 'C.9.6.54.'. To continue, upgrade the version of the report server database and verify access rights"