resource/aws_db_instance: Prevent error when using snapshot_identifier with multi_az enabled and sqlserver engine

[bflad]

When calling RestoreDBInstanceFromDBSnapshot and attempting to enable MultiAZ with Engine set to SQL Server, MultiAZ must be set to disabled since the API requires BackupRetentionPeriod and that parameter is not available for the initial API call. We then fix the configuration by calling ModifyDBInstance afterwards during creation.

Previously:

--- PASS: TestAccAWSDBInstance_SnapshotIdentifier_MultiAZ (2021.96s)
--- FAIL: TestAccAWSDBInstance_SnapshotIdentifier_MultiAZ_SQLServer (1101.33s)
	testing.go:527: Step 0 error: Error applying: 1 error occurred:
			* aws_db_instance.test: 1 error occurred:
			* aws_db_instance.test: Error creating DB Instance: InvalidParameterValue: Mirroring cannot be applied to instances with backup retention set to zero.

Fixes #5594

Changes proposed in this pull request:

• When using multi_az with snapshot_identifier and engine set to sqlserver*, remove parameter or catch specific error and require ModifyDBInstance API call afterwards
• When ModifyDBInstance is being called during resource creation, always set ApplyImmediately to true to prevent need for double apply.

Output from acceptance testing:

28 tests passed (all tests)
=== RUN   TestAccAWSDBInstance_generatedName
--- PASS: TestAccAWSDBInstance_generatedName (451.02s)
=== RUN   TestAccAWSDBInstance_basic
--- PASS: TestAccAWSDBInstance_basic (451.32s)
=== RUN   TestAccAWSDBInstance_iamAuth
--- PASS: TestAccAWSDBInstance_iamAuth (451.74s)
=== RUN   TestAccAWSDBInstance_kmsKey
--- PASS: TestAccAWSDBInstance_kmsKey (480.43s)
=== RUN   TestAccAWSDBInstance_importBasic
--- PASS: TestAccAWSDBInstance_importBasic (482.33s)
=== RUN   TestAccAWSDBInstance_optionGroup
--- PASS: TestAccAWSDBInstance_optionGroup (542.54s)
=== RUN   TestAccAWSDBInstance_namePrefix
--- PASS: TestAccAWSDBInstance_namePrefix (673.91s)
=== RUN   TestAccAWSDBInstance_separate_iops_update
--- PASS: TestAccAWSDBInstance_separate_iops_update (736.80s)
=== RUN   TestAccAWSDBInstance_s3
--- PASS: TestAccAWSDBInstance_s3 (766.48s)
=== RUN   TestAccAWSDBInstance_noSnapshot
--- PASS: TestAccAWSDBInstance_noSnapshot (855.87s)
=== RUN   TestAccAWSDBInstance_subnetGroup
--- PASS: TestAccAWSDBInstance_subnetGroup (864.45s)
=== RUN   TestAccAWSDBInstance_enhancedMonitoring
--- PASS: TestAccAWSDBInstance_enhancedMonitoring (870.26s)
=== RUN   TestAccAWSDBInstance_ec2Classic
--- PASS: TestAccAWSDBInstance_ec2Classic (425.19s)
=== RUN   TestAccAWSDBInstance_MinorVersion
--- PASS: TestAccAWSDBInstance_MinorVersion (459.21s)
=== RUN   TestAccAWSDBInstance_diffSuppressInitialState
--- PASS: TestAccAWSDBInstance_diffSuppressInitialState (449.01s)
=== RUN   TestAccAWSDBInstance_snapshot
--- PASS: TestAccAWSDBInstance_snapshot (986.33s)
=== RUN   TestAccAWSDBInstance_portUpdate
--- PASS: TestAccAWSDBInstance_portUpdate (577.52s)
=== RUN   TestAccAWSDBInstance_cloudwatchLogsExportConfiguration
--- PASS: TestAccAWSDBInstance_cloudwatchLogsExportConfiguration (514.93s)
=== RUN   TestAccAWSDBInstance_cloudwatchLogsExportConfigurationUpdate
--- PASS: TestAccAWSDBInstance_cloudwatchLogsExportConfigurationUpdate (596.49s)
=== RUN   TestAccAWSDBInstance_SnapshotIdentifier_VpcSecurityGroupIds
--- PASS: TestAccAWSDBInstance_SnapshotIdentifier_VpcSecurityGroupIds (1271.68s)
=== RUN   TestAccAWSDBInstance_SnapshotIdentifier_Tags
--- PASS: TestAccAWSDBInstance_SnapshotIdentifier_Tags (1311.95s)
=== RUN   TestAccAWSDBInstance_SnapshotIdentifier_VpcSecurityGroupIds_Tags
--- PASS: TestAccAWSDBInstance_SnapshotIdentifier_VpcSecurityGroupIds_Tags (1392.32s)
=== RUN   TestAccAWSDBInstance_SnapshotIdentifier
--- PASS: TestAccAWSDBInstance_SnapshotIdentifier (1400.45s)
=== RUN   TestAccAWSDBInstance_EnabledCloudwatchLogsExports_Oracle
--- PASS: TestAccAWSDBInstance_EnabledCloudwatchLogsExports_Oracle (752.41s)
=== RUN   TestAccAWSDBInstance_replica
--- PASS: TestAccAWSDBInstance_replica (1584.23s)
=== RUN   TestAccAWSDBInstance_MSSQL_TZ
--- PASS: TestAccAWSDBInstance_MSSQL_TZ (1508.37s)
=== RUN   TestAccAWSDBInstance_SnapshotIdentifier_MultiAZ
--- PASS: TestAccAWSDBInstance_SnapshotIdentifier_MultiAZ (1975.75s)
=== RUN   TestAccAWSDBInstance_SnapshotIdentifier_MultiAZ_SQLServer
--- PASS: TestAccAWSDBInstance_SnapshotIdentifier_MultiAZ_SQLServer (3834.22s)

[mbfrahry]

LGTM

[bflad]

This has been released in version 1.33.0 of the AWS provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading.

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. Thanks!