The following provides some background information on database development environments and discusses the two models of database development: Shared or Dedicated. We then give recommendations on how SQL Source Control fits into database development.
Background Information
Database development should have multiple environments where changes can be developed without impacting the live/production application and then pushed to this environment at off-peak times so there is minimal disruption. Ideally, there are at least 3 environments: Development, Test, and Production. You may call these environments slightly different, e.g., Test aka QA or Production aka Live, and you may even have more, e.g., CI before Test, Staging before Production.
In the development environment, there are also two different approaches to database development:
1) Shared Model – All developers connect to the same database server (hopefully using their own credentials) and develop directly against the same development database.
2) Dedicated Model – Each developer has their own copy of the development database and makes changes independently. Their own copy of the development database can reside on their own database server (this could be local to their development machine) or could be on the same centralized development database server. Changes need to be shared between developers and may even be integrated into another database for testing.
Recommendations – Dedicated Development Databases for each Developer
It is recommended that the Dedicated Model is used where each developer has their own copy of the database to work against. This is exactly how source control works with application development; each developers gets their own local working copy of the code base, makes changes independently, tests these changes, and then commits them back to source control once they are confident in their change. With SQL Source Control, your copy of the database is the “local working copy.â€
Using a Dedicated Model is recommended because you are working in a completely isolated environment. You don’t have to worry about overwriting someone else’s changes or anyone else overwriting your changes. You are also free to develop complex changes that may cause other parts of the database or the application to break. If something does break, then you are not impacting any other developers. You can also easily undo your uncommitted changes and start over.
Before SQL Source Control, the dedicated model was difficult because it took a lot of time and coordination to keep track of all the changes and share them with other database developers. SQL Source Control makes this easy because all developers link their dedicated database to the same centralized source control server. Once a change is committed, any developer can pull this change into their database from the Get Latest tab within SQL Server Management Studio. SQL Source Control also provides a view of the changes made, so these can be reviewed before you update your dedicated copy of the database.
Deploying changes to other environments (Dev > Test > Production)
We recommend deploying your changes from the source control version using SQL Compare Pro. This will force database developers to commit their changes to source control; otherwise their changes will not get pushed.
SQL Source Control stores the CREATE script for each object in source control. This gives you a history of how the object has changed overtime. SQL Compare Pro can then be used to compare this version to your target database and create a synchronization script, which automatically generates all the alter scripts in the correct order to update the target database to match the version in source control.
There are three steps to deploy your database changes:
1) Get the version you want to deploy from your source control system to a local working folder.*
Note: You may want to tag/label the version at this point, so it’s easy to get back to a “deployed†version. Later, you could use SQL Compare Pro to re-generate the synchronization script between version x and version y.
2) Use SQL Compare Pro to compare the scripts folder to your target database.
3) Walk through the Synchronization Wizard to update the target directly using SQL Compare or create a synchronization script that you can review and then run against the target server to push the changes.
This entire process can be automated, which is especially useful in a Continuous Integration environment so that your tests can run against the latest version of the application and the database.
*This feature will be added to SQL Compare in a future release.
Shared Model
SQL Source Control will also work on a Shared Model where all the developers connect to and develop against the same development database. There are a few problems when using SQL Source Control in a shared model:
1) You may commit others’ changes.
On the Commit tab, you will see all changes to the database that are not in source control. The commit list is not limited to just your own changes. Since all changes are selected by default in the commit list, please be careful to unselect objects that you did not change and don’t want to commit. Someone else may still be in the middle of making changes and this should not be committed to source control yet.
Hint: If you only have one object that you changed and want to commit just this object, then right-click on that object in the Object Explorer and select commit. This will only select that specific object in the commit list and then you don’t have to worry about unselecting all the other objects.
2) You may not see your changes on the Commit tab.
This is because of the problem above. If someone else committed your changes, then you may not see anything on the Commit tab when you expect to.
3) You may see conflicts, which you should just ignore for now.Conflicts do not make sense in a Shared Model. A conflict occurs in the Dedicated Model when a developer tries to commit a change to an object, but didn’t have the latest version of the object to begin with. Developers need to review the object to see what the latest version should be. It could already be correct in source control, or your database version might be correct, or you may need to have a combination of both changes.
In a Shared Model, you don’t need to worry about conflicts, but you may still see them. This happens because SQL Source Control maintains a local working folder for each developer and this working folder may get out of synch if you don’t visit the commit tab frequently. Once the developer who is working on the object commits it to source control and you visit the commit tab, then you will no longer see the conflict.
4) You may undo others’ changes.
The undo feature allows you to undo any changes made to the database that are not in source control. If you undo someone elses changes, you may actually revert the object back to a really early version depending on when you last committed to source control. This could be confusing to the user that is currently working on that object. Be careful to only undo the objects that you are working on.
We hope to improve these areas in a future release, but still recommend switching to a Dedicated Model.
SQL Source Control should not be used on your Production Database
We do NOT recommend using SQL Source Control on your production databases for two reasons:
1) SQL Source Control adds some overhead to your database server while it polls for changes.
2) You should never make changes directly to your production database anyway.
Changes should be done on development, tested and then pushed to production. SQL Source Control will help you track all the changes to your database so you can see who changed what, when and why.
If anyone has any concerns about switching from a shared model to a dedicated model, please post here or contact us at support@red-gate.com. We would really like to discuss these concerns.
Background Information
Database development should have multiple environments where changes can be developed without impacting the live/production application and then pushed to this environment at off-peak times so there is minimal disruption. Ideally, there are at least 3 environments: Development, Test, and Production. You may call these environments slightly different, e.g., Test aka QA or Production aka Live, and you may even have more, e.g., CI before Test, Staging before Production.
In the development environment, there are also two different approaches to database development:
1) Shared Model – All developers connect to the same database server (hopefully using their own credentials) and develop directly against the same development database.
2) Dedicated Model – Each developer has their own copy of the development database and makes changes independently. Their own copy of the development database can reside on their own database server (this could be local to their development machine) or could be on the same centralized development database server. Changes need to be shared between developers and may even be integrated into another database for testing.
Recommendations – Dedicated Development Databases for each Developer
It is recommended that the Dedicated Model is used where each developer has their own copy of the database to work against. This is exactly how source control works with application development; each developers gets their own local working copy of the code base, makes changes independently, tests these changes, and then commits them back to source control once they are confident in their change. With SQL Source Control, your copy of the database is the “local working copy.â€
Using a Dedicated Model is recommended because you are working in a completely isolated environment. You don’t have to worry about overwriting someone else’s changes or anyone else overwriting your changes. You are also free to develop complex changes that may cause other parts of the database or the application to break. If something does break, then you are not impacting any other developers. You can also easily undo your uncommitted changes and start over.
Before SQL Source Control, the dedicated model was difficult because it took a lot of time and coordination to keep track of all the changes and share them with other database developers. SQL Source Control makes this easy because all developers link their dedicated database to the same centralized source control server. Once a change is committed, any developer can pull this change into their database from the Get Latest tab within SQL Server Management Studio. SQL Source Control also provides a view of the changes made, so these can be reviewed before you update your dedicated copy of the database.
Deploying changes to other environments (Dev > Test > Production)
We recommend deploying your changes from the source control version using SQL Compare Pro. This will force database developers to commit their changes to source control; otherwise their changes will not get pushed.
SQL Source Control stores the CREATE script for each object in source control. This gives you a history of how the object has changed overtime. SQL Compare Pro can then be used to compare this version to your target database and create a synchronization script, which automatically generates all the alter scripts in the correct order to update the target database to match the version in source control.
There are three steps to deploy your database changes:
1) Get the version you want to deploy from your source control system to a local working folder.*
Note: You may want to tag/label the version at this point, so it’s easy to get back to a “deployed†version. Later, you could use SQL Compare Pro to re-generate the synchronization script between version x and version y.
2) Use SQL Compare Pro to compare the scripts folder to your target database.
3) Walk through the Synchronization Wizard to update the target directly using SQL Compare or create a synchronization script that you can review and then run against the target server to push the changes.
This entire process can be automated, which is especially useful in a Continuous Integration environment so that your tests can run against the latest version of the application and the database.
*This feature will be added to SQL Compare in a future release.
Shared Model
SQL Source Control will also work on a Shared Model where all the developers connect to and develop against the same development database. There are a few problems when using SQL Source Control in a shared model:
1) You may commit others’ changes.
On the Commit tab, you will see all changes to the database that are not in source control. The commit list is not limited to just your own changes. Since all changes are selected by default in the commit list, please be careful to unselect objects that you did not change and don’t want to commit. Someone else may still be in the middle of making changes and this should not be committed to source control yet.
Hint: If you only have one object that you changed and want to commit just this object, then right-click on that object in the Object Explorer and select commit. This will only select that specific object in the commit list and then you don’t have to worry about unselecting all the other objects.
2) You may not see your changes on the Commit tab.
This is because of the problem above. If someone else committed your changes, then you may not see anything on the Commit tab when you expect to.
3) You may see conflicts, which you should just ignore for now.Conflicts do not make sense in a Shared Model. A conflict occurs in the Dedicated Model when a developer tries to commit a change to an object, but didn’t have the latest version of the object to begin with. Developers need to review the object to see what the latest version should be. It could already be correct in source control, or your database version might be correct, or you may need to have a combination of both changes.
In a Shared Model, you don’t need to worry about conflicts, but you may still see them. This happens because SQL Source Control maintains a local working folder for each developer and this working folder may get out of synch if you don’t visit the commit tab frequently. Once the developer who is working on the object commits it to source control and you visit the commit tab, then you will no longer see the conflict.
4) You may undo others’ changes.
The undo feature allows you to undo any changes made to the database that are not in source control. If you undo someone elses changes, you may actually revert the object back to a really early version depending on when you last committed to source control. This could be confusing to the user that is currently working on that object. Be careful to only undo the objects that you are working on.
We hope to improve these areas in a future release, but still recommend switching to a Dedicated Model.
SQL Source Control should not be used on your Production Database
We do NOT recommend using SQL Source Control on your production databases for two reasons:
1) SQL Source Control adds some overhead to your database server while it polls for changes.
2) You should never make changes directly to your production database anyway.
Changes should be done on development, tested and then pushed to production. SQL Source Control will help you track all the changes to your database so you can see who changed what, when and why.