SQL Azure Migration Wizard - Duplicate Primary Key

Question

Just did a test at migrating a 120gb database onto RDS using SQL Azure Migration Wizard

Because we can't afford any downtime for "just" a test we ran the tool on the live database (meaning data was still getting inserted/updated/deleted while the tool was running)

Scripts were created ok but during the insertion of data we got the error "BCP copy in failed" "Cannot insert duplicate key"

My intuition is because the data was live when the .dat file got created, it somehow read the same row twice, but I don't really want to make an assumption I might regret when we will do the real migration, when the database will obviously be offline.

Could there be any other reason why this has happened and can I assume I'm safe using the tool on the "big day" with a read-only database?

-- Update --

We have tried again to run exact same .dat file of the table that failed and it worked. Which seems to indicate the duplicated key is not in the script... I'm even more confused, could it be a bug in the tool?

Answer 1

I ran into the same problem when trying to use SQL Migration Wizard to create a local copy of my database. I know my source data didn't have duplicate key values, so I came up with the following script. It simply causes duplicate key entries to be ignored (and a warning is generated instead of error). Please read the comments in the code block below for instructions.

-- For some reason, SQL Azure Migration Wizard tries to insert duplicate records and fails.
-- I've wasted a lot of time making many many many attempts at copying the database, failing repeatedly, 

-- So this is the workaround:
-- 1.0 In SQL Azure Migration Wizard: After selecting the database to script, select the "Advanced" button.
-- 1.1 Change the "Script Table / Data" property from "Table Schema with Data" to "Table Schema Only".
-- 1.2 Click "Next" and proceed until the "Schema Only" migrate script has been executed a new database. 

-- 2. Run THIS Sql script with @IGNORE_DUP_KEY set to 'ON' against the TARGET database.

-- 3.0 Back in SQL Azure Migration Wizard, click "Back" until you see the advanced button again, and click it.
-- 3.1 Change the "Script Table / Data" property from "Table Schema with Data" to "Data Only".
-- 3.2 Click "Next" and proceed until the script has been executed.  NOTE: Table Name may still appear red like it failed, but looking at the message will show you that it was just a warning.  
--     TO VERIFY: "SELECT COUNT(*) FROM TableName" on any tables that had the warning show in the source and target databases, and compare the counts.

-- 4. Run THIS Sql script, but set @IGNORE_DUP_KEY to 'OFF' to revert back to original/expected behavior.
DECLARE @IGNORE_DUP_KEY VARCHAR(MAX) = 'ON'; -- Options: ON, OFF

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'ALTER TABLE ' + @TableName + ' REBUILD WITH (IGNORE_DUP_KEY = ' + @IGNORE_DUP_KEY + ');'
        --print @sql
        EXEC (@sql)

        FETCH NEXT FROM TableCursor INTO @TableName
    END

CLOSE TableCursor
DEALLOCATE TableCursor

GO

I know this solution is being posted some time after the original question was asked, but hopefully this will help someone else out!

Answer 2

If your first test showed a problem, assume that there is a problem!
Problems don't just disappear on production, even if you have the best possible theory for their existence, you still need to test.

BCP copy should be using row-level locks by default.
Which should escalate to page-level locks due to the size of your data.

If despite these locks, you are getting duplicate ID's, there is something wrong with the consistency of the data obtained by the BCP utility.
There could be an issue with the identity_insert trigger. But without seeing the data and table structure it's hard to tell.

You should really test again with a copy of your production database, there is no reason to test it with the live database.
If anything, your first test failing should be ample incentive for your manager to allow you to test further.