July 2, 2019 at 7:10 am
Hi All,
We are using SQL Server 2014 Enterprise Edition.
We have 2 node Availability groups for high Availability purpose. One node as primary and one for secondary.
Now the plan is to have a DR setup which is a standalone sql instance which is remotely located.
Can we setup log shipping between an AG and a standalone sql instance? if it is possible, where will be log shipping jobs gets created?
What are the things do we need to take care of?
Thanks,
Sam
July 2, 2019 at 2:22 pm
Take a look at these links:
July 2, 2019 at 2:30 pm
It's certainly possible, but why wouldn't you add a third node in the DR site, and set it up as async?
There are things to consider when setting up the listener and the quorum, but that is how we decided to do things.
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/
July 9, 2019 at 2:52 pm
What Michael L John said. We've been doing this since 2012, works well.
July 9, 2019 at 3:39 pm
I agree with Michael. Don't complicate things. Use a third node.
July 9, 2019 at 8:05 pm
Hi there. I am in agreement with the rest about adding a third node however I am guessing that your reason for suggesting this is down to cost which is always the driver as SQL is an expensive beast and if you are utilising Availability Groups you are having to cough up for the enterprise licensing as I take it you are not using standard Basic Availability groups and I guess your second node is passive which is saving you licensing costs on that box as well?
That said a solution like you are proposing should you ever need to recover to that box it is a pain in the behind, and then when you have recovered the AG from what ever issue happened you would need to potentially feed the current db that failed over back into the AGs then re-establish the log ship which can be temperamental anyway.
That said your question was is it possible and the answer is yes it is. And here is a link to a guide on how to do it.
Should I do it? As these other people have suggested and they most certainly are not wrong adding another node to the AG is bar far the easiest solution and efficient way to keep sql alive in a dr scenario.
i hope it all goes well for you which ever option you choose.
July 9, 2019 at 8:23 pm
Hi there. I am in agreement with the rest about adding a third node however I am guessing that your reason for suggesting this is down to cost which is always the driver as SQL is an expensive beast and if you are utilising Availability Groups you are having to cough up for the enterprise licensing as I take it you are not using standard Basic Availability groups and I guess your second node is passive which is saving you licensing costs on that box as well? That said a solution like you are proposing should you ever need to recover to that box it is a pain in the behind, and then when you have recovered the AG from what ever issue happened you would need to potentially feed the current db that failed over back into the AGs then re-establish the log ship which can be temperamental anyway. That said your question was is it possible and the answer is yes it is. And here is a link to a guide on how to do it. https://social.technet.microsoft.com/wiki/contents/articles/33173.sql-server-alwayson-combined-with-log-shipping-automatic.aspx Should I do it? As these other people have suggested and they most certainly are not wrong adding another node to the AG is bar far the easiest solution and efficient way to keep sql alive in a dr scenario. i hope it all goes well for you which ever option you choose.
Not sure I agree that the licensing costs are prohibitive.
In the OP's existing scenario, there are the per-core costs + SA for the primary node.
If the secondary is read-only, the same costs apply. If it is not read-only, you only need to purchase SA.
Adding a third node that is not read-only will only require SA. The minimum number of cores is 4, that's approximately 6-7k in licensing for enterprise edition.
If log shipping is used, which can be standard edition, it will need to be fully licensed. The licensing for standard, again minimum of 4 cores, is the same 6-7k and you need to add SA, which is another 2k.
These numbers are based upon our enterprise agreement with Microsoft, your numbers may vary!
Also, if there are Enterprise level features in use on the primary server, Standard edition may not work in the event of a failover.
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/
July 9, 2019 at 9:17 pm
Agree with you comments relating to the secondary node being read only will incur additional licensing costs as the SA purchased with the primary server will give you one free standby server up to the same spec but if you query it you will need to licence it.
As for the standard version licensing it is drastically cheaper than the enterprise you would be looking at closer to £1500 per core for standard with out SA and for enterprise you are looking at about 5k per core withoutSA given that you have to licence 4 cores minimum you are looking at 20k for enterprise 6k for standard however standard does give the option to licence using the server CAL model which may be the preferred option last time I checked I believe server was Around £800 and the CALs are around £170 without SA. These costs alone are usually why a lot of companies I have worked with would simply not entertain the purchase of enterprise.
I believe depending on how you pay for licences you can licence with an open licence agreement that would include SA and would spread the cost over 3 years.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply