Working with a database under source control has many benefits. Beside tracking all the changes made against a database, including the information what the changes were and who made them, you can also track history of committed versions of all database objects which can be restored on a database at any point.
However, one of the benefits which will be explained in this article is comparing and syncing two copies of the same database. Using ApexSQL Source Control, all objects that are missing in one copy of a database can be created/restored in the second copy of a database, preserving the integrity of both databases.
In a real-life scenario, when we have a team of developers making changes against a database, there must be some sort of mechanism that will preserve the correct order of updating SQL database objects when they are pulled from the repository and executed against a new database. If the correct order is not followed, errors caused by object dependencies will likely be encountered, causing the script to fail.
Challenge
The challenge is to have a mechanism which will check all dependencies for a database object that needs to be updated, and provide the list of all dependent objects that need to be updated. Additionally, when updating a database object (or a group of objects), we need to know the update order so we do not break any of the dependencies between the objects, in which case the updating of a database will fail. Ideally, the scripts will be automatically processed in that order, every time they are pulled from the repository.
Pre-condition
For the purpose of this article, we linked a database to source control using ApexSQL Source Control, SQL Server Management Studio add-in that natively supports source control systems like Git, Subversion, Team Foundation Server, Mercurial and Perforce.
The database (called Dev_DB in our case) is linked using the Dedicated model, to a Subversion repository and initial commit is already performed. Static data for all the tables is committed to the repository, as well. Additionally, we committed two SQL scripts that will be executed against the database, after the update process is finished. One of them will create a new table and insert data in that table, which will be dependent on the Person.Person table and another script will count all the row entries for all the tables present in the database.
From another machine (where we have a copy of the Dev_DB database, called Dev_DB1), we linked the Dev_DB1 database to the same repository. At this point, the databases are compared and synced, since there were no changes since the initial commit is performed.
Solution
Let’s now make some changes in the Dev_DB database.
We added a new column to the Person.BusinessEntity table. We also added a new column to the Person.Person table, which is dependent on the Person.BusinessEntity table. And we commit all changes, for both tables to the repository.
Now, we will update the Dev_DB1 database. Start the Action center tab for the Dev_DB1 database. After the comparison step between the database and the repository is done, all differences will be listed.
In our case, we altered only two tables, and we know dependencies exist between them, so we know that first we must update the schema for the Person.BusinessEntity table and then the Person.Person table, if we want to update Dev_DB1 database without errors. Helpfully, ApexSQL Source Control has orderd the objects in the proper execution sequence to avoid dependency errors. As long as we update all objects as part of a single batch, or execute them individually, but in the correct order, we’ll be good
To select the objects to participate in the update, just make sure they are checked. One, more or all objects can be processed, in this way
By parsing the database using a proprietary algorithm, ApexSQL Source Control can order the objects in the proper sequence to avoid dependency errors. This feature only works though, if all of the objects are processed at once or if they are processed individually, done in the order suggested by the software
But if we choose to update the schema of the Person.Person table before we updated the Person. BusinessEntity schema table, the following warning will be shown under the Warnings tab of the Get changes from the repository window:
In order to avoid dependency related errors, in case altering a number of table schemas, is always the best practice to update all of them in one commit to a database. ApexSQL Source control checks dependencies, sequences the objects/data properly and updates the database objects in the right order, avoiding dependency errors.
After the complete update of the database, scripts execution will go without any problems.
December 7, 2017
