Sql-server – SQL Azure Migration Wizard – Duplicate Primary Key

amazon-rdsbcpmigrationsql serversql-server-2012

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?

Best Answer

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!

Related Question