將我的數據庫項目與我的 Azure 伺服器進行比較時，SSDT 架構比較失敗

我有一個建構企業數據庫的 SQL 數據庫項目。它使用 SSDT 的架構比較工具在內部和 AWS 託管的 SQL 伺服器上部署了多次。

當我發佈到執行 SQL Ent 2012 sp2 的 Azure Hosted Win 2012 Server 時出現的問題。它返回“比較完成。未檢測到差異”。

我知道這是錯誤的，因為我可以打開企業管理器並將架構與 SQL 項目進行比較，然後發現存在差異。

我發現有幾篇文章討論了 2014 版本是如何破壞該工具的，但這些都是版本差異。

$$ Yes I did Google this. Stating because I am notorious in forgetting to do so. $$ https://www.google.com/webhp?ie=utf-8&oe=utf-8#q=ssdt+data+compare+fail+to+detect+difference&start=10 我檢查的其他事項包括確保我的數據庫帳戶具有無限訪問權限。我可以連接管理控制台。我可以連接本地程序。

最後確認有問題：

1. 我創建了一個只有一個返回數字 1 的 SP。

2. 為了測試，它可能沒有任何回報。

3. 創建 SP 後，我在所有實例上執行架構比較，除 Azure 伺服器之外的所有實例都顯示了差異。

更新

我已經驗證這與伺服器明確相關，因為現在兩台不同電腦上的兩個不同使用者遇到了相同的問題。

TL; 博士

Visual Studio 正在執行的查詢被阻止並且查詢超時。

---

事實證明，我已經設法解決了我們的情況：由於阻塞而超時！

我對Microsoft SQL Server Data Tools的應用程序名稱、架構比較執行了一個探查器跟踪，以擷取 Visual Studio 架構比較事件。

當 VS 聲稱比較完成時，它將到達某個查詢然後停止。

這是有問題的一批查詢：

select object_id as id, 
   null as id2,
   case
       when [type] != 'D ' then [type]
       when parent_object_id != 0 then 'D '
       else 'LD' 
   end as [type],
   schema_name(schema_id) as name1,
   name as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.objects
where 
   type != 'S'
   and is_ms_shipped != 1

union

select principal_id as id, 
   null as id2,
   type, 
   name as name1, 
   null as name2, 
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.database_principals

union

select assembly_id as id, 
   null as id2,
   'CLR' as type, 
   name as name1, 
   null as name2, 
   null as name3,
   principal_id as a1,
   null as v1,
   modify_date as mod
from sys.assemblies
where
   is_user_defined =1

union

select schema_id as id, 
   null as id2,
   'SCH' as type, 
   name as name1, 
   null as name2, 
   null as name3,
   principal_id as a1,
   null as v1,
   null as mod
from sys.schemas

union

select user_type_id as id, 
   null as id2,
   case
       when is_assembly_type = 1 then 'UDT'
       when is_table_type = 1 then 'TT'
       else 'UDDT'
   end as [type],
   schema_name(schema_id) as name1,
   name as name2,
   null as name3,
   null as a1,
   binary_checksum(system_type_id, schema_id, max_length, precision, scale, is_nullable, is_assembly_type, default_object_id, rule_object_id) as v1,
   null as mod
from sys.types
where
   is_user_defined = 1

union

select idx.object_id as id,
   idx.index_id as id2,
   case
       when idx.type = 3 then 'XIX'
       when idx.type = 4 then 'SIX'
       when idx.type = 6 then 'CIX'
       else 'IDX' 
   end as [type],
   schema_name(tab.schema_id) as name,
   tab.name as name2,
   idx.name as name3,
   null as a1,
   binary_checksum(is_unique, data_space_id, ignore_dup_key, is_primary_key, is_unique_constraint, fill_factor, is_padded, is_disabled, allow_row_locks, allow_page_locks) as v1,
   null as mod
from sys.indexes idx 
   inner join sys.tables tab on idx.object_id = tab.object_id
where 
   idx.is_primary_key = 0
   and idx.name is not null
   and idx.is_unique_constraint = 0
   and idx.is_hypothetical = 0

union

select stat.object_id as id,
   stat.stats_id as id2,
   'STAT' as [type],
   schema_name(tab.schema_id) as name,
   tab.name as name2,
   stat.name as name3,
   null as a1,
   no_recompute as v1,
   null as mod
from sys.stats stat
   inner join sys.tables tab on stat.object_id = tab.object_id
where 
   stat.user_created = 1

union

select xml_collection_id as id,
   null as id2,
   'XSC' as type,
   schema_name(schema_id),
   name as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod 
from sys.xml_schema_collections
where xml_collection_id > 1

union

select 
   object_id id,
   null id2,
   'DDT' as type,
   name,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod 
from sys.triggers
where
   parent_class = 0

union

select
   database_specification_id id,
   null as id2,
   'DAS' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.database_audit_specifications

union

select 
   certificate_id id,
   null as id2,
   'CERT' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   binary_checksum(pvt_key_encryption_type, thumbprint) as v1,
   null as mod
from sys.certificates

union

select
   asymmetric_key_id id,
   null as id2,
   'ASMK' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   null as v1,
   null as mod
from sys.asymmetric_keys

union

select
   symmetric_key_id id,
   null as id2,
   case
       when name = N'##MS_DatabaseMasterKey##' then 'MK'
       else 'SYMK'
   end as type,
   case
       when name = N'##MS_DatabaseMasterKey##' then null
       else name
   end as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   null as v1,
   modify_date as mod
from sys.symmetric_keys
where
   name = N'##MS_DatabaseMasterKey##'
   or name not like N'##%'

union

select
   fulltext_catalog_id id,
   null as id2,
   'FTC' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   binary_checksum(is_default, is_accent_sensitivity_on) as v1,
   null as mod
from sys.fulltext_catalogs

union

select
   ft.object_id id,
   ft.unique_index_id as id2,
   'FTI' as type,
   schema_name(o.schema_id) as name1,
   o.name as name2,
   null as name3,
   null as a1,
   binary_checksum(is_enabled, change_tracking_state) as v1,
   null as mod
from sys.fulltext_indexes ft
inner join sys.objects o on ft.object_id = o.object_id

union

select
   stoplist_id id,
   null as id2,
   'FTSL' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   null as v1,
   modify_date as mod
from sys.fulltext_stoplists

union

select
   data_space_id id,
   null as id2,
   [type] as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   is_default as v1,
   null as mod
from sys.data_spaces

union

select
   function_id id,
   null as id2,
   'PF' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.partition_functions

union

select
   file_id id,
   null as id2,
   'FILE' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   binary_checksum(type, data_space_id, physical_name, state, growth, is_read_only, is_sparse, is_percent_growth) as v1,
   null as mod
from sys.database_files


union

select
   message_type_id id,
   null as id2,
   'MT' as [type],
   name collate database_default as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   binary_checksum(validation, xml_collection_id) as v1,
   null as mod
from sys.service_message_types

union

select
   service_contract_id id,
   null as id2,
   'SC' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   null as v1,
   null as mod
from sys.service_contracts

union

select
   service_id id,
   null as id2,
   'SERVICE' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   binary_checksum(service_queue_id) as v1,
   null as mod
from sys.services

union

select
   route_id id,
   null as id2,
   'ROUTE' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   binary_checksum(remote_service_name, broker_instance, lifetime, address, mirror_address) as v1,
   null as mod
from sys.routes

union

select
   remote_service_binding_id id,
   null as id2,
   'RSB' as type,
   name as name1,
   null as name2,
   null as name3,
   principal_id as a1,
   binary_checksum(service_contract_id, remote_principal_id, is_anonymous_on) as v1,
   null as mod
from sys.remote_service_bindings

union

select 
   object_id id,
   null as id2,
   'DEN' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.event_notifications

union

select
   priority_id id,
   null as id2,
   'SBP' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   binary_checksum(service_contract_id, local_service_id, remote_service_name, priority) as v1,
   null as mod
from sys.conversation_priorities


union

select
   principal_id id,
   null as id2,
   'LOG' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.server_principals
where
   type != 'R'

union

select
   audit_id id,
   null as id2,
   'SAD' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.server_audits

union

select
   credential_id id,
   null as id2,
   'CRD' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.credentials


union

select
   msg.message_id id,
   msg.language_id as id2,
   'SEM' as type,
   cast(msg.message_id as sysname) as name1,
   lang.alias collate database_default as name2,
   null as name3,
   null as a1,
   binary_checksum(msg.severity, msg.is_event_logged, msg.text) as v1,
   null as mod
from sys.messages msg inner join sys.syslanguages lang on (msg.language_id = lang.lcid)
where
   msg.message_id > 50000

union

select 
   base.endpoint_id id,
   null as id2,
   'SEP' as type,
   base.name as name1,
   null as name2,
   null as name3,
   base.principal_id as a1,
   binary_checksum(*) as v1,
   null as mod
from sys.endpoints base  
   left outer join sys.http_endpoints http on (base.endpoint_id = http.endpoint_id)
   left outer join sys.service_broker_endpoints sb on (base.endpoint_id = sb.endpoint_id)
   left outer join sys.soap_endpoints soap on (base.endpoint_id = soap.endpoint_id)
   left outer join sys.tcp_endpoints tcp on (base.endpoint_id = tcp.endpoint_id)
   left outer join sys.database_mirroring_endpoints mirror on (base.endpoint_id = mirror.endpoint_id)

union

select 
   server_id id,
   null as id2,
   'SLS' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.servers

union

select 
   logins.server_id id,
   logins.local_principal_id as id2,
   'SLL' as type,
   srvs.name as name1,
   lp.name as name2,
   null as name3,
   null as a1,
   null as v1,
   logins.modify_date as mod
from sys.linked_logins logins 
   inner join sys.servers srvs on (logins.server_id = srvs.server_id)
   left join sys.server_principals lp on (logins.local_principal_id = lp.principal_id)

union

select 
   object_id id,
   null as id2,
   'SST' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.server_triggers
where
   is_ms_shipped = 0

union

select 
   server_specification_id id,
   null as id2,
   'SAS' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   modify_date as mod
from sys.server_audit_specifications

union

select 
   event_session_id id,
   null as id2,
   'SES' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   null as v1,
   null as mod
from sys.server_event_sessions

union

select 
   provider_id id,
   null as id2,
   'SCP' as type,
   name as name1,
   null as name2,
   null as name3,
   null as a1,
   binary_checksum(guid, version, dll_path, is_enabled) as v1,
   null as mod
from sys.cryptographic_providers


select 
   cast(32 as tinyint) as class,
   'SES' as type,
   event_session_id as majorid,
   binary_checksum(event_id, name, package, module) as minorid,
   name as name,
   null as id1,
   null as id2
from sys.server_event_session_actions

union

select 
   cast(32 as tinyint) as class,
   'SES' as type,
   event_session_id as majorid,
   event_id as minorid,
   name as name,
   binary_checksum(predicate) as id1,
   null as id2
from sys.server_event_session_events

union

select 
   cast(32 as tinyint) as class,
   'SES' as type,
   event_session_id as majorid,
   object_id as minorid,
   name as name,
   binary_checksum(value) as id1,
   null as id2
from sys.server_event_session_fields

union

select 
   cast(32 as tinyint) as class,
   'SES' as type,
   event_session_id as majorid,
   event_id as minorid,
   name as name,
   null as id1,
   null as id2
from sys.server_event_session_events


union

select 
   perms.class as class,
   objectpropertyex(perms.major_id, 'BaseType') as type,
   perms.major_id as majorid,
   perms.minor_id as minorid,
   perms.type + perms.state collate database_default as name,
   perms.grantee_principal_id as id1,
   perms.grantor_principal_id as id2
from sys.database_permissions perms
where
   perms.major_id >= 0

union

select 
   props.class as class,
   case 
       when objs.parent_object_id > 0 then objectpropertyex(objs.parent_object_id, 'BaseType') 
       else objectpropertyex(props.major_id, 'BaseType') 
   end as type,
   case
       when objs.parent_object_id > 0 then objs.parent_object_id
       else props.major_id
   end as majorid,
   props.minor_id as minorid,
   props.name as name,
   binary_checksum(props.value) as id1,
   null as id2
from sys.extended_properties props left outer join sys.objects objs on props.major_id = objs.object_id

union

select 
   cast(4 as tinyint) as class,
   'R' as type,
   roles.role_principal_id as majorid,
   roles.member_principal_id as minorid,
   'drm' as name,
   null as id1,
   null as id2
from sys.database_role_members roles 

union

select 
   cast(6 as tinyint) as class,
   'TT' as type,
   tt.user_type_id as majorid,
   cols.column_id as minorid, 
   cols.name as name,
   binary_checksum(cols.user_type_id, cols.max_length, cols.precision, cols.scale, 
       cols.collation_name, cols.is_nullable, cols.is_ansi_padded, cols.is_rowguidcol, cols.is_identity, 
       cols.is_computed, cols.is_xml_document, cols.xml_collection_id, cols.default_object_id, cols.rule_object_id) as id1,
   null as id2
from sys.table_types tt inner join sys.columns cols on (tt.type_table_object_id = cols.object_id)


select 
   cols.object_id as tableid,
   cols.column_id as columnid, 
   binary_checksum(cols.name) as checksum
from sys.tables tabs 
   inner join sys.columns cols  on (tabs.object_id = cols.object_id)
   inner join sys.index_columns idx_cols on (idx_cols.object_id = tabs.object_id and idx_cols.column_id = cols.column_id)
where
   tabs.is_ms_shipped = 0

union

select 
   object_id as tableid,
   column_id as columnid, 
   binary_checksum(type_column_id, language_id) as checksum
from sys.fulltext_index_columns

在 SSMS 中執行查詢永遠不會在有問題的伺服器上完成執行，但在其他所有環境中都需要不到一秒鐘的時間。

所以我開始把它拆開，發現從以下 3 個系統視圖讀取的查詢是問題所在：

• sys.server_event_session_actions
• sys.server_event_session_events
• sys.server_event_session_fields

這些系統視圖中的第一個僅包含 28 條記錄（通過執行 a 找到Count(*)），但除非我使用提示，否則不會返回數據(NOLOCK)……$$ b $$鎖定問題！

找出阻塞鏈的頭部很容易，但由於它是一個擴展事件會話，所以它不能被殺死。這裡有一個解決這個問題的方法：https: //support.microsoft.com/en-gb/kb/2511963

但是希望在您的情況下，您可以很好地殺死鏈條的頭部並使事情恢復正常！

---

**編輯：**現在已經為受影響的 SQL Server 提供了一個可行的維護視窗，重新啟動已清除 SPID，現在比較再次工作！

此外，這是我編寫的一個快速腳本，用於查找可能對未來讀者有幫助的阻塞鏈的頭部：http://gvee.co.uk/files/sql/admin.blocking_chains.sql

IF Object_ID('admin.blocking_chains', 'V') IS NULL
 BEGIN
   EXEC ('CREATE VIEW admin.blocking_chains AS SELECT NULL As buffer;');
 END
;
GO

ALTER VIEW admin.blocking_chains
 AS
WITH processes AS (
 SELECT s.spid
      , s.blocked As blocking_spid
      , DB_Name(s.dbid) As database_name
      , s.program_name
      , Cast(x.text As varchar(Max)) As definition
      , hostname As host_name
      , loginame As login_name
      , nt_domain
      , nt_username
 FROM   sys.sysprocesses (NOLOCK) As s
  CROSS
   APPLY sys.dm_exec_sql_text(s.sql_handle) As x
 WHERE s.spid > 50
)
, blocking (spid, blocking_spid, blocking_statement, sequence, level, database_name) AS (
 SELECT s.spid
      , s.blocking_spid
      , s.definition
      , Row_Number() OVER (ORDER BY s.spid) As sequence
      , 0 As level
      , s.database_name
 FROM   processes As s
  INNER
   JOIN processes As s1
     ON s1.blocking_spid = s.spid
 WHERE  s.blocking_spid = 0

   UNION ALL

     SELECT r.spid
          , r.blocking_spid
          , r.definition
          , d.sequence
          , d.level + 1
          , r.database_name
     FROM   processes As r
      INNER
       JOIN blocking As d
         ON d.spid = r.blocking_spid
     WHERE  r.blocking_spid > 0
)
, results AS (
 SELECT blocking_spid
      , spid
      , blocking_statement
      , level
      , Min(sequence) As sequence
      , Min(database_name) As database_name
 FROM   blocking
 GROUP
     BY blocking_spid
      , spid
      , blocking_statement
      , level
)
SELECT Cast(CASE WHEN results.blocking_spid = 0 THEN 1 ELSE 0 END As bit) As is_head_of_chain
    , results.sequence
    , results.level
    , results.database_name
    , results.spid
    , results.blocking_spid
    , results.blocking_statement
    , processes.host_name
    , processes.program_name
    , processes.login_name
    , processes.nt_domain
    , processes.nt_username
    , Right('0000' + Cast(results.sequence As varchar(11)), 5) + '-' + Right('0000' + Cast(results.level As varchar(11)), 5) As sort_order
FROM   results
LEFT
 JOIN processes
   ON processes.spid = results.spid
;

引用自：https://dba.stackexchange.com/questions/104128