Synchronizing when schema could be different than expected
gvsoft
Posts: 20 Bronze 1
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
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
Thanks for contacting us!
This is actually an option in SQL Compare currently - see the "Add object existence checks" option here
https://documentation.red-gate.com/display/SC12/Setting+project+options
or here for the command line.
https://documentation.red-gate.com/display/SC12/Options+used+in+the+command+line#Optionsusedinthecommandline-ObjectExistenceChecks
Warm Regards,
Technical Sales Engineer
Redgate Software
Dennis
It looks like the basic functionality we need is there. I tested with a new and a dropped index, and both cases resulted in what I expected. One possible concern would be if someone had created an object with the same name but wrong/different definition, in which case, we would want to drop and re-create the object (not ignore/skip it just because it already exists). That should be a very rare occurrence, but it did come up in the conversation I had with a co-worker.
I've enabled the option in the application I wrote to generate the scripts using the SQLCompare API. We'll try it out for a while and see if there are any problems or gotchas that we need to watch out for, and will let you know if any come up.
Thanks again,
Dennis Jones