One of the things that I keep discussing with customers is whether they should create their ADFS farm with a WID backend or based on SQL server. It appears that from Microsoft’s perspective, reading up on TechNet, SQL is “the proper” thing.

What you usually do is look at the requirements at hand and then see how either database backend can deliver on these requirements. Of course, you’re also looking at future growth and realistic plans of that ADFS farm for the next, say, 12-18 months, and take that into consideration for your decision.

Looking at characteristics and summarizing the PROs and CONs of either backend, there’s the following:




# supported ADFS nodes

5, if # of RPs > 5

10, if # RPs <=5


# supported applications/Relying Parties

10, if # of ADFS nodes <=5

5, if # of ADFS nodes >5



All in the Windows license

You need to have SQL licenses separately

Replication Mode

Replication built-in and pre-configured.

You need to build/configure replication if there are multiple SQL nodes

High Availability mode

HA tied to ADFS HA – mostly on Load Balancer level.

Requires separate SQL HA configuration

Support for SAML Artifact Resolution



Support for Token Replay Detection



Complexity to setup

Low (setup all self-contained)


Complexity to maintain/operate



PRO SQL is the fact that there’s two features you only get in SQL-based ADFS farms. It’s SAML Artifact resolution and Token replay detection:

Aside from the facts collected from TechNet, you might wonder why I added the last two lines to the table. This is what I noticed with a number of customers. ADFS and the Hybrid Identity stack usually land in the Identity team or the folks that own Active Directory or the Identity Management system.

Depending on what the team structure is, they might not be familiar with SQL at all. Setting up and supporting a SQL-based ADFS backend that, most of the time fairly quickly inherits a business critical role, seems like a stretch. There’s most of the time not enough know-how or too few resources to support “another” technology. Things could be bearable if it was SQL only, but consider SQL High Availability: AlwaysOn Availability Groups for example, require Windows Clustering – another component you usually don’t support in an Identity Management group.

So technology aside, there’s a couple of things you need to look at additionally. Setup and maintenance are an important part. Licensing could be another. I’ve also seen customers that struggle providing Clustering in VMs, which makes it hard and/or costly to provide SQL HA for ADFS.

Other than these two features, functionality of WID- and SQL-based farms are identical.

If you’re unsure whether you want SQL or WID, I suggest you start off with WID – it’s less complex to setup and maintain. If you see there’s higher demand and you need more ADFS servers or need to support more Relying Parties, you can still move to SQL later – there’s a supported way to “migrate” the ADFS farm from WID to SQL: – although the title says ADFS 2.0, it works for ADFS 2012 R2 as well.


No Comment