locked
TFS 2013 - Migrating TFS Databases

    Question

  • I currently have the TFS 2013 databases on a SQL Server 2014 Standard Edition instance.  I need to move those databases to a SQL Server 2014 Enterprise instance.  I found this article about migrating the TFS databases.

    https://blogs.msdn.microsoft.com/tfssetup/2014/05/15/migrating-team-foundation-server-databases/

    The article references a MSDN article that seems to be dated as at least the TFSConfig PrepSQL command is not recognized in TFS 2013.  In addition, I am not sure if there are any additional steps needed to migrate from a SQL Server Standard instance to a SQL Server Enterprise instance.  I remember installing the TFS databases on an Enterprise instance and downgrading to a Standard edition caused several issues with bringing the TFS databases online since a few features were no longer available.

    Are there any other updated articles that I can use as a reference?  Also, can TFS only use Windows authentication to connect to the SQL Server instance?

    Monday, June 06, 2016 5:41 PM

Answers

  • It turned out to be much simpler than I thought.  Since we were going through a data center migration, I ended up just making a new TFS server.  Through the TFS Administrator console on the new TFS server, I updated the existing service to use a TFS service account provided by the IT department. After that, you simply just follow the instructions on the link below.

    https://www.visualstudio.com/en-us/docs/setup-admin/tfs/admin/move-project-collection


    • Marked as answer by J I M B O Monday, April 10, 2017 9:06 PM
    • Edited by J I M B O Friday, April 28, 2017 9:32 PM Added that a new TFS server was created due a data center migration
    Monday, April 10, 2017 9:06 PM

All replies

  • Hi JIMBO,

    Thank you for posting here.

    You install a new sql server instance and you need to restore your TFS databases to the new sql instance. You need to remap database if you intend to continue using the original TFS instance https://msdn.microsoft.com/en-us/library/ms404869(v=vs.120).aspx#clone.

    >>can TFS only use Windows authentication to connect to the SQL Server instance?

    Yes, TFS use Windows authentication and NTLM to allow users to access sql server. This is a relatively safe model.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 07, 2016 8:27 AM
    Moderator
  • Thanks Tingting.  Once I get a TFS service account, I will give this a try.
    Tuesday, June 07, 2016 1:40 PM
  • >>can TFS only use Windows authentication to connect to the SQL Server instance?

    Yes, TFS use Windows authentication and NTLM to allow users to access sql server. This is a relatively safe model.

    Since the SQL Server exists on a separate server in the same domain as the TFS server, what type of account should the TFS use to connect to the SQL Server?  Should it be a normal domain account or some type of service account?
    Wednesday, June 08, 2016 1:33 PM
  • Hi JIMBO,

    Thank you for posting here.

    It should be domain accounts and NT AUTHORITY\NETWORK SERVICE account.

    If you use the domain account to sign in TFS and do some actions, TFS use domain account to connect to Sql Server.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 09, 2016 1:28 AM
    Moderator
  • It should be domain accounts and NT AUTHORITY\NETWORK SERVICE account.

    If you use the domain account to sign in TFS and do some actions, TFS use domain account to connect to Sql Server.

    So, it is recommended that the TFS account should be a domain account and not some kind of managed service account?  Currently, TFS uses the NT AUTHORITY\NETWORK SERVICE account since TFS and SQL Server were installed on the same server.  Since I am moving the SQL Server to its separate server, would the TFS account need to be placed in the sysadmin role on the SQL Server instance as well?
    Thursday, June 09, 2016 12:56 PM
  • would the TFS account need to be placed in the sysadmin role on the SQL Server instance as well?

    First in TFS, you could both domain accounts and local accounts to connect to TFS. But you could only use one domain account and NT AUTHORITY\NETWORK SERVICE to use as TFS's service account.

    When TFS send request to SQL server, it use domain accounts or NT AUTHORITY\NETWORK SERVICE.  

    In sql server, not all account need to be set as a sysadmin. You could just set the one who is TFS administrator as a sql server sysadmin.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 10, 2016 3:28 AM
    Moderator
  • Tingting,

    Since we have a two tier setup here with TFS being on one server and the SQL Server being on another, let's ignore the NT AUTHORITY\NETWORK SERVICE since that account can't authenticate against other servers.

    It looks like my only option now is to use a domain account.  What I am concerned with is that TFS creates new databases for each collection.  This would mean that it would need at least the db_creator role on the SQL Server instance.  From the TFS installation documentation, it stated that you needed the sysadmin role on the SQL Server for installation.  If I switched to a TFS domain login, does that login still need the sysadmin role on the SQL Server.  I would think that the answer would be yes.

    Friday, June 10, 2016 1:35 PM
  • Hi JIMBO,

    Yes, just as I said that "You could just set the one who is TFS administrator as a sql server sysadmin.". In most scenarios, the person(domain account) who install TFS and create collections for TFS usually be the TFS administrator. Maybe your team doesn't like this. I'm sorry to make you confused.

    You could set the accounts in the TFS admin console to sysadmin role on the SQL Server.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 13, 2016 2:02 AM
    Moderator
  • You could set the accounts in the TFS admin console to sysadmin role on the SQL Server.


    I don't think the Administration Console Users apply to this scenario.  TFS connects to the SQL Server instance using Windows authentication.  I would think that the only user that needs to be added to the SQL Server instance with the sysadmin role would be the account used for the TFS Service account.

    Monday, June 13, 2016 2:01 PM
  • Hi,

    >>I would think that the only user that needs to be added to the SQL Server instance with the sysadmin role would be the account used for the TFS Service account.

    This is also reasonable. That's not an exact answer for this question. Different environment or scenario has different choice.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 14, 2016 1:27 AM
    Moderator
  • Tingting,

    Please do not mark your posts as the answer without confirming that they actually work.

    Thursday, June 16, 2016 1:26 PM
  • It should be domain accounts and NT AUTHORITY\NETWORK SERVICE account.

    If you use the domain account to sign in TFS and do some actions, TFS use domain account to connect to Sql Server.

    I find the latter comment very unlikely.  A majority of the TFS users do not have write access to the TFS databases.  If so, how would it be possible that their domain account is being used to perform any action directly against the TFS database.
    Thursday, June 16, 2016 2:13 PM
  • I have said that "If you use the domain account to sign in TFS and do some actions, TFS use domain account to connect to Sql Server." When TFS accessing SQL server, That's not only using TFS service account but also using those domain accounts.

    TFS users domain accounts not TFS service account. This is tfs service account:

    You could change service account to one specific domain account by clicking "Change Account".


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 20, 2016 2:16 AM
    Moderator
  • Tingting,

    Thanks for all your help on this.  I have a support ticket open with Microsoft Support for this request.  I will post the solution once we implement it.

    Monday, June 20, 2016 8:34 PM
  • Looking forward to your feedback.

    Thank you.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 21, 2016 1:30 AM
    Moderator
  • It turned out to be much simpler than I thought.  Since we were going through a data center migration, I ended up just making a new TFS server.  Through the TFS Administrator console on the new TFS server, I updated the existing service to use a TFS service account provided by the IT department. After that, you simply just follow the instructions on the link below.

    https://www.visualstudio.com/en-us/docs/setup-admin/tfs/admin/move-project-collection


    • Marked as answer by J I M B O Monday, April 10, 2017 9:06 PM
    • Edited by J I M B O Friday, April 28, 2017 9:32 PM Added that a new TFS server was created due a data center migration
    Monday, April 10, 2017 9:06 PM