Solved

SQL code management

Posted on 2009-05-16
9
444 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

13 Experts available now in Live!

Get 1:1 Help Now