Source
r/SQLServer•
-SPOF

BAG vs FCI

After a team meeting we've had on the recent occasions of power loss, it turns out that the improvement of one of our SQL Server redundancy, is crucial. After looking into all of the possible ways of achieving our task, the budget comes into play.

With the licensing models of SQL's Always-on deployment always present, the decision between BAG and FCI, is quite a difficult one. Nevertheless, our lean is more towards BAG, the only concern is - automated fail-over.

If there are any options for configuring automated fail-over in conjunction with the BAG, what's the point in FCI's existence?

Update: We will most probably end up running hyperconverged with VMware + VMware VSAN or Hyper-V with StarWind VSAN. Both of these replicate the data and we expect doubled reads with FCI.

Posted Jan 16, 2019, 9:58 PM UTC · edited

1413
conflab•

You can have automatic failover with a basic availbility group.

Failover clusters utilise shared storage while an availability group will require you have storage for each node, this does of course provide some redundancy should have have a storage failure which you don't get automatically with a FCI.

FCI has the benefit that the whole instance is moved rather than just one database (only 1 per avilability group with BAG - which could be a headache I suppose). This makes administration a little simpler in that (in particular) your logins and SQL Agent jobs need only be created once.

One potential advantage of BAG is that the SQL Server service is always running on the inactive node, which will speed up failover time vs a FCI, which has to start the services.

7
alinroc•

For an FCI, you only need one SQL Server license (as long as it's a 2-node cluster); for a BAG, you may need two.

3
conflab•

I've never heard that before. Nothing I've been able to find online suggests that either, I don't suppose you have a link? The only relevant part of the SQL Server 2016 Licensing guide is the standard:

The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive and the primary SQL Server is covered with active Software Assurance. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work”, such as additional backups being made from secondary servers, then it must be licensed for SQL Server.

2
alinroc•

That's the relevant section, AFAIK. My understanding is that for an FCI, if the passive node is truly passive (sqlsvr.exe isn't running), you don't need a second license for that node and the license coverage transfers when you do a failover.

3
conflab•

Oh right, I thought you were suggesting something specific about BAG Vs a traditional AG.

The secondary in an availability group doesn't need to be licensed if it's not active, regardless of the fact the service is running. If that weren't the case I can't imagine anyone using AGs given the cost implication, unless they were doing so specifically for the readable secondaries (as opposed to just HA/DR). When they talk about active they mention running queries (obviously), integrity checks, backups, etc, not the services.

The licensing doc does show examples of 2 node AGs which illustrate that is correct.

3
timsstuffIT Consultant•

Also you can have an AG on top of an FCI, the FCI cluster becomes the primary node in the AG and you have a third server act as the secondary node in the AG. Not sure if BAG is supported but I have a few of these running on Enterprise Edition, one site I have 4 servers total, two in FCI, third in the same site as AG node 2, and the 4th in a DR site as AG node 3 with async.

1
mr_white79•

As long as you can take th performance hit that syncronis replication incurs, then availability groups ar a solid choice, and include automatic failover. Expect maybe 20% higher waits on writes. Depends on your network and storage.

For a Basic Availably Group, your secondary doesn't cost you a SQL license, but it's also not readable, so you can't do anything with it like run your backups or reporting.

If you setup async, then you can't do automatic failover, but, you don't incur the write penalty, and your secondary might be somewhat behind your primary.

FCI, you get auto failover, but then you need to have redundant shared storage, which gets pricey and adds management complexity and cost as well. Since there is only on SQL instance alive at a time, it also does not require two licenses.

I've run both extensively and for a larger higher performance maximum uptime environment, I think an FCI is more reliable, but AGs are still a solid alternative.

4
bandana-hammock•

Unless you only have one database I would avoid bag.

2
[deleted]•

[deleted]

1
ekkowekko•

If budgets comes in play then BAG is a good Alternative for SQL FCI. SQL FCI is also hardware failover and you need shared storage. BAG is only database Failover. Keep in mind your quorum? We use a Azure FileShare as a quorum. For BAG you also need a FCI but no shared storage.

For us is BAG a goog alternative instead of only Windows FCI whit SQL Server failover Clustering.

With BAG your secondary is only passif not readable. If you secondary is only passing it cost's you one single SQL Server SE license

1