As BizTalk Server 2016 was released into the wild, we had all been waiting for the big splash for the AlwaysOn support, so naturally we all celebrated, took our tequila shots and jumped in the pool.
In previous versions to BizTalk 2016, BizTalk did not provide support for AlwaysOn SQL HA, because DTC was not available, but now that it “is supported”.
It took me a few days to read through the documentation for SQL Server, until I started to feel nauseated. Then the red flag became evident when I ran into this:
- Support for cross-database transactions within the same SQL Server instance
- Cross-database transactions within the same SQL Server instance are not supported for Always On Availability Groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same Availability Group.
- Cross-database transactions are also not supported for database mirroring.
Source: https://msdn.microsoft.com/en-us/library/ms366279.aspx
By default, BizTalk installs about 9 databases out of the box, and if you have one of your own database (which you absolutely should) you end up with about 10 databases. BizTalk Server is all about transactional executions. Who knows which Databases are called together within a transaction or scope? There are some documents describing some of it, and there are some speculations, but for the most part, Microsoft states that for this kind of configuration to “work”, we need to place these databases in separate SQL Server instances to overcome the “cross-database transaction limitation” of SQL Server 2016 AlwaysOn for DTC support.
What you end up with? Well, here’s a diagram recommended by Microsoft on how to configure BizTalk Server 2016 with SQL Server 2016 AlwaysOn setup:
Source: https://msdn.microsoft.com/en-us/library/mt743081
Pretty huh?
This diagram suggests a total number of 8 nodes, 4 Availability Groups and 8 SQL Instances. Additionally, you’re going to need another node for BAM Related SQL Activities, which by the way, it is not Highly Available. Now let’s think for a moment, 9 servers, and still no HA? And if that’s not enough, here are some known limitations for this configuration:
These limitations are for BizTalk Server, SQL Server AlwaysOn Availability Group, and Azure Virtual Machines. These limitations may or may not get addressed in future.
- Logins, SQL Agent Jobs, the SQL DB Mail profile, and accounts are not managed within Availability Groups. This requires manual modification in Jobs to make sure they run against the primary replica.
- SQL Server Analysis Services and SQL Server Integration Services do not participate in Availability Groups. Without this support from SQL Server, there is no HA solution for these in Azure Virtual Machines. BizTalk Server’s BAM capabilities are dependent on these services.
- Availability Groups does not support MSDTC between databases on the same SQL instance. Therefore, a minimum 8 SQL instances are required to configure BizTalk.
- To address MSDTC limitations with Availability Groups, BizTalk databases can be configured using a minimum of two servers hosting four SQL instances each.
- BizTalk Server cannot use Read-Only Routing.
- BizTalk Server does not set the MultiSubnetFailover connection property.
- BizTalk Backup Jobs using Log Shipping will always target the primary replica irrespective of the backup preference set on the Availability Group
Why the trouble?
Well, I guess at this point you ask yourself, what are the advantages of going with AlwaysOn vs. the traditional approach of a SQL Cluster at all?
To build a multi-server SQL Server that makes use of so many nodes, you will need to go with SQL Enterprise Edition no matter what, but, if all you need is HA, then Standard Edition will suffice, configuring an Active/Passive or Active/Active SQL Cluster. So the features of Enterprise Edition vs Standard is not really relevant here.
One true advantage that the AlwaysOn feature provides is the fact that no more Clustered Shared Disks such as SAN drives are required, where as a Clustered Configuration does. A few years ago, I ran into this company called SIOS, which they provide a product called SIOS DataKeeper Cluster Edition. What this does is simply what SQL Server AlwaysOn does at the application level but on Disk level, exposing your disks that are mirrored between the nodes as Cluster Disk Resources. How about that? The price is really reasonable, giving you the advantage of providing data redundancy as well. And bonus, this product is completely certified by Microsoft.
Microsoft recently upgraded its ILB support to handle more than 1 IP, although I haven’t tested it yet. Here’s a link: One or more Availability Group Listeners
At this moment, I wonder why Microsoft made this big announcement. Is anybody going to take this approach serious? I don’t and I wouldn’t recommend it to any of my customers. I would do it in 2 sets of SQL Clusters, I wouldn’t certainly configure them in this wasteful AlwaysOn setup, would you?
Feel free to reach out to me for any questions or concerns.