August 1, 2017 at 11:36 am
Hi all, in what follows, please bear in mind that I'm just a humble developer ...
Imagine that you have two SQL Server Standard 2016 servers, A and B.
Now imagine that you have two SQL Server instances, 1 and 2.
Instance 1 exists on server A and instance 2 on server B.
Both instances run business-critical databases.
What I think I am talking about here is a combination of
* Windows Server Failover Clustering
* 2 * Always On Failover Cluster Instances
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 1, 2017 at 3:27 pm
We actually have that set up where I work. Although we use a 3rd party tool called DxEnterprise (I do not work for them). If you have used Polyserve before, it is very similar and does exactly what you are talking about there. And I believe it is a lot cheaper than a windows enterprise license, which if i remember correctly is required for windows server failover clustering.
What DxEnterprise does is you would have 2 copies of it installed; one on A and one on B. If it detects a SQL issue on A, it will (depending on your configuration) dismount the disk with your SQL files on it from A, attach the disk to B and start up 1 on B. So B would host 1 and 2. Depending on your configuration, you can even have it auto-fail back to A afterwards. I use this when I do windows updates. I still do them after-hours, but I can install a SQL update on B for instance 1, fail 1 from A to B, update 1 on A and fail it back. And do any reboots inbetween.
Failover is pretty much as fast as SQL can restart. For our small instances, I see downtime in the 10-15 second range; the larger ones are only minutes. But our "larger ones" are in the 400 GB range.
The only problem with that sort of setup is you have no redundant secondary disk for the SQL Instances. So if 1 fails on A due to a disk failure, it won't be able to start on B as it uses the same disk.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 2, 2017 at 9:51 am
bmg002 - Tuesday, August 1, 2017 3:27 PMAnd I believe it is a lot cheaper than a windows enterprise license, which if i remember correctly is required for windows server failover clustering.
This is incorrect, Windows Server 2012 R2 Standard edition supports the Failover clustering feature
bmg002 - Tuesday, August 1, 2017 3:27 AM
What DxEnterprise does is you would have 2 copies of it installed; one on A and one on B. If it detects a SQL issue on A, it will (depending on your configuration) dismount the disk with your SQL files on it from A, attach the disk to B and start up 1 on B. So B would host 1 and 2. Depending on your configuration, you can even have it auto-fail back to A afterwards. I use this when I do windows updates. I still do them after-hours, but I can install a SQL update on B for instance 1, fail 1 from A to B, update 1 on A and fail it back. And do any reboots inbetween.
Failover is pretty much as fast as SQL can restart. For our small instances, I see downtime in the 10-15 second range; the larger ones are only minutes. But our "larger ones" are in the 400 GB range.The only problem with that sort of setup is you have no redundant secondary disk for the SQL Instances. So if 1 fails on A due to a disk failure, it won't be able to start on B as it uses the same disk.
Sounds very similar to Veritas Clsuetr Server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 2, 2017 at 9:52 am
Phil Parkin - Tuesday, August 1, 2017 11:36 AMHi all, in what follows, please bear in mind that I'm just a humble developer ...Imagine that you have two SQL Server Standard 2016 servers, A and B.
Now imagine that you have two SQL Server instances, 1 and 2.
Instance 1 exists on server A and instance 2 on server B.
Both instances run business-critical databases.
Is it possible to set things up such that Instance 1 on server A fails over to (secondary) instance 1 on server B in the event of an error on server A? And vice versa from server B to server A for instance 2?What I think I am talking about here is a combination of
* Windows Server Failover Clustering
* 2 * Always On Failover Cluster Instances
Both instances are pre installed and standalone, that's great.
Assuming you have an applicable version of Windows Server installed on each node you could go straight off and form a WSFc with the 2 nodes and use each of the replicas Instance A and Instance B as members of an Availability Group
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 3, 2017 at 6:55 am
Thanks very much to those who took the time to respond; very helpful.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply