June 14, 2018 at 9:28 am
Hi there,
I am looking for a little guidance on what would possible be the best way for me to achieve some a Microsoft SQL failover along with an elastic IIS farm.
I would like the SQL to be in different datacentres. IIS would talk to SQL in the same datacentre. If there is an issue with that datacentre it would failover the other datacentre.
The data needs to be consistent between SQL, which will have frequent but small updates to multiple databases on the same SQL instance. If SQL2 is used then when SQL1 is back the data is updated.
As for IIS I would like to spin up new instances when the need arises. The IIS servers would sit in the same datacentre as SQL so if failover occurs it happens to both.
I’d like to minimise costs as much as possible so would like to know the minimum SQL version I would need.
Thanks
June 15, 2018 at 10:05 am
You can use Standard Edition with basic availability groups. There is a chance that one group might fail over with a database and not others, but you can write scripts to ensure you have a full failover.
If you want to ensure that SQL Server is in the same data centre as IIS, you'll need some monitoring and scripting that finds the active nodes and fails the other service(s) over if it has moved. For example,
SQL 1 - Data Center 1
SQL2 - Data Center 2
IIS 1 -4 Data Center 1
IIS 5-8 Data Center 2.
If IIS 1 fails, then do you move everything to Data Center 2? Think about this and map things out. If that's the case, then you need a rule that looks to see if IIS is active on 1-5 or 5-8, and then moves SQL appropriately. The same thing is for SQL server moving, then you need to fail over or move IIS to the other nodes.
SQL Server AGs are consistent between nodes.
June 17, 2018 at 9:43 am
Steve Jones - SSC Editor - Friday, June 15, 2018 10:05 AMYou can use Standard Edition with basic availability groups. There is a chance that one group might fail over with a database and not others, but you can write scripts to ensure you have a full failover.If you want to ensure that SQL Server is in the same data centre as IIS, you'll need some monitoring and scripting that finds the active nodes and fails the other service(s) over if it has moved. For example,
SQL 1 - Data Center 1
SQL2 - Data Center 2
IIS 1 -4 Data Center 1
IIS 5-8 Data Center 2.If IIS 1 fails, then do you move everything to Data Center 2? Think about this and map things out. If that's the case, then you need a rule that looks to see if IIS is active on 1-5 or 5-8, and then moves SQL appropriately. The same thing is for SQL server moving, then you need to fail over or move IIS to the other nodes.
SQL Server AGs are consistent between nodes.
Standard Edition isn't going to work for the OP - they have multiple databases in a single instance that will need to be part of the availability group. That will require Enterprise Edition - so all databases in that group fail over as one.
You will need a listener created for this configuration - and all of your connections will be set to the listener address and not the instance. You also will need to update the connection strings to identify the partner nodes. This will allow IIS to automatically reference the current active node - regardless of which data center is hosting the databases. And you will need either a file share quorom disk - or a witness node to manage quorom votes in the cluster - or 3 nodes in the cluster (node majority).
To insure that access is failed over to the appropriate data center - you will need a load balancer setup for your web servers. If there is a failover event - you would update the configuration to utilize the web servers in the other data center. Work with the team that manages your load balancer - they should have it setup and configured to failover also. You may want this to be a manual step for all but a total data center outage - where you down the local web servers and bring up the remote web servers manually.
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
June 18, 2018 at 2:33 am
Well, I'll disagree slightly on the Standard.
If the entire instance fails, all databases will move. If there is a reason for one failover, you can detect this with a job and fail the other dbs over. There will be a slightly delay, but it is likely on the order of single digit minutes, not that long. The dbs in an AG aren't sync'd in any sense, so not sure there is a problem with BAGs or AGs.
That being said, you have to decide and write code to manually fail over all dbs if one moves (or if IIS moves).
June 19, 2018 at 5:20 am
Thanks for the replies. gives me a good idea where to start anyway.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply