When you deploy a database containing memory-optimized objects (ie memory-optimized tables, natively compiled stored procedures and memory-optimized table types) with SQL Compare, we recommend you run two deployments:

1. Deploy the memory-optimized objects without transactions
2. Deploy the remaining objects with transactions

If you don't run two deployments, you might lose data if the deployment script fails. This is because memory-optimized objects can't be deployed inside a transaction. If a deployment script doesn't use transactions and the script fails, it won't be rolled back and you could lose data. If you perform two deployments, only the memory-optimized objects are deployed outside a transaction, so you're less likely to lose data.

Example

1. Deploy the memory-optimized objects without transactions

1. In SQL Compare, specify your source and target databases.
   In this example, WidgetStaging is our source database and WidgetProduction is our target database.
   
2. In Options, select Do not use transactions in deployment scripts:
   
3. Run the comparison.
4. In the comparison results, select the memory-optimized objects for deployment.
   In this example, WidgetStaging includes two memory-optimized tables, ShoppingCart and UserSession, and a natively compiled stored procedure, usp_AssignCart:
   

5. Start the deployment wizard.

6. Make sure the Create a deployment script deployment method is selected.

   When you deploy without transactions, we recommend you back up the target database before you deploy. For more information, see Backing up before deployment.

7. Finish the deployment wizard to open the deployment script in Management Studio.

   If the deployment includes memory-optimized tables or natively compiled stored procedures, any associated server DDL triggers and server event notifications need to be dropped and recreated. SQL Compare doesn't support server level objects, so you need to manually edit the script to include the SQL to drop and recreate them.

8. Run the script to deploy the objects.

2. Deploy the remaining objects with transactions

1. In SQL Compare, click Edit Project.
2. In Options, clear Do not use transactions in deployment scripts and click OK.
3. Deploy all the objects.