Scenario: Transactional replication with queued updates. 'Subscriber wins' conflict resolution policy ( exec sp_addpublication ... @conflict_policy = N'sub wins' ...). Several publications are used to deploy database instead of a single big publication. Since publications are queued, subscribers must know about foreign key (FK) restrictions in order to prevent users from breaking the rules, thus referential integrity must be replicated to subscribers. The command to create articles matching the former requirements is:
exec sp_addarticle ... @schema_option = 0x0000000008035FDF ...
and a sparse explanation of @schema_option bitmask is:
0x0000000008035FDF
0x8000000 Create any schemas not already present on the subscriber.
0x10000 Replicates CHECK constraints as NOT FOR REPLICATION ...
0x20000 Replicates FOREIGN KEY constraints as NOT FOR REPLICATION...
0x4000 Replicates UNIQUE constraints. Any indexes related to the...
0x1000 Replicates column-level collation.
0x100 Replicates user triggers on a table article, if defined...
0x200 Replicates foreign key constraints. If the referenced table ...
0x400 Replicates check constraints. Not supported for Oracle...
0x800 Replicates defaults. Not supported for Oracle Publishers.
etc..
You can read more about sp_addarticle and @schema_option in BOL. If you use Management Studio rather than T-SQL you don't have to worry about this bitmask mess: the GUI does everything for you.
Problem: Even though SQL Server 2005 has the option to publish referential integrity among tables in a publication, this option only works for tables that are both inside the publication. If there is any referential integrity that goes TO a table outside the publication or comes FROM a table outside the publication, this foreign key (FK) is not replicated to subscribers.
Besides, if you are using SQL Server 2000, this option is not available at all. If you want to use queued updates, the subscribers must have all foreign constraints applied in order to avoid problems. The stored procedures and semi-automated way of publishing foreign keys described in this document can be used also if you are still using SQL Server 2000.
Subscribers are unaware of those restrictions and there is a chance that a subscriber receives a command that could violate a foreign key on the publisher and still be executed and queued without any warning/error to the user/client application. Afterwards, when the queued command is replicated to the publisher, it is rolled back because it goes against a foreign key that only publisher knows about.
In the following picture you can see a sample of the problem under discussion. There we have 2 publications, QUOTATIONS and ORDERS and there are foreign keys that are completely inside the publications, but there is another one FK_TOrders_TQuotations) that goes beyond the limits of a single publication and starts in ORDERS and finishes in QUOTATIONS.
This kind of foreign keys are not replicated to subscribers even though the option to replicate FKs is enabled. If you are using 'publisher wins' policy, the offending command is rolled back and the original record is restored at the subscriber (seconds or minutes later). The user there is not warned about this fact; he might still think that his changes were commited (but they were not).
On the worst possible scenario, if you are using 'subscriber wins' policy, when the offending command is sent to publisher for replication, it cannot be commited because it breaks the referential integrity, but since 'subscriber wins' is set, the command is not sent back to subscriber to be undone. In this case, subscriber will have the new (but inconsistent) record, and publisher will hold the old (but consistent) record.
After some hours, or even days, if the publication is validated on a scheduled basis using sp_publication_validation, the publication will appear has having validation errors. If the publication is then reinitialised, the subscribers will receive the publishers version of the data (foreign key consistent).
In any case, the changes committed initially at the subscriber are undone without any error nor warning by the time the statements are being executed at the subscriber.
My feedback to Microsoft on this issue: Subscriptions get desynched easily when subscriber uses queued updates and there are foreign keys that point outside the articles in the publication
Solution: The main idea behind this solution is using post-snapshot scripts to create the foreign keys needed at the subcribers that are not replicated automatically by SQL Server.
We must create pre-snapshots scripts also to drop the FKs that might prevent the tables from being dropped and recreated by the initial snapshots at the subscribers.
In our example, in order to successfully publish QUOTATIONS publication, we need first to try dropping FK_TOrders_TQuotations because it points to TQuotations, and since that FK might be already in place, TQuotations could not be dropped in order to be recreated and populated by the initial snapshot (this only applies if the action for in use object is drop and recreate ( exec sp_addarticle ... @pre_creation_cmd = N'drop' ... ).
So, we will need pre-snapshot scripts to drop those FKs that might be already in place and post-snapshot scripts to re-create them.
Is there any way to automate the process of creating those scripts? If the database you are trying to replicate is somewhat big, sure you will have hundreds of tables with at least an average of 3 or 4 foreign keys for each one. Having to manually find and script the creation (and deletion) of those FKs that are beyond the limit of a publication is a headache (at least).
Besides, you will need to mantain those scripts if the schema changes or new tables are created. Having to manually manage pre-snapshot scripts and post-snapshot scripts is not a solution for real environments.
Prerequisites: As commented in the former feedback to Microsoft, there are some conditions that your database must met in order to be able to use the scripts.
- The tables must be published using their original names.
- The scripts will try to create/delete both (incoming and outgoing) foreign keys for every single article/table. This will ensure that a FK is created if, in first instance, one of the tables involved in a FK is not already published. It will be created during the post-snapshot script of the second publication initialization.
- FKs will be created at the subscribers after publisher's name (the same name as publisher's) and, if a FK with the same name already exists, the scripts suppose it is already in place and it will not be created twice, nor with another name.
- If a FK references tables that are already (both) at the subscriber, the scripts will try to create the FK. The schema will not be checked, this is the reason for the need to deploy the tables with their original names.
- If horizontal or vertical partitions (row filters) are applied to any of the articles, you must be sure not to remove rows/columns that would violate any of the existing FK at the publisher when tried to be applied at the subscriber (the scripts will not check this).
Scripting: We will discuss here a procedure to automate the process of creating those scripts. We will use an auxiliary publication SyncForeignKeysForSubscribers that will contain 2 articles (tables) called TSyncForeignKeysForSubscribersCREATE and TSyncForeignKeysForSubscribersDROP. The scripts to create those 2 tables are:
CREATE TABLE [dbo].[TSyncForeignKeysForSubscribersCREATE](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PublicationName] [varchar](256) COLLATE Modern_Spanish_CI_AS NOT NULL,
[TableName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
[FKName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
[TheRest] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_TSyncForeignKeysForSubscribersCREATE] PRIMARY KEY CLUSTERED
( [Id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[TSyncForeignKeysForSubscribersDROP](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PublicationName] [varchar](256) COLLATE Modern_Spanish_CI_AS NOT NULL,
[TableName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
[FKName] [varchar](1024) COLLATE Modern_Spanish_CI_AS NOT NULL,
CONSTRAINT [PK_TSyncForeignKeysForSubscribersDROP] PRIMARY KEY CLUSTERED
( [Id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Those two tables will be filled at the publisher with the information about FKs and replicated to subscribers. When that information is replicated to subscribers, it will be ready to be used by pre and post-snapshot scripts to drop and recreate the FKs. UpdateForeignKeysInformationForReplicationOnAllPublications This stored procedure is to be called at the publisher. It is a wrapper for calling to UpdateForeignKeysInformationForReplicationOnPublication for every every publication on the current database.
CREATE PROCEDURE [dbo].[UpdateForeignKeysInformationForReplicationOnAllPublications]
AS
BEGIN
DECLARE
@PublicationName varchar(256)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- We retrieve the list of publications that are published
-- inside the database we are running into.
DECLARE ListOfPublications CURSOR LOCAL FOR
SELECT publication FROM distribution.dbo.MSpublications
WHERE publisher_db = (SELECT db_name(dbid) FROM master..sysprocesses WHERE spid=@@SPID)
ORDER BY publication
-- Just iterate on them calling to the other SP that we will discuss later
OPEN ListOfPublications
FETCH NEXT FROM ListOfPublications INTO @PublicationName
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
PRINT 'Processing publication ' + @PublicationName
EXEC dbo.UpdateForeignKeysInformationForReplicationOnPublication @PublicationName
END
FETCH NEXT FROM ListOfPublications INTO @PublicationName
END
CLOSE ListOfPublications
DEALLOCATE ListOfPublications
END
UpdateForeignKeysInformationForReplicationOnPublication @PublicationName varchar(256) This stored procedure is to be called at the publisher too and does all the work of retrieving FK information in order to publish it to subscribers. It should be called everytime your schema changes or new FKs are created. It iterates on FKs defined in de database that @PublicationName belongs to, looking for those that go TO a table outside the articles of the publication or comes FROM a table that is not included in it. Once a matching FK is found, its information is saved in the replicated tables so that subscribers could use this info later.
CREATE PROCEDURE [dbo].[UpdateForeignKeysInformationForReplicationOnPublication]
@PublicationName varchar(256)
AS BEGIN
DECLARE
@TableName varchar(128),
@PKTABLE_NAME varchar(128),
@PKCOLUMN_NAME varchar(128),
@FKTABLE_NAME varchar(128),
@FKCOLUMN_NAME varchar(128),
@FK_NAME varchar(128),
@sql varchar(2048)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create a temporary table to store middle calculations
CREATE TABLE #tmpFKs (
PKTABLE_QUALIFIER varchar(1024),
PKTABLE_OWNER varchar(1024),
PKTABLE_NAME varchar(1024),
PKCOLUMN_NAME varchar(1024),
PKTABLE_QUEALIFIER varchar(1024),
FKTABLE_OWNER varchar(1024),
FKTABLE_NAME varchar(1024),
FKCOLUMN_NAME varchar(1024),
KEY_SEQ int,
UPDATE_RULE int,
DELETE_RULE int,
FK_NAME varchar(1024),
PK_NAME varchar(1024),
DEFERRABILITY int)
-- 1st step: Retrieve all outgoing FK: from articles within the publication to other tables
-- outside this publication. We will use a cursor to iterate through all those articles
-- inside the publication and retrieve any FK pointing out to any other table. Afterwards, we
-- will delete those FK that fall inside the publication articles.
PRINT 'Step 1: Inside -> Outside FKs from ' + @PublicationName
DECLARE ListOfArticles CURSOR LOCAL /*SCROLL READ_ONLY*/ FOR
SELECT DISTINCT A.destination_object
FROM distribution.dbo.MSpublications P
INNER JOIN distribution.dbo.MSarticles A ON P.publication_id = A.publication_id
WHERE P.publication = @PublicationName
ORDER BY A.destination_object
OPEN ListOfArticles
FETCH NEXT FROM ListOfArticles INTO @TableName
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
SELECT @sql = 'sp_fkeys @fktable_name = N''' + @TableName + ''''
INSERT INTO #tmpFKs EXEC(@sql)
-- From those just inserted, delete those FKs that are internal (among articles inside the
-- publication). At the end, we are just interested in those FKs pointing out to tables
-- NOT in this publication.
DELETE FROM #tmpFKs
WHERE PKTABLE_NAME IN ( -- Subselect with publication's own articles.
SELECT A.destination_object
FROM distribution.dbo.MSpublications P
INNER JOIN distribution.dbo.MSarticles A ON P.publication_id = A.publication_id
WHERE P.publication = @PublicationName
)
END
FETCH NEXT FROM ListOfArticles INTO @TableName
END
CLOSE ListOfArticles
DEALLOCATE ListOfArticles
-- 2nd step: We need to retrieve all incoming FKs: from tables NOT included in the publication
-- to articles inside it. We will retrieve all incoming FK for the tables inside the publication
-- and then remove those that fall again within tables inside it.
PRINT 'Step 2: Inside <- Outside FKs from ' + @PublicationName
DECLARE ListOfOutterArticles CURSOR LOCAL FOR
SELECT [name] TableName FROM sys.objects T
WHERE T.type='U' AND
T.is_ms_shipped = 0 AND
T.[name] NOT LIKE 'conflict_%' /* AND
T.is_published = 1 */ AND
T.[name] NOT IN (SELECT A.destination_object
FROM distribution.dbo.MSpublications P
INNER JOIN distribution.dbo.MSarticles A
ON P.publication_id = A.publication_id
WHERE P.publication = @PublicationName)
OPEN ListOfOutterArticles
FETCH NEXT FROM ListOfOutterArticles INTO @TableName
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
SELECT @sql = 'sp_fkeys @fktable_name = N''' + @TableName + ''''
INSERT INTO #tmpFKs EXEC(@sql)
-- From those just inserted, delete FKs that are completely out the publication
-- (from tables outside to tables outside)
-- We are just interested in those pointing to articles inside this publication
DELETE FROM #tmpFKs
WHERE
FKTABLE_NAME = @TableName AND
PKTABLE_NAME NOT IN ( -- Subselect with publication's own articles.
SELECT A.destination_object
FROM distribution.dbo.MSpublications P
INNER JOIN distribution.dbo.MSarticles A ON P.publication_id = A.publication_id
WHERE P.publication = @PublicationName
)
END
FETCH NEXT FROM ListOfOutterArticles INTO @TableName
END
CLOSE ListOfOutterArticles
DEALLOCATE ListOfOutterArticles
-- 3rd step: Some format and cleaning of the auxiliary table.
-- If there are FKs with more than one key column, KEY_SEQ indicates the
-- order we need follow when considering FKCOLUMN_NAME and PKCOLUMN_NAME
-- We are going to combine those records where KEY_SEQ > 1, flattening
-- FKCOLUMN_NAME and FKCOLUMN_NAME into the single record where KEY_SEQ=1
PRINT 'Step 3: Reorganize #tempFKs from ' + @PublicationName
-- First of all, we use QUOTENAME for not having problems afterwards
UPDATE #tmpFKs
SET FKCOLUMN_NAME = QUOTENAME(FKCOLUMN_NAME),
PKCOLUMN_NAME = QUOTENAME(PKCOLUMN_NAME)
-- Cursor with records belonging to FKs defined with more than one key column
-- except this very first column ( > 1 )
DECLARE ListOfKeys CURSOR LOCAL FOR
SELECT PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME
FROM #tmpFKs
WHERE KEY_SEQ > 1
ORDER BY PKTABLE_NAME, FK_NAME, KEY_SEQ
OPEN ListOfKeys
FETCH NEXT FROM ListOfKeys INTO @PKTABLE_NAME, @PKCOLUMN_NAME,
@FKTABLE_NAME, @FKCOLUMN_NAME, @FK_NAME
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
-- Update the 'main' record of this FK (the one with KEY_SEQ=1)
-- and matching the rest of variables...
UPDATE #tmpFKs
SET
PKCOLUMN_NAME = PKCOLUMN_NAME + ', ' + @PKCOLUMN_NAME,
FKCOLUMN_NAME = FKCOLUMN_NAME + ', ' + @FKCOLUMN_NAME
WHERE PKTABLE_NAME = @PKTABLE_NAME AND
FKTABLE_NAME = @FKTABLE_NAME AND
FK_NAME = @FK_NAME AND
KEY_SEQ = 1
END
FETCH NEXT FROM ListOfKeys INTO @PKTABLE_NAME, @PKCOLUMN_NAME,
@FKTABLE_NAME, @FKCOLUMN_NAME, @FK_NAME
END
CLOSE ListOfKeys
DEALLOCATE ListOfKeys
-- 4th Step: Clean and final inserts
PRINT 'Step 4: Clean & final inserts from ' + @PublicationName
-- Remove records with KEY_SEQ>1 (we don't need them anymore)
DELETE FROM #tmpFKs WHERE KEY_SEQ > 1
-- Now we have all the information we need. We just need to do de inserts
-- into TSyncForeignKeysForSubscribersDROP formatting according the information
-- in this auxiliary table. This table is to be read by the stored procedure
-- UseForeignKeysInformationAndDoDropsOnPublication thrown by a pre-snapshot script
-- at the subscriber.
DELETE TSyncForeignKeysForSubscribersDROP WHERE PublicationName = @PublicationName
INSERT INTO TSyncForeignKeysForSubscribersDROP (PublicationName, TableName, FKName)
SELECT @PublicationName, FKTABLE_NAME, FK_NAME FROM #tmpFKs
-- Now the commands to recreate those FKs after the initialization of the
-- publication is done. This table is to be read by the stored procedure
-- UseForeignKeysInformationAndDoCreatesOnPublication included in every
-- post-snapshot script.
DELETE TSyncForeignKeysForSubscribersCREATE WHERE PublicationName = @PublicationName
INSERT INTO
TSyncForeignKeysForSubscribersCREATE (PublicationName, TableName, FKName, TheRest)
SELECT
@PublicationName,
FKTABLE_NAME,
FK_NAME,
'FOREIGN KEY(' + FKCOLUMN_NAME + ') REFERENCES [dbo].' + QUOTENAME(PKTABLE_NAME) +
' (' + PKCOLUMN_NAME + ') NOT FOR REPLICATION'
FROM #tmpFKs
-- Finally drop the temporary table.
DROP TABLE #tmpFKs
-- Once executed, the tables TSyncForeignKeysForSubscribersCREATE and
-- TSyncForeignKeysForSubscribersDROP contain the updated instructions that
-- subscribers need to apply to replicate the foreign keys.
PRINT ''
END
UseForeignKeysInformationAndDoDropsOnPublication @PublicationName varchar(256) This stored procedure should be called only on subscribers, since it DROPS all FKs contained in the table TSyncForeignKeysForSubscribersDROP dealing with @PublicationName. The idea is to include this command into a pre-snapshot script for every publication, so that FKs that would create conflicts when tables should be dropped and recreated are not in place when the actual DROP TABLE command arrives (that is why it should be in a pre-snapshot script).
CREATE PROCEDURE [dbo].[UseForeignKeysInformationAndDoDropsOnPublication]
@PublicationName varchar(256)
AS
BEGIN
DECLARE
@i int,
@TableName varchar(1024),
@FKName varchar(1024),
@sql nvarchar(2048),
@IsSQL2000 BINARY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Are we running on a SQL 2000 version?
-- There must be 2 spaces before 2000%, this is not a typo
SELECT @IsSQL2000 = COUNT(*) FROM (SELECT @@version AS V) AUX
WHERE AUX.V LIKE 'Microsoft SQL Server 2000%'
DECLARE FKsToDROP CURSOR LOCAL FOR
SELECT TableName, FKName FROM TSyncForeignKeysForSubscribersDROP
WHERE PublicationName = @PublicationName
OPEN FKsToDROP
FETCH NEXT FROM FKsToDROP INTO @TableName, @FKName
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
SET @i = 0
IF @IsSQL2000<>0
SELECT @i = COUNT(*) FROM dbo.sysobjects
WHERE xtype='F' AND type='F' AND Name = @FKName
ELSE
SELECT @i = COUNT(*) FROM sys.foreign_keys
WHERE Name = @FKName
IF (@i = 1) BEGIN -- The FK is in place, we need to remove it
SELECT @i = COUNT(*) FROM sysobjects WHERE TYPE = 'U' AND Name = @TableName
IF @i > 0 BEGIN -- If the table exists, we can go on
SELECT @sql = N'ALTER TABLE ' + QUOTENAME(@TableName) +
' DROP CONSTRAINT ' + QUOTENAME(@FKName)
EXEC sp_executesql @sql
PRINT 'Successfully dropped: ' + QUOTENAME(@FKName)
END ELSE BEGIN
PRINT 'No action done: ' + QUOTENAME(@TableName) + ' doesn''t exist.'
END
END ELSE BEGIN -- The FK is not there, do nothing
PRINT 'No action done: ' + QUOTENAME(@FKName)
END
END
FETCH NEXT FROM FKsToDROP INTO @TableName, @FKName
END
CLOSE FKsToDROP
DEALLOCATE FKsToDROP
END
UseForeignKeysInformationAndDoCreatesOnPublication @PublicationName varchar(256) This stored procedure is to be called on subscribers. It uses the information stored in TSyncForeignKeysForSubscribersCREATE to issue CREATE commands for FKs related to @PublicationName. It should be inserted in every post-snapshot script so that FKs are recreated after the initial snapshot is sent.
CREATE PROCEDURE [dbo].[UseForeignKeysInformationAndDoCreatesOnPublication]
@PublicationName varchar(256)
AS
BEGIN
DECLARE
@i INT,
@FKTableName varchar(1024),
@PKTableName varchar(1024),
@FKName varchar(1024),
@TheRest varchar(1024),
@sql nvarchar(2048),
@IsSQL2000 BINARY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Are we running on a SQL 2000 version?
-- There must be 2 spaces before 2000%, this is not a typo
SELECT @IsSQL2000 = COUNT(*) FROM (SELECT @@version AS V) AUX
WHERE AUX.V LIKE 'Microsoft SQL Server 2000%'
DECLARE FKsToCREATE CURSOR LOCAL FOR
SELECT FKTableName, PKTableName, FKName, TheRest FROM TSyncForeignKeysForSubscribersCREATE
WHERE PublicationName = @PublicationName
OPEN FKsToCREATE
FETCH NEXT FROM FKsToCREATE INTO @FKTableName, @PKTableName, @FKName, @TheRest
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
SET @i = 0
IF @IsSQL2000<>0
SELECT @i = COUNT(*) FROM dbo.sysobjects
WHERE xtype='F' AND type='F' AND Name = @FKName
ELSE
SELECT @i = COUNT(*) FROM sys.foreign_keys
WHERE Name = @FKName
IF (@i = 0) BEGIN -- If the FK is not in place, we will try to create it
SELECT @i = COUNT(*) FROM sysobjects
WHERE TYPE = 'U' AND (Name = @FKTableName OR Name = @PKTableName)
IF @i = 2 BEGIN -- If both tables exist, we can go on
SELECT @sql = N'ALTER TABLE ' + QUOTENAME(@FKTableName) +
' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(@FKName) + ' ' + @TheRest
EXEC sp_executesql @sql
SELECT @sql = N'ALTER TABLE ' + QUOTENAME(@FKTableName) +
' CHECK CONSTRAINT ' + QUOTENAME(@FKName)
EXEC sp_executesql @sql
PRINT 'Successfully created and applied: ' + QUOTENAME(@FKName)
END ELSE BEGIN
PRINT 'Nothing done, tables do not exist: ' + QUOTENAME(@FKTableName) +
' or ' + QUOTENAME(@PKTableName)
END
END ELSE BEGIN -- If it is not there, do nothing
PRINT 'No action done, already in place: ' + QUOTENAME(@FKName)
END
END
FETCH NEXT FROM FKsToCREATE INTO @FKTableName, @PKTableName, @FKName, @TheRest
END
CLOSE FKsToCREATE
DEALLOCATE FKsToCREATE
END
Download: You can also download the scripts described in this article.
Keywords: transactional replication, queued updates, foreign keys, SQL Server 2005, Microsoft, bug, error, workaround, resolution, transact-sql, stored procedure, replicate foreign keys to subscribers
