mark 4643 (10/19/2016)
Hi AllOur dev team is growing. We use red-gate toolbox, so we have schema compare, data compare and source control. for the past few years we have used a shared database for source control (with SVN). Using a shared database is becoming problematic, as we have multiple developers working on stuff that has interdependence on stuff other developers are working on.
So, we need to move to each developers having their own databases. Most of the work we do requires queries that span multiple databases, so we can't just take the orders DB (for example) and make Tom_Orders, Dick_Orders, Harry_Orders.
My two choices are an instance for each developer, a local set of DB's and SQLServer developer for each dev. We have a free copy of SQLServer dev with VisualStudio license, so there's no cost. Instances on the main server would be best, but not sure we have sufficient hardware resources to run another 4 SQL instances.
If each dev has a local copy, I'm concerned about backups. All dev laptops are connected to a domain and all docs are stored on a server. Oflline files are used to keep in sysnc when offline. Can't imagine it's advisable to do this for SQL data and log files. If I install DB's on local disk, I'm concerned about backups.
Would be grateful if some of you could share your setup
TIA
Mark
This is exactly what schemas, and more specifically schema ownerships are for. As with any tech related there a 100+ different ways to do things but if you don't want to spread resources around you create 1 dev instance with each dev having their own schema and when you need to propagate to test/prod etc you do your schema compares to determine which to move up (and even back to the other schemas that don't have the approved changes)
Since you are already using a tool that does this very nicely (Visual Studio also does this as well) this solution will work well for you.