We currently have a two node,Multi Instance SQL 2014 FCI that we will be upgrading in the near future to2016 as well as adding an additional Instance and additional node. I’ve been tasked with examining AG’s to seeif it makes better business sense than the FCI. We’ve had a few issues with the FCI and some are asking if AG’s would beable to solve the issues…
We have a SAN for sharedstorage so that is not an issue.
We have about 125 databases between two instances. All told the databases use about 10 TB of data between the two instances.
The issue we’ve experienced in the past is that with thenumber of databases and amount of memory in use (~400 GB per instance), ittakes the FCI a while to checkpoint all of the databases, release the memoryback to the OS and go through crash recovery on the other node to come backonline for manual failovers (patching, other maintenance). This can take as long as 5-10 minutes. We have a 24x7 web presence that we need tokeep online. We’re upgrading to machineswith 3TB of memory and there is concern that failover time could dramaticallyincrease.
In the past we’ve stayed away from AGs because they did not providea full recovery solution and our current fci based solution was workingwell. With the newer versions there aresome features that are looking attractive, including readable secondaries, AGDTC’s (we use linked servers to pass data between the instances) and automaticpage recovery among others.
I’m leaning towards the existing FCI for local redundancy,with the possibility of one or more AGs to a remote and local Datacenter for DR and reporting/read-onlyoffloading respectively. Currently we use file basedreplication to a warm standby site which I working well.
Can anyone comment on similar experiences and what is workingfor your large OLTP environments? Also,I’d like to hear about gotchas and other issues you may have seen with thistype of environment.
Thanks in advance,
To help us help you read this[/url]For better help with performance problems please read this[/url]