Failed to Change the Instance Class of an RDS for SQL Server Instance or Perform a Primary/Standby Switchover

Scenarios

• When you change the instance class of your RDS for SQL Server instance on the console, the error message "Primary/standby replication is abnormal. Try again later." is displayed in the upper right corner.
• When you perform a primary/standby switchover on the console, the error message "Failed to perform the switchover because the primary/standby replication status is abnormal." is displayed in the upper right corner.

Fault Analysis

One possible cause is that the replication between the primary and standby instances is abnormal. You can use either of the following methods to analyze the fault:

• Method 1: Checking the Metric Replication Delay on the Console
  1. Log in to the management console.
  2. On the Instances page, locate the DB instance and click View Metrics in the Operation column.

     You can also click the DB instance name and, on the displayed page, click View Metrics in the upper right corner.

  3. On the displayed page, you can see the replication delay of the instance. If the value is too large, for example, 99999, the replication between the primary and standby instances is abnormal.
• Method 2: Checking the Database Replication Status Using SQL Server Management Studio (SSMS)

  As shown in Figure 1, (Principal, Synchronized) is displayed following the database name for databases with a normal replication, for example, rdsadmin.

  If the db1 database does not have a replication status displayed, the replication is not set up. In this case, a primary/standby switchover cannot be performed. If the status (Principal, Disconnected) is displayed, the replication was interrupted and a primary/standby switchover cannot be performed.

  

  If your database engine uses 2017 Enterprise Edition and the database replication is normal, only (Synchronized) is displayed.

  Figure 1 Database replication status
  

Solution

Check the properties of the database for which no replication is set up.

Log in to the SSMS client. Right-click the problem database and choose Properties from the shortcut menu to view the values of Recovery model and Auto Close.

Figure 2 Viewing database properties

• If Recovery model is set to Simple, no replication can be established. Change it to full through the SSMS or run the following SQL statement:

  ALTER DATABASE [database_name] SET RECOVERY FULL WITH NO_WAIT

  [database_name]: name of the database

  Example:

  ALTER DATABASE [db1] SET RECOVERY FULL WITH NO_WAIT

• If Auto Close is set to True, no replication can be established, either. Change it to False through the SSMS or run the following SQL statement:

  ALTER DATABASE [db1] SET AUTO_CLOSE OFF WITH NO_WAIT