How to versioning SQL databases architecture using GitHub and Visual Studio

#sql #database #git #tutorial

This post was part of a series talking about How to create a good architecture for SQL Databases

How to versioning SQL databases architecture using GitHub and Visual Studio
How to organize SQL legacy Databases
How to debug SQL Databases
How to migrate SQL databases to a new version

There's a very recurrent problem in corporations and older T.I professionals that the business logic needs to be in the database, although this discussion is still debated and some people say that there are Benefits that must be considered.

The truth is, that over the years, many disadvantages are being discovered with this approach, and being exchanged for more robust solutions.

Common problems of leaving business logic in the database

A large portion of the corporations in which I acted, in fact, use this approach, and this irritated me at first, after all, there are several problems that I passed with this approach, such as:

Code versioning was almost nonexistent unless you have some commented code from a previous version
Debugging was impossible to be done, because the database does not come set up for you Debugar applications, and usually are heavy banks to replicate on the Local machine and "Debugger" Procedures and Jobs
Programming asynchronous, libraries and packages that could help us become unviable technologies in the project because we can not simply install these packages in the bank *Advantages of Object orientation, Solid, DDD, Microservices and Cloud Computing become expendable since we cannot work with classes, so SQL code duplications in queries are very common

These are some of the problems that we have when we use business logic in SQL, all this for having a small gain in "Performance", which can sometimes be invalid if the application does not respond to the queries created.

An Inconvenient Truth

The fact is that we can rarely escape this model, there are so many people conditioned with this approach, we have difficulty understanding how to solve these problems, because we often need to change the mindset of the biggest tiers gives the area of T.I to in fact have a significant change.

What we have left to at least suffer less in our work of having to deal with it, was creating a structure that enables a healthy evolution of the database and that can be easily replaced, and how to create a better structure is what we will assemble here now.

1- Versioning

The main problem that I see was how to create a versioning framework for Procedures and Jobs created. A Simple strategy that I used was to find more suitable software to work with programming in Fact, as I work with C#, we will use the Microsoft environment as an example.

You will need the following items:

Visual Studio (I'm using the 2017 community)
SqlServer installed with management Studio (I'm using SQL Server Express).
A GitHub account

SQL Management Studio vs. Visual Studio

SQL Management Studio is an excellent tool, if you want to use SQL commands on your database to run queries and commands, this is good for DBMS, if you need to correct, pick up or impute data, but when we are talking about programming in our Database, We have better options, follow the steps below to versioning your SQL database, and this will work with the AdventureWorks Database that is on Microsoft GitHub on this link If you want a more detailed tutorial on how to create a Database Project in Visual Studio, I used this link.

First Install the AdventureWorks OLTP database on your SQL server
Create A new database project in Visual Studio

Now, link your installed database with the project by right-clicking on the project>>Import>>Database.

Now The folders have been created from the base

For last, Just add your code to version control

Add the database in source code control
Now only give Commit of this and ready your project will be on GitHub with versioning control.

After all, your project is versioned, with history for when you change, now your projects will be versioned, DbMasters or programmers will have access to the project and can implement improvements in the project. Further forward we will see how create a good database structure to better separate the domain and have a better understanding of the bases.