I have a problem with my transactional replication when I use articles filters and delete operation. When I insert the new data into the publisher all the data that meet the specified filter is inserted to the subscriber - everything works fine. When I delete some data from the publisher not all data that meet the specified filter is deleted from the subscriber and I don't know why.
I have three servers:
A – publisher (Microsoft SQL Server 2012),
B – distributor (Microsoft SQL Server 2012),
C – subscriber (Microsoft SQL Server 2012).
There are six tables in my publisher database. Each table has primary key and some of them have foreign key as shown in the attached picture:
Only the first manufacturer (manufacturer ID=1) data I want to replicate from the publisher to the subscriber, so I use these filters:
SELECT <published_columns> FROM [dbo].[Manufacturer]
WHERE [dbo].[Manufacturer].ID IN (Select M.ID from [dbo].[Manufacturer] AS M where M.ID = 1)
SELECT <published_columns> FROM [dbo].[Catalog]
WHERE [dbo].[Catalog].ID IN (Select C.ID from [dbo].[Catalog] AS C
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)
SELECT <published_columns> FROM [dbo].[Cars]
WHERE ID IN (Select CA.ID from [dbo].[Cars] AS CA
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)
SELECT <published_columns> FROM [dbo].[CarParts]
WHERE ID IN (Select CP.ID from [dbo].[CarParts] AS CP
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)
SELECT <published_columns> FROM [dbo].[Parts]
WHERE ID IN (Select P.ID from [dbo].[Parts] AS P
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)
SELECT <published_columns> FROM [dbo].[PartDetails]
WHERE ID IN (Select PD.ID from [dbo].[PartDetails] AS PD
INNER JOIN [dbo].[Parts] AS P ON P.ID = PD.PartID
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)
When I insert the new data into the publisher CarParts table, Parts table or PartDetails table all the data that meet the specified filter (manufacturer ID=1) is inserted to the subscriber.
When I delete data from the publisher CarParts table, Parts table or PartDetails table not all data that meet the specified filter (manufacturer ID=1) is deleted from the subscriber - only deletes data from CarParts table, but not from Parts table and PartDetails table.
The same filter is used for insert and delete operations. Where could be the problem? Maybe it is Microsoft SQL Server 2012 Bug?