1

We currently have a rather manual, fiddly, messy & error prone way of running SQL deployment scripts when we update our clients' software installations. We're considering finding a 3rd party SQL deployment tool to automate this process.

However, I'm pushing the idea of building our own SQL deployment tool into the application itself. It would be simple - on application startup, it would:

1) Check the existing database schema version (eg. "35")

2) Check against "up to date" database schema version (eg. "38")

3) Retrieve relevant SQL deployment scripts from resource files (eg. "36", "37", "38")

4) Lock the database and run each required SQL deployment script

Note that this would still be run by an IT technician in case any errors occurred, not by end users.

It seems unorthodox but I don't really see any problem. Your thoughts?

Dave Newton
  • 158,873
  • 26
  • 254
  • 302
Brendan Hill
  • 3,406
  • 4
  • 32
  • 61
  • I've seen a similar database deployment done as an SSIS package. It was time-consuming to maintain the package, but after some testing and tweaking it worked well. – supergrady Feb 25 '13 at 23:14

2 Answers2

2

I don't see anything inheritly wrong with this. At a company I've worked for, they built a custom SQL-script installer that would allow them to automatically apply changes to the database, roll back the changes if necessary, and keep tabs on the version of what's been applied.

No matter the desired result of the application, you'll need to set conventions (i.e. database releases should have this folder structure, etc.) and identify the needs and processes that will be used in running the tool (i.e. just how automated you'll make it)

brazilianldsjaguar
  • 1,409
  • 1
  • 20
  • 45
0

Don't build your own. Far too common a problem for a bespoke solution.

You're looking for a database migration tool, my recommendation would be liquibase. It can be run from the command line or integrated into the build process. Unique features that are especially valuable to me is the generation of SQL upgrade (and downgrade) scripts, which are often demanded from us when supporting production installs.

For more a more detailed listing of alternative migration tools see the following answer:

Community
  • 1
  • 1
Mark O'Connor
  • 76,015
  • 10
  • 139
  • 185