AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server

AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server

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:


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:

Net start adfssrv
6.       Verify that the service starts up successfully.

7.       Repeat these steps for every federation server in this Windows Internal Database-based farm.

Step 5: Enabling this computer on the load balancer

 
Enable the computer in the load balancer so that requests are sent to it.

 

 


 

 

See Also

Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Jorge de Almeida Pinto [MVP-DS] edited Revision 29. Comment: The config DB connection string is set on a PER SERVER basis, therefore you must adjust it on ALL ADFS STS servers. The artifact DB connection is an ADFS property stored in the config DB, therefore changing it once on one ADFS STS server is enough. When retargeting any second or later ADFS STS server with a new connection string for the config DB it will automatically get the new artifact connection string already changed on the first ADFS STS server

  • Richard Mueller edited Revision 28. Comment: Added tags

  • Sean Ivey [MSFT] edited Revision 26. Comment: Step 3.3, changed use mastergo to use master <return> go

  • Brian Desmond -MVP- edited Revision 25. Comment: Added missing line breaks

  • Ed Price - MSFT edited Revision 22. Comment: Formatting code

  • Ed Price - MSFT edited Revision 12. Comment: TOC

  • Ed Price - MSFT edited Revision 11. Comment: Font

  • Odin1 edited Revision 9. Comment: fixed the code block in Step 3 item 4

  • Odin1 edited Revision 7. Comment: I corrected a typo by inserting some new lines for the SQLCMD code in step 4.3.

  • Odin1 edited Revision 8. Comment: re-doing change for apearance

Page 1 of 1 (10 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Odin1 edited Revision 7. Comment: I corrected a typo by inserting some new lines for the SQLCMD code in step 4.3.

  • Odin1 edited Revision 8. Comment: re-doing change for apearance

  • Odin1 edited Revision 9. Comment: fixed the code block in Step 3 item 4

  • Thanks! Great article.

  • According to this technet article it isn't supported to upgrade the internal database to sql.

    technet.microsoft.com/.../ee913581(WS.10).aspx

    Note  

    The migration of an AD FS configuration database from a Windows Internal Database to an instance of SQL Server is not supported in AD FS 2.0.  

    Is this true?

    Thanks

  • I tried doing these steps in the default UI in management studio and I got an error - something like "not supported". I wll have to try these script directions next time. Very interesting article.

  • Ed Price - MSFT edited Revision 11. Comment: Font

  • Ed Price - MSFT edited Revision 12. Comment: TOC

  • Ed Price - MSFT edited Revision 22. Comment: Formatting code

  • Nice Article..Thanks

  • Nice Article..Thanks

  • Nice Article..Thanks

  • Brian Desmond -MVP- edited Revision 25. Comment: Added missing line breaks

  • On point 3 of Step 3 please modify "use mastergo" to "use master" and on next line "go"

  • Sean Ivey [MSFT] edited Revision 26. Comment: Step 3.3, changed use mastergo to use master <return> go

Page 1 of 2 (23 items) 12