In our environment we have an always on availability group and the reporting server is acting as a secondary replica with Synchronous Mode, manual failover. XSLT report editor that is a vendor application will connect to the application server which in turn connects to the database server. The application server (separate server) connects to the listener to serve client connections. My question is if I set up a separate application server just for the XSLT report which in turn connects to the reporting, now will there be any problems if the reporting server for some disaster recovery becomes the primary where the Client application and the XSLT application will both connect, both the application is same that is installed on separate server one for client another for the xslt report.

I am not comfortable to set up transactional replication which is an additional overhead. Any advice will be greatly appreciated.

  • 2
    If the application has connection string of applicationIntent=ReadOnly then listener will redirect that to the secondary - provided your secondary allows read connection.
    – Kin Shah
    Dec 20 '18 at 14:35
  • The application settings only asks for the database server name or IP there is no read only intent option and the reporting replica will be read only Dec 20 '18 at 14:36
  • Then your application will always connect to Primary.
    – Kin Shah
    Dec 20 '18 at 14:37
  • 1
    So the client app connects to the listener, and the XSLT report connects directly to the secondary reporting server right? Since the reporting server is a manual failover server, you will manually failover to this one if disaster happens, and then your XSLT reporting and Client App will be sending queries to the same server, that will increase I/O. Dec 20 '18 at 14:37
  • I am fine with the increased I/O as in the Always on availability group there are 4 Replica- 2 with Synchronous Automatic Failover, 1 Reporting server onsite manual failover synchronous mode and 1 Disaster Recovery server offsite. Dec 20 '18 at 14:40

From a technical standpoint, there are no problems with multiple applications, running on multiple different machines, connecting to the same SQL Server instance.

If you experience the failover scenario that you described, it's possible that the reporting workload will interfere with your other workloads from a performance standpoint.

In other words, all the long read queries your reporting app does could block queries coming from your other apps. And in general the reporting app queries will require CPU time and I/O time that is normally solely used by the other app.

It's hard to say without more detail whether this would just slow things down a little, or bring your whole app grinding to a halt due to blocking or resource contention.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.