deploying temporal tables to azure managed instance

kpakala

Hi

I am trying to deploy existing database schema (source controlled in GIT) to a new Azure managed instance database which is empty.

Here is the azure sql version details : "Microsoft SQL Azure (RTM) - 12.0.2000.8   Dec  4 2019 21:24:18   Copyright (C) 2019 Microsoft Corporation" 

I have some temporal tables (system versioned) which are not getting deployed correctly.

When I checked the deployment script, noticed that the period column i.e. "PERIOD FOR SYSTEM_TIME (SYS_START_TIME, SYS_END_TIME)" is missing in the create table query :

CREATE TABLE [foo].[bar]
(

[table_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[field_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
...
...

[SYS_START_TIME] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

[SYS_END_TIME] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
)

This is causing the temporal tables being created as two separate tables one for the main and the other one for history.

Note: When we promote changes between dev,staging & prod instances on the onprem mssql database, temporal tables do  get created correctly.

Any inputs on what might be causing this ?

regards
kiran

KarlB

Hi Kiran,

It appears this may have come up previously by another user.

I've attempted to replicate this here at redgate using the same Azure DB version and the latest version of SQL Compare and am not able to reproduce the same behavior where it was creating two tables for you.

Can I suggest upgrading to the latest version of SQL Compare - Click Here - released 3 days ago and retrying from your side please.

Another behavior to note which I learnt whilst checking this was that at this time Azure DB subscription must be S3 (Standard level 3) or higher.

kpakala

thanks, will check and get back to you

kpakala

As Karl mentioned upgrading to SQL Compare 14 fixed the issue.

But I ran into another issue i.e when I am generating the scripts with Azure managed instance DB as source, the temporal table/system versioned create table scripts are not getting generated correctly. I am using "SQL Source Control" version  7.1.5.10110.

Thanks
Kiran

KarlB

Hi Kiran,

It appears this may have come up previously by another user.

I've attempted to replicate this here at redgate using the same Azure DB version and the latest version of SQL Compare and am not able to reproduce the same behavior where it was creating two tables for you.

Can I suggest upgrading to the latest version of SQL Compare - Click Here - released 3 days ago and retrying from your side please.

Another behavior to note which I learnt whilst checking this was that at this time Azure DB subscription must be S3 (Standard level 3) or higher.

KarlB

Hi Kiran,

Great to hear that the initial issue has been solved! I'll solve the case for this one and will open a new one for you on my system regarding the 2nd issue, using this thread for the discussion for the 2nd query,

Regarding the temporal table/system scripts not being created correctly, what seems to be wrong with the script files and do you have an example you could share?