Sudhir's Point of View...

On Cloud Computing, Integration Technology, Mobility, RFID, ERP etc...


November, 2006

November, 2006

  • Point of View...

    Using SQL Log Shipping with TFS


    Let's continue our discussion around Failover options in TFS. We already spoke about Backup/Restore, SQL Mirroring or SQL Clustering. One more option available is SQL Log Shipping. Log Shipping is very close to Mirroring. The only difference is Mirroring is almost real-time synchronization of databases whereas Log shipping is time delayed synchronization. In short you can synchronize the databases every X minutes. This helps when the geographic locations of primary and secondary servers are far apart for e.g. if you want to have Primary server in US and Secondary backup server in Asia. You do not want to do mirroring in this case, as it may slow down the primary server.

    Caution: This is not an officially supported configuration. I have tried it and it works fine. Please test this on your test environments before trying it in production.

    Terminology: Primary Server – Main TFS Data Tier, Secondary Server – Backup Data tier Server

    Before you begin you should have installed SQL Server 2005 on secondary server. Following are the steps to setup Log Shipping for TFS

    Configure Log shipping

    1. Connect to the primary server using SQL Server Management Studio
    2. Repeat steps 3-8 for all the TFS Databases(list provided below)
    3. Right Click on each database and select properties
    4. On Properties dialog select Transaction Log Shipping option
    5. Select "Enable this as a primary database in a log shipping configuration"
    6. Click on "Backup Settings" button, this should startup "Transaction Log Backup Settings"
      1. Enter the Network and local paths to the backup directory
      2. Enter reasonable time for delete and alert settings
      3. Click on "Schedule button" to open Job Schedule properties dialog. Select the schedule for transferring the transaction logs based on your environment. For example if you have primary server in US and Secondary in Asia you may want to schedule it for every hour. Once you have selected your schedule click "Ok"
      4. Click "OK" to get back to Database properties dialog.
    7. Next Click on "Add" to add a secondary server. This will open a Secondary database settings dialog
      1. Click on "Connect" button and enter connection information to connect to secondary server
      2. Select "Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if it doesn't exist)".
      3. Click "Restore options" button to select the location of data files and transaction log files on secondary server.
      4. Select the "Copy Files" tab and enter the destination folder for the copied files on secondary server.
      5. Click "OK" to get back to database properties dialog
    8. Click on "OK" and the system will automatically setup the secondary database with log shipping setup.

    Note: You should configure the transaction log shipping schedule (step 6.c) for all databases at same time. For e.g. all of them can run after every 1 hour starting 7:00PM 01/01/2007. This is crucial to keep the databases in sync.

    Failover to the secondary Database

    Important: To failover to the secondary server we have to failover all the databases at the same time.

    1. On the Team Foundation application-tier server, prepare and stop the services.(Detailed steps)
    2. On the new Team Foundation data-tier server
    • Connect to the secondary server using SQL Server Management Studio.
    • Run following TSQL Query for each TFS database. This will restore all the secondary databases and make them operational

      Restore database <TFSDBName> with Recovery

    1. On the Team Foundation application-tier server, configure the application tier to use the new data tier.
    • On the Team Foundation application-tier server, open the Command Prompt window.
    • At the command prompt, locate the program file directory, and then type:

      cd Microsoft Visual Studio 2005 Team Foundation Server\Tools

    • Run the TFSAdminUtil tool with the RenameDT option to map the Application Tier to Secondary Server

    TFSAdminUtil RenameDT <SecondaryServer>

    1. On the Team Foundation application-tier server, reconfigure Report Server .(Detailed steps)
    2. On the Team Foundation application-tier server, reconfigure SharePoint. (Detailed steps)
    3. On the Team Foundation application-tier server, restart the services. (Detailed steps)
    4. Update the work item cache. (Detailed steps)


    TFS Databases: ReportServer, ReportServerTempDB, STS_Config_TFS, STS_Content_TFS, TfsBuild, TfsIntegration, TfsVersionControl, TfsWarehouse, TfsWorkItemTracking, TfsWorkItemTrackingAttachments, TfsActivityLogging

    Note: We are working on officially supporting this configuration. I will keep you all updated

    Please let me know if you tried using these steps and found them useful.

  • Point of View...

    TFS Extranet Support

    TFS SP1 has support for Basic (or digest) authentication with TFS via an ISAPI filter. This could be used for clients connecting to TFS over the internet. James Manning has a detailed post on this topic.


  • Point of View...

    Visual Studio Team System Chat - Wed, Nov 8th

    Join members of the Visual Studio Team System product group to discuss features available in Visual Studio Team Foundation Server, Team Editions for Architects, Developers, Database Pros, and Testers. In addition, discuss what's new in the latest Community Technology Preview (CTP).

    Join the chat on Wednesday, November 8th, 2006 from 10:00am - 11:00am Pacific Time.

    Add to Calendar

    Additional Time Zones


  • Point of View...

    Using SQL Mirroring with TFS - Update


    As I mentioned in my last post we are working on documentation on how to use SQL Mirroring with TFS. We have the draft white paper published today at

    Please let us know if you have any questions on the content in the paper.



Page 1 of 1 (4 items)