Friday, February 20, 2015

Migrating your ADFS 2012 R2 SQL Database to a new or different SQL Server

I couldn't find good instructions on exactly how to migrate an existing ADFS SQL configuration to a new SQL server.  In my case, I was migrating from a stand alone SQL server to an AlwaysOn SQL Server for higher availability. 

ADFS has two databases it uses - AdfsConfiguration and AdfsArtifactStore.  So you have two places that you need up update the path to the new SQL Server.

Here are the steps I performed:

1.  Stop all ADFS Servers in your ADFS Farm.  ADFS Proxy servers are OK to leave up.
2.  Copy the two DB's to the new SQL server.
3.  Run the following command on each ADFS server in your farm to get the current information and take screenshots in case you need to revert the changes:

Get-WmiObject -namespace root/ADFS -class SecurityTokenService






Get-AdfsProperties


4. Run the following commands to update the location of the  "Adfsconfiguration" DB on each ADFS server you have: 
 

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

5.   Start ADFS Services now because the following command cannot be run with ADFS Service off.

6.  Run the below command on one of your ADFS server to update the location of the "ADFSArtifactStore" DB (Doesn't have to be run on all ADFS servers like the first command):


PS:\> Set-AdfsProperties –artifactdbconnection ”Data source=<YourSQLServer>;Initial Catalog=AdfsArtifactStore;Integrated Security=True”

7.  After the command is run, it will let you know to restart ADFS services on all servers in the farm, so do so at this time. 



8.   Run the following commands to verify that everything is now pointing at the new SQL server location:


Get-WmiObject -namespace root/ADFS -class SecurityTokenService
Get-AdfsProperties

9.  Test your configuration. You should now be pointing to the new SQL server.



26 comments:

  1. Hi, We have an ADFS 3.0 environment pointing to an internal DB and would like to move it to SQL. We have done all of the prep work for SQL, can I just follow the same commands to get the DB moved?

    ReplyDelete
  2. Essentially yes. Here is a guide for migrating ADFS 3.0 from WID to SQL. There are some extra steps http://social.technet.microsoft.com/wiki/contents/articles/23563.windows-server-2012-r2-ad-fs-migrate-your-ad-fs-configuration-database-from-wid-to-sql-server.aspx

    ReplyDelete
  3. One thing you may want to include in your instructions: enable service broker for ADFSConfiguration database. This is enabled when the db is originally created, but not automatically set when the db is migrated. This can cause problems later on after migration: See http://social.technet.microsoft.com/wiki/contents/articles/1436.ad-fs-2-0-query-notification-delivery-failed-because-of-the-following-error-in-service-broker-the-conversation-handle-guid-is-not-found.aspx

    ReplyDelete
    Replies
    1. Isn't that only for ADFS 2.0?

      Delete
    2. I was wrong we just moved one of our ADFS 3.0 farms from WID to SQL 2016 and needed to run:
      alter database AdfsConfiguration set enable_broker with rollback immediate

      go

      Delete
  4. Are you utilizing a DR site for you ADFS/SQL farm? What kind of recommendations can you provide to adding an ADFS server to the farm in a DR location? Would you add another server to the farm in the DR location so that it uses the AlwaysOn listener? Or would you setup a new replica farm that points to a replica database in the DR location? Currently I use a separate farm in DR but I am redoing a previous persons work to rebuild DR as another server in the farm to use AlwaysOn and then perform a DNS change in the event of a disaster.

    ReplyDelete
    Replies
    1. Sorry for the post as Unknown - My profile was set as anonymous and not Google+ :(

      Delete
  5. I am utilizing a DR site for my ADFS/SQL farm. I am using SQL AlwaysOn and would like to confirm if ADFS supports the MultiSubnetFailover=true option. I found the commands to use if I was using SQL Mirroring but this wont work with AlwaysOn. Anyone run across this?

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

    ReplyDelete
  6. I see that on my SQL server there is a ADFSCluster database. its not mentioned here. is that stored somewhere else?

    ReplyDelete
  7. Hmm...I'm not familiar with that DB being used with ADFS 2.0. We only had the two DB's mentioned to move.

    ReplyDelete
  8. Hi.
    Any guide on how to do this from SQL to WID ??

    ReplyDelete
  9. From SQL to WID there are cetain tools that you can follow.

    ReplyDelete
  10. Did you find that you had to update permissions at all in SQL or did they copy over?

    ReplyDelete
    Replies
    1. I will add that I did add the permissions of the user account I was using to SQL prior to copying the DBs over. It was also important not to switch versions of SQL. Also after attaching the DBs to the new server, I had to take them out of read-only by going under the options of the DB in SQL Management Studio.

      Delete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. I cannot run:
    $temp.put()
    Exception calling "Put" with "0" argument(s): ""
    At line:1 char:1
    + $temp.put()
    + ~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    This is on 2012R2, PS4.0

    Any ideas?

    ReplyDelete
  13. This comment has been removed by a blog administrator.

    ReplyDelete
  14. This comment has been removed by a blog administrator.

    ReplyDelete
  15. This comment has been removed by a blog administrator.

    ReplyDelete
  16. This comment has been removed by a blog administrator.

    ReplyDelete
  17. The open-source tooling uses the AWS s3 storage bucket to handle the workload. The experts also provide recommendations on the AWS server instance configuration after the migration. Themigration servicealso helps in moving the core applications after the migration. All the AWS migration experts provide the services at affordable costs.

    ReplyDelete