共计 10166 个字符，预计需要花费 26 分钟才能阅读完成。
行业资讯
数据库
关系型数据库
怎么解决 SQL 2017 SQLPS 执行 Add-SqlAvailabilityDatabase 异常问题
这篇文章主要讲解了“怎么解决 SQL 2017 SQLPS 执行 Add-SqlAvailabilityDatabase 异常问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“怎么解决 SQL 2017 SQLPS 执行 Add-SqlAvailabilityDatabase 异常问题”吧！
SQL Server 2017 SQLPS 执行 Add-SqlAvailabilityDatabase 遇到问题
这个错误简直逆天，查了下该 cmdlet 的帮助
常规参数里有 Debug。
执行 Debug，输出如下：
PS C:\Windows\system32 Add-SqlAvailabilityDatabase -InputObject $ag -Database $DatabaseList -Debug
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) In method Proce***ecord. Resolving targets.
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Resolving targets: Parameter Set = ByObject
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Resolved target [App1On]
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) GetShouldProcessTargetString in SqlCmdlet.
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Validating Target
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) ValidateTarget in SqlCmdlet. Returning true.
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Target is valid. Calling BeginTargetProcessing.
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) In BeginTargetProcessing
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Subscribing to server events: InfoMesage, StatementExecuted
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Done with BeginTargetProcessing. Calling ProcessTarget.
详¨º细? 信? 息¡é:
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]
#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
详¨º细? 信? 息¡é:
drop table #tmpag_availability_groups
drop table #tmpag_availability_group_states
drop table #tmpag_availability_replicas
drop table #tmpag_availability_replica_states
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Exception occurred Microsoft.SqlServer.Management.Common.ExecutionFa
ilureException: 执¡ä行 D Transact-SQL 语®? 句? 或¨°批¨²处ä| 理¤¨ª时º¡À发¤¡é生¦¨²了¢? 异°¨¬常¡ê。¡ê --- System.Data.SqlClient.SqlException: 列¢D 名? required_co
pies_to_commit 无 T 效¡ì。¡ê
在¨² Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Obj
ect execObject, DataSet fillDataSet, Boolean catchException)
在¨² Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
--- 内¨²部? 异°¨¬常¡ê堆? 栈? 跟¨²踪Á¨´的Ì? 结¨¢尾 2 ---
在¨² Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
在¨² Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand command)
在¨² Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String
query)
在¨² Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con,
StatementBuilder sb, RetriveMode rm)
在¨² Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection
sql, Object connectionInfo, StatementBuilder sb)
在¨² Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult,
ResultType resultType)
在¨² Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
在¨² Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
在¨² Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
在¨² Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
在¨² Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
在¨² Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orde
在¨² Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby
在¨² Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
在¨² Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDef
aultValue)
在¨² Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDe
faultOnMissingValue)
在¨² Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String property
Name, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
在¨² Microsoft.SqlServer.Management.Smo.AvailabilityGroup.get_PrimaryReplicaServerName()
在¨² Microsoft.SqlServer.Management.PowerShell.Hadr.CmdletUtilities.IsReplicaPrimary(AvailabilityGroup
ag, SmoRecordContext context)
在¨² Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand.ProcessTarg
et(AvailabilityGroup target, SmoRecordContext context)
在¨² Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.Proce***ecord()
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Calling EndProcessing.
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) In EndTargetProcessing
调Ì¡Â试º?: (Add-SqlAvailabilityDatabase) Unsubscribing from server events: InfoMesage, StatementExecuted
Add-SqlAvailabilityDatabase : 执¡ä行 D Transact-SQL 语®? 句? 或¨°批¨²处ä| 理¤¨ª时º¡À发¤¡é生¦¨²了¢? 异°¨¬常¡ê。¡ê
所¨´在¨²位? 置? 行 D:1 字Á? 符¤?: 1
+ Add-SqlAvailabilityDatabase -InputObject $ag -Database $DatabaseList -Debug
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Add-SqlAvailabilityDatabase], ExecutionFailureExcep
+ FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ExecutionFailureException,Micros
oft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand核心错误信息如下：
调试: (Add-SqlAvailabilityDatabase) Exception occurred Microsoft.SqlServer.Management.Common.ExecutionFa
ilureException: 执行 Transact-SQL 语句或批处理时发生了异常。— System.Data.SqlClient.SqlException: 列名 required_copies_to_commit 无效。
将相关语句单独提取出来执行：
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]
#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得出如下报错：
消息 207，级别 16，状态 1，第 10 行
列名 required_copies_to_commit 无效。
语句中
用到的是
master.sys.availability_groups
中 required_copies_to_commit 列，而实际上该系统视图根本没有该列，只有 required_synchronized_secondaries_to_commit 列。
查了下网上有同样的问题：
https://social.msdn.microsoft.com/Forums/azure/en-US/3e5db95a-0231-4e29-b0c0-68c4d8e9583d/exception-occurred-microsoftsqlservermanagementcommonexecutionfailureexception?forum=sqltools
当前运行的 SQL Server 2017 没有来得及更新 SQLPS。
尝试打补丁
从 https://support.microsoft.com/en-us/help/4047329 下载最新的累积补丁 CU6，安装 SQLServer2017-KB4101464-x64.exe。实际并没有对 SQLPS 模块进行更新。该问题依然存在。
“
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/ 下。
加载即用、用时注册：
Import-Module -Name SqlServer
使用新的 SqlServer 模块后，没有遇到问题了。
感谢各位的阅读，以上就是“怎么解决 SQL 2017 SQLPS 执行 Add-SqlAvailabilityDatabase 异常问题”的内容了，经过本文的学习后，相信大家对怎么解决 SQL 2017 SQLPS 执行 Add-SqlAvailabilityDatabase 异常问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
