0

We need to migrate a database from SQL Server to an Azure SQL Database. In this database we do use rowversions (timestamp) extensively for synchronisation purposes with other systems. It is therefore important that these rowversions remain unchanged.

Normally, restoring a database using the regular BAK file will preserve all rowversion values. However, I do not think it is possible to use these backup files to restore the data in Azure SQL Database.

I have tried a few ways of migrating the data, all resulted in new rowversion values. Tried methods:

  • Migrate using SQL Management Studio (option: Deploy Database to Microsoft Azure SQL Database...)
  • Using Microsoft Data Migration Assistant tool
  • Import a bacpac file using the Azure Portal

Is there a way to migrate a database from a SQL Server to an Azure SQL Database, while preserving the rowversions? And if not, is there a way to bumb up the database timestamp (@@DBTS) so we can make sure new rowversions are always larger in value than the original rowversion value?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Chris
  • 323
  • 2
  • 12
  • 1
    Migrating the rowversions as-is wouldn't make sense (from the engine's point of view) because there would be no way to ensure the database's internal timestamp is consistent with these values -- this only makes sense with a restore. It is possible to restore a SQL Server backup to Azure, by putting the backup in the cloud -- [here's an article detailing that](https://stevethompsonmvp.wordpress.com/2018/03/12/backup-sql-server-to-an-azure-storage-account/) (disclaimer: not tested it myself and possibly outdated). – Jeroen Mostert May 31 '21 at 09:28
  • @JeroenMostert Thanks for your reply. The problem is that Azure SQL Databases do not support regular backup and restore commands. So unfortunately it is not possible to restore a backup this way into the Azure SQL DB. In the [feature comparison chart](https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison) of MS at the _Restore_ line they state for Azure SQL Database '_No_'. For Azure SQL Managed Instance they state '_Yes, with mandatory FROM URL options for the backups files placed on Azure Blob Storage_.' – Chris May 31 '21 at 14:34
  • Well, that's unfortunate then. As for bumping the `@@DBTS`: there is no way to do this explicitly, and doing it by starting a lot of (data-modifying) transactions could take a *long* time, even though that is the only way I can think of. You may want to consider either 1) shelling out for a managed instance or 2) taking this opportunity to think about ways to transition away from `rowversion` for this, like replacing the columns with a true (DB-independent) timestamp, or `BINARY(8)` that is filled with `@@DBTS` with a trigger, or a checksum. – Jeroen Mostert May 31 '21 at 14:46
  • you could restore into Azure SQL DB Managed Instance. The timestamps are not guaranteed to line up, as previously mentioned, but they would not change on a physical restore. You could also examine other models for synchronization, from CDC to replication to even temporal tables, if that would give you an alternative way to sync changes to other systems. – Conor Cunningham MSFT Jun 02 '21 at 13:34

0 Answers0