Schema Switch-A-Roo : Part 2

Back in August I wrote a post on my schema-swap methodology for T-SQL Tuesday. The approach essentially allows you to lazy load a copy of a table (say, a lookup table of some kind) in the background to minimize interference with users: once the background table is up to date, all that is required to deliver the updated data to users is an interruption long enough to commit a metadata change.

In that post, I mentioned two caveats that the methodology I have championed over the years doesn't currently cater to: foreign key constraints and statistics. There are a host of other features that may interfere with this technique as well. One that came up in conversation recently: triggers. And there are others: identity columns, primary key constraints, default constraints, check constraints, constraints that reference UDFs, indexes, views (including indexed views, which require SCHEMABINDING), and partitions. I'm not going to deal with all of these today, but I thought I would test out a few to see exactly what happens.

I will confess that my original solution was basically a poor man's snapshot, without all of the hassles, whole-database, and licensing requirements of solutions like replication, mirroring, and Availability Groups. These were read-only copies of tables from production that were being "mirrored" using T-SQL and the schema swap technique. So they didn't need any of these fancy keys, constraints, triggers and other features. But I do see that the technique can be useful in more scenarios, and in those scenarios some of the above factors can come into play.

So let's set up a simple pair of tables that have several of these properties, perform a schema swap, and see what breaks. :-)

First, the schemas:

CREATE SCHEMA prep;
GO
CREATE SCHEMA live;
GO
CREATE SCHEMA holder;
GO

Now, the table in the live schema, including a trigger and a UDF:

CREATE FUNCTION dbo.udf()
RETURNS INT 
AS
BEGIN
  RETURN (SELECT 20);
END
GO

CREATE TABLE live.t1
(
  id INT IDENTITY(1,1),
  int_column INT NOT NULL DEFAULT 1,
  udf_column INT NOT NULL DEFAULT dbo.udf(),
  computed_column AS CONVERT(INT, int_column + 1),
  CONSTRAINT pk_live PRIMARY KEY(id),
  CONSTRAINT ck_live CHECK (int_column > 0)
);
GO

CREATE TRIGGER live.trig_live
ON live.t1
FOR INSERT
AS
BEGIN
  PRINT 'live.trig';
END
GO

Now, we repeat the same thing for the copy of the table in prep. We also need a second copy of the trigger, because we can't create a trigger in the prep schema that references a table in live, or vice-versa. We'll purposely set the identity to a higher seed and a different default value for int_column (to help us better keep track of which copy of the table we're really dealing with after multiple schema swaps):

CREATE TABLE prep.t1
(
  id INT IDENTITY(1000,1),
  int_column INT NOT NULL DEFAULT 2,
  udf_column INT NOT NULL DEFAULT dbo.udf(),
  computed_column AS CONVERT(INT, int_column + 1),
  CONSTRAINT pk_prep PRIMARY KEY(id),
  CONSTRAINT ck_prep CHECK (int_column > 1)
);
GO

CREATE TRIGGER prep.trig_prep
ON prep.t1
FOR INSERT
AS
BEGIN
  PRINT 'prep.trig';
END
GO

Now, let's insert a couple of rows into each table and observe the output:

SET NOCOUNT ON;

INSERT live.t1 DEFAULT VALUES;
INSERT live.t1 DEFAULT VALUES;

INSERT prep.t1 DEFAULT VALUES;
INSERT prep.t1 DEFAULT VALUES;

SELECT * FROM live.t1;
SELECT * FROM prep.t1;

Results:

Results from live.t1

Results from prep.t1

And in the messages pane:

Now, let's perform a simple schema swap:

 -- assume that you do background loading of prep.t1 here

BEGIN TRANSACTION;
  ALTER SCHEMA holder TRANSFER prep.t1;
  ALTER SCHEMA prep   TRANSFER live.t1;
  ALTER SCHEMA live   TRANSFER holder.t1;
COMMIT TRANSACTION;

And then repeat the exercise:

SET NOCOUNT ON;

INSERT live.t1 DEFAULT VALUES;
INSERT live.t1 DEFAULT VALUES;

INSERT prep.t1 DEFAULT VALUES;
INSERT prep.t1 DEFAULT VALUES;

SELECT * FROM live.t1;
SELECT * FROM prep.t1;

The results in the tables seem okay:

Results from live.t1

Results from prep.t1

But the messages pane lists the trigger output in the wrong order:

So, let's dig into all of the metadata. Here is a query that will quickly inspect all of the identity columns, triggers, primary keys, default and check constraints for these tables, focusing on the schema of the associated object, the name, and the definition (and the seed / last value for identity columns):

SELECT 
  [type] = 'Check', 
  [schema] = OBJECT_SCHEMA_NAME(parent_object_id), 
  name, 
  [definition]
FROM sys.check_constraints
WHERE OBJECT_SCHEMA_NAME(parent_object_id) IN (N'live',N'prep')
UNION ALL
SELECT 
  [type] = 'Default', 
  [schema] = OBJECT_SCHEMA_NAME(parent_object_id), 
  name, 
  [definition]
FROM sys.default_constraints
WHERE OBJECT_SCHEMA_NAME(parent_object_id) IN (N'live',N'prep')
UNION ALL
SELECT 
  [type] = 'Trigger',
  [schema] = OBJECT_SCHEMA_NAME(parent_id), 
  name, 
  [definition] = OBJECT_DEFINITION([object_id])
FROM sys.triggers
WHERE OBJECT_SCHEMA_NAME(parent_id) IN (N'live',N'prep')
UNION ALL
SELECT 
  [type] = 'Identity',
  [schema] = OBJECT_SCHEMA_NAME([object_id]),
  name = 'seed = ' + CONVERT(VARCHAR(12), seed_value), 
  [definition] = 'last_value = ' + CONVERT(VARCHAR(12), last_value)
FROM sys.identity_columns
WHERE OBJECT_SCHEMA_NAME([object_id]) IN (N'live',N'prep')
UNION ALL
SELECT
  [type] = 'Primary Key',
  [schema] = OBJECT_SCHEMA_NAME([parent_object_id]),
  name,
  [definition] = ''
FROM sys.key_constraints
WHERE OBJECT_SCHEMA_NAME([object_id]) IN (N'live',N'prep');

Results indicate quite a metadata mess:

Metadata duck-duck-goose

The problems with the identity columns and constraints don't seem to be a big issue. Even though the objects *seem* to point to the wrong objects according to the catalog views, the functionality – at least for basic inserts – operates as you might expect if you had never looked at the metadata.

The big problem is with the trigger – forgetting for a moment how trivial I made this example, in the real world, it probably references the base table by schema and name. In which case, when it is attached to the wrong table, things can go… well, wrong. Let's switch back:

BEGIN TRANSACTION;
  ALTER SCHEMA holder TRANSFER prep.t1;
  ALTER SCHEMA prep   TRANSFER live.t1;
  ALTER SCHEMA live   TRANSFER holder.t1;
COMMIT TRANSACTION;

(You can run the metadata query again to convince yourself that everything is back to normal.)

Now let's change the trigger *only* on the live version to actually do something useful (well, "useful" in the context of this experiment):

ALTER TRIGGER live.trig_live
ON live.t1
FOR INSERT
AS
BEGIN
  SELECT i.id, msg = 'live.trig'
    FROM inserted AS i 
    INNER JOIN live.t1 AS t 
    ON i.id = t.id;
END
GO

Now let's insert a row:

INSERT live.t1 DEFAULT VALUES;

Results:

id    msg
----  ----------
5     live.trig

Then perform the swap again:

BEGIN TRANSACTION;
  ALTER SCHEMA holder TRANSFER prep.t1;
  ALTER SCHEMA prep   TRANSFER live.t1;
  ALTER SCHEMA live   TRANSFER holder.t1;
COMMIT TRANSACTION;

And insert another row:

INSERT live.t1 DEFAULT VALUES;

Results (in the messages pane):

prep.trig

Uh-oh. If we perform this schema swap once an hour, then for 12 hours out of every day, the trigger is not doing what we expect it to do, since it is associated with the wrong copy of the table! Now let's alter the "prep" version of the trigger:

ALTER TRIGGER prep.trig_prep
ON prep.t1
FOR INSERT
AS
BEGIN
  SELECT i.id, msg = 'prep.trig'
    FROM inserted AS i 
	INNER JOIN prep.t1 AS t 
	ON i.id = t.id;
END
GO

Result:

Well, that's definitely not good. Since we are in the metadata-is-swapped phase, there is no such object; the triggers are now live.trig_prep and prep.trig_live. Confused yet? Me too. So let's try this:

EXEC sp_helptext 'live.trig_prep';

Results:

CREATE TRIGGER prep.trig_prep
ON prep.t1
FOR INSERT
AS
BEGIN
  PRINT 'prep.trig';
END

Well, isn't that funny? How do I alter this trigger when its metadata isn't even properly reflected in its own definition? Let's try this:

ALTER TRIGGER live.trig_prep
ON prep.t1
FOR INSERT
AS
BEGIN
  SELECT i.id, msg = 'prep.trig'
    FROM inserted AS i 
    INNER JOIN prep.t1 AS t 
    ON i.id = t.id;
END
GO

Results:

This is no good either, obviously. It seems there isn't really a good way to resolve this scenario that doesn't involve swapping the objects back to their original schemas. I could alter this trigger to be against live.t1:

ALTER TRIGGER live.trig_prep
ON live.t1
FOR INSERT
AS
BEGIN
  SELECT i.id, msg = 'live.trig'
    FROM inserted AS i 
    INNER JOIN live.t1 AS t 
    ON i.id = t.id;
END
GO

But now I have two triggers that say, in their body text, that they operate against live.t1, but only this one actually executes. Yes, my head is spinning (and so was Michael J. Swart's (@MJSwart) in this blog post). And note that, in order to clean this mess up, after swapping schemas back again, I can drop the triggers with their original names:

DROP TRIGGER live.trig_live;
DROP TRIGGER prep.trig_prep;

If I try DROP TRIGGER live.trig_prep;, for example, I get an object not found error.

Resolutions?

A workaround for the trigger issue is to dynamically generate the CREATE TRIGGER code, and drop and re-create the trigger, as part of the swap. First, let's put a trigger back on the *current* table in live (you can decide in your scenario if you even need a trigger on the prep version of the table at all):

CREATE TRIGGER live.trig_live
ON live.t1
FOR INSERT
AS
BEGIN
  SELECT i.id, msg = 'live.trig'
    FROM inserted AS i 
    INNER JOIN live.t1 AS t 
    ON i.id = t.id;
END
GO

Now, a quick example of how our new schema swap would work (and you may have to adjust this to deal with each trigger, if you have multiple triggers, and repeat it for the schema on the prep version, if you need to maintain a trigger there too. Take special care that the below code, for brevity, assumes that there is only *one* trigger on live.t1.

BEGIN TRANSACTION;
  DECLARE 
    @sql1 NVARCHAR(MAX),
    @sql2 NVARCHAR(MAX);

  SELECT 
    @sql1 = N'DROP TRIGGER live.' + QUOTENAME(name) + ';',
    @sql2 = OBJECT_DEFINITION([object_id])
  FROM sys.triggers
  WHERE [parent_id] = OBJECT_ID(N'live.t1');

  EXEC sp_executesql @sql1; -- drop the trigger before the transfer

  ALTER SCHEMA holder TRANSFER prep.t1;
  ALTER SCHEMA prep   TRANSFER live.t1;
  ALTER SCHEMA live   TRANSFER holder.t1;

  EXEC sp_executesql @sql2; -- re-create it after the transfer
COMMIT TRANSACTION;

Another (less desirable) workaround would be to perform the entire schema swap operation twice, including whatever operations occur against the prep version of the table. Which largely defeats the purpose of the schema swap in the first place: reducing the time users can't access the table(s) and bringing them the updated data with minimal interruption.