October 19, 2016 at 4:51 am
Hi All
Our 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
October 19, 2016 at 6:01 am
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
Local instances for the developers is the way to go, IMO.
Your concerns with backups are, I presume, related to source code in development? If your concerns are related to data (you talk about backups, so I'm not sure), I'd like to know why. Data in dev databases should not be anything of value.
Otherwise, this is handled relatively easily if you use something like Git / Github as your VCS. Local feature branches can be checked into a central/cloud server without affecting other developers, or the main or QA branches.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 19, 2016 at 6:17 am
The best way to do this is to give each developer their own local instance and their own local copy of the database. For this to work well, you have to have a mechanism for automating your database builds and the cleanup of those databases from production (assuming you're going to use backup & restore, a common practice). From there, you're going to want to have, at minimum, one integration point where the code from each developer is put through it's paces in combination with the code from the other developers. You may want to also get into continuous integration.
This is a huge topic.
I'd suggest starting with this article on automating database builds[/url]. If you are then going on to do CI, here's a more detailed article [/url]on that process around databases. This article[/url] drills down on the various methods you can use to build your database. This article [/url]addresses designing out an entire process, including testing, pre-production, etc.
Going down this path is very rewarding for you and your dev team. However, there's quite a bit of work involved in setting it up correctly and successfully. Plan for that.
There are some shortcuts. For example, Redgate Software, my employer, makes a whole suite of tools focused on Database Lifecycle Management[/url]. That will help, after you've established your process. The process definition has to come first.
Just talking only about provisioning the database, we have another tool, just going into beta, called SQL Clone which will help with this process a ton. You can read more about that here[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2016 at 3:33 am
Phil Parkin (10/19/2016)
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
Local instances for the developers is the way to go, IMO.
Your concerns with backups are, I presume, related to source code in development? If your concerns are related to data (you talk about backups, so I'm not sure), I'd like to know why. Data in dev databases should not be anything of value.
Otherwise, this is handled relatively easily if you use something like Git / Github as your VCS. Local feature branches can be checked into a central/cloud server without affecting other developers, or the main or QA branches.
Thanks for reply. yes it is backups of source code I'm concerned about (not data). We use red-gate SourceControl for main dev server, so not sure how we'd use a second source control option for local work
We just got all the new VS2015 pro licenses with SQLdev and I wrote all script yesterday to copy main dev db's to local stations
October 20, 2016 at 3:37 am
Grant Fritchey (10/19/2016)
The best way to do this is to give each developer their own local instance and their own local copy of the database. For this to work well, you have to have a mechanism for automating your database builds and the cleanup of those databases from production (assuming you're going to use backup & restore, a common practice). From there, you're going to want to have, at minimum, one integration point where the code from each developer is put through it's paces in combination with the code from the other developers. You may want to also get into continuous integration.This is a huge topic.
I'd suggest starting with this article on automating database builds[/url]. If you are then going on to do CI, here's a more detailed article [/url]on that process around databases. This article[/url] drills down on the various methods you can use to build your database. This article [/url]addresses designing out an entire process, including testing, pre-production, etc.
Going down this path is very rewarding for you and your dev team. However, there's quite a bit of work involved in setting it up correctly and successfully. Plan for that.
There are some shortcuts. For example, Redgate Software, my employer, makes a whole suite of tools focused on Database Lifecycle Management[/url]. That will help, after you've established your process. The process definition has to come first.
Just talking only about provisioning the database, we have another tool, just going into beta, called SQL Clone which will help with this process a ton. You can read more about that here[/url].
Thanks Grant, will checkout DLM. Would like to get to CI eventually
In interim we are moving to local dev DB's and in red-gate source control, we will change to a dedicated model (main dev instance). Now that were writing code in DB's on laptops and committing to source control on main dev instance, what do other users do about backing up work in progress TSQL code (mostly SP's) on laptops?
October 20, 2016 at 7:16 am
mark 4643 (10/20/2016)
Thanks Grant, will checkout DLM. Would like to get to CI eventuallyIn interim we are moving to local dev DB's and in red-gate source control, we will change to a dedicated model (main dev instance). Now that were writing code in DB's on laptops and committing to source control on main dev instance, what do other users do about backing up work in progress TSQL code (mostly SP's) on laptops?
Work in progress should be going to source control, absolutely. However, the best way to do this is using a more sophisticated source control system like Git. It maintains local copies and then migrates those up to a shared, main copy. You can also look to creating branches and merging branches (again, Git is good at this, TFS & others, not as much) as a way to separate in-flight changes from the stuff that you want in the integrated stream.
Hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2016 at 7:40 am
Grant Fritchey (10/19/2016)
The best way to do this is to give each developer their own local instance and their own local copy of the database. For this to work well, you have to have a mechanism for automating your database builds and the cleanup of those databases from production (assuming you're going to use backup & restore, a common practice). From there, you're going to want to have, at minimum, one integration point where the code from each developer is put through it's paces in combination with the code from the other developers. You may want to also get into continuous integration.This is a huge topic.
I'd suggest starting with this article on automating database builds[/url]. If you are then going on to do CI, here's a more detailed article [/url]on that process around databases. This article[/url] drills down on the various methods you can use to build your database. This article [/url]addresses designing out an entire process, including testing, pre-production, etc.
Going down this path is very rewarding for you and your dev team. However, there's quite a bit of work involved in setting it up correctly and successfully. Plan for that.
There are some shortcuts. For example, Redgate Software, my employer, makes a whole suite of tools focused on Database Lifecycle Management[/url]. That will help, after you've established your process. The process definition has to come first.
Just talking only about provisioning the database, we have another tool, just going into beta, called SQL Clone which will help with this process a ton. You can read more about that here[/url].
Grant,
Has RedGate written any books on Continuous Integration and Lifecycle Management from the perspective of database development, or can you recommend a good book on the topic? There are a lot of books that briefly touch on the topic, and a lot of articles have been written, but I can't recall ever seeing a book focused just on this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 20, 2016 at 8:28 am
Eric M Russell (10/20/2016)
Grant,Has RedGate written any books on Continuous Integration and Lifecycle Management from the perspective of database development, or can you recommend a good book on the topic? There are a lot of books that briefly touch on the topic, and a lot of articles have been written, but I can't recall ever seeing a book focused just on this.
We were writing a book, but the project kind of fell through. Those articles I link above were several of the chapters of the book that I did complete. To my knowledge, we cover more on DLM, CI & DevOps for the data pro in those links and the other ones than any other source I've found. Keep an eye on Simple-Talk. There's going to be a lot more DevOps for databases coming out. Not that I have inside information or anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2016 at 2:31 pm
what we do at my shop is as follows.
All databases on DEV/UAT/FIT are named as follows
XXX_YYY_999_databasename
where XXX is one of DEV/UAT/FIT
YYY is the developer code/name
999 is the project being worked on
All block databases reference the others when required through the use of synonyms (which is also done on PROD)
Some issues (solved)
We have several type of databases - some are standalone, others work in blocks.
Standalone pose no problem - each developer gets its own.
block ones we have 2 situations
1 - One or more of the databases are static from the development point of view
this would be for example databases that are from other teams and which are not changed by a particular team.
In this case a single copy is shared by all developers, and synonyms target those commonly
2 - all databases are dynamic and changeable for each developer
In all the situations above we have sets of scripts that are called passing the desired database names, which on block databases could be several of them, and which create/restore the desired databases and apply the required synonyms.
As an example if one of our teams was working on the SAP project 3 databases would be created with following command
create_sap -Env DEV -User Frederico -Project 925
As would also be expected from the above setup, everything that accesses these databases, being it command line applications, web interfaces, SSIS or others do use configuration files that contain the desired names.
Some have to be changed by the developers as they work through the code, others may be done automatically by selecting which database to work with on startup of the application (this is the case for some web apps where the users (non prod) enter the database they wish to work on).
All development is done in branches, and goes through several stages until it is ready to go to production - once it reaches UAT all build is done automatically and gets promoted to the next level once the corresponding projects are signed off for next stage.
October 22, 2016 at 5:25 pm
frederico_fonseca (10/20/2016)
what we do at my shop is as follows.All databases on DEV/UAT/FIT are named as follows
XXX_YYY_999_databasename
where XXX is one of DEV/UAT/FIT
YYY is the developer code/name
999 is the project being worked on
All block databases reference the others when required through the use of synonyms (which is also done on PROD)
Some issues (solved)
We have several type of databases - some are standalone, others work in blocks.
Standalone pose no problem - each developer gets its own.
block ones we have 2 situations
1 - One or more of the databases are static from the development point of view
this would be for example databases that are from other teams and which are not changed by a particular team.
In this case a single copy is shared by all developers, and synonyms target those commonly
2 - all databases are dynamic and changeable for each developer
In all the situations above we have sets of scripts that are called passing the desired database names, which on block databases could be several of them, and which create/restore the desired databases and apply the required synonyms.
As an example if one of our teams was working on the SAP project 3 databases would be created with following command
create_sap -Env DEV -User Frederico -Project 925
As would also be expected from the above setup, everything that accesses these databases, being it command line applications, web interfaces, SSIS or others do use configuration files that contain the desired names.
Some have to be changed by the developers as they work through the code, others may be done automatically by selecting which database to work with on startup of the application (this is the case for some web apps where the users (non prod) enter the database they wish to work on).
All development is done in branches, and goes through several stages until it is ready to go to production - once it reaches UAT all build is done automatically and gets promoted to the next level once the corresponding projects are signed off for next stage.
that might work when all queries are within one database, but when nearly every query is cross database (bad design on 3rd party vendor product) it's not good
October 23, 2016 at 3:16 am
mark 4643 (10/22/2016)
that might work when all queries are within one database, but when nearly every query is cross database (bad design on 3rd party vendor product) it's not good
I think you missed the part where I said
All block databases reference the others when required through the use of synonyms (which is also done on PROD)
in one particular "block" one of the databases is the entry point for all queries by the users - this database has views -> synonyms to at least another 5 databases.
October 24, 2016 at 9:39 am
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.
October 24, 2016 at 10:00 am
In addition to using a version control system like Git or TFS, where a copy of working code is committed both locally and on the server, it also helps if teams have something like a 15 minute daily stand-up meeting, so everyone is on the same page about what the other is working on. For example, if John casually mentions that he's planning to refresh the development copy of the Product database from production over the weekend, James has an opportunity to point out that he and others currently have some stored procedures that are work in progress there.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 28, 2016 at 8:23 pm
I have 2 SQL Servers strictly for Developers where Developers can keep their copies of various Production databases. Each Developer can have as many 2 databases of each type across those 2 Servers. Additionally I have 1 standalone SQL Server where DBA load Production Databases to, so those Production Databases ( scraped from some sensitive info) can be distributed to Developers on demand via some SQL Scripts at any time thru the Web GUI. Backup of Developers databases(vs Backup of Production databases) is a feature of the Web GUI, and is the responsibility of Developers.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply