asked
Making Reporting Services changes en masse
I work for an organization whose internal reporting consists of about 25 source databases across a dozen or so servers. In SSRS we have two Solutions, each containing a few thousand reports. Every now and then, some comic genius in the infrastructure team will advise us that they're renaming a server instance, or moving a DB onto a different server. Obviously this breaks all the affected reports (mainly where linked servers come into play - which is a lot) and we have to go through each affected rdl and fix and deploy. Manually. Or so it would seem. The latest is that the "look and feel" police have told us that the organization logo is changing. It's embedded in every report. How do others handle mass "fix and deploy" situations?
Synonyms are an abstraction layer, so in short: yes, they would be useful in your situation. I've used them to clean up cross-database queries that had DB names hard-coded, not interesting if you've got different environments where the same database is named differently. Quite sure they can be used to avoid having server names in the queries too.
Hey I just saw a post by Jeff Moden about what I thought was an unrelated subject... synonyms. I have not used these before and am not familiar with them but they sound like a way to at least partially ease my pain. As I indicated earlier, a big part of our report maintenance headache comes from linked server syntax, so the SQL in the rdl is littered with stuff like [LIVE1-yyy01\zz01].flightcontrol.dbo.client. Does using a synonym mean I can get it down to flights.client and just change the synonym when the infrastructure people start their shenanigans in renaming servers and moving databases and so on? Are there any limitations with using synonyms?
What I do with the datasources is use a shared datasource, that way when a database name or server name changes I just have one change to make. By using a shared datasource when multiple reports use the same datasource you can make a single change to update the database name or server name. As for the logo I am not sure how easily that can be updated if the size of the logo is changing. If the logo is the same size I would think you could simply name the image file the same name as the old logo and replace it. I have tested that though.
+1 for shared data source. Logo stuff would probably work, but you'll probably need to redeploy the project. Unless the image is hosted outside of the project. Why is SSRS so bad at CSS?
I went through a data center migration a little over a year ago where my infrastructure team tried to "fix" our DNS issues by having us rename all our servers. Kinda silly right. Anyway after suffering through that exercise I decided to start using DNS aliases servers. Also on consolidation servers I have multiple aliases where each system/app uses their own respective alias. If I have to fail over a database to another server I just have my DNS guy update the alias with the new IP address. In cases where I have a dedicated DR server both IP addresses are configured in the alias with one active and the DR suspended. In many cases I have used these aliases in a linked server situation. When the databases have moved I haven't had to touch my linked servers in this situation. I haven't worked with synonyms but will be researching them to see if it is something I can apply.
@TRAD: Thanks for going to the trouble of answering, but the answer doesn't really go to the crux of the question. What you say (if I understand you correctly) is very obviously true but it doesn't deal with the scenario where you have linked server syntax in your underlying query's SQL. Sorry I didn't make that clearer. And with 25 source databases over a dozen different servers, we have a painfully large number of queries with linked server syntax. The logo image is actually EMBEDDED in the rdl, so it seems we need a way to replace every logo image IN each rdl.
Have you considered using PowerShell:
http://timbenninghoff.com/archive/2010/03/creating-a-reporting-services-rdl-from-powershell/ It might not be a quick fix right now but something that can be built into a good solution. I don't mess with Reporting Services much but a quick search on Google showed there are options available to interact with RS with PowerShell in deploying reports. I would imagine there has got to be something out there on being able to modify them against multiple servers.
2 People are following this question.
