Solved

SQL code management

Posted on 2009-05-16
9
445 Views
Last Modified: 2012-08-13
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 a 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!
0
Comment
Question by:Zberteoc
9 Comments
 
LVL 6

Assisted Solution

by:bull_rider
bull_rider earned 50 total points
ID: 24402953
0
 
LVL 3

Assisted Solution

by:ddanonimity
ddanonimity earned 100 total points
ID: 24402996
Create a store procedure, which will run each script in turn. Either that or create a BAT file to call them in order as a command line utility.

Alternatively you could create a service in C# or windows executable, which would allow you to set the chronological order. Using configuration AppSettings you could order them by using an inclining interger, and run them against an SQL database in the program. You could make this even better by adding configuration for SQL connection strings so that once this is set it can run the SQL scripts on a new database.

Hope this helps
DDanonimity
0
 
LVL 2

Assisted Solution

by:pedro_sland
pedro_sland earned 50 total points
ID: 24402998
"Is there really need for a source control environment for the sql code as is already present in the DEV database unlike application code?"
Not in my opinion, unless its quite complicated. I would store the sql for any default values and the structure separately though (not just relying on the schema of the database).
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 24403061
>>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.<<

These are two totally seperate subjects.  You should not allow one topic (How to maintain versioning of SQL scripts.) to influence the other (How to deploy the SQL Scripts).

You should always have some sort of version control for source code.  Be it VB6, .NET or SQL for that matter. Visual Studio supports that using Source Safe.  In conjunction with that or if you are not prepared to do that, at the very least you should implement something like this:
Easy code archiving in SQL Server 2005
http://articles.techrepublic.com.com/5100-10878_11-6149654.html

On the subject of deployment, use a reputable 3rd party tool such as:
SQL Compare®
http://www.red-gate.com/products/SQL_Compare/index.htm

>>Is there really need for a source control environment for the sql code as is already present in the DEV database unlike application code?<<
Yes.  But don't take my word for it:  Go without for a few months and see what happens.  A few of us have learned the hard way and had to update our resumes soon after.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 24403320
I too use RedGate SQL Compare and wouldn't live without it. I also use SQL Data Compare (also from RedGate) to compare data (very useful for reference tables)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24611992
I have faith that Zberteoc will do the right thing and close this thread.
0
 
LVL 26

Author Comment

by:Zberteoc
ID: 24614914
Ooops!
0
 
LVL 26

Author Closing Comment

by:Zberteoc
ID: 31591886
I knew this question was not with one answer only. Even though it wasn't answered in a way that would make me see a new and clear direction at least it confirmed that what I am doing is within fair practice.

I awarded the points based on the effort and information relevance.

Thank you all.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now