My question is how: To have All SSRS Reporting running on a separate server from OLTP (DB's) and pulls from data on that SSRS reporting server rather than the OLTP DB's. The OLTP needs to send it's inserts updates and deletes to the SSRS reporting server periodically. I have also set up AOAG for ReportServer and ReportServerTemp db, but I want SSRS reports to hit the "read only" secondary Replica DB's and not the Primary AOAG OLTP Replica DB's if possible.
It is possible, I've done it. You add the SSRS instance to your Availability Group as a replica, then synchronize the databases. Do not use the same AG you're using for your ReportingServers databases, create a new one just for OLTP DBs.
You can then choose to have your SSRS data sources read only that replica (by machine name or IP address), or you can configure the Availability Group listener and set up read-only routing. I haven't done the latter (can't remember the reason but I think it was a technical limitation) but you can read about it here:
Strongly suggest you keep your SSRS replicas asynchronous, and never use them for failover purposes. There's already a write burden on the secondaries, and SSRS has its own database stuff to do, it's best to minimize the impact of an Availability Group on it.
You should review the credentials you use for reporting purposes vs. writing/modifying data. You'll probably need to synchronize logins on each server, that includes the security ID (SID) of the login, they should be the same. You can't ensure that by creating logins in SSMS, you need to script them out and specify the SID explicitly. If you're only using Windows authentication/logins then the SIDs are already managed for you.
We used a separate Reporting SQL login, scripted out with explicit SID and hashed password. That was created on all nodes of the AG, with all the appropriate read-only permissions assigned, but disabled the login on the non-reporting nodes. That ensured no one who had the login could access the writable replicas and run a huge query.
1 Like
Robert... TY so much for the quick response and guidance. I have come across some suggestions like what you have mentioned, but was unable to get SSRS reports to run from the "read only" secondary replica, but I did have everything in one AG. I will test having two separate AG's now as you have suggested (TY). Is it better/easier to utilize the AOAG for sync'ing the DATA needed for the SSRS reports from the OLTP (AOAG) DB's or is it better to avoid the Always On for SSRS and create a separate Data Sync process to run once or twice a day from Active AOAG Prod DB's over to copies of the OLTP DB's on the SSRS server? Years ago, we used to have separate SSRS server that was tied to the PROD OLTP DB's to collect DATA using Cubes & Data Warehousing, but am being advised to use the AOAG capabilities now instead. Again... TY for you time and guidance. Greatly appreciated.
To be able to connect directly to the read-only secondary you have to add the users to the database(s) on the primary and grant permissions. If the login/user is a SQL account, then you need to create a login on both primary and secondary with the same SID - grant access to the database(s) - then you can disable/delete the login on the primary.
The accounts that are setup in SSRS for the data sources (these should be shared data sources) - can then be configured with the appropriate credentials to connect directly to the read-only secondary. Reports are then setup to use those shared data sources.
There are no reasons to synchronize data to the reporting services instance. That instance should only be utilized to manage SSRS only. If you need to build tables or other supporting structures (data marts, data lakes, etc.) - then create a new database on the secondary server and create the objects in that database. This facilitates accessing the OLTP databases because they are on the same instance - where you can then use 3-part names (or better yet, synonyms) to access the production data.
In that new database - you can also add stored procedures, functions, views - anything else needed to facilitate your reporting requirements.
2 Likes
Jeff, TY for the additional info and guidance. You do touch on an area that has been giving me issues. It is about the SP's (Stored Procedure's) that some of our SSRS reports have built into them that actually create Temp tables while gathering DATA for the reports. This is not allowed on a secondary AOAG nod as I have it configured currently and the reports crash if they cannot get back to the Active Primary AG node that allows writes. I have a 2 node AG group with SSRS installed and configured on both nodes. My goal is to have a call to SSRS hit and run completely on a secondary node that is already in Sync with the Active Primary Node DATA bases, but not causing a performance hit for all using the Primary active node if at all possible. It almost sounds like I may need to have a three node AOAG (AG) with one Primary replica and Two Secondary replica's with the 2nd secondary replica in an asynchronous mode in a "read-Only Intent" setting applied along with having the SSRS Data source connection string set to include: ApplicationIntent=ReadOnly Not sure if I am on the right track for this to get all SSRS report calls to only hit a secondary "read-Only" node, but still allow a dependent SP to create the needed Temp tables for the report DATA to be pulled from without the need to hit the Primary Read/Write Replica.
You don't need another node - you need another database on the secondary. For example, on my read-only secondary I have created a 'Reports' database. In that database, we create stored procedures - functions - views to support reporting requirements.
Since that database is on the same instance as the AG - we can easily setup access from SSRS to the AG databases using 3-part names (or synonyms).
Stay away from read-intent for reporting...that isn't what it is designed to do. Read-intent is designed to offload application reads to a secondary and should only be implemented when there is a clear case that it will improve performance for the application(s).
Assume a 3-node cluster, node1 = primary, node2 = HA and node3 = reporting. The HA node is set to synchronous with automatic failover. The reporting node is set to asynchronous and manual failover - but will never be used for failover. It does not require all databases to be included, only those needed for reporting - it does not need the same resources as node1/node2 - just enough to support reporting requirements.
Users do not connect to the listener to get to the reporting node - they connect directly to node3, which means your SSRS data sources are configured to connect directly to node3.
3 Likes
Jeff, This helps clear some questions for me TY. For clarification... If I were to stay with just a 2-Node AG with Node1=Primary and Node2=HA... you are saying that I can get away with creating a new (non-AG) DB say called: "Reports" that is Read/Write, I can then have all the needed SP's & Views created within that and all of the existing AG DB's on Node2 can stay in Read only mode and the reports should work even though the SSRS reports will need to hit many of the existing AG DB's to pull data? OR.... are you saying it is better/cleaner to just create a 3-Node Cluster with my existing Node1=Primary, Node2= HA and the new 3rd Node will have all of the needed AG DB's that are on NODE1/2, but with an extra DB ("Reports") that is not part of the AG DB's and will have Read/Write capabilities to allow SP's to do what they want within the "Reports" DB and pull needed data from the synched (existing) AG DB's? My quandary is that many current SP's are written to create temp Tables within a "MAIN" SQL DB (on primary) to work. Unless these SP's are re-written to NOT touch a MAIN SQL AG DB within the Primary or on a Read only Secondary (NODE2) they would fail correct? I also believe I do NOT need to have the SSRS ReportServer nor ReportServerTempDB as part of the AG in sync mode, since if a failover occurred SSRS service would fail unless manual or a Cluster Role was setup to handle the switch from one server (Primary) to a secondary (new primary) server correct?
Thank you (all of you) again for being patient with me on this and forgive me for the rebounding questions. I feel like I am very close to understanding what it is I need to do from the posts here to make this work and I owe that to your kindness and assistance in mapping out the path. TY.
Yes - you can create a database on any instance that is not part of the AG. And - you can access any instance in an AG directly without going through a listener.
The purpose of having a listener is to redirect the connection to the correct node hosting the primary database. The primary database will be read/write so any activity can be performed.
If you only maintain a 2-node cluster where the secondary node is set as an HA node - then you would not want reporting to be done from that node. Consider what happens if/when you failover to that node - and how any report processes would then impact your production environment and application(s).
By setting up a 3-node cluster - the HA node has all databases in the AG and all the resources to support your production environment. The Reporting node only has those databases needed for reporting (which may or may not be all databases) - and only the resources necessary for reporting requirements (less memory - not as many CPUs - maybe???).
The gap here is if that reporting node goes down - then all reporting stops until the node is back up and available. If you lose the node then it will have to be rebuilt and the AG setup again on that node - but that is a small price to pay and can be mitigated. Worse case - restore a backup of your Reports database on the HA secondary (temporarily), switch that secondary to read-only.
I generally don't worry about setting up SSRS in a cluster. I have a dedicated host for SSRS on a VM - which hosts the database engine and reporting service. The only time I would consider moving the databases to a shared host (e.g. a cluster) would be when I needed to scale-out SSRS.
In this configuration - stored procedures, functions, views, supporting tables (lookups, data marts, etc.) are created in the Reports database (or even other databases) and SSRS/SSIS connects to that instance and the Reports database.
Note: you still have to consider permissions - and grant permissions on the primary database. The permissions are then replicated to the secondary. For domain accounts - you only need to add the user to the database. For SQL logins you need to create the login on one - get the SID - and create the login on the other servers with that SID (but you already have to do that for the HA node).
Jeff, TY again for such quick and informative responses for someone who is a bit green to this and has not attempted this in an AG environment before.
So in short, I could do the following to avoid AG complications?:
- Set up a standalone VM/Azure SSRS server with the default ReportServer and ReportServerTempDB on it.
- Configure the SSRS DATASOURCE/S to point to my existing Primary AOAG Replica housing the needed DB's for the SSRS report DATA needed, i.e. Data Source=XX01DB-XX01;Initial Catalog="XXmainSQLdbXX01;
This would have all of the SSRS report over-head be run on the standalone SSRS server and remove that added performance stress from the main Primary/Prod AOAG replica.
I have been tasked with the following:
- Improve SSRS reporting performance (Highest priority)
** Make SSRS redundant since plants are affected by any SSRS downtime (currently even in our AOAG/HA setup, SSRS is broken until manual intervention occurs or failover back to original Primary replica occurs) ... so there is some downtime that occurs if AOAG/WSFC failover occurs for any reason currently.
*** Remove as much Resource/Performance "Strain" from the PROD replica due to SSRS running on it locally.
As a first step - it could help but won't be as helpful as you think. First - if you stand up a new server in Azure, you may find that performance is worse. It all depends on where that Azure server is and the latency between networks.
The stored procedures and queries that generate the data for the reports would still be running on the primary node. It is those queries that are contributing to performance issues in the application - since they take up IO, CPU and memory resources on that node.
To offload that performance, you need a separate instance on a separate node where you can run those queries. Using a read-only secondary is part of that - because that offloads that IO/CPU/memory to a different host and won't impact the production system.
To truly improve performance of SSRS - you need to build out tables and other structures to support the reports. These can be data marts, data lakes - cubes - etc...
From just an environment point of view - a read-only secondary with a separate reports database. To make SSRS redundant, you have several options - a standard windows cluster with shared storage is one option. Another is using VMWare (vmotion) - if something happens to the VM host the guest can be moved to another host. Azure also provides redundancy (I believe).
The problem with placing SSRS databases (ReportServer and ReportServerTempDB) in an AG is that you must manually restart the reporting services service after a failover. That service doesn't automatically redirect to a secondary even if configured to point to a listener. Moving those to a dedicated SSRS instance reduces the downtimes to only when that dedicated server is down (patching for example).
My guess is that report procedures running on the primary will still be slow - because they are competing with resources and access from the application itself. It may also be the lack of appropriate indexes to support the report queries. If that is the case, it makes more sense to build specific tables with appropriate indexes in a reporting database - which can be automated to refresh on a daily basis.
2 Likes
Also to add to all the amazing stuff @jeffw8713 and @robert_volk have stated, you might want to think about a proper datawarehouse or similar to feed your SSRS reports. Right now your reports are still riding on an oltp database though secondary. My assumption is You are trying to avoid performance issues for others using the primary by creating this secondary ao. Long term you might think of a proper dw or similar
1 Like
Yosiasz, Thank you. I have thought of the DW path for improving SSRS reporting, but our company has a few large DB's (2.3+TB) that the reports have to hit for as "real-time" DATA as possible and I believe DW will handle VERY Large DB's (lots of row returns) better than OLTP, but more for "Historical" Data.
TY again to ALL. I am definitely learning a lot here and Greatly Appreciate it.
Not really, you can make near real as possible
1 Like