We all make mistake and we all wish that we have not made those mistakes. In the field of the development, there are proper solutions, but in the world of SQL, there are not many solutions. I recently asked the same question to my friend Andrey from Devart and he has provided me a wonderful blog post about how one can do dedicated database development with SQL Source Control.
Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server, speaks at SQL Saturdays, contributes to open source projects and writes his own blog: codingsight.com
It is a good practice for database developers to have their own “sandbox” environment, rather than having everyone work in a shared environment. Thus, developers can work separately, unaffected by the changes made by other team members. Once a developer completes and tests a change, they commit the change to VCS and it becomes available to other developers.
In this article, we will discuss how to build such workflow with help of dbForge Source Control for SQL Server.
Database development models
There are two common approaches for teams to develop databases. However, some teams may use combinations of the two.
Dedicated
Developers work with their own copy of the database. The copy might be located on a local PC or on a central server. All developers make changes independently, then test those changes and commit to a source control. As soon as changes are committed, other team members can apply them to their working copies.
Each developer works in their “sandbox”, thus there are no risks to overwrite someone else’s changes.
Shared
Developers share a single copy of the database. All changes are made directly to the database. That is why there is no need to care about the state of local working copy. This approach is not too safe, because one developer may overwrite others’ changes.
Setting up SQL Source Control for dedicated development
- Install dbForge Source Control for SQL Server
Download and install the tool. Once it is installed, you will see the Source Control Tasks shortcut menu inside SSMS Object Explorer.
- Create version control repository
The tool supports the most popular source control systems: SVN, GIT, Mercurial, TFS and many others. For the demonstration purposes, Visual SVN will be used. You can use one of your favorite source control systems. At this step, you need to set up your source control system and the repository. You can place a database script folder inside the repository or just leave the repository blank. In this particular demo, the repository contains the database script folder. The folder contains DDL scripts for the sales_demo1 database objects.
- Link a database to the source control
To link a database to the source control, you need to create a new database in SSMS. Right-click the database and navigate to the Source Control Tasks shortcut menu. Then, click Link Database to Source Control.
The next step is to set up the connection to the repository. Click the Source control repository text box and then click Manage.
Select a required source control system. It is SVN in our case. Provide the URL to the repository. Click OK.
There it is! Note the database icon has changed in the Object Explorer. Also, it contains all required objects.
- Get latest
The tool automatically detects that there are some changes in the remote repository. To get the remote changes, select all required checkboxes and click Get Latest.
The process will take a while. Once it is completed, you get the following report:
Now, just refresh the sales_demo1 database in SSMS object explorer. You will see that the database contains all required objects.
- Working with changes
Assume someone from the team has changed an object in their local sandbox and committed the change into the repository. You need to get the update from the repository. To get remote changes, just refresh the source control tool. The tool automatically detects the remote modifications and provides you a list of all changes. You can see which lines of code have changed. If you agree to the changes, select them and click Get Latest!
All changes will be applied to your local copy of the database.
As you can see, it is very simple to manage database changes with dbForge Source Control for SQL Server. Most database developers, however, relying on a shared database development model. Nevertheless, changes made by other developers can break the code and affect the entire project, causing delays and frustration. Even so, most developers would likely choose the dedicated model.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
what about scripting to release in prod?
what about scripting default values in some tables?
what about preventing the loss of data? (when a column name has been renamed)
what about partitioned tables? (when each dedicated database can have a different number of partition in place)
With this tool, you can work with SQL source code and a version control system as programmers work with their program code. You can commit, update, merge SQL code without any needs to generate object DDL manually each time. The tool automatically compare the code inside a pero and the DDL on your local instance and provides you the picture of what is going on. Your task is to decide what to do.
I understand, its like the Visual Studio Database tool. but how this tool manages the data movement? is there a pre deployment script? how to package a database for a release to other servers? (generally UAT, QA, PROD servers are not connected to a source control system)
can we automate the deployment through Powershell scripts?
Or, apply changes from a schema compare between the versionned DB project and your local sandbox. It works well and it is free!
Sorry to disappoint you, but this tool is not free, from which I saw. The initial price is 250$, and it is for one year subscription. After that next year would be ~100$. Is there any alternative to this, as free (as free beer) / opensource application?