The configuration database is one of the components that make part of an AD Federation Server deployment. It contains all configuration data regarding the Federation Service and includes information the Federation Services needs to identify certificates, claims etc… One of the decisions related to an AD FS deployment is whether to use SQL or a Windows Internal Database (WID) to store the ADFS configuration database in. Unfortunately, there’s not much documentation available (except for this article on TechNet maybe).
In order for the AD FS service to work you need an active (live) connection to the database. There’s no caching happening, which means that your federation server will stop processing requests as from the moment the connection to the database is lost. This immediately brings up some interesting questions. It means that the availability of your federation service also depends on the availability of your database… In this article, I will try to shed a light on the different options which will, hopefully, help you better understand your options and therefore lead to better decisions.
Note Before continuing I must clarify that I wrote this article, solely with the usage of ADFS in combination with Office 365 in mind. It is very well possible that conclusions or any of the statements below might not entirely apply if other usage scenarios are taken into account.
The choice of the database type directly impacts what you can (or cannot) do. In some way, it also dictates how you should setup your federation servers. The table below depicts some of the most important differences between SQL and the Windows Internal Database when used as configuration database store for AD FS:
|(AD FS) Feature||Windows Internal Database (WID)||SQL Server|
|Scalability||Limited to five servers in the farm||No limitation|
|High Availability||built-in “replication” mechanism||Needs SQL cluster|
|Adv. features||Not available||SAML artifact resolution &
SAML/WS-Federation token replay detection
As you can see, there are only small differences. Obviously, if you need some of the advanced features you’ll only have one option: SQL. On the other hand, ADFS & Office 365 does not use any of these advanced features leaving the WID also as a valid choice.
So, the question one could ask himself is: “What database type do I need to use?”. I’ve come across some articles over the past few months that seem to slightly favor SQL over the WID. The question, in my opinion is rather: “Do I really need to use a SQL database”? The answer depends somewhat on your personal preference but mostly comes down to some parameters that you can use to outweigh one choice against the other. When comparing both options, I usually cannot find a single persuasive argument why choosing SQL server over the Windows Internal Database is justified. Of course, there’s the argument of centralized management, so-called better performance, easier backup etc.
To justify my statement, let’s evaluate some of the claims against the Windows Internal Database.
1. Deployment, Management and High Availability
When using SQL, all your ADFS servers will be connecting to a central database. Essentially this means you only have to manage a single database instance. However; if you want to have some sort of high-availability, you’ll have to defer to clustered SQL servers. This not only leads to more servers, but also to an increased complexity (although I’m sure there are some SQL-guys out there that disagree with me right now). If you’re using a WID, High Availability comes somehow built-in into the product. The first AD FS server that you install will automatically become the “primary” server. Any subsequent AD FS server that you add to the farm will start replicating changes from the primary configuration database. This is an automatic process that polls for changes every 5 minutes:
It goes without saying that it requires a lot less effort setting up and configuring an AD FS farm based on the WID: it happens automatically. Moving from the WID to SQL is supported, but requires you to reconfigure each AD FS server. More information on that process can be found here: http://social.technet.microsoft.com/wiki/contents/articles/948.ad-fs-2-0-migrate-your-ad-fs-configuration-database-to-sql-server.aspx
2. Better performance
The AD FS Design guide cites that SQL could be used to achieve a better performance. The reasoning behind it is that when using the WID, you’re actually using a tiny local SQL database which consumes more resources on the AD FS server and moving this database off the server to a dedicated SQL server will save you some of these resources. Although I do understand the logic behind it, I think we should put it into perspective.
According to the AD FS 2.0 Capacity Planning Sizing Spreadsheet, a single AD FS server can easily service up to multiple thousands of users:
It should be noted that these recommendations were obtained through tests with a dedicated SQL server. However; the same page also describes the following:
It states that the load on the SQL server was – even when the AD FS server(s) were under high pressure – never really very high. From personal test, I have not seen any significant load on the SQL server either. The leads me to conclude that even when an AD FS server is under load, modern servers are more than capable of handling the additional load a WID might put on the AD FS server. I’m currently running some benchmarks to spot noticeable differences in AD FS performance between a WID or SQL; however the results have not been conclusive so far.
This is perhaps the one point where the WID definitely lacks behind SQL. In the event of a failure of your primary federation server, your only option is to restore that “primary” configuration database, reactivating the replication between the different members of the federation server farm. Until the database is back up and running, you will not be able to make any changes to your farm’s configuration. This automatically means that you have to take backups of your primary federation server (system state) at least every time you make a configuration change. Compared to the backup process of SQL (daily full or event incremental) you might have going on, this is definitely an additional effort (and one you cannot allow yourself to ignore!). Furthermore, in case you are using SQL there is no “primary” federation server: a failed server can easily be replaced by adding a new member in the farm.
Despite the limitations and some of the inconveniences that come with the Windows Internal Database, to me it’s the easier and my first choice when deploying an AD FS server farm. I’m not saying that using SQL is a bad idea; I just don’t see the point in the additional effort you need to put in the configuration and management unless you need the advanced features or are required to scale out beyond 5 servers. Unless Microsoft brings out some (better) guidance towards the choice of the database which explicitly explains why you should use SQL over the WID, I’ll stick with the WID.
As always: Please, feel free to comment.