Database development works best when each developer has a dedicated development database

While it is possible to use Redgate Change Control with a single development database that is shared by multiple users, Redgate recommends that you provision a dedicated development database for each user. 

Redgate has found that shared database development environments increase human error, limit the ability of team members to experiment, and curtail testing of features early in the development process. 

Why Redgate recommends dedicated development database environments

Distributed version control systems, such as Git, cause human error when used 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 periodically synced to and from the centralized repo, and users commonly work in dedicated branches to develop new changes.

This immediately introduces problems when teams utilize a shared development database: the database is shared, but local version control is not shared.

This misalignment makes it unclear which changes in the development database have been committed to the repo, by whom, and in which branch at any given time. This lack of clarity makes it easy for a developer to accidentally overwrite someone else's changes or to erroneously include someone else's work in progress in their own commits. 

By contrast, dedicated development database environments align well with distributed version control systems in an intuitive way. 

Dedicated development databases create a safe place for experimentation 

Users who have a private development environment are free to experiment with any schema or data changes necessary to design or test a database change. 

By contrast, users who share a single development database must be very careful not to impact other users. Any changes to schema or data in the environment could delay another user, cause another user to see unexpected results, or cause confusion. 

The ability to experiment and to make any changes necessary to test a change help developers make the right changes and identify any problems early.

Dedicated development databases promote good habits in software development 

Users who have a private development environment must share changes with one another by frequently committing those changes to version control. 

By contrast, users with a shared development environment often rely on the ability to share changes through the database itself and refrain from checking in those changes for longer periods. This results in a buildup of uncommitted changes in the development database. It's easy to lose track of the status of these changes, to not remember who made what change and why, to accidentally overwrite these changes, and even to accidentally commit these changes when it is not necessary. If the shared development database is corrupted or accidentally dropped, these changes may be lost altogether. 

Dedicated development databases allow higher permissions for developers, which builds knowledge and allows better troubleshooting 

Users who have a private development environment are often granted higher permissions on this environment, as it is only for their use. These higher permissions allow the users to develop more expertise in the database technology they are using and to develop their troubleshooting skills when problems arise. 

By contrast, users in shared databases are often restricted to more limited permissions because of the risks of accidentally impeding the work of other users. This disempowers these users and limits their long-term knowledge of the database technology. 

Simplified provisioning of dedicated development databases with Spawn

Redgate's Spawn is a database provisioning tool that lets you create data containers in seconds, simplifying the process of creating and resetting database development and testing environments. 

Reducing costs for development databases when using Platform as a Service (PaaS) databases 

If you choose to use a database hosted by your cloud provider for your development and shadow databases, you may choose to use some of your cloud provider's options to reduce costs for these environments.

For example, if you are using Azure SQL Database, you may use the serverless compute tier to reduce costs for the environment when not in use. Please note that the serverless compute tier auto-pauses databases when note in use. Connections which trigger the database to resume return an error stating that the database is unavailable with error code 40613. If you are using the serverless compute tier, you will see this error bubble up in Redgate Change Control when you trigger auto-resuming. Re-try your action following the error. 

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

Recommendations if you must use a shared development database environment

If you are unable to provision dedicated development environments or to utilize Spawn for database provisioning, Redgate Change Control can be used with a shared development database.  

Please note that: 

• We recommend that you leave the Repeatable Migrations feature disabled in the project if you utilize any form of branching in your version control system
• Each person generating migrations must have their own shadow database or shadow schema