This post is provided by Premier Field Engineer JJ Jacob who takes a look at the benefits of SQL Server Objects as software artifacts.

A recent customer engagement revealed several pain-points.  One of them is the issue of not being able to pinpoint the exact database schema the development team delivered to the production team.

The issue is about pointing to the one version of truth with regard to SQL deliveries into production.
What compounds this issue is that software delivery is made through updates to a file share, which is prone to the following risks:

1. Files are overwritten
2. There is not auditing or traceability as to who made the changes.
3. Furthermore, there’s no traceability as to which bug or feature the changes were made.

We started thinking as to what technology could address this. Two issues we needed to solve were:

1. Making the Database schema an artifact of version control – this could be easily solved by Team Foundation Server as the repository
2. The bigger problem was to get a representation of the schema into a set of version-controlled artifacts.

We find that Visual Studio along with SQL Server Data Tools, works very well with Team foundation Server’s version control system.

With this approach we were able to demonstrate:

1. Visual Studio’s capability to create a SQL solution, and import a database representation of it’s objects into *.sql file
2. Checking in that solution file into version control and tracking changes and comparisons between versions.
3. Comparing versions using schema compare between the solution and the live DB, or between schema snapshots
4. Ability to publish or deploy to Test/Dev environments.

The other value prop we realized was that DBA’s prefer to wok on SQL Server Management Studio, making changes to a DEV database. At the end  of the day, the Lead DBA had the capability to import the final DEV DB and check the schema changes in.

Visual Studio along with SQL Server Data Tools, in tandem with Team Foundation Server enables this scenario of tracking SQL schema as software artifacts – leveraging capabilities that are in-built in Visual Studio, SSDT and TFS.

Premier Support for Developers provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality.  Contact your Application Development Manager (ADM) or email us to learn more about what we can do for you.