Using with Always Encrypted fields, and with multi-tenant database

Jovie

1) Can I use SQL Data Compare on tables which contain Always Encrypted fields?  (I do not expect to be able to compare/update the encrypted fields themselves, just the non-encrypted fields)

2) Is there any feature that would allow comparison only for a single tenant in a multi-tenant database?  Something like, "only compare rows where TenantId = 1 in both tables" and don't compare/update any other rows.

Dan_J

Hi @Jovie

Thanks for reaching out to us regarding this.

I have engaged with our development team who have conducted some testing on the following scenario:

1) We created two different SQL Server 2019 instances, each with a copy of the Adventure Works sample database. For one of these, we set up with Always Encrypted (AE) encryption for one column of the Production.Product table (using the Always Encrypted Wizard in SSMS). Note that this column was not in the primary key of that table.

We initially attempted a 'default' comparison (i.e. without explicitly deselecting any tables, views or columns from the comparison) between these two live databases which failed. However, after deselecting that particular column in the Production.Product table from the comparison project settings (via the "Tables & Views" tab), we were able to:

- Successfully run comparisons, with the AE-database as the source or as the target;

- Edit a row (i.e. update an existing row) from that table of the non-AE-database, in a column different to the primary key and different to the column corresponding to the encrypted one, compare it with the AE-database as the target, and then deploy that difference to the AE-database; and,

- Edit a row (i.e. update on existing row) in that table of the AE-database, in a column different to the primary key and different to the encrypted one, compare it with the non-AE-database as the target, and then deploy that difference to the non-AE-database.


2) Please correct us if we are misunderstanding your question here, but we think you are picturing the TenantId as a column in the tables? If so, then filtering the comparison with a WHERE clause ( https://documentation.red-gate.com/sdc/setting-up-the-comparison/filtering-the-comparison-with-a-where-clause ) should help you here.


I hope this helps!

Jovie

Thank you VERY much for this thorough answer, this is exactly the answer I was hoping for. 

Dan_J

Hi @Jovie

That's great to hear, I'm very glad we could help!