Sunday, July 16, 2017

SQL Server 2016 AG Setup Part 4–AG1 between SQL01\AGINST1 and SQL02\AGINST4

During this example, different port used for as part of illustration. Different port number between AG and instance is not a requirement, it’s a best practice instead as one instance can support multiple AG groups.
  • Install SQL instance, configure port number, as well as enable for AlwaysOn
SQL01\AGINST1: port 2444, SQL02\AGINST4: port 2446, AG1: port 2447
VirtualBox_SQL01_15_07_2017_13_19_39VirtualBox_SQL01_15_07_2017_13_20_04VirtualBox_SQL01_15_07_2017_13_21_19VirtualBox_SQL01_15_07_2017_13_21_37VirtualBox_SQL01_15_07_2017_13_21_42VirtualBox_SQL01_15_07_2017_13_23_57
VirtualBox_SQL01_15_07_2017_13_41_14
VirtualBox_SQL01_15_07_2017_13_44_42
--- Steps for SQL02\AGINST4 omitted
  • Configure AG1 (DB/translog backup is required)
VirtualBox_PEGAAD_15_07_2017_14_16_08VirtualBox_PEGAAD_15_07_2017_14_25_16
InkedVirtualBox_PEGAAD_15_07_2017_14_26_43_LI
VirtualBox_PEGAAD_15_07_2017_14_26_49VirtualBox_PEGAAD_15_07_2017_14_28_20VirtualBox_PEGAAD_15_07_2017_14_28_04VirtualBox_PEGAAD_15_07_2017_14_28_09

VirtualBox_PEGAAD_15_07_2017_14_28_57

VirtualBox_PEGAAD_15_07_2017_14_30_28
VirtualBox_PEGAAD_15_07_2017_14_30_40VirtualBox_PEGAAD_15_07_2017_14_35_57

I was able to resolve above error by a new translog backup/restore performed for this DB1.
VirtualBox_PEGAAD_15_07_2017_14_59_32

Additional, it’s not possible to select instances on the same SQL Server Node as part of AG group replica.