How to drop an obsolete table from a SQL Remote setup?

[VolkerBarth]

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:

1. Dropping the obsolete tables from the publications (on cons and remotes) prevents any data changes on these tables to replicate.
2. All stored procs, triggers etc. referencing these table have to be altered.
3. On the remotes, the tables can be dropped immediately, and new remotes can be created without them.
4. 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...).
5. 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.)

[regdomaratzki · Accepted solution]

Your plan sounds good Volker, particularly the part at the end where you say that the steps and going to be tested (three times).

Two comments :

• I like the paranoia you exhibit in step 4. Even if remote databases don't have outstanding data in the tables you'll be dropping, if you remove the table from the consolidated, and then some un-informed user inserts a row into the table before it receives the passthrough session to drop it, then an insert will be sent to the consolidated database, which will cause an error, since the table no longer exists. Again, no big deal, UNLESS the insert on the non-existent table is in the same transaction as an operation that you want to keep. IMHO, there is no harm in keeping the table at the consolidate until such a time that the minimum confirm_sent value in the SYSREMOTEUSER table us higher than the log offset where you executed the passthrough session.

• You talk in step 4 about "dropping the table" once the remotes have confirmed the schema change, but remember that you also will need to modify your stored procedures and triggers at the consolidated at this time as well. I think it's also important to convince yourself that the stored procedures and triggers are logically equivalent before and after your change, even though the reference to the tables is gone after wards.

Reg

[VolkerBarth · Accepted solution]

Sounds like SQL Remote schema changes and paranoia form a nice pair:) - Thanks for your comments, partiucularly the 2nd one. I have planned to run the 2nd step against both consolidated and remotes (and in my particular case this should work) but I see that the general (and more paranoid) approach would be to delay these actions for the consolidated until step 4. - Or to split it up: E.g. code with inserts/updates in the obsolete tables might be already removed in step 2 whereas code with FK checks or the like might have to be retained until step 4. (As you see, I'm just "brain-storming")