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
   
   
    
2. Select the project and right-click on the selected project and select Import  > Database.
   
   
    
3. Click on New Connection.
   
   
    
4. Add the server name and choose the database name that you want to import.
   
   
   
   
    
5. Select all the checkboxes to import permission and DB settings (in import settings).
   
   
    
6. Click on Start.
   
   
    
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.
   
   
    
9. Right-click on the project and click on Schema Compare.
   
   
    
10. Source is your local project and Target is the SQL Server DB.
    
    
     
11. Click on Compare. You can see there is no difference in both the DBs because both are the same.
    
    
     
12. Now modify one Stored Procedure and again compare the schema.
    
    
     
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.
    
    

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.