10

What is the best way to manage database changes? I need to have a solutions regardless the database client's language. Also I'd like to be able to use specific database features in those changes such as stored procedures, triggers and so on.

mkadunc
  • 696
  • 6
  • 12
Szymon Lipiński
  • 27,098
  • 17
  • 75
  • 77

6 Answers6

13

First of all, make sure you have your entire Database Build scripted so you can rebuild the database if needed.

Each change should then be written as an Update script. That way you can run each change individually against the your databases.

Once the change has been commited to the codebase, merge the change script with the build process so it happens automatically...and then archive the change script in case any questions arise.

Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
7

First and foremost, put all database changes in scripts and put them into the source control system.

Next remove any permissions to production that developers have. Exactly two people should have rights on production in a small to medium shop, the designated dba and his or her designated alternate. Once devs can't make changes to prod, you will find it easier to get them to actually write and use scripts.

Never run a script on prod that wasn't first loaded to QA or staging. If there are issues with the script, it should be found at this point.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
6

Use idempotent change scripts (and maybe have a look at LiquiBase or dbdeploy).

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
1

Not sure what you are asking here, but if it's a good way to manage schema changes, and keeping them in synch between versions and deployments, it's hard to go wrong with Visual Studio Database Edition. It's sole purpose in life is to manage database schema changes, validate the schema, build and generate deployment scripts. If you have Visual Studio Developer Edition or Visual Studio Team Suite, you can get it for free.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
0

In my case, I build a SH script for this work:

https://github.com/reduardo7/db-version-updater

And a MySQL script:

https://github.com/reduardo7/db-version-updater-mysql

Eduardo Cuomo
  • 17,828
  • 6
  • 117
  • 94
0

Well, we are currently using Redgate's toolbelt which contains a Database Compare, Data Compare, etc.

You can also use any source control to track changes to your database objects.

Allov
  • 1,308
  • 1
  • 13
  • 23
  • If you're using Red Gate tools, you will be able to use SQL Source Control to connect your existing source control system to SQL Server via SSMS. – David Atkinson Sep 25 '11 at 09:40