At Credera we find database lifecycle management is the biggest hurdle to continuous integration and continuous deployment. Teams often implement processes and tools to ease the deployment of their application source code, but deploying the database tier remains a painfully manual process.
Microsoft’s DevOps stack enables collaboration between teams and team members to build the right thing, at the right time, in the right way; that includes our database code! One of the features of the Microsoft development stack is the little-known gem called Visual Studio Database Projects.
Visual Studio Database Projects is a project template that enables developers to represent their SQL server database objects as text files. That’s it! That’s the big secret. What you get, however, is a heck of a lot more than you might expect.
Start tracking changes to your database by adding SQL objects to Source Control.
You can version your application code and schema at the same time (support for branching and merging too!).
Team members can work in isolation:
Development teams no longer require a shared development database.
No one sees a check-in until they do a “get latest.”
Database code participates in the same lifecycle as the rest of the code:
Full support for Team Foundation Server’s code review workflow.
When we do continuous integration we can build and unit test our database.
Using DACPAC files, we can create deployment artifacts to automate the deployment process.
Visual Studio cross-references your SQL objects to provide IntelliSense.
Compile-time support for schema validation.
Includes a Schema Comparison tool.
For starters, I’d like to showcase just how easy it is to get started using Database Projects for the solutions you’re working on today. For reference sake, I’m going to use the Adventure Works sample database provided by Microsoft.
The first step to building a database project from an existing database is to create a new project (to access the correct project template, you will need to install the SQL Server Data Tools). Once the data tools are installed, create the project as you would any other Visual Studio project.
Go to File -> New -> New Project
After the project has been created, your Solution Explorer should look something like this:
For the next step, right-click on the AdventureWorks database project and you’ll see the option to Import from an existing database.
This will open the Import Database wizard.
Click the “Select Connection…” button to create a connection to your database.
Click “Start” to begin the import process and you can watch as Visual Studio busily updates your blank database project with all of tables, views, stored procedures, and functions in the database you selected.
Click Finish to close the dialog.
Now that I’ve imported all of the objects from my existing database, I can right-click the solution file and add the project to Source Control to begin keeping track of changes made to my database.
Now that I have my database structure and code committed to a source repository I get several benefits:
I am not at risk of losing code.
I am provided tools to manage merging changes with other developers.
I have established the beginnings of true traceability from user stories and bugs to the code that resolves the request.
This is the first step in evolving mature database lifecycle management practices. If you are facing the challenge of modernizing long-standing processes around monolithic database applications, we recommend approaching the changes one step at a time, starting here. First comes database source control, next comes world domination… or at least developer sandboxes.
–
Transform your business operations with our Microsoft solutions
Explore Our Microsoft Consulting Services →
–
Contact Us
Ready to achieve your vision? We're here to help.
We'd love to start a conversation. Fill out the form and we'll connect you with the right person.
Searching for a new career?
View job openings
