Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL code management

Posted on 2009-05-16
9
Medium Priority
?
459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Assisted Solution

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

Assisted Solution

by:ddanonimity
ddanonimity earned 300 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 150 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
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 75

Accepted Solution

by:
Anthony Perkins earned 900 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
 
LVL 70

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 27

Author Comment

by:Zberteoc
ID: 24614914
Ooops!
0
 
LVL 27

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

722 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