The Microsoft In-Memory OLTP feature was introduced with SQL Server 2014 as a way to improve transaction performance. It includes two parts:
The main advantage of memory-optimized tables is that rows in the table are read from and written to memory which leads to non-blocking transactions at high speed. The second copy of the data is stored on the disk and during database recovery, data is read from the disk-based table.
When synchronizing databases containing memory-optimized objects with Schema Compare, it is highly recommended to conduct synchronization in two steps.
1. Synchronize the memory-optimized objects with the option Use a single transaction disabled.
2. Synchronize the remaining objects using a single transaction.
Otherwise, you might lose data if the synchronization script fails. Memory-optimized objects can’t be synchronized inside a transaction, since the execution of ALTER, DROP, and CREATE DDL statements is not supported within a user transaction. If a synchronization script doesn’t use a single transaction and the script fails, it won’t be possible to roll it back and you might lose data. Thus, in case you perform two synchronizations, only the memory-optimized objects are synchronized outside a transaction, so you’re less likely to lose data.
Synchronizing memory-optimized objects with Schema Compare should be conducted with the option Use a single transaction disabled.
To synchronize memory-optimized objects via Schema Compare:
1. In the New Schema Comparison wizard, specify your source and target databases.
2. Configure and run the comparison.
3. In the comparison results, select the memory-optimized objects for synchronization.
4. Start the Schema Synchronization Wizard.
5. In the Transactions sections on the Options tab, select to disable Use a single transaction option.
6. Run the synchronization.
Note
It is highly recommended to back up the target database before synchronizing with the option Use a single transaction disabled.
The remaining objects should be synchronized with the option Use a single transaction enabled.
1. In the comparison results, select the remaining objects for synchronization.
2. Open the Schema Synchronization Wizard.
3. In the Transactions sections on the Options tab, select to enable Use a single transaction option.
4. Run the synchronization.