Bug: System-versioned history tables in wrong schema

a.higgins

I've got a project that's comparing a pair of databases. One database has the relevant data in the 'dbo' schema; the other puts it into the 'X' schema.

I've used the Owner Mapping section of the Project Options dialog box to map Schema X to Schema Y.

Both database contain a table, SomeTable, with system versioning on. History is being stored in a temporal table named "SomeTable_TemporalHistory".

When I run SQL Compare, it correctly maps dbo.SomeTable over to X.SomeTable. However, it incorrectly maps dbo.SomeTable_TemporalHistory over to a new table, dbo.SomeTable_TemporalHistory.

If I try to run the script, it then tries to create the history table on the 'dbo' schema, rather than correctly interpreting it as the 'X' schema.

I believe this is a bug, and not a configuration error, since it's putting the main table in the right schema but the history table in the wrong schema (not respecting the 'Owner Mapping' choices I've made).

Mark J

Thanks for the feedback - this is actually something we're hoping to look at soon. Our current implementation of temporal table support in SQL Compare doesn't preserve any customisation on the history table, such as putting it into a different schema (like the case here) or adding extra indexes and constraints (which some of our other customers have run into). We'll be looking to see if we can improve the situation so that we do a better job here, although we want to try and avoid complicated dependency issues that could arise if we start treating the history table as another first-class table.

a.higgins

That would be great, Mark: both of those sound like really important features.

Am I understanding you correctly in saying that you would regard creating both the base and history tables in a non-default schema to be a customization? I'm not clear on whether the problem is caused by the fact that the tables aren't in the 'dbo' schema, or by the fact that I used Owner Mapping to put the tables in a different target schema than the source.

Mark J

Honestly I'm not exactly sure what's causing your problem at the moment (it's been some time since we implemented temporal table support so the code isn't particularly fresh in my head) but I think the cause is to do with the way that SQL Compare currently just reads the name of the history table and deploys that, rather than treating the history table as an independent object with its own properties.

I think you're probably right in that this case it's more likely to be a problem with mappings than a problem with customisation as such, but if we have the history table as its own thing in the code then we're more likely to be able to apply mapping to it properly.

Either way, I'll try and make sure we do something to improve this situation

StuartM

I'm running into this issue now in version 13.3.5.6244. Adding custom nonclustered indexes to the history tables is not being picked up on a compare. Assuming this is still an issue?

sam.blackburn

I don't think we've changed anything to do with indexes on history tables since the 13.1.8 release - could you contact support@red-gate.com so we can reproduce your issue?