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.



20 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. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training

    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. Please read this carefully as multiple advanced duplication software will be used to scan millions of other websites. http://zum345v4zc.dip.jp http://xdwv74aji6.dip.jp http://0ra8v7bfhj.dip.jp

    ReplyDelete
  14. Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp a great deal more around this condition. and I could assume you are an expert on this subject. Same as your blog i found another one Data Migration Software .Actually I was looking for the same information on internet for Data Migration Assistant and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.

    ReplyDelete
  15. Data migration processrefers to the complex process of transferring data from one computer system to another, or to another medium. This can be done to transfer from one computer to another, or to a new system altogether. This process should only be done by a knowledgeable professional, as data transfer is a very delicate and precise task.

    ReplyDelete