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 a 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 a 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 the rich set of database tools and features to complement 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 contain 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 in 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 the 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.
Samir is a frequent speaker at conferences such as PASS Summit, IT/Dec Connections, CodeStock, SQL Saturdays and CodeCamps. He is the Co-Chapter Lead of the Steel City SQL Server UserGroup, Birmingham, AL. He is the author of www.dotnetvibes.com
View all posts by Samir Behara
- What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more - December 26, 2017
- Review of SQL Cop for SQL unit testing - September 29, 2017
- Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461 - September 8, 2017