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 include:

1.       Backing up the federation server

2.       Temporarily disable the computer in the load balancer

3.       Performing steps on the primary federation server

4.       Performing steps on all of the secondary federation servers

5.       Enabling the computer on the load balancer

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.

http://technet.microsoft.com/en-us/library/ee913581%28WS.10%29.aspx

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.

More information about how to backup the AD FS configuration database will be out soon. Once this content is provided we will update this link.

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 WindowsPowerShell 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.       Change the artifact connection string to point to the new SQL Server-based artifact data location. Open a WindowsPowerShell 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

7.       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

8.       Verify that the service starts up successfully.

9.       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. 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s