Database Tracking in SQL Using TFS

Introduction

In most projects, we see that we cannot track the database properly. If we have permitted every developer to make some changes in the database, then if anyone makes a small change, that can create a problem. Because there is no tracking in SQL Server (for Stored Procedures, Functions, Tables, login permissions, and so on), 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 track the changes for a specific Stored Procedure, function, and so on. So updating directly in SQL Server can create a problem in an extensive application and be challenging 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 of who has made a change, what the difference is, and even if any wrong change can be reverted.

Procedure to create the project and keep track

Open Visual Studio and create one SQL Server Project.

Select the project and right-click on the selected project, and select Import  > Database.

Click on New Connection.

Add the server name and choose the database name you want to import.

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

Click on Start.

Once the Operation is complete, click on Finish.

Then you can see all the project's stored procedures, Tables, Functions, and permissions.

Right-click on the project and click on Schema Compare.

The source is your local project, and Target is the SQL Server DB.

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

Now modify one Stored Procedure and again compare the schema.

Now you can see the change in that Stored Procedure. You can interchange the target and the source from here 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, we will check that specific Stored Procedure in our Local TFS DB. In this way, we can keep track of the entire DB changes.

Conclusion

In this article, we learned about database tracking in SQL Using TFS.