February 16, 2015 at 9:03 am
Hello again SQL Server Central Community,
First off, a big thanks to Perry Whittle for his help a while back with my HA architecture questions; it really helped me to understand what I needed to build a perfect scalable design. I was definitely moving down the wrong path with my ideas. Although they looked cool on paper, they were over engineered to a point that would cause problems down the road.
I've since come up with a much cleaner 5 node geocluster that we will begin rolling out in the second quarter of this year.
Until then though, I still need to maintain the existing inherited infrastructure. Currently, I have a very interesting project that I am hoping you might be able to provide some insight on:
I have a set of HA databases (2 node HA cluster, Data Centre 01) in our main data-centre that I need to replicate (poor choice of words maybe) to another data-centre (2 node HA cluster, Data Centre 02) for DR purposes.
Although these 2 clusters exist on the same domain; they are separated by a 30Mbps line and the Atlantic Ocean.
What do you think would be the best solution for this?
I have so far done quite a bit of research into using replication; which would allow me to have 2 way communication between the databases, but requires a separate distribution server (I do not have this available currently). Merge replication will also change the schema of the tables which would require me to do extensive testing inside the application to ensure it does not cause any issues.
I have done some digging into database mirroring, but I am unable to determine if I can set this up on top of the HA group without causing some issues with the underlying AlwaysOn technology.
I would not need to have the database active in both locations at the same time, although I will need to ensure that when the application becomes active in Data Centre 02; the data is replicated (again; maybe a poor choice of words) back over to Data Centre 01 when the link is restored.
We will be performing a disaster recovery test next Friday (11 days from now) and I am really hoping you can help come up with a workable solution by then.
Thanks for taking the time to read; if any more information is required, please just ask!
February 16, 2015 at 10:34 am
Transaction log shipping. From your description that will be sufficient for your needs and is VERY simple!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2015 at 10:39 am
Ozzmodiar (2/16/2015)
Hello again SQL Server Central Community,First off, a big thanks to Perry Whittle for his help a while back with my HA architecture questions; it really helped me to understand what I needed to build a perfect scalable design. I was definitely moving down the wrong path with my ideas. Although they looked cool on paper, they were over engineered to a point that would cause problems down the road.
Thank you, you are too kind.
Ozzmodiar (2/16/2015)
Until then though, I still need to maintain the existing inherited infrastructure. Currently, I have a very interesting project that I am hoping you might be able to provide some insight on:I have a set of HA databases (2 node HA cluster, Data Centre 01) in our main data-centre that I need to replicate (poor choice of words maybe) to another data-centre (2 node HA cluster, Data Centre 02) for DR purposes.
Although these 2 clusters exist on the same domain; they are separated by a 30Mbps line and the Atlantic Ocean.
What do you think would be the best solution for this?
I have so far done quite a bit of research into using replication; which would allow me to have 2 way communication between the databases, but requires a separate distribution server (I do not have this available currently). Merge replication will also change the schema of the tables which would require me to do extensive testing inside the application to ensure it does not cause any issues.
I have done some digging into database mirroring, but I am unable to determine if I can set this up on top of the HA group without causing some issues with the underlying AlwaysOn technology.
I would not need to have the database active in both locations at the same time, although I will need to ensure that when the application becomes active in Data Centre 02; the data is replicated (again; maybe a poor choice of words) back over to Data Centre 01 when the link is restored.
We will be performing a disaster recovery test next Friday (11 days from now) and I am really hoping you can help come up with a workable solution by then.
Thanks for taking the time to read; if any more information is required, please just ask!
Depends on what you need replicated and how often. I've used Log shipping as a readable standby in previous times to replicate a database from the UK out to Arizona.
Again it all depends on what you want this remote database to do, just read or write too?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 16, 2015 at 10:42 am
It's been a long while since I've used log shipping to be honest; I wasn't aware that it was a 2-way process.
Assuming I went forward with log shipping, have you had any experience with adding your warm standby into an HA group at the destination? From a purely technical point of view I can see this process as being very simple to achieve; but I am weary of the implementation (the initial full backup required to add the db into the HA group comes to mind)
How would the data be replicated back to the original host when the warm standby was brought online? Would I need to set up log shipping on the new primary after it has been brought online? In addition; wouldn't I need to reinitialize the database on the original host? as the log sequence would not match what was there.
Thanks!
February 16, 2015 at 10:46 am
Ozzmodiar (2/16/2015)
It's been a long while since I've used log shipping to be honest; I wasn't aware that it was a 2-way process.Assuming I went forward with log shipping, have you had any experience with adding your warm standby into an HA group at the destination? From a purely technical point of view I can see this process as being very simple to achieve; but I am weary of the implementation (the initial full backup required to add the db into the HA group comes to mind)
How would the data be replicated back to the original host when the warm standby was brought online? Would I need to set up log shipping on the new primary after it has been brought online? In addition; wouldn't I need to reinitialize the database on the original host? as the log sequence would not match what was there.
Thanks!
The only thing I saw about "2-way" was that if you fail over you need to then get stuff from new primary back over to the original primary. That is simply setting up log shipping in the opposite direction. Obviously moving large amounts of backups over a 30mb pipe may not be as fast as you desire/need.
Oh, BTW: it is just silly for a company to expect a complete new design, documentation, development, implementation and FULL TEST of a DR project in TWO WEEKS!!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2015 at 10:49 am
Thanks Perry,
The data in the second data centre will not be used regularly; but we do perform a DR test once a quarter, so every 3 months at the very least.
My main concerns with log shipping are:
How to get it added into an HA group in Data Centre 02, and how to have data replicated back to the original host without a large manual process.
Could I add a blank db at the destination, put it into the HA group; then enable it as a destination for log shipping?
What would happen upon a failover at Data Centre 01? Assuming I use a UNC path for the log files, will it continue shipping uninterrupted?
Looking forward to hearing your response!
February 16, 2015 at 10:51 am
It's not that it's expected; more of a nice to have.
Everyone is very much aware of our limitations on this and that it is a big request; I just thought I could give it a shot. It will be a small test group of databases that will be moving over.
Thanks for your quick response.
February 16, 2015 at 11:54 am
I'm running through it in a test environment now. I'll let you guys know how it goes.
February 16, 2015 at 11:58 am
Log shipping can pull from the secondary to acquire log files on a UNC path, which presumably will remain active when your primary "dies". So you should be able to get the most recent file(s) and apply them, then do WITH RECOVERY on the last to get the database into a read-write state.
But again, going back in the other direction requires setting up log shipping in the opposite direction, which will require initializing the database via backup (manual or during LS setup). That could take a while over WAN. But given that this is a TERTIARY DR site, moving back should be in a controlled manner and time delays shouldn't matter much after whatever blew up the primary site is fixed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2015 at 12:04 pm
Yup, that's exactly what I was thinking in regards to pulling them from the secondary server. It also gets around the problem of having a second set of TL backups on the primary server. I would obviously need to disable any existing log backups on the current primary if I was using it as a source, which would put this db (or set of db's outside of my beloved SOE).
Thanks again!
February 16, 2015 at 12:41 pm
You can always set up your own log shipping instead of using the built-in stuff, which can use whatever backups you already do. I had to do that for a client that had over 7400 databases on one server. Normal stuff just doesn't work at that scale! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2015 at 1:20 pm
Ozzmodiar (2/16/2015)
It's been a long while since I've used log shipping to be honest; I wasn't aware that it was a 2-way process.
It isnt as such. You can role reverse in log shipping, but when the primary is part of an alwayson group it gets complicated.
If this is DR then you would add a secondary to your AO group and use that to provide redundancy on your mission critical databases.
Ozzmodiar (2/16/2015)
The data in the second data centre will not be used regularly; but we do perform a DR test once a quarter, so every 3 months at the very least.
Using Log shipping with AO groups sort of defeats the object in my opnion. The more technologies you integrate the more complex it becomes, you're going to the expense of AO groups just extend it to provide a DR secondary.
Ozzmodiar (2/16/2015)
My main concerns with log shipping are:How to get it added into an HA group in Data Centre 02, and how to have data replicated back to the original host without a large manual process.
Could I add a blank db at the destination, put it into the HA group; then enable it as a destination for log shipping?
What would happen upon a failover at Data Centre 01? Assuming I use a UNC path for the log files, will it continue shipping uninterrupted?
Looking forward to hearing your response!
Keep it simple and try to stick with a single technology where possible
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 16, 2015 at 1:44 pm
Is there Always On already established in the existing infrastructure?? I must have missed that. I read it that the AG stuff was going to be introduced in the new plan later this year.
Perry is correct I think. If you have AG already locally then it may well be best to use that to construct the tertiary copy across the Atlantic. There are considerations about sync/async and other issues. But as he said a single technology would simplify things (not that Always On is simple by any means!!).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 16, 2015 at 2:21 pm
not there already but imminent i think.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 16, 2015 at 2:27 pm
If you absolutely have to do something meantime, implement a basic LS plan in the interim until your AO config goes live and then remove the LS plan once AO groups are in place
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply