1

We have an on-premises SQL server 2016 with several production databases that we are moving to SQL Azure. These databases each have quite a few tables that use identity columns. Our thought to move to Azure with minimal downtime was to replicate the databases to Azure and then "cut" the replication and point the applications to Azure copy. Because of application inter-relationships all of the databases need to move at the same time.

Our sticking point right now is the identity columns. When the tables are copied to Azure the identity attributes are removed from the columns. The only scriptable option I have seen is to copy the tables using a methodology similar to this https://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/. Besides being tedious, this will add time to the cutover. The only other option I have discovered is to not use replication, but use the "deploy database to Azure SQL database" option in SSMS. This also is time consuming and manual.

If we can get the replication to occur retaining the identity attributes, we would enforce by business rule that the updates would only occur on the on-premises database until cutover.

Have I missed anything?

Thanks in advance, Eric Logsdon.

1
  • Have you considered log shipping? Jul 9, 2021 at 21:14

2 Answers 2

1

In going through all of the article options and our scripts, we discovered that the sp_changearticle that was being used to set some options did not include option 0x04, 'Identity columns are scripted using the IDENTITY property.' So this was a self inflicted wound.

0

When the tables are copied to Azure the identity attributes are removed from the columns.

That is not the expected behavior of Replication. The identity columns should be created on the subscriber with the NOT FOR REPLICATION option which allows the replication agents to insert the values generated on the publisher.

2
  • I agree, that is not what I expected to see. Looking at the column properties in Table Designer (SSMS), the source table ID field shows Is Identity Yes, the target shows Is Identity No. Jul 12, 2021 at 18:09
  • I just ran through setting up Replication to Azure SQL Database, and the IDENTITY columns were created properly. Jul 12, 2021 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.