Developing databases in a secure and efficient way is very important for each database developer in the team. To maintain and keep an eye on any changes made to the databases, resolve conflicts, and roll back to the earlier versions if needed, the good solution will be to use a version-controlling tool. It can also help developers to compare versions and identify what changes have been made and who is the author.
Developers can either work on the database development separately, having a local database copy (dedicated database development model), or share the same database copy (shared database development model).
In this article, we are going to look at the main differences between shared and dedicated database development models implemented with dbForge Source Control.
To begin with, we’ll figure out what a shared and dedicated database development model mean.
Shared Database Development Model
A shared development model allows every developer to work on the same database and repository that contains the most recent database version. This means that you will not face any conflicts and you do not have to get the latest version of the database each time you decide to commit the changes to the repository.
In this model, all your changes are applied directly to the database and after that, you can commit and push them to a repository. Therefore, be careful, if you are a person who last updated the database, your changes will overwrite the changes made by the other developer and vice versa. If someone commits the changes to a source control after you did, your changes will be overwritten.
If you want to test your changes before pushing them to a repository, this cannot be performed because they may impact others’ changes.
Dedicated Database Development Model
In a dedicated development model, developers work with their own or cloned copy of the database running locally. They can update the database copy with their changes and test them without worrying that this may break other developer’s code. Once the changes are committed and pushed to the remote repository, your team members can pull and apply them to their local or cloned database copies.
Unlike the shared development model, a local copy you have can disrupt you from overwriting the changes made by other developers. However, if your working copy differs from the latest one stored in the repository, you’ll receive conflicts after you get the most recent changes from the repository. Thus, to avoid any conflicts, you need to keep your copy updated and to fetch the latest version of the database from the repository prior to making any changes.
Now, let’s see how these models work in dbForge Source Control.
Implementing differences using dbForge Source Control
To get started, you need to download and install dbForge Source Control on your computer.
After that, in the Object Explorer of SSMS, right-click the database you want to link to a source control system, and click Source Control > Link to Source Control. For more information about how to link to different source control systems, refer to our Documentation Center.
Then, you need to connect to a source control repository. For this, click the plus sign in the Source control repository field, select a source control system, provide settings, and click Ok.
Now, it’s time to select a database development model and click Link. This will link the database to source control and add objects to the database copy. In the Object Explorer, a linked database will be displayed with a specific label.
Keep in mind that linking a database only connects a copy of your database to the repository. To be able to work with the database and make any changes, the person who links a database to the repository should make an initial commit.
When choosing a shared database development model, Source Control Manager will list all the local changes made to the database objects: added, removed, or modified. Clicking Refresh will update your local copy with the remote changes made by other developers.
After you finished making the changes, you can push them to a source control repository by performing one of the following steps:
- In the Source Control Manager, select the changes, write a comment, and click Commit.
- In Object Explorer, right-click the database and then click Source Control > Commit. By default, this will select all the local changes to commit. To commit the changes, add a comment to the text field and click Commit.
Once done, the repository will have all the changes made to the database objects. In case several developers have been working on the same object, no conflict will arise. Instead, in the shared database development model, the latest commit will overwrite the previous commit and those changes will be lost.
When working in the dedicated database development model, you will have a separate local copy of the database. Thus, prior to making changes to the database, you should update your local copy of the database and get the remote changes from the repository by selecting Remote changes and clicking Get latest in Source Control Manager.
Now, you can work with the local copy of the database, make the changes, test them and commit these changes to the source control repository so that your team members can apply them to their database local copies.
If anyone in your team has modified the object in their local copy of the database and committed the changes to the repository, you need to update your local copy to avoid any conflicts. For this, click Refresh. In the Source Control Manager, there will be a list of remote changes. If you want to check what exactly has been done in the object, click the object. The change will be highlighted in the code.
However, there may be situations when you and another developer have been working on the same database object and committed the changes to the repository. Unlike the shared database development model, you will get the conflict that can be resolved in two ways:
- Get Local. Selecting this option will apply your changes.
- Get Remote. Selecting this option will apply the changes made by another developer.
Conclusion
In this article, we have covered the differences between dedicated and shared database development models. As you can see, managing and developing databases is much easier with dbForge Source Control. You can boost database development, track the changes, and see what has been changed, by whom, and when, as well as resolve conflicts if any.
Depending on your project requirements, it is up to you to decide which database development model suits best to your team. Choosing a dedicated model will help you keep track and improve database development by testing the changes in your sandbox. This may reduce the errors and keep your data secure. Also, the dedicated development model can easily eliminate the risk of data loss and avoid cases when changes may be overwritten and the code may be broken. Still, with the shared development model, you don’t track the database copy – you will always have the updated copy of the database.
If you want to know more about the tool, refer to the dbForge Source Control page.
Want to evaluate how the tool works, download a trial version of dbForge Source Control!
