In the previous post I wrote, I explained how to setup replication from an on-premise SQL Server instance to an Azure SQL database. While doing this, I came across a very strange issue (or maybe even bug) when setting up replication.
The problem child
After working on reproducing the issue for a day, and trying to reduce the issue to a small-scale problem, I came to the conclusion that the problem was (probably) caused by a single primary key on a table in the database:
CREATE TABLE dbo.BuggedTable ([Day] DATE NOT NULL, SomeId VARCHAR(50) NOT NULL, Amount INT NOT NULL, CONSTRAINT PK_BuggedTable PRIMARY KEY CLUSTERED ([Day] ASC, SomeId ASC ) ) GO
Creating publication & subscriber
The setup of the publication and subscriber wasn’t that difficult. As I said before, there are a few things you need to configure differently then you would do for SQL Server to SQL Server replication.
So I won’t talk you through the whole process again, but refer you to the articles instead. Continue reading Replication: Snapshot Agent fails on date conversion