Continuous Integration using SSDT in Visual Studio

By Samir Behara on January 2, 2016 • ( 0 )

What do you mean by Continuous Integration?

Continuous Integration is the practice of frequently integrating one’s code changes with the existing code repository. The primary goal of CI is to provide quick feedback, so that if a defect is introduced, it can be identified and fixed at the earliest. Hence we need to check in regularly, integrate our code early and often, so that we can minimize rework & save cost and time.

One of the foremost prerequisite for CI is to keep all the project artifacts under version control – not just the source code but also the database objects, tests, build and deployment scripts.

Problems with traditional Database Development —

.Net Code	Database Objects
Under Source Control	No Source Control
Versioning information is available.	No versioning information is available. Production is the version.
Code can be Unit Tested	Cannot be Unit Tested
Automated Deployments	No automated deployments

What is SSDT?

SQL Server Data Tools in Visual Studio transforms the traditional database development by allowing us to view, design, maintain and refactor database objects without having to jump from Visual Studio IDE to any other toolset like SSMS.

The database project is a special type of Visual Studio.NET project. Its purpose is to create and manage SQL database objects. It converts the database objects into text files, which can be searched, modified and stored in source control just like your code files.

Capabilities of SSDT —

Version Control	Puts the entire database schema under Source Control.
Deployment via a DACPAC	Dacpac is a single deployment file & is used for deploying SQL Server objects to an instance of SQL Server. When the DACPAC is deployed, it uses the information in the DACPAC file as the source database schema. It compares this source with the defined target to generate an appropriate change script.
Disconnected Development	Allows to maintain a local database and do all the development using that.We can merge to the target database, at a later point of time.
Refactoring	Increases developer productivity by providing lot of refactoring capabilities like renaming an object & moving to a different schema.
Unit Testing	Ability to write unit tests improves the quality of the SQL Server database applications.
Static Code Analysis	It warns about any anti-patterns in the database code.
Compile Time Error Checking	Assists in catching errors early by providing extensive error checking during build time.
Data Comparison	Analyzes data differences between two databases.
Schema Comparison	Provides the ability to identify differences between source and target – which can be a a combination of connected database, database project or dacpac file.
Integrated Development	Database Project resides in the same Visual Studio solution as the .Net projects.
Intellisense	Helps developer to write code by making suggestions, just like the .Net code.
Code Editing Experience	Ability to see the design view and the code view in a single screen is very helpful. There is no need to move across screens. The changes you do to the design view will be affected to the code immediately and vice versa.