I've installed TFS in more than one environment where Reporting Services is already in use. The big problem that everyone yells about is that TFS requires a blank RS instance in order to install it successfully. In case you don't have a spare SQL database instance laying around, a nice workaround is to rename the RS databases from their default names. This allows TFS to create the new RS databases it requires with the default and you can move on with your life.
Thanks to Bob Coppedge over at Simplex-IT for his original blog post on this. I was able to follow his steps with some slight variations to rename the Reporting Services databases.
- Backup the ReportServer and ReportServerTempDB databases you plan on renaming--just in case something goes wrong...which never happens, of course.
- Stop Reporting Services using the Reporting Services Configuration Tool.
- Script the stored procedures from the ReportServer database to new query window.
- In the script, search for and replace CREATE PROC with ALTER PROC (one instance contains two spaces between CREATE and PROC.)
- Save the script.
- Rename the ReportServer and ReportServerTempDB databases.
- Go back to the stored procedure script and search for and replace ReportServer with the new database name (ex. ReportServer to ReportServer-NEW.)
- Save a copy of the script with a new name.
- Execute the script.
- Start Reporting Services using the Reporting Services Configuration Tool.
- Go to the Database Setup "tab".
- Click the Connect button and then click OK on the pop-up.
- Select the new database name in the dropdown list.
- Click Apply and then click OK on the pop-up. An error will probably occur when saving the new connection info. Click Apply again and it should succeed.
Finished! You should be able to test the reports now. I would go so far as to recommend this as a best practice. You could leverage the same database server for multiple RS instances this way. At the very least, it enables you to install Team Foundation Server against a database server that is already supporting an existing RS instance.
jb
6 comments:
My TFS install problem was solved with the help of your post...so a big THANK YOU is in order and I can totally confirm from my own experience that after performing these steps everything works like a charm!
In addition to this, there is one trigger on the Schedule table in the ReportServer database that needs to be updated. It contains a reference to the ReportServerTempDB and gets called when you try to update a subscription.
StockExposer: Which trigger? Both contain no reference to the TempDB.
There is a trigger on the Schedule table called Schedule_UpdateExpiration that also needs to be updated (SQL Reporting Services 2005, not sure about other versions).
The FROM clause of the trigger contains:
[ReportServerTempDB].dbo.ExecutionCache AS EC
I've had to do this too, and it does seem top work (even if not technically MS supported). One tip - when searching the script to change from Create to Alter Procedure, it's best to do a regex find/replace for "CREATE +PROC" to "ALTER PROC", which will match multiple spaces between the keywords, plus CREATE PROC and CREATE PROCEDURE, both valid syntax
Thanks for the article, it was very helpful. I'm running 2008 R2 RTM and had to update the following:
o All stored procedures
o Function: ExtendedCatalog
o Trigger: Schedule.Schedule_UpdateExpiration
o View: ExtendedDataSets
o View: ExtendedDataSources
Post a Comment