One of the most important things for a DBA when it comes to managing databases is schema change auditing. This involves tracking changes over time, identifying differences between two versions and even rolling back changes to a previous baseline
If only schema changes are made in the source control repository, a 3rd party tool – ApexSQL Diff can be used to perform the schema synchronization. ApexSQL Diff is a tool that can be used to compare and synchronize schema differences between live SQL database, source control repositories, database backups, script folders and ApexSQL snapshots. It can handle any dependencies, triggers, etc. and create an error-free synchronization script, while ensuring a database integrity.
Once you have your continuous integration (CI) and or continuous delivery (CD) aka Database lifecycle management process built, configured, successfully testing and ready to roll, the next decision point is when to run it and how.
As a DBA considering version controlling a database, there are a lot of challenges to setting up the team with software to check in changes, to manage the development environment (shared or dedicated models) and set the rules of the game – locking vs not locking etc. But before committing to this fully, a potentially preliminary stage would be to set up a centralized system, where the database was automatically committed to source control each night. This could be done by a single person and wouldn’t require developer participation or even knowledge.
When working on a SQL database development, there is sometimes a requirement to keep two databases in sync. For example, you have a development environment and want to automatically synchronize changes with a QA database frequently, so that tests can always be run on the most recent version. The implemented mechanism should handle this by detecting a specific schema change in the DEV database and automatically synchronizing with the QA database, fully unattended and on a schedule. The whole process should be run unattended and to be fully automated and the databases will be updated in near real time as we’ll schedule the process to run every 15 minutes
For the purpose of versioning a SQL database, there should be a mechanism that allows for quickly linking a database to an existing or to a blank repository and to initially Commit all database objects, or at least a set of objects that needs to be versioned.
One of the challenges these days is how to pull the latest changes (of SQL objects) from the source control repository and deploy them into a SQL database. This process is particularly helpful in the CI workflow Build step, when developers want to build a SQL database from the committed changes in the source control repository, so they can test if their changes compromised SQL objects from the source control repository or they will be built successfully.
A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.