I am not sure if this was an intentional compatibility break for SQL Server 2012, but I have not been able to find any docs on it, so I am going to post it here to see.
We upgraded our SQL Server that publishes reference data to other servers. The upgrade moved us from SQL Server 2008 R2 to SQL Server 2012.
After the upgrade we noticed that some of our replications would break because tables that were designed to never delete rows were trying to replicate deletes.
After a lot of research I found that my SSIS package that populates the source table every night was starting the chain of events that lead to the deletes. This SSIS package fills a stage table, then does a MERGE to the "live" data. (The idea is that any changes to data will then be replicated to the subscribing databases. This all worked perfectly with SQL Server 2008 R2.)
The SSIS Package is very careful to never delete any rows. Any NOT MATCHED on Source rows have an IsActive column set to false (not a DELETE).
However, after upgrading to SQL Server 2012, this merge statement would cause every row in the table to try to publish a delete and an insert statement to the subscribers.
In an attempt to get to the point of this issue I need to define a scenario that can cause a Deferred Update:
If a MERGE statement has the clustered key in an UPDATE statement (in the MATCHED clause) then every row affected will post as a Deferred Update (Causing a Delete and an Insert to be published to subscribers).
However, new in SQL Server 2012, is the fact that even if the value of the clustered key DOES NOT CHANGE it will still replicate as a Deferred Update. Infact, even if nothing in the row has changed at all, the row will still replicate a delete and an insert.
I tested this with traces an found it to be true and totally reproduceable every time.
So, I have 3 questions:
- Has anyone else seen this? (Can it be reproed by more than just me?)
- Is it a Bug?
- Is there a way around it (besides using a compatibility mode)?
(NOTE: Trace Flag 8207 will only work if you are updating a single row, my merge statement affects the whole table.)
For now we removed the Clustered Key (Primary Key) from the update part of the merge statement. (It never, ever, changes, so we did not need it there anyway.) But I would still like to know what is going on here.
- Edited by Vaccanoll Friday, May 31, 2013 4:20 PM
I have not seen this, but can I see the schema of the table and the merge statement? You may need to run profiler to capture it.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
Below is the table schema and merge statement.
I was told that I can post a "De-Identified" version. This is very close to the real thing, but has had tables, schemas and columns renamed and a few unrelated columns removed.
It should be functionally the same thing. If you need the real one, let me know and I will try to get approval for it.
CREATE TABLE [dbo].[Order]( [OrderId] [bigint] NOT NULL, [OrderName] [varchar](100) NULL, [OrderNumber] [char](7) NOT NULL, [ShipTypeId] [int] NOT NULL, [OrderTypeId] [int] NULL, [OrderSubTypeId] [int] NULL, [CreatedWhen] [datetime] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ( [OrderId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_OrderSubType] FOREIGN KEY([OrderSubTypeId]) REFERENCES [TestRefCodesMaster].[OrderSubType] ([OrderSubTypeId]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_OrderSubType] GO ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_OrderType] FOREIGN KEY([OrderTypeId]) REFERENCES [TestRefCodesMaster].[OrderType] ([OrderTypeId]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_OrderType] GO ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_ShipType] FOREIGN KEY([ShipTypeId]) REFERENCES [codes].[ShipType] ([ShipTypeId]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_ShipType] GOMerge Statement
merge [dbo].[Order] as target using (select OrderId, OrderName, OrderNumber, OrderTypeId, OrderSubTypeId, TestCreatedWhen, IsActive, ShipTypeId from Stage.OrderStage) as source (OrderId, OrderName, OrderNumber, OrderTypeId, OrderSubTypeId, TestCreatedWhen, IsActive, ShipTypeId) on (target.OrderId = source.OrderId) when MATCHED then update set OrderId = source.OrderId, OrderName = source.OrderName, OrderNumber = source.OrderNumber, OrderTypeId = source.OrderTypeId, OrderSubTypeId = source.OrderSubTypeId, CreatedWhen = source.CreatedWhen, IsActive = source.IsActive, ShipTypeId = source.ShipTypeId when NOT MATCHED BY SOURCE then update set IsActive = 0 when NOT MATCHED then insert (OrderId, OrderName, OrderNumber, OrderTypeId, OrderSubTypeId, CreatedWhen, IsActive, ShipTypeId) values (source.OrderId, source.OrderName, source.OrderNumber, source.OrderTypeId, source.OrderSubTypeId, source.CreatedWhen, source.IsActive, source.ShipTypeId);
Stage.OrderStage is the same as dbo.[Order] but it has no keys or constraints of any kind. And all columns are nullable.
- Edited by Vaccanoll Friday, May 24, 2013 10:10 PM
This is not a "Support" forum, but a community forum. If you want MS official support on this issue, you must contact MS directly http://support.microsoft.com/ph/1044.
However I can tell you I have not noticed this issue and use MERGE extensively. Did you install SQL 2012 SP1? Please post the results of SELECT @@VERSION.
- Edited by Tom Phillips Friday, May 31, 2013 9:51 PM
@ Tom Phillips - Thanks for the reply.
Microsoft has an MSDN Subscriber benefit that states:
As an MSDN subscriber, you receive priority support in select MSDN Forums. With this benefit, a Microsoft engineer will respond within 2 business days to your posting if the community hasn't already gotten you the answer.
This form (Sql Server Replication) is listed as one of the "select" MSDN Forums. And it has been a week since I posted...
(Note, I am not trying to be difficult here, just responding with what the MSDN benefit page says.)
SELECT @@VERSION of my development server
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
The error happened in my Dev, Test and Prod environments. The result of the query was the same on the publishing server and the subscribing server. (They are both on SQL Server 2012 SP1.)
- Edited by Vaccanoll Friday, May 31, 2013 10:08 PM
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.
If you have any feedback on our support, please click here.
TechNet Community Support
From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
If Microsoft determines that a problem is the result of a defect in a Microsoft product, you will not be charged for that incident.
From a support perspective this is really beyond what we can do here in the forums.
So that I don't waist your time and mine on future questions, what is the criteria for a "forums eligible" question?
Was it too hard to reproduce? (If you tried to reproduce it and could not, I would like to hear that.)
If you did not try to reproduce it, I am wondering why not. I know replication is a bit involved to set up, but this is a forum devoted to replication questions.
Is there some other reason that makes it something you cannot answer in the forums?
Either way, I don't have the time to go through a Support run. I have found a work around that hides this issue for me. If your team does not want to look into it, I guess you can leave it for someone else to find.
I imagine as more people upgrade to SQL Server 2012, someone else will run into this issue and be willing to go through support to get it fixed. I will try to watch the service packs or future versions for a fix.