January 6, 2020 at 10:31 am
Hi all
Our ETL process is quite slow and we are wanting to speed it up. I've come to the conclusion it's slow as we have to use linked servers to get to the data. This is the current set-up:-
server 1 - feed from 3rd-party application (SQL 2012 Enterprise)
server 2 - mirrored databases which are snapshotted to give us access to the data (SQL 2012 Standard)
server 3 - our production server (SQL 2016 Standard)
server 4 - our development/test server (SQL 2016 Developer)
Servers 3 & 4 are currently fed from the data held on server 2 (the snapshotted databases).
In the future we are looking to get rid of servers 1 & 2 but that is an ongoing project. I'm looking for a temporary solution to get around our long load times.
In the meantime, we are looking to use availability groups to push the data directly from server 1 to server 3 & 4.
If I'm correct, this would remove the need to use linked servers to access the data on servers 3 & 4.
Now for the questions:-
Can we use availability groups to get the data from server 1 to servers 3 & 4 with them being on different versions?
When we finally get the go-ahead to decomission servers 1 & 2, can we fail-over from server 1 to server 3 or will we need to remove the AG and start again?
This is a complicated set-up we've inherited and I'm trying to get things sorted out but it's slow going.
Any help on this would be greatly appreciated.
If I've missed anything or you need any more info, feel free to let me know.
Regards
Richard
::edit::
Forgot to mention, we would need the databsses on servers 3 & 4 to be read-write so we can test indexing (among other things).
January 6, 2020 at 7:51 pm
No - you cannot setup an availability group between versions like this and you cannot make the databases read/write on the secondaries. At best you can make the databases read-only on the secondaries so you can extract data.
I am a bit confused by your ETL process - are you using a tool for that process like SSIS or Informatics? If so - then why would you utilize linked servers to extract the data? If you are not utilizing one of these tools - then I would recommend you consider it.
Is your production database the destination for your ETL processes? Or is that the source system - and what exactly is a feed from a 3rd party application?
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
January 9, 2020 at 12:17 pm
Hi Jeffrey
In some semblance of order:-
That's fine, read-only is fine for what we need (not sure why I wrote read/write in my original question).
We use SSIS for program flow but it runs a lot of stored procedures. Unfortunately, because of the server setup, we have to go cross-server for some of the data (there's a plan being written to do away with a lot of that but we'll still need availability groups).
The 3rd-party application database is our source data which we then transform into usable information (the source data is not normalised or relational at all). This is then pushed into our production database on our production server. We also have a development/QA server for new functionality/bug-fixes/etc.
Cheers
Richard
January 27, 2020 at 3:24 pm
As a follow-up question on Availability Groups, do all the servers have to be on the same edition of SQL?
They will all be on SQL2016 but (assuming my plan is approved), one will be on Enterprise and one will be on Developer.
If my modified plan is approved, we'll have two servers on Enterprise and one on Developer.
We may also have one on Standard edition that will also need to be fed from the Enterprise edition.
Will this work?
TIA
Richard
January 27, 2020 at 3:35 pm
You won't be able to put a developer server into anything that's mixed with Standard or Enterprise for licensing implications.
If your DEV/TEST server needs to be part of the availability group it must be a licensed edition.
Standard edition only supports basic availability groups, no more than 2 servers, only 1 database per availability group, so it has it's real limitations.
What is your proposed topology and how are you expecting things to hang together?
January 27, 2020 at 3:40 pm
Thanks Anthony
I've actually just finished a "map" of how things currently hang together and how I see them hanging together.
I'll attached the spreadsheet (I found it easier to dfraw it in there, bizarrely).
January 27, 2020 at 3:59 pm
You're probably in breach of the licensing terms right now, the documents state in the "licensing for non prod" that if anything connects to another prod server, then it is also a prod server.
So Meditech pushing to DEV-DW or DEV-DW pulling from Meditech, means that DEV-DW should be licensed.
From the docs it's unclear how many servers, what lives where, whats going in what availability group etc to see if the proposed is fit for purpose.
For me the application would be its own set of servers, its own availability group.
The DW again is own set of servers its own availability group.
They SSIS used to pull from the application AG into the DW AG.
Dev would have to have its own environment spun up which can not talk to production in anyway to be truly non-prod. Restored copies of the prod databases, obfuscated, test data etc.
January 27, 2020 at 4:02 pm
Thanks Anthony
Looks like I'll have to talk to our licencing people to see what they say.
They know far more about this than I do, I just wanted to know what was possible.
Paying for it comes out of someone else's budget.
February 6, 2020 at 11:22 am
I'm still reading up on AGs and I've now got a question I can't find an answer to.
Looking through all the documentation I can find, it looks like we can set the Ags up to be either synchronous or asynchronous.
My next questions is:-
Can one database be part of two diffrerent AGs?
We may want to set up a synchronous AG and an asynchronous AG (for different purposes) but using the same database(s).
Is this possible?
February 6, 2020 at 11:36 am
No a DB can only be part of 1 AG.
But you can switch servers to be Sync or ASync.
Usually this is done for DR purposes, 3 node AG, 2 in primary site giving local HA, 1 in DR site for DR.
Local Site pair sync on a sync basis, the DR server syncs on an Async basis.
Sync requires transactions harden on all sync pairs first so can cause some transaction latency if the sync servers are not connected via a great link. Where as async is best for slower links.
February 6, 2020 at 12:42 pm
Thanks Anthony. So we can change the AG type on a server-by-server basis.
Useful to know.
There's quite a bit to learn about this.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply