Configuring your ADFS 2.0 Farm to use a SQL Mirror

Hi All,

In this blog I will discuss the steps taken to make an ADFS Server which is using SQL as its configuration database to be mirror aware. Mirroring your database can help in making your ADFS Server Farm more redundant in the event of a server outage. In order to compete the steps in this blog, you will need to have and ADFS 2.0 server which is using SQL for it’s configuration database and a mirrored copy of the database will need to be created on a second SQL server. Creating the actual SQL mirrored copy is out of the scope of this blog. For information about creating an ADFS Server to use SQL as it’s configuration database please see Configuring ADFS Server as the First server in the ADFS Farm using SQL for the Configuration Database.

1. Create a mirror copy of the ADFS Configuration and Artifact Database on a second SQL server.

2. Make sure your ADFS Service Account has the proper permissions to the mirrored database the same as it does for the master copy.

3. Now you will need to configure the ADFS Server to use the failover configuration database by following these steps:

Please Note: You will need to complete the following steps on all ADFS servers in the farm.

a. Logon to your ADFS Server and open PowerShell as Administrator.

b. Stop the ADFS Server from the by typing “net stop adfssrv”.

c. Now run the following 3 PowerShell commands replacing OriginalSqlServerName with the name of your original SQL instance and replace MirrorSQLServerName with the name SQL instance that has the mirrored copy:

$temp= Get-WMIObject -namespace root/ADFS -class SecurityTokenService

$temp.ConfigurationdatabaseConnectionstring=”Data Source=OriginalSqlServerName; Failover Partner=MirrorSQLServerName;Initial Catalog=AdfsConfiguration;Integrated Security=true”

$temp.put()

d. Now you can start the ADFS service back up by typing “net start adfssrv”.

e. To verify the new setting you can run the following PowerShell command:

Get-WmiObject -namespace root/adfs -class securitytokenservice

You will see the “ConfigurationdatabaseConnectionstring” property has been updated to include the failover SQL Server instance.

4. Next step will be to configure the ADFS Farm so the Artifact Resolution database can use the mirrored copy for failover following the steps below:

Please Note: This will only need to be run on one server in the ADFS Server Farm but the ADFS service will need to be restarted on all ADFS Servers in the farm after it is run.

a. Logon to your ADFS Server and open PowerShell as Administrator and run the following 2 PowerShell commands replacing OriginalSqlServerName with the name of your original SQL instance and replace MirrorSQLServerName with the name SQL instance that has the mirrored copy:

Add-PSSnapin Microsoft.ADFS.Powershell

Set-adfsproperties –artifactdbconnection ”Data Source=OriginalSqlServerName; Failover Partner=MirrorSQLServerName;Initial Catalog=AdfsArtifactStore;Integrated Security=true”

After running the commands above you will receive the following warning:

“WARNING: PS0038: This action requires a restart of the AD FS 2.0 Windows Service. If you have deployed a federation server farm, restart the service on every server in the farm.”

b. Restart the ADFS Service on every server in the farm.

c. To verify the new setting you can run the following PowerShell command from any server in the farm:

Get-ADFSProperties

You will notice the “ArtifactDBConn” property has been updated with the failover SQL Server instance.

At this point you are done. Now you can test by shutting down the master copy of the database and testing your farm. You can also review the ADFS event viewer logs for failover events. As always I recommend testing all of this in a lab scenario first. If you have any questions or feedback, please feel free to leave a comment. I hope this helps.

Related Links:

Installing Active Directory Federation Services (ADFS) 2.0

Configuring ADFS Server as the First server in the ADFS Farm using SQL for the Configuration Database

Adding an additional ADFS Server to your ADFS Farm when using SQL for the Configuration Database

Installing and Configuring an ADFS 2.0 Proxy Server

 

4 Responses to Configuring your ADFS 2.0 Farm to use a SQL Mirror

  1. Steve says:

    I have done this setup, I then tried changing the hosts file on one of the ADFS servers so that it could not connect to the primary SQL database. I then tried restarting ADFS thinking I would see it failover but instead the service just doesnt start. Am I missing something?

  2. BC says:

    I think a better test would be switch the database over to the mirrored copy and then test to see of your ADFS servers are still working with the mirrored copy (testing DB redundancy). Changing the host file would only cause the ADFS server to fail when resolving the SQL server’s name.

  3. JH says:

    “Now you can test by shutting down the master copy of the database and testing your farm”. Does shutting down assume you have manually fail over the Principal database server and the failover has become the principal? I found that if you don’t promote the mirror to principal this does not work. Did i miss something?

    • BC says:

      When you do he manual failover for testing, they should just switch roles. There shouldn’t be any need to promote the other. I hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *