February 15, 2024 at 3:07 pm
Hello,
I am looking for an efficient method to add a 3rd Instance/Node C for reporting purposes.
The solution has to be:
efficient/not expensive,
not Enterprise Edition,
Minimal licensing,
Will work on 2016/19/22
not affect the Nodes A & B performance when either is Primary.
Easy to manage.
So far it appears the only method is Transactional Replication.
Here is one link I have already read....
Any suggestions most welcome.
February 15, 2024 at 3:18 pm
Replication or log shipping will be your only ones to be fair, although log shipping brings with it complexities about what to do if people are reporting and a log needs to be restored, but then replication brings complexities as you don't want to be replicating all the tables under TX replication and then its every table must have a PK and other DB design decisions.
Or you write some ETL methods to pull the data you need and load it into the 3rd machine on a schedule which suits the reporting requirements.
February 15, 2024 at 4:25 pm
I would take log shipping any day over replication, but your primary and secondaries have to be the same version in order to read the log shipped replica. When replication works well, it is totally fine, but when it doesn't it is terrible. It also tends to grow into a large dependency web that can be labor intensive to address during sql version upgrades.
As ant-green mentioned you will have to decide how to handle connected report users. In my own implementations I allow users to hold up log shipping restores for up to a number of failed log ship restores that equal 30 minutes and then reconfigure the replication to disconnect connected users, run the log shipping restores , then once it is complete, re-enable the setting to allow users to block log shipping restore. If you use some sort of cached report, you can make it nearly transparent to users.
Doing an ETL would be the best way to do this, but most development time.
February 15, 2024 at 9:43 pm
If you need near real-time access to the data for reporting - then your only option is going to be replication. All other options result in a point-in-time solution.
If you don't need near real-time, then log shipping or backup/restore will work.
From my point of view, the additional cost of Enterprise Edition is well worth it if you need near real-time read-only access to the data. The overhead and management of replication will easily offset that additional cost - and setting up a read-only asynchronous replica is much easier and much easier to manage and maintain.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 16, 2024 at 1:56 pm
Jeffrey,
Many thanks for the reply!
It needs to be near live 1 hour MAX.
So just checking, would Replication be OK with the 3rd node & readable with Standard Edition?
Also with Log Shipping are sessions kicked out when playing logs?
February 16, 2024 at 2:34 pm
Replication you don't want to replicate the whole DB, that just asks for trouble. If you do replication, make sure its the core subset of what is needed and not everything.
Log Shipping, yes sessions can be kicked out to replay the logs, by default they aren't but if someone is using the read DB then the logs wont restore as exclusive access cannot be granted.
The business told us once they want the whole DB in TX replication, we said no, they said yes, so we did it, then when performance crippled the eCommerce site they soon retracted and went they only needed say 20 of 1000 tables, so if someone tells you they want everything challenge it at every turn and only give them explicitly what they need and not everything.
April 20, 2024 at 5:18 am
Why use log shipping or any of the other options suggested when you are already using the only tool you need.??
Simply add a 3rd stand alone node (non-member node) to the ag, this will allow the data to be replicated, readable, and will not interfere with the performance of the primary ag.
I’ve done this in the past and have the instructions on how to set it all up.
pm me if you want further details.
August 9, 2024 at 2:23 pm
Hi, from the University of Colorado Boulder,
Our DBA team ran into your post and was hoping you could share what you did with the adding in the reporting node.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply