The AD FS configuration database stores all the configuration data that represents a single instance of AD FS 2.0 (also known as the Federation Service). You can store this configuration data in either a Microsoft SQL Server® database or using the Windows Internal Database. The Windows Internal Database is a Windows Server feature that is automatically installed on the computer whenever you complete the AD FS 2.0 Federation Server Configuration Wizard for the first time.

Since the wizard does not provide a UI option to choose SQL Server as the store for the AD FS configuration database it is understandable how many would continue to use the wizard defaults to see if it will work well for their infrastructure. It is highly possible that in time you may want to scale out your federation server farm to use more than 5 federation servers by migrating the configuration database to SQL Server. By migrating to SQL you will obtain scale, high availability and also be able to use SQL’s backup mechanisms.

This topic is provided for just this situation and will walk you through all the steps necessary to migrate your existing AD FS configuration data from your current Windows Internal Database store (in a production environment) to a new SQL Server store. Follow steps 1, 2, 3, and 5 on the primary federation server. Follow steps 1,2, 4 and 5 on each of the secondary federation servers in the farm. These steps are included in the following sections:

Step 1: Backing up the federation server
Step 2: Temporarily disable the computer in the load balancer
Step 3: Performing steps on the primary federation server
Step 4: Performing steps on the secondary federation server
Step 5: Enabling this computer on the load balancer
See Also

For more information about the pros and cons of using either Windows Internal Database or SQL Server to store AD FS 2.0 configuration data, see The Role of the AD FS Configuration Database in the AD FS 2.0 Design Guide.

Step 1: Backing up the federation server

Use Windows Server Backup to back up the entire federation server computer including the AD FS configuration database stored in Windows Internal Database. You can also use Windows Server Backup to restore the AD FS configuration database.

See this article for more detail: AD FS 2.0 - How to backup the Federation Service

Step 2: Temporarily disable the computer in the load balancer

If your federation server is running in a farm and you have a load balancer, temporarily remove this machine from the load balancer configuration.

Step 3: Performing steps on the primary federation server

1. On the primary federation server in the farm, download the SQL Server 2008 Management Studio Express software and install it on the primary federation server using this link (http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en). This software is necessary in order to install and register the sqlcmd command-line tool which is necessary in an upcoming step.

2. Stop the AD FS 2.0 Windows Service on the primary federation server. Open an elevated command prompt, type the following command-line to stop the AD FS 2.0 Windows Service, and then press ENTER:

net stop adfssrv

3. Connect to the Windows Internal Database that currently stores the AD FS configuration database and then detach both the AD FS configuration and artifact databases. In the command prompt window, type the following sqlcmd command-line syntaxes in order, and then press ENTER after each one.

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
use master
go
sp_detach_db 'adfsconfiguration'
go
sp_detach_db 'adfsartifactstore'
go

4. Connect to SQL server and attach the configuration and artifact database from the primary federation server. In the command prompt window, type the following sqlcmd command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.

sqlcmd -S <SQLServer\SQLInstance>
use master
go
sp_attach_db 'adfsconfiguration', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsconfiguration.mdf', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsconfiguration_log.ldf'
go
sp_attach_db 'adfsartifactstore', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsartifactstore.mdf', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsartifactstore_log.ldf'
go
alter database AdfsConfiguration set enable_broker with rollback immediate
go

5. Change the configuration database connection string to point to the new SQL Server-based AD FS configuration database. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.

$temp= GEt-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial catalog=adfsconfiguration;integrated security=true”
$temp.put()

6. Open an elevated command-line prompt, type the following command-line syntax to start the AD FS 2.0 Windows Service, and then press ENTER:

Net start adfssrv

7. Change the artifact connection string to point to the new SQL Server-based artifact data location. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the artifact data to. For example, contososrv01\adfs-artifact.

Add-pssnapin microsoft.adfs.powershell
Set-adfsproperties –artifactdbconnection “data source=<SQLServer\SQLInstance>; initial catalog=adfsartifactstore;integrated security=true”

8. Stop and restart the AD FS 2.0 Windows Service to refresh the new settings. Open a regular command-line prompt, type the following command-line syntaxes to stop and start the AD FS 2.0 Windows Service, and then press ENTER after each one:

Net stop adfssrv
Net start adfssrv

Step 4: Performing steps on the secondary federation server

1. Make sure the primary federation server has been added back to the load balancer before proceeding with this section.

2. Make sure the secondary federation server has been temporarily removed from the load balancer before proceeding.

3. On a secondary federation server in the farm, open an elevated command prompt, type the following command-line to stop the AD FS 2.0 Windows Service, and then press ENTER:

net stop adfssrv

4. Change the configuration database connection string to point to the new SQL Server-based AD FS configuration database. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.

$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial catalog=adfsconfiguration;integrated security=true”
$temp.put()

5. Open a regular command-line prompt, type the following command-line syntax to start the AD FS 2.0 Windows Service, and then press ENTER: