September 15, 2022 at 1:50 pm
Hi,
I've been trying to find the answer on the internet but was not successful.
I don't have the setup done yet (hence can't try it out) but this is basically how it's going to look like:
Produktion:
2 servers - Always On Availability Group - SQL 2019 Standard Edition hence no secondary read only copy.
QA:
2 servers - Always On Availability Group - SQL 2019 Standard Edition.
I would like to setup log shipping from the Produktion environment to the QA environment.
So two sets of AG servers and then log shipping from the Production server (AG) to the QA server (AG).
What I'm aiming for is to have a read only copy of a database from the Production enironment in the QA enviroment while both Produktion and QA are AGs.
The read only log shipping database should be part of the QA AG.
Is this possible?
A simple Yes or No is fine as an answer but should the answer be Yes you are more than welcome to provide any how-to links should you have them near by.
Cheers
Mikael
September 15, 2022 at 6:26 pm
To clarify, you have DatabaseA in production in an AG. You also have that same database in QA in an AG.
You want to log ship from Prod DatabaseA to QA DatabaseA?
If so, then NO.
If you want to log ship Prod DatabaseA to QA DatabaseX, then yes. But you will not be able to add it to the AG.
What is your goal?
Do you want a read-only copy of the database for things like reporting and ad-hoc queries? Then, I would suggest upgrading to Enterprise. Probably 5x the cost of standard though. Or, restoring a backup, replication, or log shipping.
Or, do you want a regular refresh of the QA environment from production? If that's the case, then restoring a backup, and adding the DB to the AG can be 100% automated
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 16, 2022 at 5:44 am
Thank you for the feedback Michael!
"To clarify, you have DatabaseA in production in an AG." - Yes
"You also have that same database in QA in an AG." - No. The goal was to log ship a copy from DatabaseA in production to DatabaseX in another AG on another set of servers.
"But you will not be able to add it to the AG." Thanks, that was my goal but now I know that's not possible.
"Do you want a read-only copy of the database for things like reporting and ad-hoc queries?" - Yes
"Then, I would suggest upgrading to Enterprise." - Yes, I wish that was an option 🙂 But to expensive.
"Or, do you want a regular refresh of the QA environment from production? If that's the case, then restoring a backup, and adding the DB to the AG can be 100% automated" - Yes, I guess that is an/the option. I will sure look into that.
Many thanks for your feedback Michael. I do appretiate it 🙂
September 16, 2022 at 1:33 pm
Could you have the database on in QA but not in an AG and log ship to it with the standby option so it can be read? You can log ship to multiple servers so could have it going to QA primary and secondary servers if you wanted that.
Like Michael said, you can't log ship to a database in AG. So if the above is no good then replication or a periodic refresh might be in order
September 16, 2022 at 5:44 pm
Thank you!
My concern with your propasal (log shipping to both QA-servers) is the connection string. I then need to know which server is considered to be primary as there is no listener in place without an AG. Maybe the secondary is down for maintenance.
Or am I missing something with your proposal?
Thank you and have a nice weekend 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply