SSCA with a database containing memory optimized tables

Sebazzz

We're trying out SQL Change Automation and have an existing database that has memory optimized tables.

I went to the steps of setting up the project - I choose no baseline for now.

Then I generated and committed the first migration script.

After that the following message is shown and SSCA is unusable:

Is this a known issue in SSCA? What are the workarounds and is this going to be fixed?

Kendra_Little

Hello,

SQL Change Automation uses a local database to validate that all your migration scripts can run against a fresh environment successfully  -- this is called the shadow database.

What's happening here is that when the commands to create a memory optimized table run against the shadow, SQL Server raises an error because that shadow database hasn't been configured for memory optimized tables.

There are two different ways you can handle this:

Approach 1 - Clone as Baseline
If you are also doing a trial of SQL Clone and/or already have Redgate's SQL Clone, you can use the "Clone as Baseline" feature. 

With this approach, an image of the production database (which you may mask if you're using Data Masker) is used as the "starting point" for the shadow. All of the properties of the production database, like the configuration you have for memory optimized tables, are already present in that image / clone, so they carry through and this automatically works. 

(Note: with this approach you would only need to create migration scripts for changes you want to make, you wouldn't need to create a migration script for existing objects at all.)

Approach 2 - Pre-Deployment Scripts

If clone as baseline isn't right for you, then you can use a pre-deployment script to configure database settings (like enabling memory optimized tables) for the verification and build process, which are run against empty databases.

With this approach, variables will be very useful.  You will likely want to:

• Have "USE $(DatabaseName) " at the top of the pre-deployment script to set the context to the shadow
• Use the $(IsShadowDeployment) variable (or some other method of your choice) to ensure that the pre-deployment script only runs against the correct environments. Usually folks only want the database configuration scripts to run against verify and build environments.

Note: If you are working with a Pre-Sales engineer as part of this process, they are very skilled at helping folks with this as well.

Hope this helps,
Kendra