这篇文章主要讲解了“怎么解决SQL 2017 SQLPS执行Add-SqlAvailabilityDatabase异常问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么解决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)InmethodProce***ecord.Resolvingtargets.调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)Resolvingtargets:ParameterSet='ByObject'调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)Resolvedtarget[App1On]调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)GetShouldProcessTargetStringinSqlCmdlet.调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)ValidatingTarget调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)ValidateTargetinSqlCmdlet.Returningtrue.调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)Targetisvalid.CallingBeginTargetProcessing.调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)InBeginTargetProcessing调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)Subscribingtoserverevents:InfoMesage,StatementExecuted调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)DonewithBeginTargetProcessing.CallingProcessTarget.详¨º细?信?息¡é:select*into#tmpag_availability_groupsfrommaster.sys.availability_groupsselectagstates.group_id,agstates.primary_replicainto#tmpag_availability_group_statesfrommaster.sys.dm_hadr_availability_group_statesasagstatesselectgroup_id,replica_id,replica_metadata_idinto#tmpag_availability_replicasfrommaster.sys.availability_replicasselectreplica_id,is_local,roleinto#tmpag_availability_replica_statesfrommaster.sys.dm_hadr_availability_replica_statesSELECTAG.nameAS[Name],AG.group_idAS[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_idAS[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_groupsASAGLEFTOUTERJOIN#tmpag_availability_group_statesasagstatesONAG.group_id=agstates.group_idINNERJOIN#tmpag_availability_replicasASAR2ONAG.group_id=AR2.group_idINNERJOIN#tmpag_availability_replica_statesASarstates2ONAR2.replica_id=arstates2.replica_idANDarstates2.is_local=1WHERE(AG.name=@_msparam_0)droptable#tmpag_availability_groupsdroptable#tmpag_availability_group_statesdroptable#tmpag_availability_replicasdroptable#tmpag_availability_replica_states详¨º细?信?息¡é:droptable#tmpag_availability_groupsdroptable#tmpag_availability_group_statesdroptable#tmpag_availability_replicasdroptable#tmpag_availability_replica_states调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)ExceptionoccurredMicrosoft.SqlServer.Management.Common.ExecutionFailureException:执¡ä行DTransact-SQL语®?句?或¨°批¨²处ä|理¤¨ª时º¡À发¤¡é生¦¨²了¢?异°¨¬常¡ê。¡ê--->System.Data.SqlClient.SqlException:列¢D名?'required_copies_to_commit'无T效¡ì。¡ê在¨²Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlActionaction,ObjectexecObject,DataSetfillDataSet,BooleancatchException)在¨²Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommandcommand)---内¨²部?异°¨¬常¡ê堆?栈?跟¨²踪Á¨´的Ì?结¨¢尾2---在¨²Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommandcommand)在¨²Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(Stringquery,SqlCommand&command)在¨²Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSqlexecSql,Stringquery)在¨²Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollectionquery,Objectcon,StatementBuildersb,RetriveModerm)在¨²Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultTyperesultType,StringCollectionsql,ObjectconnectionInfo,StatementBuildersb)在¨²Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResultsqlresult,ResultTyperesultType)在¨²Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResultresult)在¨²Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()在¨²Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Requestreq,Objectci)在¨²Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(ObjectconnectionInfo,Requestrequest)在¨²Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Requestreq)在¨²Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[]fields,OrderBy[]orderby)在¨²Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[]fields,OrderBy[]orderby)在¨²Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(BooleanallProperties)在¨²Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(Stringpropname,BooleanuseDefaultValue)在¨²Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32index,BooleanuseDefaultOnMissingValue)在¨²Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(StringpropertyName,BooleanthrowOnNullValue,BooleanuseDefaultOnMissingValue)在¨²Microsoft.SqlServer.Management.Smo.AvailabilityGroup.get_PrimaryReplicaServerName()在¨²Microsoft.SqlServer.Management.PowerShell.Hadr.CmdletUtilities.IsReplicaPrimary(AvailabilityGroupag,SmoRecordContextcontext)在¨²Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand.ProcessTarget(AvailabilityGrouptarget,SmoRecordContextcontext)在¨²Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.Proce***ecord()调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)CallingEndProcessing.调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)InEndTargetProcessing调Ì¡Â试º?:(Add-SqlAvailabilityDatabase)Unsubscribingfromserverevents:InfoMesage,StatementExecutedAdd-SqlAvailabilityDatabase:执¡ä行DTransact-SQL语®?句?或¨°批¨²处ä|理¤¨ª时º¡À发¤¡é生¦¨²了¢?异°¨¬常¡ê。¡ê所¨´在¨²位?置?行D:1字Á?符¤?:1+Add-SqlAvailabilityDatabase-InputObject$ag-Database$DatabaseList-Debug+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+CategoryInfo:NotSpecified:(:)[Add-SqlAvailabilityDatabase],ExecutionFailureException+FullyQualifiedErrorId:Microsoft.SqlServer.Management.Common.ExecutionFailureException,Microsoft.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_groupsfrommaster.sys.availability_groupsselectagstates.group_id,agstates.primary_replicainto#tmpag_availability_group_statesfrommaster.sys.dm_hadr_availability_group_statesasagstatesselectgroup_id,replica_id,replica_metadata_idinto#tmpag_availability_replicasfrommaster.sys.availability_replicasselectreplica_id,is_local,roleinto#tmpag_availability_replica_statesfrommaster.sys.dm_hadr_availability_replica_statesSELECTAG.nameAS[Name],AG.group_idAS[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_idAS[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_groupsASAGLEFTOUTERJOIN#tmpag_availability_group_statesasagstatesONAG.group_id=agstates.group_idINNERJOIN#tmpag_availability_replicasASAR2ONAG.group_id=AR2.group_idINNERJOIN#tmpag_availability_replica_statesASarstates2ONAR2.replica_id=arstates2.replica_idANDarstates2.is_local=1WHERE(AG.name='App1On')droptable#tmpag_availability_groupsdroptable#tmpag_availability_group_statesdroptable#tmpag_availability_replicasdroptable#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异常问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！