remote_data_archive_migration_state

alexjf

I can no longer compare any of my SQL Azure v12 databases using SQL Compare 11.5 as of 19th February.

The error I get says:

Invalid column name 'remote_data_archive_migration_state'.

They did all compare yesterday, so I assume it's an update that Microsoft has rolled out over night. It's really worrying not being able to compare them though.

Will this be fixed in a minor release soon?

Mark J

Hi there

Thanks for the report - I'm just looking into this now.

Unfortunately I don't seem to be able to reproduce it from here - I've just re-run all our azure v12 tests and they seem to be happy. The column we're looking for is in sys.tables - what columns do you see there at the moment?

alexjf

Hi Mark, thanks for the quick reply.

@version I get

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 17 2016 01:00:11 Copyright (c) Microsoft Corporation

Just for some further information, we're in the Australia East data center, maybe due to us being in the furthest ahead time zone we were released with a version before other data centers? On your v12 databases that you run tests against can you see if you're running the same version?

The columns in my sys.tables for my production database are:

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row is_tracked_by_cdc lock_escalation lock_escalation_desc is_filetable is_memory_optimized durability durability_desc temporal_type temporal_type_desc history_table_id is_remote_data_archive_enabled is_external

The columns in my sys.tables for the master database are:

name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row is_tracked_by_cdc lock_escalation lock_escalation_desc is_filetable is_memory_optimized durability durability_desc temporal_type temporal_type_desc history_table_id is_remote_data_archive_enabled is_external

There doesn't look to be a remote_data_archive_migration_state column.

I can only assume it's a Microsoft update because everything compared fine the day before and nothing compared today, but no changes were actually rolled out from me on either day.

alexjf

I tried to sync again this morning and it has worked, however it looks like MS has rolled back the version of SQL Azure.

@version I get this:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 9 2016 00:13:50 Copyright (c) Microsoft Corporation

as opposed to what I was getting on the 19th of February which was:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 17 2016 01:00:11 Copyright (c) Microsoft Corporation

Notice that the version numbers are the same but the date is different...

So to confirm - I can currently sync with 11.5, however I'd be wary that they might upgrade again and cause this to break my removing the column I mentioned.

Is this something you guys should follow up with Microsoft?

Mark J

Interesting - on our test instance I get

Microsoft SQL Azure (RTM) - 12.0.2000.8 Jan 30 2016 11:11:59 Copyright (c) Microsoft Corporation

Thanks for the update - good to hear that it's working for you again. I believe we are currently asking Microsoft about what happened here, so hopefully we'll have a bit more warning next time

alexjf

Hi Mark,

Unfortunately it's happening again today. I can no longer sync any of our SQL Azure databases in the Australia East data-center.

Today the version build date has changed again, but the version number, yet again, remains the same whilst the column no longer exists. This is extremely frustrating

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 21 2016 21:14:02 Copyright (c) Microsoft Corporation

The column remote_data_archive_migration_state has yet again been removed.

Is this something that one of your future releases will be able to cater for? Because not being able to sync is extremely frustrating, however I do totally understand that this isn't Red Gates fault.

What's more worrying than the whole thing is the fact that the version number remains the same, however entire columns are being dropped. IMHO that warrants a version increment, unfortunately trying to get hold of anyone who can help within the Azure team seems to be an impossible task.

Can you recreate this problem if you use the Australia East data-center?

Mark J

Ok, I'll try creating an Australia East Sql server instance and see if that makes a difference.

In the meantime, I'm curious what you see for the following query:

SELECT
(@@microsoftversion / 0x1000000) & 0xff AS VersionMajor,
(@@microsoftversion / 0x10000) & 0xff AS VersionMinor,
@@microsoftversion & 0xffff AS BuildNumber

SELECT SERVERPROPERTY('productversion') AS ProductVersion

On our test server we get:

VersionMajor VersionMinor BuildNumber
------------ ------------ -----------
13           0            702

(1 row(s) affected)

ProductVersion
------------------------
12.0.2000.8

(1 row(s) affected)

@microsoftversion instead...

Mark J

Unfortunately it looks like I can't create an Australia East instance with my subscription:

alexjf

Hi Mark,

When I run your query I get the following:

13.0.703
12.0.2000.8
Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 21 2016 21:14:02 Copyright (c) Microsoft Corporation

The build number is different.

I actually thought you may have trouble creating a database in the Australia East data center. If you private message me your static IP address I'll create a test database and server and give you access to it in my subscription.

Microsoft are definitely throwing some spanners in the works with their versioning. Really looking forward to a solution to this

larsnikolajsen

We are experiencing the same issues as of yesterday in the western europe datacenter

winzenried

I have exactly the same prolem with an azure db in West-EU

pluscode

Same here, also in western europe datacenter. Verry worrying...

Mark J

Good news! It looks like our test instance has now received this change, so we should be able to reproduce and figure out how to handle it now (and any other changed columns that might have sneaked in as well Hopefully we'll have a fix out soon.

sapnaj

Hi RedGate team

Can you please clarify what mechanism you use to query sys.tables? If you used SSMS (which uses SMO), it is likely you have an outdated version of SMO libraries that expect this column to exist in sys.tables, when in versions of SQL going forward, it is not supposed to exist. If this is the case, can you please download the latest SSMS from https://msdn.microsoft.com/en-us/library/mt238290.aspx and verify you don't see this issue? Please let us know if you still see the issue, with information on how (what mechanism) you query sys.tables.

Thanks
Sapna

alexjf

mark.jordan wrote:

Good news! It looks like our test instance has now received this change, so we should be able to reproduce and figure out how to handle it now (and any other changed columns that might have sneaked in as well Hopefully we'll have a fix out soon.

Awesome - eagerly awaiting a patch

intonet

I'm having this issue to, on N. Europe V12 Azure database.

I just bought SQL Compare to specifically work with these database, so I'm a bit annoyed it's useless to me. Is there any kind of workaround?

sapnaj

Hello RedGate team

We have been in touch with SQL Compare tool team. To close on this issue, SQL Compare team is working on updating their tool to reflect the new schema. Thank you SQL Compare team.

Thanks
sapna

pbaugh

One possible new piece of information, the problem seems to be in making and holding the connection open. I can generally get it to work after 5 tries or so.

karlgjertsen

I am also getting this in 12.0.2000.8, which shows as 13 0 702.

My database is US East.

karlgjertsen

I found this information, which might help:
https://msdn.microsoft.com/en-GB/library/ms187406.aspx

ripteq

Having the same issue in Eastern Australia region.

ripteq

Also interesting to note that the remote_data_archive_migration_state column seems to relate to 2016 Preview schema rather than v12 ?

kodexe-per

pbaugh wrote:

One possible new piece of information, the problem seems to be in making and holding the connection open. I can generally get it to work after 5 tries or so.

Not here. Same result on every try; immediate fail.

Are you using Stretch?

Or is your database in more than one data center?

CodaDave

I've having the same problem in the West Europe region Is there a patch/update available yet?

intonet

Happens every time for me.

Could someone from RedGate please give us an indication of how long we might have to wait for a release to fix this? My expensive tool purchase is useless until it's patched.

Mark J

Sorry for the delay - we've got a quick fix and we're just working on releasing it at the moment

Mark J

We've now released a Frequent Updates version of Compare (11.5.0.397) with a fix for this issue.

For information on enabling Frequent Updates see: https://documentation.red-gate.com/disp ... nt+Updates

intonet

Thank you! Works a treat.

kodexe-per

intonet wrote:

Thank you! Works a treat.

Same here.

Don't forget to bash Microsoft for treating their Gold ISVs this way.

CodaDave

Thankyou, it working now.

bcapps

This error is still occurring with Sql Source Control version 4.2.2.186. Please provide a status for when this fix will be available.