Development database environment configuration
Published 04 June 2021
Note: It is possible to set a SQL Source Control project as the development source for SQL Change Automation. In that case, a development database is not required for SQL Change Automation itself.
SQL Change Automation works best when each developer has a dedicated development database
While it is possible to use SQL Change Automation with a development database that is shared by multiple developers, this quickly can introduce complexity in processes. Depending on how you use version control, you may wish to change some default behaviors of SQL Change Automation.
Distributed version control systems, such as Git, don't mix well with shared databases
The most popular version control system (VCS) in use is Git. Git is a distributed version control system, meaning that users clone down a local, private copy of the repository in which to do work.
When working with Git, committed changes are only periodically synced from the centralized repo.
This immediately introduces challenges when teams utilize a shared development database. It is unclear which changes in the development database have been committed to the repo, and by who, at any given time. This makes it easy for a developer to accidentally overwrite or add to work that is uncommitted, and even to erroneously include someone else's work in progress in their own commits.
More downsides of shared development databases
Troy Hunt wrote an excellent article in 2011, "The unnecessary evil of the shared development database." The problems cited in this article remain true.
One thing that has changed since the publication of this piece is that Microsoft SQL Server Developer Edition is now free.
Options for dedicated development databases
SQL Clone is a database provisioning tool that lets you create full copies of SQL Server databases and backups in seconds, using around 40 MB of disk space per clone.
SQL Change Automation fully supports SQL Clone as a development database, and additionally integrates with SQL Clone to quickly and efficiently baseline a database.
Recommendations if you choose to share a development database with SQL Change Automation
If you choose to use SQL Change Automation with a shared database and plan to use Git, we recommend that you:
- Disable the Programmable Objects feature in SQL Change Automation. This can be done by modifying the SQL Change Automation project settings file and adjusting the ProgrammableObjectHandling setting to ScriptInMigrations.
- When making this change, you may also wish to ensure that the SyncToOfflineSchemaModelObjectTypes is set to include programmable objects in the offline schema model (example code).