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?

more ▼

asked May 27, 2011 at 01:20 PM in Default

avatar image

4.9k 42 52 58

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?

May 29, 2011 at 01:00 AM GPO

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.

Dec 12, 2012 at 12:54 PM Valentino Vranken

Thanks Valentino. Since I posted that in May we have been progressively replacing linked server syntax with synonyms. It is a much better way to go.

Dec 19, 2012 at 04:13 AM GPO
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 27, 2011 at 01:27 PM

avatar image

40.9k 39 95 168

  • 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?

May 27, 2011 at 01:29 PM ThomasRushton ♦♦

@GPO, sorry you didn't care for my answer. I was simply trying to help and offer suggestions that have worked for me. I can sympathize with your frustrations at work with dealing with infrastructure folks.

May 28, 2011 at 06:57 AM Tim

@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.

May 28, 2011 at 12:58 PM GPO

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.

May 29, 2011 at 06:25 AM Tim
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 29, 2011 at 05:13 PM

avatar image

6.6k 21 26 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 27, 2011 at 01:20 PM

Seen: 3834 times

Last Updated: Dec 19, 2012 at 04:13 AM

Copyright 2018 Redgate Software. Privacy Policy