- How To Use Ssdt In Visual Studio
- Ssdt In Visual Studio
- Ssdt In Visual Studio 2012
- How To Install Ssdt In Visual Studio 2015
- Ssdt In Visual Studio 2013
By: Haroon Ashraf | Updated: 2020-10-19 | Comments | Related: More >Database Design
Problem
As an entry level database developer, I would like to create and manage SQL databasesusing a simplified source control approach especially when working as a solo developer.
How To Use Ssdt In Visual Studio
Solution
The solution is to start your development journey with SQL Database Project usingSQL Server Data Tools (SSDT) for Visual Studio along with using Git source controlcentralized workflow.
Centralized Workflow and SQL Database Project
This tip is primarily focused on using SQL Database Project to build and managedatabases and a simpler approach to put your database code under source controlprovided the developer is familiar with database development and source controlconcepts.
Visual Studio 2017 has several features specific to database development to keep developers more productive, while ensuring that application’s data and schemas are part of the DevOps best practices (automatic deployment, continuous integration). Developers can leverage SQL Server Data Tools (SSDT) included in all Visual Studio editions. Visual Studio / SSDT. So as well as handling larger projects it is faster as well, a small project (one proc/table) was about 17 seconds to build. Task 1: Add SQL Server Database Tools (SSDT) to Visual Studio Include the Data storage and processing workload to add the SQL Server Database Tools features to your Visual Studio installation. If the Data storage and processing workload is already installed in Visual Studio, skip this section. To install SSDT during Visual Studio installation, select the Data storage and processing workload, and then select SQL Server Data Tools. If Visual Studio is already installed, use the Visual Studio Installer to modify the installed workloads to include SSDT. Launch the Visual Studio Installer.
Ssdt In Visual Studio
There are many ways to create SQL databases and using SQL Server Data Tools (SSDT)is one of them. Similarly, there are many patterns which can be used with Git sourcecontrol and centralized workflow is one of them. Let us get some more understandingof this workflow before we implement it in a walkthrough.
What is a Centralized Workflow?
A centralized workflow is a simple way of saving database code changes to a centralrepository which serves as single source of truth.
Why we use Centralized Workflow?
A centralized workflow is easy to use and manage and often preferable for theprojects migrated from a centralized source control system to Git until thedevelopment team behind the projects is ready to utilize the full potential ofGit source control.
When is Centralized Workflow most Preferable?
When a single developer is working on a database project then centralized workflowis a safe and flexible option with some exceptions.
However, that does not mean a team of 2-3 developers cannot use it, but they haveto be aware of the pros and cons.
How is Centralized Workflow Implemented?
A centralized workflow is implemented by using a single branch of code untilcompletion. Since only one developer or a very small team of 2-3 developers workon a single branch of code it is relatively easier to avoid conflicts when releasingthe final code.
For example, when you connect your SQL database project to an Azure DevOps Gitrepository you start working on a local version of master branch which is used tosave the very first changes along with any new changes to the database project tothe remote master branch until the project is complete.
In other words, you work on a local version of the master branch and you saveyour changes to the remote version and the story continues.
Is Centralized Workflow the way forward?
We can say, No. Centralize workflow is not the best way forward, but an excellent sourcecontrol approach to begin with until you are fully ready to leverage the fullyfeatured Git sourcecontrol.
On the flip side we can say, Yes. It can be the way forward if a team is comfortablewith this approach, but they may have to understand the caveats (precautions) thatcome up with this approach. It also depends on their willingness to consider the flexibility and benefits of other approaches fully supported by Git.
At the end of the day it depends on many factors including the development teamitself, their level of expertise, the business requirements and preferences.
Using SQL Database Project with Centralized Workflow
Let us get hands-on experience of working with centralized workflow to save databaseproject changes.
Pre-Requisites
This tip assumes that the readers are interested in following the walkthrough are familiarwith the following technologies:
- Azure DevOps
- SQL Database Project
- Git source control
Create Azure DevOps Project
Pleasesignup to create a new Azure DevOps account if you don’t have one and areinterested to implement this example.
Please sign in to your onlineAzureDevOps account and create Azure DevOps project named SQL Database Projectwith Centralized Workflow:
Rename Repository
Please navigate to the Repository section of Azure DevOps and rename the current repositoryas sql-centralized-workflow:
Now if we click on Branches under Repos fromthe left navigation bar, we need to select the option Switch to the defaultsql-centralized-workflow repository to switch to the repository which hasjust been renamed:
Create Local Copy of the Repository
Next, you have to create a local copy of the repository to start working locally byclicking the following options:
Please follow the instructions to complete the process:
Connect to the Repository
Please click on the sql-centralized-workflow repository in thestatus bar in Visual Studio as follows:
Right click on the repository and click Connect as shown below:
Things before creating a new SQL Database Project
Once you are connected to the repository this means you are now using a local copyof the master branch either to create a dev branch out of it or straight away startworking on the master branch.
In a centralized workflow you are going to carry on with master branch providedyou know that all the database changes are directly going to be pushed to the onlymain steam branch which is responsible for development, testing and release.This may look like a code safety hazard, but it is fine to do so especially when you arethe only developer working on this project and aware of the merits and demeritsof this approach.
Create a new SQL Database Project
Time to create a new SQL database project so please click New…under solutions:
Please create a new SQL database project named WatchesCW undera solution called Centralized Workflow and click on the Create button:
Please switch to the Solution Explorer to the see the newly created SQL DatabaseProject:
Add first database object
We need to add a database object to the project now.
Right click on the project (WatchesCW) and then click Add followedby clicking Table… as shown below:
Create a new table called Customer leaving it to be createdwith default settings which means a primary key column ID is alsogoing to be there:
Commit (Save) Changes to the Git Source Control
We have just created the first database object so please Build the project byclicking F5 to get the green signal (and keep it a habit) before saving your changesto the source control:
Next, click the pen icon in the status bar or use the shortcut key CTRL+ALT+F7to open Changes section.
Add the comments 'Customer table added' and select Commit All and Pushto save the changes to the remote repository:
Once all the changes are successfully saved, the Team Explorer window shouldbe showing the following messages:
Add second database object
Let us now add another table called Supplier using the defaultsettings as shown below:
Commit (Save) Second Database Object to the Git Source Control
Please push the new database code changes just like the way we did earlier addingthe comments 'Supplier table added' and selecting 'CommitAll and Push':
If we switch to the Solution Explorer, we can see our database solution with bothtables and padlock icons next to them showing they are protected by the source control:
Git Repository Check
Let us now navigate to the Azure DevOps project we created earlier to see howthese changes are maintained by the Git source control.
Go to Repos > Pushes from the left navigation bar to see all the changes wesaved directly to the master branch in a historical order as shown below:
Ssdt In Visual Studio 2012
Similarly go to Commits under Repos to viewthe Graph of changes (commits) which is simple and straight without any complications:
How To Install Ssdt In Visual Studio 2015
Finally, please navigate to the Files under Reposto view the complete database code:
Congratulations, you have successfully implemented centralized workflow usingGit source control for SQL Database Projects. This is an excellent approach to beginwith and to be carried on as long as your requirements are simple and linear (oneafter the other) and there are no chances of team conflicts because eitheryou are the only developer working on these projects (using centralized workflow)or (if working in a team) your team is aware of the pros and cons of this approachand are happy to continue with it.
Next Steps
- Please go through the tipCreating Multiple SQL Server Database Backup Formats in SSDT - Part 1 toadd the Watch tablefollowed by adding its reference data in two different steps where each stepmust be committed to the Git source control using centralized workflow as discussedin this tip
- Please have a look at the tipManage Multiple Database Backup Formats in SQL Server Data Tools (SSDT) - Part2 and try adding WatchOrdertable followed by adding its reference data in stages by committingall the code of each stage into the Git source control using centralized workflow
- Please have a read throughSQL Database Project Code Branching in Git to broaden your knowledgeof working with SQL Database Projects using Git source control
Last Updated: 2020-10-19
Ssdt In Visual Studio 2013
About the author
Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).
View all my tips