Solved

SQL code management

Posted on 2009-05-16
9
448 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 40
MySQL recovery 7 33
Need help with convert Informix SQL SELECT statement to Microsoft SQL 1 26
Not listening to where 1 22
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 …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 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