If you started using TFS 2010 on a single server then you might get to a point where you want to move one or more Project Collection databases off that server. The reaons for that could be improved performance, or that you installed SQL Express and your database hit the 4GB limit (now 10GB).
Another common scenario where these steps might be handy is if you're in an environment with Shared SQL Server infrastructure, and your DBA's don't want your TFS account having "sysadmin" access on the server. What you can do is grant your TFS account "sysadmin" access for a few minutes while you configure the server (see Part 1 below), then they can take it away for good. Alternatively you could try give a DBA account TFS Administrator access and get them to follow the steps in Part 1. You can then create Project Collections on your SQL Express instance, and have them moved in a controlled manner using the steps in Part 2 below.
I found it difficult to find "easy to follow" instructions on how to move an individual TFS Project Collection database. Some instructions show how to move all databases including SharePoint and Reporting Services which makes the instructions complicated if you just want to move one of the databases. So my disclaimer is "this worked for me"! If you do try this yourself and hit any issues or have any questions please leave a comment on this blog post and I will see if I can help.
Part 1: Prepare the SQL Server Instance for TFS:
This part can actually be the most challenging because you need a service account that is a TFS Administrator, and has "sysadmin" access on the SQL Server box! You better either have really nice DBA's who will temporarily grant your service account "sysadmin" access on the SQL box, or you better be a nice TFS Administrator and prepared to give your DBA's account TFS Administrator access.
I went with the "nice DBA" option and got the DBA's to give my TFS service account "sysadmin" access on a SQL box, so I can't confirm whether giving a DBA account TFS Administrator access works but there is no reason it should not.
Here are the steps:
Another common scenario where these steps might be handy is if you're in an environment with Shared SQL Server infrastructure, and your DBA's don't want your TFS account having "sysadmin" access on the server. What you can do is grant your TFS account "sysadmin" access for a few minutes while you configure the server (see Part 1 below), then they can take it away for good. Alternatively you could try give a DBA account TFS Administrator access and get them to follow the steps in Part 1. You can then create Project Collections on your SQL Express instance, and have them moved in a controlled manner using the steps in Part 2 below.
I found it difficult to find "easy to follow" instructions on how to move an individual TFS Project Collection database. Some instructions show how to move all databases including SharePoint and Reporting Services which makes the instructions complicated if you just want to move one of the databases. So my disclaimer is "this worked for me"! If you do try this yourself and hit any issues or have any questions please leave a comment on this blog post and I will see if I can help.
Part 1: Prepare the SQL Server Instance for TFS:
This part can actually be the most challenging because you need a service account that is a TFS Administrator, and has "sysadmin" access on the SQL Server box! You better either have really nice DBA's who will temporarily grant your service account "sysadmin" access on the SQL box, or you better be a nice TFS Administrator and prepared to give your DBA's account TFS Administrator access.
I went with the "nice DBA" option and got the DBA's to give my TFS service account "sysadmin" access on a SQL box, so I can't confirm whether giving a DBA account TFS Administrator access works but there is no reason it should not.
Here are the steps:
- Choose "Account X" which is already a TFS administrator service account, grant it "sysadmin" access on the Destination SQL Server
- Remote into the TFS Server and fire up a command prompt running as "Account X"
- Run the "TFSConfig PrepSQL" command passing through the Destination SQL Server Instance name
- Revoke the "sysadmin" access for "Account X" on the Destination SQL Server
Assuming this works, the server is now ready to host any TFS databases! It seems that command somehow notifies TFS about the SQL Instance as well as SQL Server being "prepared". That is why the account needs such a high level of access on both systems.
Part 2: Move the database and Reconfigure TFS:
- Remote into the TFS Server and open the TFS Admin app
- Stop the Project Collection you are going to move
- Take a full backup of the current Project Collection database
- Restore the Project Collection database to the Destination SQL Server
- Back in the TFS Admin App, edit the Project Collection giving it the Destination SQL Server Instance name and hit "Save" (this took a few minutes for me)
- Bring the Project Collection back online
I have definitely noticed a massive performance improvement having the Project Collection database running on a high-spec SQL Server Instance rather than the lower-spec single server install that TFS is on.
Comments
Post a Comment