August 6, 2020 at 2:53 pm
Hi all
I'm trying to my facts in a row before I go to managers with this one.
We have one copy of our production data (from the software vendor) on server A (I know, it's the only copy and I've been shouting about that forever).
What we want to do is set up availability groups to get the data to two other servers (for both resilience and to make reporting easier).
The setup we have is as follows:-
Vendor server - server A - SQL2012 Enterprise edition
Reporting server - server B - SQL2016 Enterprise edition
Test server - server C - SQL2016 Developer
All the above have the same amount of cores but differing amounts of RAM (not sure if that will make a difference further down the line).
The vendor has stated that we cannot use availability groups for DR/HA (their limitations) so we just want copies of data elsewhere.
What we want to do is set up an Availability Group between server A and servers B & C.
My thinking is that we will then have "local" copies of the data on servers B & C to query without going cross-network/server to the "main" databases.
Firstly, are we covered to do this from a licence point of view (all three servers are properly licenced)? Reading the documentation, I'm 99% sure we are, but would just like confirmation without going to our licence provider as they usually try to sell us something we don't need.
Secondly, with the different versions/editions, is it even possible to set up availability groups on these servers (we are going from server A to servers B & C)? Looking on-line, it looks like it is possible but, again, I'd like confirmation.
Thirdly (I'm already assuming this is a "no"), assuming we can get this set up (and the licencing is fine), will we be able to add our own indexes on servers B & C or will the data just be read-only?
Apologies for the number of questions, but thanks in advance.
Richard
August 6, 2020 at 6:08 pm
The setup we have is as follows:-
Vendor server - server A - SQL2012 Enterprise edition
Reporting server - server B - SQL2016 Enterprise edition
Test server - server C - SQL2016 Developer
The above cannot be utilized for an AG - the secondary must be on the same Edition as the primary. And they should be on the same SP and CU for that Edition. You can upgrade a secondary to a higher Edition - but that is only for an upgrade path where you plan to upgrade all instances.
The vendor has stated that we cannot use availability groups for DR/HA (their limitations) so we just want copies of data elsewhere.
This eliminates any possibility of using an availability group. If the vendor does not support it - then adding that to your primary (vendor) system will be a contract violation and the vendor could refuse to support the system until it has been removed. I would be very careful in setting this up without getting the vendor to sign-off on the configuration.
I had the same scenario at one point - and the vendor required the databases to be removed from the AG when they performed an application upgrade. This required a complete rebuild of the AG after the application upgrade every time.
Secondly, with the different versions/editions, is it even possible to set up availability groups on these servers (we are going from server A to servers B & C)? Looking on-line, it looks like it is possible but, again, I'd like confirmation.
No - you need to be on the same Edition
Thirdly (I'm already assuming this is a "no"), assuming we can get this set up (and the licencing is fine), will we be able to add our own indexes on servers B & C or will the data just be read-only?
No - the secondaries would not be available unless you set them up as read-only (and not read-intent). If setup as read-only you cannot modify the databases.
This is all a moot point...you cannot setup the AG without getting the vendor to agree and you have to use the same Edition.
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
August 7, 2020 at 7:41 am
Thanks for the info.
Just so I'm clear, if we upgrade server A to 2016 Enterprise would that then work, or will the Developer edition on server C kill off that idea as well?
This bit was probably badly phrased, the vendor has no objections to us using availability groups, providing we don't use the failover options.
The indexes might be a showstopper. We're currently testing a home-grown version of AG (we grab any new/updated records from server A via a linked server and some SQL and port it across to server C). In this way we can add our own indexes because it's a read-write database on server C but really wanted something that was a bit more "built-in".
Thanks
Richard
August 7, 2020 at 9:30 am
You would need to change ServerC to be Enterprise also, you can't go mixing Dev & Ent in an AG as if you get audited well your going to be in for a right nightmare.
What about replication instead of an AG? ServerA replicates to ServerB in real time. You can then add whatever indexes you like on the ServerB database
You can then go and take a backup from ServerB and restore it to ServerC for testing purposes.
You will want to be careful of Server C accessing directly to ServerA or ServerB via linked servers, the licensing doc details any server which talks to a production server must be licensed correctly, so your linked server updating ServerC would void that clause and you would need to license ServerC with Enterprise even though you have developer installed.
The only real way to get the data to ServerC with developer edition would be to backup and restore as your then accessing a file share to get the BAK file and not the SQL processes so you are cleared from the "not accessing production" clause in the licensing doc
August 12, 2020 at 6:18 am
@anthony.green - we can't use replication either (vendor won't allow it, and the way they make database changes is weird).
We could replicate from server B to server C (at a push, but it would be painful every time the vendor made a database change).
I just need a reliable way to get the data from server A to servers B & C in relatively real-time but it doesn't look like it's going to be possible unless someone has any ideas?
Thanks
Richard
August 12, 2020 at 7:19 am
Sounds like your going to have to write your own methods.
If they don't allow replication then they wont allow CDC. What about change tracking instead of CDC more lightweight not as powerful but it may solve your issues.
Then you write a load of SSIS or powershell using the dbatools module to suck data from A and but it into B.
Server C is your issue, Server A or Server B are not allowed to directly communicate with Server C under the licensing terms unless you go and license Server C.
You would have to backup restore to Server C
August 12, 2020 at 7:33 am
Thanks for that, I've never heard of change tracking (I'll do some research) but it sounds like it's going to be a lot of work to get it set up.
Something else to add to the list.
On a different tack, what about log-shipping?
Would that work or would we still be in the same boat?
Thanks
Richard
August 12, 2020 at 12:51 pm
From Server A to ServerB, it would work but not without upgrading server A to be SQL 2016 first
Once you bring the DB online on ServerB to be standby the upgrade method would be invoked and then you would need to start logshipping from scratch each time as you wont be able to apply more logs.
You then wont be able to add any custom indexes to ServerB, so everything would need to be done on ServerA in that respect. You then have to think about restores, if you want this to be updated in near real time, you would have to kick everyone out of the databases ever log restore, what impact would that have, say if you restored every 15 minutes, but a report or a process takes 20 minutes, that process would never finish and never work as it would be terminated each time.
I again wouldn't recommend this on ServerC as while its a backup and restore some information is written between the two servers so some may construed this as being prod access. I would write a custom routine which goes and checks an UNC path and does the restores on C, you may be able to take sp_allnightlog from Brent Ozar and make that work from the perspective on restoring the stuff on C
August 12, 2020 at 12:59 pm
Thanks Anthony.
Looks like we've got some big discussions coming up on the best way to do this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply