Samir Behara

Continuous Integration with SQL Server Data Tools in Visual Studio 2017

July 17, 2017 by

Do you want to make your deployments risk free? Do you want to deliver business values to your customers faster? Do you want to increase the efficiency of your Team? Are your application and database deployment separate processes? Do you encounter issues while deploying your database changes? Is your database slowing you down?

If the answer to any of the above questions is a Yes, then this article is a good starting point for you.

The objective of this article is to help you understand the problems with traditional database development, why organizations are moving towards achieving Continuous Integration, the problems it tries to solve and learn about the toolsets which will assist you in this journey towards painless database deployments.

What is Continuous Integration

Continuous Integration is a development practice which encourages developers to check in code changes to source control as frequently as possible. Since the code is checked in and integrated more often in smaller pieces, it is easier to identify and resolve issues at an earlier stage. Every code commit triggers an automated build in a separate CI server and executes the unit and integration tests. Code Quality is maintained, since any build which has failing tests are not allowed to get checked in to source control repository.

One important thing which you need to remember is that you cannot do Continuous Integration without Source control.

Why is Continuous Integration required

Risk Mitigation

With Continuous Integration, you will commit smaller pieces of code more frequently into source control. This will clearly lead to less merge conflicts and you will be able to catch issues more easily.

Shorter Feedback Loops

With every code commit, the automated test suite is triggered which gives an immediate feedback to the developers if there are any issues with the latest check in.

Increasing Team Efficiency

Since code commits are done more frequently, Team spends less time in resolving merge conflicts and engaging in manual activities. This results in greater Team collaboration and awareness of processes.

Faster Delivery of Business value

The iterative model ensures that there are no last minute surprises for the customer. Since the changes are pushed to Production frequently, Business can verify the functionality more often. With shorter feedback cycles, development speed will also increase.

What are the problems with traditional Database Development

Data is hard to manage. You can overwrite and redeploy your code from scratch every time, but the same cannot be done with data. Databases have existing data which needs to be persisted and hence it cannot be wiped off and recreated during deployments – just like the code.

Having no Source Control for your database objects implies that there is no versioning information available for your database changes. Production is the version and system of truth. This makes it almost impossible to unit test your changes and automate your deployments.

Developers have to spend lot of time in creating the DML scripts. They also need to account for the tedious rollback scripts, in case the overall deployment had to be rolled back. The Database Administrator has to spend lot of time in executing scripts against multiple environments.

Removing manual work and inefficiencies in your database development process is one of the reasons why organizations are moving towards automating their database deployments.

What is SQL Server Data Tools

SQL Server Data Tools provides an integrated development environment inside Visual Studio with rich set of database tools and features to compliment database development. Be it a T-SQL Developer or DBA, you spend most of your time inside SQL Server Management Studio to interact with your SQL Server instance. Most of the developer capabilities and functionalities that SQL Server Management Studio provides are now available within SSDT, hence enabling developers to perform tasks like creating tables, views, stored procedures and modifying them.  

How to install SQL Server Data Tools in Visual Studio 2017

Visual Studio 2017 provides a new installation experience, where you have the option to select and install features based upon your requirements – hence ensuring that you are installing only the components which you need and not every default component which comes with Visual Studio. This helps in decreasing the install size and also making the installation process a lot faster, since no unnecessary components are installed.

To install SQL Server Data Tools you have 2 options. You can select the Data storage and processing workload while installation:

You can also install SQL Server Data Tools as an Individual component:

What are the basic capabilities of SQL Server Data Tools

Version Control

Using a Database Project – a special type of Visual Studio project – you can put your entire database schema under source control. All database objects like tables, stored procedures and user defined types are stored as individual T-SQL source files inside the project which can be searched and modified in source control just like your code files.

Disconnected Database Development

SSDT allows you to maintain a local database and do all the development using that. It is important to understand that you are working with a Database Project inside Visual Project and not against the connected instance of your database. This is what is termed as ‘Offline/Disconnected support for database development’ with Database Projects. The Database projects contains all the database objects stored as separate T-SQL source files inside the project. You can merge your changes to the target database at a later point of time.

Declarative approach to Database Development

SQL Server Data Tools follow a State based approach – which means that the source of truth is the database schema inside source control, and not the live database. As a developer, you don’t have to deal with writing cumbersome ALTER scripts. All you need to do is to mention the state you want your database to be, and SSDT takes care of the rest. It is intelligent enough to compare the modified state of the database in source control with the target database, and generate difference scripts to get both the environments in sync.

DACPAC Deployments

DACPAC is a self-contained deployment file which is used for deploying SQL Server objects to an instance of SQL Server. You can also think of DACPAC to be like a database snapshot file, which can serve as the in-memory representation of database objects and can be stored for maintaining version history.

When the DACPAC is deployed, it uses the information in the DACPAC file as the source database schema. It compares this with the defined target to generate an appropriate change script, which is then executed against the target to sync up both the environments.

Enhanced Code Editing Experience

SQL Server Object Explorer is a new window provided inside Visual Studio IDE, which provides developers with the same user experience as SQL Server Management Studio. The new Table Designer enables developers to see the design view and code view of your database objects in the same screen, which is very helpful. You have the option to either modify inside the code view or the design view, and irrespective of which view you make the change, the other view gets updated immediately.

Schema Comparison

SSDT provides the ability to identify differences between any 2 database models – which can be a combination of Offline Database Projects, Connected Database or Database Snapshot file.

SSDT generates a difference script which can be executed against the target, so that it matches the state of the source database. As part of the Schema Comparison functionality, it displays the list of objects which are different between the source and target database, and you have the option to include/exclude the objects before generating the change script.

Data Comparison

SSDT lets you analyze the data differences between source and target databases and generate a change script to update the target database to match with the source.

This functionality is very helpful when you want to sync up reference/ look up tables between different database environments.

Conclusion

Continuous Integration has become an important practice for modern application and database development projects. SQL Server Data Tools helps to transform traditional database development into a modern declarative model inside Visual Studio environment. Using SQL Server Data Tools you can put your database schema into source control. And once you do that, you can rest assured that you have taken that very important first step towards the DevOps journey.

References


Samir Behara

Samir Behara

Senior Developer at EBSCO Industries
Samir Behara has a Bachelor Degree in Computer Science and has been part of the IT Industry since he graduated in 2006. He has worked on large-scale enterprise applications involving complex business functions, web integration, and data management in various domains like Insurance, Manufacturing, and Publishing.Samir builds software solutions using cutting edge Microsoft technologies, primarily C# and SQL Server.

Amidst working in a wide range of technologies - both front end and back end, one thing which has not changed is his love for SQL Server. He is passionate about developing his SQL skills, contributing to the SQL Server community and keeping in pace with the new technologies and advancements. He is the organizer of the Steel City SQL Server Users Group – which is the Birmingham, AL chapter of PASS. He is the author of www.dotnetvibes.com

View all posts by Samir Behara
Samir Behara
SQL Database development

About Samir Behara

Samir Behara has a Bachelor Degree in Computer Science and has been part of the IT Industry since he graduated in 2006. He has worked on large-scale enterprise applications involving complex business functions, web integration, and data management in various domains like Insurance, Manufacturing, and Publishing. Samir builds software solutions using cutting edge Microsoft technologies, primarily C# and SQL Server. Amidst working in a wide range of technologies - both front end and back end, one thing which has not changed is his love for SQL Server. He is passionate about developing his SQL skills, contributing to the SQL Server community and keeping in pace with the new technologies and advancements. He is the organizer of the Steel City SQL Server Users Group – which is the Birmingham, AL chapter of PASS. He is the author of www.dotnetvibes.com View all posts by Samir Behara

2,407 Views
  • Hiram Fleitas León

    Catch: data compare doesn’t include all tables, tables must have a key ie PK in order to be able to compare yet it doesn’t show which tables didn’t qualify for compare so for failsafe recommend using a row count at the very least per table incase you’re trying to compare a whole db.

  • Brian Lockwood

    Thanks for the detailed comment. You should be an author! 😉