Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

SQL code management

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
Zberteoc
Asked:
Zberteoc
4 Solutions
 
bull_riderCommented:
0
 
ddanonimityCommented:
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
 
pedro_slandCommented:
"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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Anthony PerkinsCommented:
>>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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Anthony PerkinsCommented:
I have faith that Zberteoc will do the right thing and close this thread.
0
 
ZberteocAuthor Commented:
Ooops!
0
 
ZberteocAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now