Over the years, our SQL Remote setup has experienced several schema changes. Now there are some (empty) tables that aren't in use anymore (since a few years already). Note that they are not referenced by other tables.
So far, I have left them in the publications since they do not disturb and since dropping a table from a publication looks dangerously. However, I'd try to achieve this now as part of another schema change.
Preconditions:
- Each schema change is tracked with a schema version number in each remote (and the consolidated). Therefore it's possible to assure that each remote is using a schema where the respected tables are treated as obsolete.
- Each application is tracked with a version number in each remote (and the consolidated), too. Therefore it's possible to assure that each remote is using applications that do not touch/need the respected tables.
- Application logic assures that schema versions and application versions fit together, or that otherwise the user has to update his applications or upgrade the schema (by running SQL remote).
As such I can guarantee no site is using these obsolete tables anymore. However, they are still referenced inside procedures, triggers and the like.
After studying some newsgroup threads, the important parts seems to be:
- Dropping the obsolete tables from the publications (on cons and remotes) prevents any data changes on these tables to replicate.
- All stored procs, triggers etc. referencing these table have to be altered.
- On the remotes, the tables can be dropped immediately, and new remotes can be created without them.
- On the consolidated, the tables should be dropped only when all remotes have confirmed the schema change (In my case, that's not really necessary as I can guarantee that no remote has outstanding data for these tables but SQL Remote has made me think take care...).
- Steps 1-3 are going to be done within a schema change script - applied to the remotes via passthrough mode, whereas step 4 is done afterwards.
Question:
Do these steps and their order make sense, or do I have overseen something of importance?
(Of course these steps are going to be tested, tested, tested before production usage.)
asked
23 Jun '10, 11:51
Volker Barth
39.7k●357●545●814
accept rate:
34%