Database Tracking in SQL Using TFS

Overview

In most projects we see that we are not able to track the database properly. If we have given permission to every developer to make some changes in the database then if anyone makes a small change then that can create a problem. Because there is no tracking in SQL Server (for Stored Procedures, Functions, Tables, login permissions and so on) like we have the View History option in TFS. So now in Visual Studio 2012 or 2013 we have a feature to create a SQL project and we can track the changes for a specific Stored Procedure, function and so on. So updating directly in SQL Server can create a problem in a large application and be difficult to track. So instead of updating in SQL Server now we need to update in SQL Server from TFS. So that the DB Administrator or whoever handles DB can keep track properly of who has done a change and what the change is and for even any wrong change can be reverted.

Procedure to create the project and keep track.

  1. Open Visual Studio and create one SQL Server Project

    Creating Sql Server Database Project
     
  2. Select the project and right-click on the selected project and select Import  > Database.

    Importing Database
     
  3. Click on New Connection.

    Creating New Connection
     
  4. Add the server name and choose the database name that you want to import.

    Define Server Name

    Connection Properties
     
  5. Select all the checkboxes to import permission and DB settings (in import settings).

    Permissions and Database Settings
     
  6. Click on Start.

    Connection Summary
     
  7. Once the Operation is complete click on Finish.
     
  8. Then you can see all the Stored Procedures, Tables, Functions and permissions in the project.

    Stored Procedure in Project
     
  9. Right-click on the project and click on Schema Compare.

    Importing Schema
     
  10. Source is your local project and Target is the SQL Server DB.

    Select Target Schema
     
  11. Click on Compare. You can see there is no difference in both the DBs because both are the same.

    Schema Comparision
     
  12. Now modify one Stored Procedure and again compare the schema.

    Modifying Stored Procedure
     
  13. Now you can see the change in that Stored Procedure . Even from here you can interchange the target and the source and click on update. It will update SQL Server as well and check in that Stored Procedure.

    Updated Stored Procedure

Now if we update the SQL Server from TFS and then we will check in that specific Stored Procedure in our Local TFS DB. In this way we can keep track of entire DB changes.