Now we will create all of our n+1 version objects in the database. Remember, these are applied to the Stage schema. For simplicity I really just care about BatchTot and the new length of my columns.
Now we need a mechanism to keep the data synchronized. My choice for this is transactional replication. So we create a new publication with an article of dbo.BatchTot. The subscriber is the same database. The destination is simply Stage.BatchTot. The sync_type should be 'database snapshot' so that snapshotting the table will hold the shortest-duration locks on dbo.BatchTot as possible.
Once replication is synchronized you can do a "check-out" of your new application if desired. You are done with the pre-downtime steps. The beauty is that if anything goes wrong we have not impacted our production users of the current version of our software. Replication is only a slight performance penalty, but you should of course determine what is best for your needs.
When you are ready for your downtime you simply need to quiesce your system. This means that you have no active transactions and distribution agents are completed. We are "truing-up" the system.
Now we run two ALTER SCHEMA TRANSFER commands. The first moves the current version objects to the Deprecated schema from dbo. The second moves the Stage schema objects to dbo. This process moves all "dependent" objects as well to the destination schema. By "dependent" I mean any indexes, constraints, and keys. It does not, however, move permissions. Those are "lost" during the transfer, so you simply need to reapply them. I would assume this is a conscious decision by Microsoft to ensure we don't introduce any gaping security holes.
As mentioned above we now also need to teardown replication.
You can also remove the Deprecated and Stage schemas whenever it is convenient. I usually keep them around "just in case" for a few days.
The ALTER SCHEMA TRANSFER process never seems to take us longer than 7 seconds, even for large tables or Stage schemas with many objects. I assume under-the-covers that Microsoft has implemented this as a "metadata-only" operation.
I have only scratched the surface of what can be done with "Darwin" and ALTER SCHEMA TRANSFER. I believe this is an extremely simple method to get to near-zero downtime database upgrades. It is not as costly as Oracle's GoldenGate and is conceptually simple to understand for most people. It even has a built-in "rollback" mechanism. I hope this blog post was interesting and helps you in the future.
Dave Wentzel CONTENT
sql server data architecture