您好,登錄后才能下訂單哦!
這篇文章主要講解了“怎么解決SQL 2017 SQLPS執(zhí)行Add-SqlAvailabilityDatabase異常問(wèn)題”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“怎么解決SQL 2017 SQLPS執(zhí)行Add-SqlAvailabilityDatabase異常問(wèn)題”吧!
SQL Server 2017 SQLPS執(zhí)行Add-SqlAvailabilityDatabase遇到問(wèn)題
這個(gè)錯(cuò)誤簡(jiǎn)直逆天,查了下該cmdlet的幫助
常規(guī)參數(shù)里有Debug。
執(zhí)行Debug,輸出如下:
PS C:\Windows\system32> Add-SqlAvailabilityDatabase -InputObject $ag -Database $DatabaseList -Debug
調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) In method Proce***ecord. Resolving targets. 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Resolving targets: Parameter Set = 'ByObject' 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Resolved target [App1On] 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) GetShouldProcessTargetString in SqlCmdlet. 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Validating Target 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) ValidateTarget in SqlCmdlet. Returning true. 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Target is valid. Calling BeginTargetProcessing. 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) In BeginTargetProcessing 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Subscribing to server events: InfoMesage, StatementExecuted 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Done with BeginTargetProcessing. Calling ProcessTarget. 詳¨o細(xì)?信?息?é: select * into #tmpag_availability_groups from master.sys.availability_groups select agstates.group_id, agstates.primary_replica into #tmpag_availability_group_states from ma ster.sys.dm_hadr_availability_group_states as agstates select group_id, replica_id, replica_metadata_id into #tmpag_availability_replicas from master.s ys.availability_replicas select replica_id, is_local, role into #tmpag_availability_replica_states from master.sys.dm_had r_availability_replica_states SELECT AG.name AS [Name], AG.group_id AS [UniqueId], ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference], ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel], ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout], ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName], ISNULL(arstates2.role, 3) AS [LocalReplicaRole], AR2.replica_metadata_id AS [ID], ISNULL(AG.basic_features, 0) AS [BasicAvailabilityGroup], ISNULL(AG.db_failover, 0) AS [DatabaseHealthTrigger], ISNULL(AG.dtc_support, 0) AS [DtcSupportEnabled], ISNULL(AG.is_distributed, 1) AS [IsDistributedAvailabilityGroup], ISNULL(AG.cluster_type, 0) AS [ClusterType], ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit] FROM #tmpag_availability_groups AS AG LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AN D arstates2.is_local = 1 WHERE (AG.name=@_msparam_0) drop table #tmpag_availability_groups drop table #tmpag_availability_group_states drop table #tmpag_availability_replicas drop table #tmpag_availability_replica_states 詳¨o細(xì)?信?息?é: drop table #tmpag_availability_groups drop table #tmpag_availability_group_states drop table #tmpag_availability_replicas drop table #tmpag_availability_replica_states 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Exception occurred Microsoft.SqlServer.Management.Common.ExecutionFa ilureException: 執(zhí)??行D Transact-SQL 語(yǔ)??句?或¨°批¨2處?|理¤¨a時(shí)o?à發(fā)¤?é生|¨2了¢?異°¨?常?ê。?ê ---> System.Data.SqlClient.SqlException: 列¢D名? 'required_co pies_to_commit' 無(wú)T效?ì。?ê 在¨2 Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Obj ect execObject, DataSet fillDataSet, Boolean catchException) 在¨2 Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command) --- 內(nèi)¨2部?異°¨?常?ê堆?棧?跟¨2蹤á¨′的ì?結(jié)¨¢尾2 --- 在¨2 Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command) 在¨2 Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command) 在¨2 Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query) 在¨2 Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm) 在¨2 Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb) 在¨2 Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType) 在¨2 Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result) 在¨2 Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() 在¨2 Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci) 在¨2 Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request) 在¨2 Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req) 在¨2 Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orde rby) 在¨2 Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby ) 在¨2 Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties) 在¨2 Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDef aultValue) 在¨2 Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDe faultOnMissingValue) 在¨2 Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String property Name, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue) 在¨2 Microsoft.SqlServer.Management.Smo.AvailabilityGroup.get_PrimaryReplicaServerName() 在¨2 Microsoft.SqlServer.Management.PowerShell.Hadr.CmdletUtilities.IsReplicaPrimary(AvailabilityGroup ag, SmoRecordContext context) 在¨2 Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand.ProcessTarg et(AvailabilityGroup target, SmoRecordContext context) 在¨2 Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.Proce***ecord() 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Calling EndProcessing. 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) In EndTargetProcessing 調(diào)ì??試o?: (Add-SqlAvailabilityDatabase) Unsubscribing from server events: InfoMesage, StatementExecuted Add-SqlAvailabilityDatabase : 執(zhí)??行D Transact-SQL 語(yǔ)??句?或¨°批¨2處?|理¤¨a時(shí)o?à發(fā)¤?é生|¨2了¢?異°¨?常?ê。?ê 所¨′在¨2位?置? 行D:1 字á?符¤?: 1 + Add-SqlAvailabilityDatabase -InputObject $ag -Database $DatabaseList -Debug + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Add-SqlAvailabilityDatabase], ExecutionFailureExcep tion + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ExecutionFailureException,Micros oft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand
核心錯(cuò)誤信息如下:
調(diào)試: (Add-SqlAvailabilityDatabase) Exception occurred Microsoft.SqlServer.Management.Common.ExecutionFa
ilureException: 執(zhí)行 Transact-SQL 語(yǔ)句或批處理時(shí)發(fā)生了異常。 ---> System.Data.SqlClient.SqlException: 列名 'required_copies_to_commit' 無(wú)效。
將相關(guān)語(yǔ)句單獨(dú)提取出來(lái)執(zhí)行:
select * into #tmpag_availability_groups from master.sys.availability_groups select agstates.group_id, agstates.primary_replica into #tmpag_availability_group_states from master.sys.dm_hadr_availability_group_states as agstates select group_id, replica_id, replica_metadata_id into #tmpag_availability_replicas from master.sys.availability_replicas select replica_id, is_local, role into #tmpag_availability_replica_states from master.sys.dm_hadr_availability_replica_states SELECT AG.name AS [Name], AG.group_id AS [UniqueId], ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference], ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel], ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout], ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName], ISNULL(arstates2.role, 3) AS [LocalReplicaRole], AR2.replica_metadata_id AS [ID], ISNULL(AG.basic_features, 0) AS [BasicAvailabilityGroup], ISNULL(AG.db_failover, 0) AS [DatabaseHealthTrigger], ISNULL(AG.dtc_support, 0) AS [DtcSupportEnabled], ISNULL(AG.is_distributed, 1) AS [IsDistributedAvailabilityGroup], ISNULL(AG.cluster_type, 0) AS [ClusterType], ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit] FROM #tmpag_availability_groups AS AG LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1 WHERE (AG.name='App1On') drop table #tmpag_availability_groups drop table #tmpag_availability_group_states drop table #tmpag_availability_replicas drop table #tmpag_availability_replica_states
得出如下報(bào)錯(cuò):
消息 207,級(jí)別 16,狀態(tài) 1,第 10 行
列名 'required_copies_to_commit' 無(wú)效。
語(yǔ)句中
用到的是
master.sys.availability_groups
中required_copies_to_commit列,而實(shí)際上該系統(tǒng)視圖根本沒(méi)有該列,只有required_synchronized_secondaries_to_commit列。
查了下網(wǎng)上有同樣的問(wèn)題:
https://social.msdn.microsoft.com/Forums/azure/en-US/3e5db95a-0231-4e29-b0c0-68c4d8e9583d/exception-occurred-microsoftsqlservermanagementcommonexecutionfailureexception?forum=sqltools
當(dāng)前運(yùn)行的SQL Server 2017沒(méi)有來(lái)得及更新SQLPS。
嘗試打補(bǔ)丁
從https://support.microsoft.com/en-us/help/4047329下載最新的累積補(bǔ)丁CU6,安裝SQLServer2017-KB4101464-x64.exe。實(shí)際并沒(méi)有對(duì)SQLPS模塊進(jìn)行更新。該問(wèn)題依然存在。
“
There are two SQL Server PowerShell modules; SqlServer and SQLPS. The SQLPS module is included with the SQL Server installation (for backwards compatibility), but is no longer being updated. The most up-to-date PowerShell module is the SqlServer module. The SqlServer module contains updated versions of the cmdlets in SQLPS, and also includes new cmdlets to support the latest SQL features.
Previous versions of the SqlServer module were included with SQL Server Management Studio (SSMS), but only with the 16.x versions of SSMS. To use PowerShell with SSMS 17.0 and later, the SqlServer module must be installed from the PowerShell Gallery. To install the SqlServer module, see Install SQL Server PowerShell.
”
嘗試安裝SqlServer模塊
先下載保存模塊:
Save-Module -Name SqlServer -Path C:\powershellgallery
將下載的SqlServer模塊文件夾拷貝到%ProgramFiles%/WindowsPowershell/Module/下。
加載即用、用時(shí)注冊(cè):
Import-Module -Name SqlServer
使用新的SqlServer模塊后,沒(méi)有遇到問(wèn)題了。
感謝各位的閱讀,以上就是“怎么解決SQL 2017 SQLPS執(zhí)行Add-SqlAvailabilityDatabase異常問(wèn)題”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì)怎么解決SQL 2017 SQLPS執(zhí)行Add-SqlAvailabilityDatabase異常問(wèn)題這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。