CODEWITHSUNDEEP
sql
Alex G.P.
Alex G.P.

Reputation: 10008

Common practice to implement SQL scripts application

I have quite old application with current database (on MSSQL but it does not matter). I scripted it completely with all required static data. Now I want to introduce DB change only via update scripts. So each function, each SP will be placed in stand-alone file and all schema update scripts will be stored in files named like 'SomeProduct01_0001' what means that this script belongs to product SomeProduct, sprint 1 and it is first schema update script.

I know that each script must be absolutely re-runnable, but anyway I want to have functionality to combine these scripts into one based on DB version (stored in DB table).

  1. What common best practices there is to handle bunches of update scripts?
  2. What is better - implement version anylyzis in collector (bat or exe file) or add some SQL header to each file? From other point of view I am already have version - it will consist of sprint identifier and script identifier, not sure that it is ok to duplicate this information in script header.
  3. How to skip file content if user tries to apply it to newer database but keep availability combine this script with any other to perform updates of other old database?
  4. How to avoid database conflicts if combined scripts operates columns/table which still does not exists in database but will be created byt this script (for example, in line 10 table created and in line 60 it is used in trigger or constraint, as I know script will not be validated)? Maybe wrap in EXEC('') entire script? What I need to escape besides sigle quote characters?

UPD: As David Tanzer asnwered it is better to use ready solutions for DB migrations, so it may be best solution for cases like mine. It was not and answer exactly for my question, but it is suitable for new solutions.

Upvotes: 0

Views: 102

Answers (1)

David Tanzer
David Tanzer

Reputation: 2742

You don't have to implement this yourself, there are tools that do it. Take a look at dbmaintain, it provides almost exactly the functionality you described:

http://www.dbmaintain.org/overview.html

I know of and have worked with several teams who use it to manage their database schemas in all their environments: Development, Testing, Staging and Production.

http://flywaydb.org/ seems to be another tool to do this, and it has even more features. They even have a comparison of multiple tools on their homepage (including dbmaintain)

Upvotes: 1

Related Questions