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.

Reducing costs for development databases when using Azure SQL Database

If you are using Azure SQL Database for your development database or shadow database, you may use the serverless compute tier to reduce costs for the environment when not in use.

Another option to reduce costs when using Azure SQL Database for development or shadow databases is to use an elastic pool. 

Recommendations if you choose to share a development database with SQL Change Automation

If you choose to use SQL Change Automation with a shared development 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). 

Please note that each user will still need to have their own shadow database. Shadow databases may be deleted when they are not in use.