A database schema is a set of objects such as tables, views, procedures, and functions update of which cannot be done without modifying data. dbForge Source Control allows linking and version-controlling database schemas and static table data in the Source Control Manager.
Static data is a set of fixed values that do not often change or cannot be changed by users. Transactional type data can reference this data. For example, static data can include reference data, code, lookup tables, configuration data, abbreviations for company departments, geographical locations, etc.
Since static data can influence the database performance and proper functioning, keeping track of what has been changed is a good practice. This allows developers to access, work, and share database objects with their static data within one tool. Version-controlling static data with Source Control also helps optimize database development.
Note
It is important to link static data to source control to prevent any data loss when updating your local database with the latest changes from the remote repository or reverting database changes made to the object and static data in a database.
Before you use the Source Control Manager, you must link a database to the version control repository by following the steps outlined in the relevant how-to topic:
The linked database gets the following icon in Object Explorer:
dbForge Source Control checks if there have been any changes made to the database locally or remotely and verifies whether these changes have been committed to the repository. In the same way, the tool checks if any changes from the version control have been deployed to the linked database.
In addition, the tool identifies conflicts that may occur if several developers are making changes to the same file both in the repository and the local database.
The Refresh progress window opens automatically showing the stages of the refresh operation.
After the refresh operation is complete, Source Control Manager opens displaying all the changes either in all three sections, two of them, or just one section.
When two or more users are making the changes to the same file, such as reference or contact data, a conflict occurs as a version source control system cannot determine whose changes to prioritize and marks them as a conflict. In such cases, users must resolve the conflict before committing the changes to the repository or updating the database with the latest changes from the remote repository.
You can view the conflicting changes in the Different category at the bottom panes: the left pane displays the local changes, while the right one shows the remote changes. To resolve the conflict, do either of the following:
If you want to update your local copy with the changes from the remote repository, select the static data and click Get Remote. The changes will then appear in the Remote changes section of Source Control Manager, and the change type will change from Conflict to Modify. Next, select the changes and click Get Latest as described in Get the latest version. This action will synchronize the local database with the database from the remote repository.
If you want to overwrite the remote repository data with local changes, select the static data and click Get Local. The changes will then appear in the Local changes section of Source Control Manager, and the change type will change from Conflict to Modify. Next, select the changes and click Commit as described in Commit changes. You can also roll back the local changes that have not been committed yet by clicking Undo.
Note
Conflicts can occur only in the Dedicated database development mode.
The Remote changes section displays the changes from the remote repository. Clicking Get Latest will update your local repository with these changes. For more information, see Get the latest version.
The Local changes section displays the changes made locally. You can select the static data, write a comment, and click Commit. This will update the linked database with the latest version of the database stored in the remote repository. For more information, see Commit changes.
Static data must be linked to version control before the deployment. To link static data:
1. In Object Explorer, right-click a database containing required tables with static data and select Source Control > Link / Unlink static data.
2. In the Source Control Link Static Data dialog that opens, select the required tables with static data and click Apply.
Note
You can link and source-control only tables with properly defined primary keys.
The Refresh dialog opens automatically showing the stages of the refresh operation.
4. Once the refresh is complete, Source Control Manager opens. Navigate to the Local Changes section and select the linked tables with static data. Then, write a comment and click Commit.
Note
Local changes section
- The Add change type indicates that the static data was created locally in the database that is being version-controlled using Source Control and has not been committed to the remote repository yet. Selecting the static data and clicking Undo will remove it from the local database.
- The Remove change type means that the static data was deleted from the local database that is being version-controlled using Source Control but still exists in the remote repository. Selecting the static data and clicking Undo will restore it in the local database.
- The Modify change type indicates that changes were made to the static data in the local database that is being version-controlled using Source Control. As a result, the DDL of the object with the static data in the local database differs from the version stored in the remote repository. Selecting the static data and clicking Undo will discard the changes made to the data in the local database, reverting it to the version from the repository.
Remote changes section
- The Add change type indicates that the static data has been added to the remote repository since the last synchronization and does not exist in the local database linked to this repository via Source Control. Clicking Get Latest will add the static data to the local database.
- The Remove change type means that the static data has been removed from the remote repository since the last synchronization but exists in the local database linked to this repository via Source Control. Clicking Get Latest will remove the static data from the local database.
- The Modify change type indicates that the static data has been modified in the remote repository since the last synchronization. As a result, the DDL of the object with the static data in the repository differs from the version stored in the local database linked to this repository via Source Control. Clicking Get Latest will update the static data in the local database to match the version from the repository.
All changes can be conveniently checked in the following tabs, as shown in the screenshot above:
Tab | Description |
---|---|
All | List of all records |
Added | List of records that will be added |
Changed | List of records that will be modified |
Removed | List of records that will be removed |
Identical | List of records that will remain identical |
Note
Source-controlling table data (including the Get Latest, Commit, and Refresh operations) may be time-consuming. Thus, it is highly recommended to source-control small amounts of data. Committing large static data tables with numerous records may slow down the performance of dbForge Source Control or cause a time-out.
It’s better to use dbForge Data Compare for SQL Server to compare and synchronize large amounts of static data. For more information, see Working with Scripts Folder and Static Data.
You can unlink static data in dbForge Source Control using the Link/Unlink Static Data or Unlink Database from Source Control options.
Link/Unlink Static Data
In Object Explorer, right-click the required database and select Source Control > Link/Unlink Static Data.
Clear the Table checkbox and click Apply.
Unlink Database from Source Control
In Object Explorer, right-click the required database and select Source Control > Unlink Database from Source Control.
For more information, refer to Unlink a database from dbForge Source Control.