May 5, 2021 at 1:23 pm
Hi,
We have 4 SQL instances (2014, 2016, 2017, 2019) all using AOAG.
What's the score with SSRS connecting to these instances? I'm thinking for every SQL version we have, we need a dedicated SSRS server to connect to a specific instance (in this case the AG Listener). I'm not aware that SSRS can simultaneously connect to multiple instances.
Thanks
May 5, 2021 at 2:33 pm
PS: Before anyone says "upgrade everything to one instance". We have hundreds of DBs all from different suppliers who all have maximum supported versions 🙂
May 6, 2021 at 10:18 am
Hi,
We have 4 SQL instances (2014, 2016, 2017, 2019) all using AOAG.
What's the score with SSRS connecting to these instances? I'm thinking for every SQL version we have, we need a dedicated SSRS server to connect to a specific instance (in this case the AG Listener). I'm not aware that SSRS can simultaneously connect to multiple instances.
Thanks
Dont need dedicated RS servers, install multiple instances if you need to separate reporting out and dont want the headache of managing RS permissions.
When you say connecting do you mean in the sense of report datasets\datasources?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 6, 2021 at 8:30 pm
I didn't think you can install multiple instances on the same Windows host anymore, at least that's what I'm reading for SQL 2019.
I mean connecting SSRS to the actual SQL Instance (Database Engine), so;
SSRS1 connects to SQL1
SSRS2 connects to SQL2
SSRS3 connects to SQL3
and so on. I can't have one SSRS connected to multiple SQL Instances simultaneously.
Thanks for responding ??
May 6, 2021 at 8:30 pm
That ?? at the end was supposed to be a thumbs up!
May 6, 2021 at 9:22 pm
The only reason to connect SSRS to a specific database engine is if you are hosting the reportserver databases on that instance. The 'usually' better option is to install a single instance of SSRS and the database engine on a single host - hosting the report servers databases on that same host.
You would then have a single SSRS instance and reports deployed to that instance can connect to any ODBC data source required for each report. The data source for a report is independent from the instance of SSRS where the report is deployed - and ideally you use shared data sources so it is easy to update when a source system is upgrade/migrated.
If you are using the scale-out option in Enterprise Edition of SSRS - then you would host the databases on one server, and install the reporting services (web portion) on multiple servers for the scale-out solution. You can then use either DNS round robin or your favorite load balancer (Netscaler - for instance) to load balance access to the SSRS web farm.
If you are creating separate instances of SSRS for each application - all you are really doing is spending money. The only reason to do that is if there are specific application requirements to have that applications reports hosted on a dedicated instance. For anything else - creating an appropriate folder structure and permissions for deployed reports allows for reports across systems to be co-located in the same folder so users don't have to navigate to 10 different web sites for related reports.
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
May 6, 2021 at 10:59 pm
I didn't think you can install multiple instances on the same Windows host anymore, at least that's what I'm reading for SQL 2019.
where are you reading this, what link.
I mean connecting SSRS to the actual SQL Instance (Database Engine), so;
SSRS1 connects to SQL1
SSRS2 connects to SQL2
SSRS3 connects to SQL3
and so on. I can't have one SSRS connected to multiple SQL Instances simultaneously.
Thanks for responding ??
That's correct an SSRS instance has its system databases on a single sql instance, reports within the instance though can connect out to any sql instances that support what the report is doing
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 6, 2021 at 11:09 pm
wow , been a short while since I touched ssrs, it seems they have withdrawn multi instance support in favour of virtualisation of ssrs nodes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 7, 2021 at 11:42 am
If you are creating separate instances of SSRS for each application
Not per application, but per SQL Database Engine Instance - unless you are using these terms interchangeably. We have 4 SQL Instances. Each Instance is 'shared' so running multiple applications' databases.
I understand what you mean in the 2nd paragraph. We could have one 2019 Report Server running (or 2 in scale-out) and even if a supplier who maxes out at Database Engine version 2017 say, could still put their reports in the 2019 RS but connect to the 2017 Database Engine via a specific Data Source. That makes absolute sense.
So it would be down to whether the supplier's application supports a certain version of RS.
And although not using scale-out right now, I imagine that will become the plan so want to build it now with that in mind. Plus we're using AGs everywhere so installing RS on only one of the replicas seems kind of not right.
Thanks for the replies everyone.
May 7, 2021 at 5:14 pm
Not per application, but per SQL Database Engine Instance - unless you are using these terms interchangeably. We have 4 SQL Instances. Each Instance is 'shared' so running multiple applications' databases.
SSRS has 2 components - the database engine and the reporting services. If - as you stated above - you have separate servers for each SSRS instance that has both components installed on that SSRS server then each instance of SSRS is not 'tied' to a specific SQL Server database engine. If - however - you have separate SSRS servers where only the reporting services component has been installed - and the databases for SSRS are hosted on the SQL Server database engine then you have 'tied' that instance of SSRS to that database engine.
This isn't needed - one instance of SSRS with both the database engine and reporting services components can support any number of reports that connect to any number of instances and databases. You could have reports connecting to Oracle, SQL Server, MySQL, Postgres, Vertica - or any other source system that has an ODBC data source available.
In almost all cases - SSRS is backward compatible. That means a report that was built to be deployed to a 2014 system can still be run on a 2019 SSRS instance. Ideally - you would upgrade the reports but it isn't necessary unless that specific report is failing.
The question I have now is - what license do you have for the SSRS instances? If you are not licensed for Enterprise - then scale-out is not an option. If you are on Enterprise - then I would look at converting from 4 separate instances of SSRS to a scale-out implementation.
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
May 8, 2021 at 2:40 pm
This was removed by the editor as SPAM
May 9, 2021 at 12:09 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply