remote_data_archive_migration_state

alexjfalexjf Posts: 7
edited March 18, 2016 9:18AM in SQL Compare 11
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?
· Share on Twitter
«1

Comments

  • Mark JMark J Posts: 125 Bronze 5
    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?
    · Share on Twitter
  • alexjfalexjf Posts: 7
    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.
    · Share on Twitter
  • alexjfalexjf Posts: 7
    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?
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    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 :)
    · Share on Twitter
  • alexjfalexjf Posts: 7
    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?
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    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...
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    Unfortunately it looks like I can't create an Australia East instance with my subscription:

    Prh3Odg.png
    · Share on Twitter
  • alexjfalexjf Posts: 7
    Hi Mark,

    When I run your query I get the following:

    DVvkzWN.png
    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 :)
    · Share on Twitter
  • larsnikolajsenlarsnikolajsen Posts: 1 New member
    We are experiencing the same issues as of yesterday in the western europe datacenter
    · Share on Twitter
  • winzenriedwinzenried Posts: 1
    I have exactly the same prolem with an azure db in West-EU
    · Share on Twitter
  • pluscodepluscode Posts: 1 Bronze 1
    Same here, also in western europe datacenter. Verry worrying...
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    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.
    · Share on Twitter
  • sapnajsapnaj Posts: 2
    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
    · Share on Twitter
  • alexjfalexjf Posts: 7
    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 :)
    · Share on Twitter
  • intonetintonet Posts: 7
    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?
    · Share on Twitter
  • sapnajsapnaj Posts: 2
    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
    · Share on Twitter
  • pbaughpbaugh Posts: 1
    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.
    · Share on Twitter
  • karlgjertsenkarlgjertsen Posts: 2
    I am also getting this in 12.0.2000.8, which shows as 13 0 702.

    My database is US East.
    · Share on Twitter
  • karlgjertsenkarlgjertsen Posts: 2
    I found this information, which might help:
    https://msdn.microsoft.com/en-GB/library/ms187406.aspx
    · Share on Twitter
  • ripteqripteq Posts: 4 Bronze 1
    Having the same issue in Eastern Australia region.
    · Share on Twitter
  • ripteqripteq Posts: 4 Bronze 1
    Also interesting to note that the remote_data_archive_migration_state column seems to relate to 2016 Preview schema rather than v12 ?
    · Share on Twitter
  • kodexe-perkodexe-per Posts: 2
    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?
    · Share on Twitter
  • CodaDaveCodaDave Posts: 2
    I've having the same problem in the West Europe region :( Is there a patch/update available yet?

    redgate_error.png
    · Share on Twitter
  • intonetintonet Posts: 7
    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.
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    Sorry for the delay - we've got a quick fix and we're just working on releasing it at the moment :)
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    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
    · Share on Twitter
  • intonetintonet Posts: 7
    Thank you! Works a treat.
    · Share on Twitter
  • kodexe-perkodexe-per Posts: 2
    intonet wrote:
    Thank you! Works a treat.
    Same here.

    Don't forget to bash Microsoft for treating their Gold ISVs this way. :)
    · Share on Twitter
  • CodaDaveCodaDave Posts: 2
    Thankyou, it working now. :)
    · Share on Twitter
  • bcappsbcapps Posts: 1
    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.
    · Share on Twitter
Sign In or Register to comment.