SQL Server Always On High Availability

Question

Hi:

We have this setup in SQL Server HA. We want to copy a database daily from Production to Reporting, so that our users using the database for reporting can connect to the Reporting Instance instead of Production. The Reporting Server is also HA and has 2 nodes. We currently use Failover Cluster Instance and by using Windows Server Failover Clustering is there any advantage?

Questions:

• If we restore the database daily will that create a problem in Reporting Node 1 to Node 2 and will that take lot of time to copy the data versus if it is copied to standalone server since it has to removed and added back to HA?
• • Can we instead use Production Node 2 for reporting?

Thanks!

Answer 1

Hi SQL,

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your query; I'll be more than glad to help you out.

Your post is slightly confusing; maybe you just mixed up some words...

For SQL Server, there are two HA scenarios... SQL Server on a Windows Failover Cluster and SQL Server AvailabilityGroups

With an FCI, your SQL Server runs on one node of your cluster. Therefore you can't use the second node for reporting purposes, and it doesn't matter if you restore the database once every day.

If you are talking about an Availability Group; you already have a fully licensed Cluster, so - depending on your requirements - I would say, you don't need the second AOAG, you just can use the secondary node of your main AOAG cluster, If your requirements tell you, you have to have a separate node for reporting, just add a third node to the main AOAG for reporting

If you want to stay with this 2x2 scenario... also no problem ( depending on the size of the database ) with a daily backup&restore refresh of you reporting environment.

So finally... to both of your questions... It is not a problem to do so ;-)

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

@Peters, Thanks for the detailed response. This is helpful but I have a follow up question:

With the 2x2 scenario, I guess we will have to first remove the database from AOAG, restore the database and then add it again to AOAG? Since the database size is 200GB I guess it will take lot of time (probably more than 2 hours)?

Answer 3

@Bjoern Peters Are these the correct steps?

Answer 4

@Bjoern Peters Yes, we are on same release level and it is SQL 2019. Here is the updated sequence: