Competition: What’s your favorite Redgate tool? Enter now.

Synchronizing when schema could be different than expected

gvsoftgvsoft Posts: 20 Bronze 1
edited February 17, 2017 6:54PM in SQL Compare 11
We have an automated process to create our upgrade scripts from a known database. We assume customer databases will look like our database (because it *should*, as long as nobody messes with it), which means we only create one upgrade script that should work for all customers. However, we often encounter a customer database that has changed in a way we do not anticipate (e.g. someone may have added or removed something they shouldn't have), and our upgrade script will fail. Most of the time, this is a result of something missing, so any attempt to drop that object will fail, requiring us to manually add code to check if the object exists before dropping it, or some other hack to get around the problem temporarily. We have searched in vain for a way for SQLCompare to automatically add a check for objects before dropping them, but no such option seems to exist.

We also sometimes create objects in the field to resolve a problem for a customer before we update our upgrade script (adding an index is a common occurrence). We later add the index to our test database and generate our upgrade script which will add the DML statements to create the index. But if that script ever runs on a customer database where the index has been created by hand, it will fail because it already exists. In this case, we would want to check for the existence of the index and then drop it before attempting to create it.

I would like to hear how others deal with situations like these.

And RedGate, if you're reading this, here are a couple of ideas for options that could help to improve the synchronization process and minimize trouble in the field:

1) "Add a check for existing objects before dropping" -- if not exists, don't drop
2) "Add a check for existing objects before altering" -- if not exists, create instead of alter
3) "Add a check for existing objects before creating" -- if exists, alter or drop/re-create (as appropriate)

Here, the word, "object" is generic and refers to any applicable database objects. You might choose to create separate options for stored procedures, tables, columns, etc., or a single option that works for anything that can be created, dropped, or altered.

Dennis Jones

Comments

Sign In or Register to comment.