May 16, 2009 at 9:12 am
I have a question(s) in regards with database development in general.
In a development cycle structure with development, quality assurence, staging and production what is the best way of managing the sql code like table creation and alter scripts, views, functions and stored procedures scripts? Along with that what is the best way to promote this code through different environments from DEV to QA to STAGING and to PROD.
The problem with sql code is different than the application code where the it resides in files that are kept in a source control environment like SourceSafe or PerForce. In application the files are in a collection that is promoted and files saved in any order and they are versioned but with sql code we need to create scripts in a certain order and when promoted they have to be executed in the same chronological order. The way I did it so far is creating scripts named so that their alphabetical order is actually their chronological order and I used command lines utilities like osql for MS-SQL and mysql for MySQL.
Is there really need for a source control environment for the sql code as is already present in the DEV database unlike application code? We have shared DEV and QA environments.
I know this is not a one answer question, actually they are several questions in one, but I would like to hear opinions.
Thank you in advance!
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 16, 2009 at 9:24 am
We store .sql and .bak files in VSS and TFS the later is native in VSS it takes manual work through server explorer or actual copy and paste in VSS. We used naming so after QA and staging then it is deployment. You could try it and post again.
Kind regards,
Gift Peddie
May 16, 2009 at 9:45 am
Gift Peddie (5/16/2009)
We store .sql and .bak files in VSS and TFS the later is native in VSS it takes manual work through server explorer or actual copy and paste in VSS. We used naming so after QA and staging it is deployment. You could try it and post again.
I am interested how do you ensure the order in which the .sql scripts need to be run? Do you keep the scripts for every object in VSS or just the ones that are needed for development? What exactly is TFS?
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 16, 2009 at 10:05 am
TFS( Team Foundation Server) that is the new fancy Microsoft source control system, it runs with SQL Server native and uses Windows Sharepoint services to manage documents. How we added the code each developer added his or her stored procs and our team lead adds the .bak files which is everything. And when it is time to deliver code we pass any changed code to the lead developer who make sure all changes are in VSS and run it then the team lead creates the .bak which is passed to QA and Staging DBA who wants us to do all the work. Check the links below to test drive TFS, SQL Server enterpriser is also needed. So you need TFS, SQL Server Enterprsie and Windows Sharepoint Services to run TFS.
http://blogs.vertigosoftware.com/teamsystem/archive/2006/04/04/2512.aspx
Kind regards,
Gift Peddie
May 17, 2009 at 4:46 am
Gift Peddie (5/16/2009)
TFS( Team Foundation Server) that is the new fancy Microsoft source control system, it runs with SQL Server native and uses Windows Sharepoint services to manage documents. How we added the code each developer added his or her stored procs and our team lead adds the .bak files which is everything. And when it is time to deliver code we pass any changed code to the lead developer who make sure all changes are in VSS and run it then the team lead creates the .bak which is passed to QA and Staging DBA who wants us to do all the work. Check the links below to test drive TFS, SQL Server enterpriser is also needed. So you need TFS, SQL Server Enterprsie and Windows Sharepoint Services to run TFS.http://blogs.vertigosoftware.com/teamsystem/archive/2006/04/04/2512.aspx
Thanks for this inside, which indeed deserves attention. However I see this solution a rather very expensive one which doesn't apply to other technologies than Microsoft. It also seems to be tight to SQL 2008.
I would still like to now how can be ensured in a shared environment with multiple developers the right chronology for the scripts execution. Let's say I have a s1.sql that adds a column, then a s2.sql that adds an index to that column, then a s3.sql that is a view that uses that column, s4.sql is a stored proce that uses the view and maybe updates the column from s1.sql and so on... How can you make sure that when the code is promoted to the next environments, QA, STAGING, PROD, the scripts are run in the correct order?
As for now I see only 2 possibilities: 1 name the files so that their alphabetical order is the same with their chronological order and 2, keep a document or a table with the name of the scripts in the order they need to be run.
Is there a system, product, method that takes care of this?
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 17, 2009 at 7:34 am
Thanks for this inside, which indeed deserves attention. However I see this solution a rather very expensive one which doesn't apply to other technologies than Microsoft. It also seems to be tight to SQL 2008.
No TFS comes with both SQL Server 2005 and 2008 if you are using VS2005 TFS you use SQL Server 2005, VS2008 TFS you use SQL Server 2008. And there is an add in which cost $399 to enable uses with Unix and Java development.
I would still like to now how can be ensured in a shared environment with multiple developers the right chronology for the scripts execution. Let's say I have a s1.sql that adds a column, then a s2.sql that adds an index to that column, then a s3.sql that is a view that uses that column, s4.sql is a stored proce that uses the view and maybe updates the column from s1.sql and so on... How can you make sure that when the code is promoted to the next environments, QA, STAGING, PROD, the scripts are run in the correct order?
That is very good question you have get to the main point one person must be in charge of adding columns because we were not allowed, the customer Architect must approve all column adding and dropping so we just send and get email when changes are made. Views were allowed but we did not add any. So you must have someone in charge of adding and dropping columns and views.
As for now I see only 2 possibilities: 1 name the files so that their alphabetical order is the same with their chronological order and 2, keep a document or a table with the name of the scripts in the order they need to be run.
Is there a system, product, method that takes care of this?
Don't just give the hungry man a fish, teach him how to catch it as well.
So make sure developers are in charge of adding their code to VSS, Lead developer in charge of making sure all code is added and build the database, someone in charge of changing Tables, adding Views and Team lead adding .bak to VSS and to the QA, Staging and production. So get a conference room put all this stuff to paper get others involved and create responsibility chart and it is not complicated.
Kind regards,
Gift Peddie
May 17, 2009 at 7:43 am
Thanks, Gift Peddie for your reply.
When you say add .bak to VSS do you mean the database backup? Sounds a bit too much, why the need of backup in VSS? Is that a backup of after or before promotion?
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 17, 2009 at 7:53 am
We had to add .bak in VSS because our database server failed twice in one month so yes .bak is the database full backup in added in VSS. You can make changes to based on your team and your needs. When you have .bak in VSS that is the only thing given to QA, Staging and production so there is tight control of the database build.
Kind regards,
Gift Peddie
May 17, 2009 at 7:58 am
Gift Peddie (5/17/2009)
...When you have .bak in VSS that is the only thing given to QA, Staging and production so there is tight control of the database build.
So you say that actually QA gets the .bak from DEV to be restored? You can't do that in prod though as you would overwrite the data. How then will you test the scripts promotion to make sure you will not get surprises in prduction?
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 17, 2009 at 8:08 am
That is correct QA DBA gets the .bak and QA passes .bak to Staging from staging to production some compare is needed that is why you need a DBA or a compare tool.
Kind regards,
Gift Peddie
May 17, 2009 at 8:21 am
Gift Peddie (5/17/2009)
That is correct QA DBA gets the .bak and QA passes .bak to Staging from staging to production some compare is needed that is why you need a DBA or a compare tool.
Interesting approach. The problem is that you can have structure modifications and data manipulation in DEV that needs to be executed in prod as well. With compare you can do a structure delta and apply it, I agree, but you can't do that with the data though as you would have test data in dev beside the data that really needs to get into prod. When the environment are also replicated more problem will arouse.
As I see it the conclusion is that there is NO really a system or software in place that will take care of this promotion process so in the end is a matter of choice based on the particular situation.
Thanks a lot!
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 17, 2009 at 8:31 am
Interesting approach. The problem is that you can have structure modifications and data manipulation in DEV that needs to be executed in prod as well. With compare you can do a structure delta and apply it, I agree, but you can't do that with the data though as you would have test data in dev beside the data that really needs to get into prod. When the environment are also replicated more problem will arouse.
Actually you are the one with no structure in place because there is no reason for code in development to be executed in production. When everything is structured and a clean responsibility chart created the only code that gets to production is from staging. Replication is a production related step that goes back to you also need a separate production DBA if you have Replication.
Kind regards,
Gift Peddie
May 17, 2009 at 8:42 am
Actually you are the one with no structure in place because there is no reason for code in development to be executed in production. When everything is structured and a clean responsibility chart created the only code that gets to production is from staging. Replication is a production related step that goes back to you also need a separate production DBA if you have Replication.
But that's the point, how do the modifications get to PROD or even STAGING from DEV? At some point you have to brake the backup restore method. And if you have data structure, database code and data modification you can't just use compare.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 17, 2009 at 8:47 am
And in regards to the backup-restore promotion method, would work only if the database is in within reasonable size limits, if is few hundred of gigs then becomes a problem.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
May 17, 2009 at 9:01 am
The .bak stops at staging and the DBA moves the changes to production, if you are using VS2005/8 Team Suites you can compare both Schema and data.
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply