February 28, 2020 at 3:30 pm
We have five different report servers, with 13 or so different databases, all with the same structure, for our production databases. (well, some are on different versions of SQL, mid-upgrade cycle, whatever. not significantly different)
Currently, I'm storing all the servername/dbname combinations in a table, and we have linked server connections set up to all of them. When we write a report for something, we write it up using dynamic SQL and ask for an @environment variable from the user. Based on the parameter, we locate the servername/dbname in the table and pull it into the query string before executing.
DECLARE @SQL varchar(max), @environment varchar(4)
SELECT @SQL = COALESCE(@SQL,'') + 'SELECT '''+servername+''' as servername, '''+dbname+''' as dbname,1 as [yourField(s)]
FROM ['+servername+'].'+dbname+'.dbo.YourTableNameHere aliasHere
WHERE 1=1 --replace with condition here
UNION
'
FROM myDatabase.dbo.VW_serverReference
WHERE --change this WHERE clause as necessary
dbname LIKE '%production%'
AND lob = @environment
SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-7) + 'ORDER BY dbname'
PRINT @SQL
EXEC(@SQL)
Reasoning is that at least the report logic is consistent across all environments.
If someone wants to execute a query in SSMS, they can create a registered server and run it against everything at once, or similar using Central Management Server, but I don't know if that's possible for SSRS to do the same.
I don't want to run it against everything usually, I just want to run it against one of the environments at a time. Other than using sp_executesql (yeah, I know, I have it on the list to fix, no excuse, but I do validate that the parameter is in the valid list before using it, just didn't for the example above), is there a better way to handle this? Should I be doing something like registered servers/CMS in our SSRS reporting?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 28, 2020 at 4:17 pm
I tend to avoid dynamic SQL, and I'd usually just deploy the same report to all servers and call it without having to do any linked server or anything else. I prefer each server to stand alone in most cases.
Not sure you can use a CMS in SSRS, but I wouldn't do this because of dependencies. I guess linked servers might also create complexity/dependencies, but less, IMHO.
That being said, it's more preference than anything. I think the way you're doing this here makes sense, and if others on staff understand this, leave it alone. It's likely a good solution for you.
February 28, 2020 at 7:08 pm
I would use a report database in all environments that contains the same code - and can easily be reconciled using any of the many available comparison tools. And if you are using a code repository to manage your code, you can easily configure the deployment scripts to deploy changes to all environments.
This has the added advantage of being able to setup configuration tables specific to each environment along with lookup tables specific to that environment that can be referenced by the reports. The actual report deployed to SSRS would only need to change the data source to access the appropriate tables and code.
You could also publish the report in SSRS to a hidden folder - and create linked reports that have a hidden parameter that defines the data source to be utilized. Users would then be directed to the appropriate folder based on their permissions and would never need to see that the report can be run for different environments.
For anyone that wants to be able to run the code across all servers - they can use registered servers to run a multi-server query that executes the procedure in that report database and retrieve the results dependent on how they have configured SSMS for multi-server queries.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply