SQLTeam.com | Weblogs | Forums

Read scale availability group without cluster - SQL Server Always On

Hi,

I have AlwaysOn cluster with 4 AGs. I need to leverage 2 of the AGs to reporting. I am planning to use read scale availability node without cluster for reporting purpose. Since my reporting load is heavy, I need 4 read scale nodes without cluster as standalone copies. My question is:

  1. Is it possible to configure four read scale nodes from the cluster? If yes - is that configured only from primary or it could be achieved from secondary replica as well?
  2. Reporting clients are accessing the standalone read scale copies using server IP (not the listener IP), so even if the primary is not available, the read replica will still be accessible or it will go to resolving mode?

Thanks

Did you have a look at Read only routing option in AG?
This is best suited for this kind of situation, where you want the reporting systems to connect to the secondary replicas without putting load on the primary replica.

Hi Ahmeds08,

Thanks for your reply, actually I am looking for read scale, as on top of the AGs there are few standalone DBs with SSIS package to be combined to generate the report.
So I dont want that to be part of the cluster.
Could you please also respond to both of my questions, if you know the answers for.

Thanks.

If you already have multiple secondary systems - why would you now want to add the complexity of trying to add a separate distributed read-scale setup?

All you need to do is add any new secondary systems - set them up as read-only and direct the users to those nodes directly (not through a listener). These new secondary instances would be setup in asynchronous mode and manual failover - as you would not want to be able to fail over. For these you could also limit the databases to only those needed for reporting.

I don't see the requirement for read-scale.

1 Like

Hi Jeffw8713,

Thanks for your reply, as mentioned earlier - the main reason for read scale replica is on top of the replicas I need to run few other DBs populated through SSIS package (which is of a high intense OLTP), I dont want my cluster or the primary DB get hampered due to this.
So keeping that out of cluster as a standalone to serve only for reporting, I don't need to have a synchronous replica, it is fine to have 10 mins latency (max) could be managed.

Just would like to understand can we have multiple read scale replica(async)? (without cluster). If yes - how many ? any other cons on this approach.

If by any chance the primary AG DB log is full, would that make this read scale copy's db to resolving mode?

You can have additional databases on a secondary - and end users can access any secondary directly without having to go through a listener. You don't need a read-scale replica to accomplish those goals.

A secondary can and will be affected by the primary - whether or not that impacts the status of the secondary depends on many different factors. The secondary, whether read-scale or not - has a redo queue - and if that queue is backed up it could affect the primary negatively.

If the primary transaction log fills up - that directly affects the primary instance. The reason the primary transaction log is full needs to be determined, as it could be caused by one or more secondary replica's being backed up. If a secondary is backed up, the primary transaction log cannot be marked as reusable and will fill up.

Back to your original request - I see no reason to add the complexity of read-scale when it can be managed using normal secondary read-only replicas.

According to my org policy - inside cluster we should not install/run SSIS along with an AG replica.

Considering that, thought of having this as a standalone read scale copy and run the package.

So in my case, how may standalone read scale I could create from the primary AG?

You don't need a read-scale secondary to run SSIS packages. All you need is a read-only secondary or the primary database where you can push/pull the data.

Create a stand-alone SQL instance specifically for SSIS - with support databases as needed for any SSIS processes. Create your SSIS packages to extract the data from one of the read-only secondary instances (if you have read-intent enabled, then make sure your connection has the ApplicationIntent parameter to make sure you are redirected to a secondary).

Hi Jeffw8713,

Tried the method you have mentioned, it is not working as the SSIS load is too huge (approx 400GB data transfer), when I include it in the same cluster with secondary the network layer is slow due to this the primary connections is getting impacted.
To isolate this from the OLTP, thought of having this read-scale. My only query is how many read scale with async can be configured from primary.

Sorry - this doesn't make sense. If the load is too much on a secondary - it will be too much on a different secondary.

Where are you pulling the data from and where are you sending it?

You can keep asking about read-scale, but I have no answers for you on that. In my opinion - you don't need it and all it does is make an already complex environment even more complex for no benefit.

What I mean is - the system is an real time analytics on top of an OLTP, to do analytics I need data from OLTP and vendor specific data which populates through SSIS. (OLTP is 20% and SSIS and reporting is 80%)
Currently the system is OLTP+Reporting, which makes everything slow and the idea behind is to segregate the OLTP in a cluster and reporting in a standalone server (along with the SSIS), since SSIS and reporting are isolated there is no load to the OLTP server, so thought of using the read scale. Hope you understand, thanks for your response and good that I came to know you don't have answer to read-scale.

You still don't need a read-scale...just another secondary replica set to read-only. On that secondary you can add any additional services needed - just the same as in your read-scale solution.

The OLTP database(s) would be available as read-only with additional databases as needed for reporting/analytics, integration services catalog for the SSIS packages, etc...

You wouldn't need SSIS packages to read data from the OLTP databases...simple stored procedures to read the data and populate a local database. This would have zero impact on the production OLTP databases...unless you create a process that blocks the redo queue for an extended length of time (which would be the same in a read-scale environment). The transaction log on the primary cannot be truncated if there are outstanding transactions waiting to be committed on any secondary.

The read-scale solution doesn't get you anything more than adding another node to the existing cluster, except a more complex solution to manage and maintain. The basis for a read-scale availability group is to offload read access to a separate geographic location - one that would be closer to the expected reporting services.

As I already mentioned adding another secondary replica to the existing cluster cannot be done due to org policy. Since SSIS /RS/AS are expected to run on standalone not on an OLTP DB's cluster.