Sunday, October 31, 2010

SQL 2008 - Stupid options and sensible practices

http://www.danrigsby.com/blog/index.php/2008/09/26/sql-server-2008-error-saving-changes-is-not-permitted/
H/T to Dan Rigsby for saving me a metric crapload of time.

Ok I can see the sense, somehow - that option prevents you from saving table changes that require the table to be copied, dropped and re-created with the changes, for example, when changing column nullability.

But to have this enabled when you're in the thick of the database design and creation process is just bloody annoying. Took me a few expletives raged at the monitor too. Grrr.

After thinking about this a bit further, it's actually a really stupid option. Anyone making changes to production databases using the table designer should be put up against a wall and shot.

Production databases. These are things that must be updated with extreme caution.

Scripting is the only way to go. At a push, use SqlCompare if you're lazy, but I've seen that go wrong too.

Script any and all database changes as you make them in development. Round-table these with the others  if you're working in a team. Work out a strategy for updating, migrating data, and so on.

In other words, do it properly ffs. Database changes are the cause of at least 176% of all production problems and if you and the guys work out a process, you are thinking correctly. If you rely on stupid table designer features to save you from your own stupidity, then you're stupid.

Thanks for listening.

1 comment:

  1. I thought normally you can use Visual Studio Database Edition (you can sync the database schema with your schema scripts and generated the script for the schema differences) - instead of using SQL compare (which you need to pay if I'm not mistaken)

    ReplyDelete