0

I have two databases in server, dbs-01.One is a transactional database and a reporting server database.Reporting is minimum,that is why we have it together in the same instance.

I have transactional replication set to replicate the data from server dbs-01 to dbs-02.Currently i am replicating only the transactional databse only.

I want to replicate the reporting server database also which i can implement. But my questions is : If i want my application to point to dbs-02,i can do that my changing the connection strings in the application. But what about the reporting server database.Because we have the Server Name in the Reporting Services Configuration Manager as shown below(as dbs-01). enter image description here If my dbs-01 is down and my application is pointing to dbs-02 , how can i make the reporting services to work from the dbs-02.?

I am not an application developer so i am not aware of any settings which will help to make that switch. Also configuring Reporting services for dbs-02 from the scratch is not a solution i am looking for.

2
  • 3
    You can have a dns entry pointing to the active server. When you failover change your alias pointing to the new server. I use Always On Availability Group so my alias always point to the listener name. Commented Jun 19, 2019 at 19:18
  • @SqlWorldWide : I am using SQL Server 2016 Standard and i believe for Always on Availability Group to work i should have Enterprise edition. Commented Aug 12, 2019 at 20:48

1 Answer 1

1

You can use AlwaysOn Availability Groups to replicate the SSRS Databases to other servers, and reference the Availability Group Listener in place of the server names to allow for failover between servers.

Be aware that Availability Groups do not replicate server level objects such as logins or jobs. If you have any subscriptions in SSRS these will be scheduled jobs that will need to replicated outside the availability group, and you will need to put a mechanism in place to stop them firing when their server is not the primary node.

Here are a number of blog posts that go into further detail about setting up SSRS onto an Availability Group: https://marcosfreccia.com/2018/06/25/reporting-services-databases-availability-groups/ https://jbswiki.com/2017/09/06/reporting-services-using-alwayson-availability-group/ http://techtalklive.org/ttlblog/making-sql-server-reporting-services-highly-available/

1
  • I cannot use AlwaysOn Availability group as the SQL Server Edition my client has is SQL Server 20160 Standard. Commented Jun 20, 2019 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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