July 6, 2020 at 9:41 pm
Hello all,
I have a 2 node cluster, with a database that is part of the AG, now I have a third machine were I need a copy of just one of the databases of the cluster, that copy must be near-realtime, and allowing reads, in the same way as the second node of the cluster, so far I think my options are replication, log shipping, any others?
I am biased against replication due to the size of the objects (about 1TB) and transactions and the horrible experiences we had in the past where that thing failed and that lead to days trying to recreate it.
It would have been ideal if when adding a db to the AG I could select on which nodes I wanted to be replicated
So I am open to suggestions
thanks
July 7, 2020 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 8, 2020 at 6:35 pm
Are you on Enterprise Edition - or are you using Standard Edition?
Assuming Enterprise Edition, you can add that third machine into the cluster, install a stand-alone SQL Server instance and setup a new AG from the primary to this new instance. Set that secondary as read-only with asynchronous and then add the database(s) you want to be accessible from that node.
You do not need a listener for this - have the users access that instance directly for read-only reports and extracts. You will need to create the login and users on the primary then create the login with the same SID on this secondary. Once the logins have been created on both nodes (SQL logins only - windows users don't need a login on the primary) - you can disable or delete the login on the primary.
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
July 8, 2020 at 6:39 pm
Thanks that was my original idea (yes I am in enterprise)
the thing is that at the second node I have that database replicated already, and when I created a new AG it didn't let me add the database to it because it existed already at another AG...
and if I just add all the dbs for the third node that will replicate many dbs that I do not need and do not have the space for it...
July 8, 2020 at 7:22 pm
I missed that this database was already included in the other AG. You are fairly stuck here...
If you already have a secondary that is set to read-only, why do you need another secondary setup as read-only? I assumed you had a secondary specifically for HA and it isn't set to read-only.
If you just need a secondary for HA - setting up a FCI (failover cluster instance) satisfies that requirement much easier than an AG. Which would then allow for a read-only secondary of only those databases needed for reporting/ETL processes. But, it all depends on what you are trying to accomplish with the cluster.
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
July 8, 2020 at 7:27 pm
it is for a very specific use, where I need that db to be in a completely separate server, since the secondary copy has its uses and can't be share, so I guess replication and log shipping are my only options
July 8, 2020 at 7:39 pm
Log shipping won't get you near-real time data in a read-only database. You have to stop applying log files and switch the database to STANDBY for it to be accessible, then switch back and apply log files up to current.
Replication may be the only option - but now you have the issue of which tables need to be replicated and what indexes.
If I already had a secondary setup as read-only I would just grant access to that secondary. Not sure why it can't be shared if it is already available in that capacity. Barring that...I would push for enough storage to be made available to meet the requirements. The person/group requesting this access shouldn't expect to get it for free - if that is the requirement then they should purchase the additional storage that is needed.
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
July 8, 2020 at 8:03 pm
yeah, so, I have an idea, I could remove that db from its current ag, create a new one and have it at the three machines, how about that?
July 8, 2020 at 8:27 pm
That works, but you lose the fail over if that is a concern
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
July 8, 2020 at 8:32 pm
I think that is something we can handle, thanks, by voicing this out , was able to find a good solution
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply