locked
Moving SQL DB to 2016 SQL Cluster RRS feed

  • Question

  • Im experiencing a little problem in attempting to move the SQL DB from the site server to a 2016 SQL Cluster. When running the modify SQL information of the SCCM setup, there are two sets of problems that have presented themselves. The first is that when the installation, fails the cluster it waits for AOAG to become healthy again. The problem is that one of our DBs is rather large and takes longer that i believe the script is waiting for, thus when it attempts to continue and do the same process for the next sql member it states that the DBs are synchronized. See log snipplet:

    INFO: ConfigureAOReplicas (AG) - Failing over to this secondary to set DB options.	Configuration Manager Setup	4/5/2020 8:04:19 AM	9248 (0x2420)
    INFO: ConfigureAOReplicas (AG) - Wait for availability group to stabilize. (This can take a few minutes to complete)	Configuration Manager Setup	4/5/2020 8:04:25 AM	9248 (0x2420)
    INFO: ConfigureAOReplicas (AG) - Setting db_owner, clr state, trustworthy, db owner, and broker priority.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    INFO: SQL Connection succeeded. Connection: <SQL2016B address> CM_XXX, Type: Secure	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    INFO: Creating local group ConfigMgr_DViewAccess on SQL2016B for distributed view access.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    Configuring sql distributed view login SQL2016B\ConfigMgr_DViewAccess on SQL2016B	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    Call NetLocalGroupAdd on server <\\SQL2016B>	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    INFO: SQL Connection succeeded. Connection: <SQL2016A Address> MASTER, Type: Secure	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    INFO: ConfigureAOReplicas (AG) - Failing over to original primary [SQL2016A].	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    *** IF EXISTS(SELECT * ~ FROM sys.availability_replicas R ~ INNER JOIN sys.dm_hadr_availability_replica_states S ON R.replica_id = S.replica_id and R.group_id=S.group_id ~ INNER JOIN sys.availability_groups ag on R.group_id = ag.group_id ~ where ag.name = 'SQL2016AOAG' and R.replica_server_name = @@SERVERNAME and S.role != 1) ~ALTER AVAILABILITY GROUP [SQL2016AOAG] FAILOVER;	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    *** [42000][41142][Microsoft][SQL Server Native Client 11.0][SQL Server]The availability replica for availability group 'SQL2016AOAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group. If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    ERROR: SQL Server error: [42000][41142][Microsoft][SQL Server Native Client 11.0][SQL Server]The availability replica for availability group 'SQL2016AOAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group. If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    INFO: Executing SQL Server command: <IF EXISTS(SELECT * ~ FROM sys.availability_replicas R ~ INNER JOIN sys.dm_hadr_availability_replica_states S ON R.replica_id = S.replica_id and R.group_id=S.group_id ~ INNER JOIN sys.availability_groups ag on R.group_id = ag.group_id ~ where ag.name = 'SQL2016AOAG' and R.replica_server_name = @@SERVERNAME and S.role != 1) ~ALTER AVAILABILITY GROUP [SQL2016AOAG] FAILOVER;~>	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    ERROR: Availability group failover failed.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    ERROR: ConfigureAOReplicas (AG) - Failed to set original primary replica <SQL2016A Address> as primary.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    ERROR Failed to configure secondary replica database properties.	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)
    ~===================== Failed Configuration Manager Server Setup - Modify Site Settings =====================	Configuration Manager Setup	4/5/2020 8:09:27 AM	9248 (0x2420)

    In working with my DBA on this issue, he was able to temporary remove the DB in question so that installation would proceed pass this part. However, that did not happen, instead I was presented with different error about the ConfigMgr_DViewAccess already existing. We removed the account from both cluster members, and was still presented the error. 

    INFO: ConfigureAOReplicas (AG) - Failing over to this secondary to set DB options.	Configuration Manager Setup	4/6/2020 7:29:42 PM	5712 (0x1650)
    INFO: ConfigureAOReplicas (AG) - Wait for availability group to stabilize. (This can take a few minutes to complete)	Configuration Manager Setup	4/6/2020 7:29:47 PM	5712 (0x1650)
    INFO: ConfigureAOReplicas (AG) - Setting db_owner, clr state, trustworthy, db owner, and broker priority.	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    *** set quoted_identifier on;set ansi_warnings on;set ansi_padding on;set ansi_nulls on;set concat_null_yields_null on;set arithabort on;set numeric_roundabort off;set DATEFORMAT mdy;	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    *** [08S01][10054][Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    ERROR: SQL Server error: [08S01][10054][Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    *** set quoted_identifier on;set ansi_warnings on;set ansi_padding on;set ansi_nulls on;set concat_null_yields_null on;set arithabort on;set numeric_roundabort off;set DATEFORMAT mdy;	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    *** [08S01][10054][Microsoft][SQL Server Native Client 11.0]Communication link failure	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    ERROR: SQL Server error: [08S01][10054][Microsoft][SQL Server Native Client 11.0]Communication link failure	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    *** Failed to connect to the SQL Server.	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    INFO: SQL Connection failed. Connection: <SQL2016A Address> CM_xxx, Type: Secure	Configuration Manager Setup	4/6/2020 7:29:57 PM	5712 (0x1650)
    INFO: SQL Connection succeeded. Connection: <SQL2016A Address> CM_xxx, Type: Secure	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    INFO: Creating local group ConfigMgr_DViewAccess on SQL2016A for distributed view access.	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    Configuring sql distributed view login SQL2016A\ConfigMgr_DViewAccess on SQL2016A	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    Call NetLocalGroupAdd on server <\\SQL2016A>	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    *** IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name='SQL2016A\ConfigMgr_DViewAccess')  BEGIN  CREATE USER [SQL2016A\ConfigMgr_DViewAccess] FROM LOGIN [SQL2016A\ConfigMgr_DViewAccess]  END	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    *** [42000][15063][Microsoft][SQL Server Native Client 11.0][SQL Server]The login already has an account under a different user name.	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    ERROR: SQL Server error: [42000][15063][Microsoft][SQL Server Native Client 11.0][SQL Server]The login already has an account under a different user name.	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    ERROR: Failed to create user for ConfigMgr_DViewAccess	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    INFO: ConfigureAOReplicas (AG) - Failed to create distributed view local group.	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    ERROR Failed to configure secondary replica database properties.	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    ~===================== Failed Configuration Manager Server Setup - Modify Site Settings =====================	Configuration Manager Setup	4/6/2020 7:30:00 PM	5712 (0x1650)
    

    At this point not sure how to proceed. Ideally, I would like to modify the SQL settings without having to have my DBA remove the extremely large DB and secondly, I would like to get this pass this particular error. As it seemed to do before we removed the DBs. 

    Tuesday, April 7, 2020 12:20 PM

All replies

  • Hi,

    >ERROR: SQL Server error: [42000][41142][Microsoft][SQL Server Native Client 11.0][SQL Server]The availability replica for availability group 'SQL2016AOAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group.

    Looks you have same error like this article mentioned, kindly check if the cumulative updates for SQL Server are installed:
    https://support.microsoft.com/en-hk/help/4492604/fix-manual-failover-between-forwarder-and-secondary-replica-fails-with

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, April 8, 2020 6:43 AM
  • The article suggest upgrading to CU 7 for 2016 SP2, but we are already running CU 11 on our SQL AOAG.  
    Thursday, April 9, 2020 2:24 PM
  • Hi,

    Try below to see if it helps:
    Check if *_FailoverCluster_health_XeLogs confirm that the cluster was started in forced_quorum mode (forced_quorum True in availability_replica_automatic_failover_validation event).​​ 
        When a cluster is started in forced_quorum mode (net.exe start clussvc /forcequorum, OR Failover Cluster Manager>left pane>right-click Windows cluster>choose Force​​ Cluster Start>Confirm by clicking Yes.) on primary replica, the availability group is required to be started by issuing command ALTER AVAILABILITY GROUP <AGNAME> FORCE_FAILOVER_ALLOW_DATA_LOSS;. Because this is the original primary, despite issuing force allow data loss, NO data loss occurs
        When a cluster is started in forced_quorum mode on secondary replica, first check sys.dm_hadr_database_replica_cluster_states.is_failover_ready on secondary. If this value is 1, and be assured no data loss will occur. If​​ this value is not 1 (for example because it’s asynchronous commit), then can check sys.dm_hadr_database_replica_states.last_commit_time, so as to know estimate start time of data loss. The sys.dm_hadr_database_replica_states.last_hardened_lsn value may be​​ used to consider other secondaries for failover. Then can initiate failover with ALTER AVAILABILITY GROUP <AGNAME> FORCE_FAILOVER_ALLOW_DATA_LOSS; on secondary.

    The reference(Third-party link, just for reference):
    http://sqlconjuror.com/tag/alwayson/

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, April 14, 2020 8:58 AM