Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1003471%22%20slang%3D%22en-US%22%3ECreate%20Distributed%20Availability%20Group%20in%20Sql%20Server%20Linux(Redhat)%20---%20step%20by%20step%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1003471%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20article%20describes%20how%20to%20create%20a%20SQL%20Server%20Distributed%20Availability%20Group%20(AG)%20for%20high%20availability%20on%20Linux(Redhat%2FCentos).%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EHere%20is%20the%20topology%20of%20the%20DAG%20environment%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CTABLE%20style%3D%22width%3A%20577px%3B%20height%3A%20147px%3B%22%20border%3D%221%22%20width%3D%22496%22%20cellspacing%3D%220%22%20cellpadding%3D%222%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20valign%3D%22top%22%20style%3D%22width%3A%20302px%3B%22%3E%3CP%20align%3D%22center%22%3E%3CSTRONG%3EData%20Center%201%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20valign%3D%22top%22%20style%3D%22width%3A%20274px%3B%22%3E%3CP%20align%3D%22center%22%3E%3CSTRONG%3EData%20Center%202%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20valign%3D%22top%22%20style%3D%22width%3A%20302px%3B%22%3E%3CUL%3E%0A%3CLI%3ECen1%3A192.168.1.61%20(Primary%20replica)%3C%2FLI%3E%0A%3CLI%3ECen2%3A192.168.1.62%3C%2FLI%3E%0A%3CLI%3ECen3%3A192.168.1.63%3C%2FLI%3E%0A%3CLI%3EAvailability%20group%20name%3AAG1%3C%2FLI%3E%0A%3CLI%3Eag1-listener%3A192.168.1.65%3C%2FLI%3E%0A%3CLI%3ESQL%20Server%3A%202017%20CU17%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FTD%3E%0A%3CTD%20valign%3D%22top%22%20style%3D%22width%3A%20274px%3B%22%3E%3CUL%3E%0A%3CLI%3ECen4%3A192.168.1.71%20(Forwarder)%3C%2FLI%3E%0A%3CLI%3ECen5%3A192.168.1.72%3C%2FLI%3E%0A%3CLI%3EAvailability%20group%20name%3AAG2%3C%2FLI%3E%0A%3CLI%3Eag2-listener%3A192.168.1.75%3C%2FLI%3E%0A%3CLI%3ESQL%20Server%3A%202017%20CU17%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3EPlease%20make%20use%20following%20ports%20are%20opened%20on%20all%20the%20servers%20in%20both%20datacenters.%3C%2FP%3E%0A%3CP%3ETCP%3A5022%2C2224%2C3123%2C21064%3C%2FP%3E%0A%3CP%3EUDP%3A5405%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EFor%20RHEL%2C%20you%20need%20to%20have%20the%20'high%20availability%20subscription'%20enabled.%20Please%20refer%20%3C%2FSPAN%3E%3CA%20style%3D%22background-color%3A%20transparent%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23146cac%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20underline%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fsql-server-linux-availability-group-cluster-rhel%3Fview%3Dsql-server-ver15%23configure-high-availability-for-rhel%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fsql-server-linux-availability-group-cluster-rhel%3Fview%3Dsql-server-ver15%23configure-high-availability-for-rhel%3C%2FA%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%20for%20detail.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId--743759891%22%20id%3D%22toc-hId--743759891%22%3E%3CSTRONG%3ESection%201%3ACreate%20Availability%20group%20AG1%20in%20datacenter1%3C%2FSTRONG%3E%3C%2FH4%3E%0A%3CP%3E1.%20Updates%20%2Fetc%2Fhosts%20on%20all%20the%20nodes%20Datacenter1%2C%20add%20IP%20%2C%20replica%20name%20and%20AG%20Listener%20name.%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20screenshot.%20All%20servers%20in%20the%20datacenter1%20should%20have%20same%20setting.(Please%20note%2C%20the%20IP%20resources%20of%20Listeners%20will%20be%20created%20in%20later%20steps%2C%20it's%20ok%20to%20add%20at%20the%20very%20beginning)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157218iE412D3DCE8DD8AA4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E2.%20Run%20following%20bash%20commands%20on%20all%20servers%20in%20datacenter1%20to%20enable%20Alwayson%20Feature%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20%3C%2FFONT%3Esudo%20%2Fopt%2Fmssql%2Fbin%2Fmssql-conf%20set%20hadr.hadrenabled%201%0A%3CFONT%20size%3D%222%22%3E%24%20%3C%2FFONT%3Esudo%20systemctl%20restart%20mssql-server%20%20%20%20%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157217i769332139426C5B2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E3.%20Run%20following%20T-SQL%20to%20Enable%20Always%20On%20Health%20check%20event%20session%20on%20all%20the%20servers%20in%20datacenter1%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20EVENT%20SESSION%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EAlwaysOn_health%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EON%20SERVER%20WITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3ESTARTUP_STATE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E4.%20Create%20a%20certificate%2C%20run%20the%20query%20on%20primary%20replica%20in%20datacenter1(cen1%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Euse%20master%0Ago%0ACREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'**%3CMASTER_KEY_PASSWORD%3E**'%3C%2FMASTER_KEY_PASSWORD%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ego%0ACREATE%20CERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20SUBJECT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'dbm'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ego%0ABACKUP%20CERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ETO%20FILE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdbm_certificate.cer'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20PRIVATE%20KEY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFILE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdbm_certificate.pvk'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENCRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'**%3CPRIVATE_KEY_PASSWORD%3E**'%0A%3C%2FPRIVATE_KEY_PASSWORD%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E5.%20Copy%20the%20certificate%20to%20rest%20of%20the%20servers%20in%20datacenter1(cen2%2Ccen3%20in%20this%20case)%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20cd%20%2Fvar%2Fopt%2Fmssql%2Fdata%0A%24%20scp%20dbm_certificate.*%20root%40**%3CNODENAME%3E**%3A%2Fvar%2Fopt%2Fmssql%2Fdata%2F%3C%2FNODENAME%3E%3C%2FFONT%3E%20%20%20%20%3C%2FPRE%3E%0A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20649px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157216i4FF5774E227C676A%2Fimage-dimensions%2F649x193%3Fv%3D1.0%22%20width%3D%22649%22%20height%3D%22193%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E6.%20Give%20permission%20to%20the%20mssql%20user%20to%20access%20the%20certificate%20files%20in%20rest%20of%20the%20servers(cen2%2Ccen3%20in%20this%20case)%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3Ecd%20%2Fvar%2Fopt%2Fmssql%2Fdata%0Achown%20mssql%3Amssql%20dbm_certificate.*%3C%2FFONT%3E%20%20%20%20%3C%2FPRE%3E%0A%3C%2FDIV%3E%0A%3CP%3E7.%20Create%20the%20certificate%20on%20rest%20of%20the%20servers%20by%20restoring%20the%20certificate%20backup(cen2%2Ccen3%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Euse%20master%0Ago%0ACREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'**%3CMASTER_KEY_PASSWORD%3E**'%3C%2FMASTER_KEY_PASSWORD%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ego%0ACREATE%20CERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20SUBJECT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'dbm'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ego%0ABACKUP%20CERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ETO%20FILE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdbm_certificate.cer'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20PRIVATE%20KEY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFILE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdbm_certificate.pvk'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENCRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'**%3CPRIVATE_KEY_PASSWORD%3E**'%0A%3C%2FPRIVATE_KEY_PASSWORD%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E8.%20Create%20endpoint%20on%20all%20servers%20in%20datacenter1.%20The%20port%205022%20is%20used.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20ENDPOINT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BHadr_endpoint%5D%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAS%20TCP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ELISTENER_PORT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E5022%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFOR%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20lime%3B%22%3EDATABASE_MIRRORING%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EROLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITNESS%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTHENTICATION%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENCRYPTION%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EREQUIRED%20ALGORITHM%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EAES%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20ENDPOINT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BHadr_endpoint%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESTATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESTARTED%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E9.%20Create%20AG%20with%203%20synchronous%20replicas%20in%20datacenter1.%20Run%20following%20query%20on%20primary%20replica%20(cen1%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag1%5D%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDB_FAILOVER%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECLUSTER_TYPE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFOR%20REPLICA%20ON%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'cen1'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENDPOINT_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'tcp%3A%2F%2Fcen1%3A5022'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESYNCHRONOUS_COMMIT%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC)%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'cen2'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENDPOINT_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'tcp%3A%2F%2Fcen2%3A5022'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESYNCHRONOUS_COMMIT%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC)%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'cen3'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENDPOINT_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'tcp%3A%2F%2Fcen3%3A5022'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESYNCHRONOUS_COMMIT%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%3CBR%20%2F%3E%3CBR%20%2F%3EALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGRANT%20CREATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EANY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATABASE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20green%3B%22%3E--grant%20create%20any%20database%20permission%0A%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20make%20sure%20the%20port%205022%20is%20in%20the%20firewall%20exception%20list%2C%20else%20you%20may%20run%20into%20following%20error%20messages%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%223%22%3E2019-09-11%2016%3A10%3A18.560%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20spid40s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Error%3A%208471%2C%20Severity%3A%2016%2C%20State%3A%202.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%223%22%3E2019-09-11%2016%3A10%3A18.560%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20spid40s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20An%20SNI%20call%20failed%20during%20a%20Service%20Broker%2FDatabase%20Mirroring%20transport%20operation.%20SNI%20error%20'10022(An%20invalid%20argument%20was%20supplied.)'.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%223%22%3E2019-09-11%2016%3A10%3A18.570%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20spid40s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Error%3A%209642%2C%20Severity%3A%2016%2C%20State%3A%201.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%223%22%3E2019-09-11%2016%3A10%3A18.570%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20spid40s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20An%20error%20occurred%20in%20a%20Service%20Broker%2FDatabase%20Mirroring%20transport%20connection%20endpoint%2C%20Error%3A%208471%2C%20State%3A%202.%20(Near%20endpoint%20role%3A%20Initiator%2C%20far%20endpoint%20address%3A%20'1')%3C%2FFONT%3E%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E10.%20You%20will%20see%20below%20message%20in%20errorlog%20file%20of%20primary%20replica.%20The%20timeout%20happens%20because%20the%20secondary%20replicas%20have%20not%20joined%20the%20AG%20group%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A00%3A44.530%20spid62%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20The%20state%20of%20the%20local%20availability%20replica%20in%20availability%20group%20'ag1'%20has%20changed%20from%20'NOT_AVAILABLE'%20to%20'RESOLVING_NORMAL'.%20The%20state%20changed%20because%20the%20local%20availability%20replica%20is%20joining%20the%20availability%20group.%20For%20more%20information%2C%20see%20the%20SQL%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A00%3A44.570%20spid62%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Always%20On%3A%20The%20local%20replica%20of%20availability%20group%20'ag1'%20is%20preparing%20to%20transition%20to%20the%20primary%20role.%20This%20is%20an%20informational%20message%20only.%20No%20user%20action%20is%20required.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A00%3A44.610%20spid62%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20The%20state%20of%20the%20local%20availability%20replica%20in%20availability%20group%20'ag1'%20has%20changed%20from%20'RESOLVING_NORMAL'%20to%20'PRIMARY_PENDING'.%20The%20state%20changed%20because%20the%20availability%20group%20is%20coming%20online.%20For%20more%20information%2C%20see%20the%20SQL%20Server%20error%20log%20or%20clu%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A00%3A44.620%20Server%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20The%20state%20of%20the%20local%20availability%20replica%20in%20availability%20group%20'ag1'%20has%20changed%20from%20'PRIMARY_PENDING'%20to%20'PRIMARY_NORMAL'.%20The%20state%20changed%20because%20the%20local%20replica%20has%20completed%20processing%20Online%20command%20from%20Windows%20Server%20Failover%20Clustering%20(WS%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A00%3A54.630%20spid39s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%20connection%20timeout%20has%20occurred%20while%20attempting%20to%20establish%20a%20connection%20to%20availability%20replica%20'cen2'%20with%20id%20%5B6C1CD374-CB97-477A-B8DB-589D94A0FCB6%5D.%20Either%20a%20networking%20or%20firewall%20issue%20exists%2C%20or%20the%20endpoint%20address%20provided%20for%20the%20replica%20is%20no%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A00%3A54.650%20spid40s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%20connection%20timeout%20has%20occurred%20while%20attempting%20to%20establish%20a%20connection%20to%20availability%20replica%20'cen3'%20with%20id%20%5BD8E1CE2A-10CF-409B-98B1-9D3B35FEE91A%5D.%20Either%20a%20networking%20or%20firewall%20issue%20exists%2C%20or%20the%20endpoint%20address%20provided%20for%20the%20replica%20is%20no%3C%2FFONT%3E%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E11.%20Join%20the%20AG%20group%2C%20run%20the%20following%20T-SQL%20queries%20on%20%3CSTRONG%3Eall%20the%20secondary%20servers%20in%20datacenter1%3C%2FSTRONG%3E(cen2%2Ccen3%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EJOIN%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECLUSTER_TYPE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGRANT%20CREATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EANY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATABASE%0A%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157219iB0F425C4BACC102E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E12.%20You%20will%20see%20below%20message%20in%20errorlog%20file%20of%20primary%20replica.%20Connection%20established%20with%20two%20secondary%20replicas.%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A01%3A23.820%20spid11s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%20connection%20for%20availability%20group%20'ag1'%20from%20availability%20replica%20'cen1'%20with%20id%20%5B03D58956-AAC2-4A1D-975E-3F3B788752A2%5D%20to%20'cen2'%20with%20id%20%5B6C1CD374-CB97-477A-B8DB-589D94A0FCB6%5D%20has%20been%20successfully%20established.%20This%20is%20an%20informational%20message%20only.%20N%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%3E2019-09-05%2020%3A02%3A04.950%20spid31s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%20connection%20for%20availability%20group%20'ag1'%20from%20availability%20replica%20'cen1'%20with%20id%20%5B03D58956-AAC2-4A1D-975E-3F3B788752A2%5D%20to%20'cen3'%20with%20id%20%5BD8E1CE2A-10CF-409B-98B1-9D3B35FEE91A%5D%20has%20been%20successfully%20established.%20This%20is%20an%20informational%20message%20only.%20N%3C%2FFONT%3E%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3E13.%20Create%20database%20and%20add%20to%20AG%20group%20on%20primary%20replica.(cen1%20in%20this%20case).%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20DATABASE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdb1%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20DATABASE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdb1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESET%20RECOVERY%20FULL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EBACKUP%20DATABASE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdb1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ETO%20DISK%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdb1.bak'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EBACKUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20magenta%3B%22%3Elog%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdb1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ETO%20DISK%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdb1.trn'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%0AALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag1%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EADD%20DATABASE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdb1%5D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EThe%20database%20db1%20will%20created%20in%20the%20rest%20of%20the%20servers%20by%20auto-seeding.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157221iF4CCB33CB7A65C0A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_4.png%22%20title%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E14.%20Create%20SQL%20login%20pacemaker%20on%20all%20servers%20in%20datacenter1%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20LOGIN%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BpacemakerLogin%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3Ewith%20PASSWORD%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'ComplexP%40%24%24w0rd!'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%0AALTER%20SERVER%20ROLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bsysadmin%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EADD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EMEMBER%20%5BpacemakerLogin%5D%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EThe%20Availability%20group%20has%20been%20created.%20Unlike%20SQL%20Server%20in%20Windows%2C%20SQL%20Server%20Linux%20does%20not%20create%20any%20resource%20in%20Linux%2C%20you%20need%20to%20create%20these%20resource%20manually.%20Step%2015~29%20will%20cover%20the%20detail%3A%3C%2FP%3E%0A%3CP%3E15.%20Install%20SQL%20Server%20resource%20agent%20for%20SQL%20Server.%20Run%20the%20following%20bash%20command%20on%20all%20servers%20in%20datacenter1.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20yum%20install%20mssql-server-ha%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E16.%20Install%20pacemaker.Run%20the%20following%20bash%20command%20on%20all%20servers%20in%20datacenter1.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20yum%20install%20pacemaker%20pcs%20fence-agents-all%20resource-agents%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E17.%20Set%20the%20password%20for%20the%20default%20user%20that%20is%20created%20when%20installing%20Pacemaker%20and%20Corosync%20packages.%20Run%20following%20command%20on%20all%20servers%20in%20datacenter1(This%20command%20asks%20for%20password.%20The%20password%20should%20be%20same%20for%20the%20servers%20in%20same%20datacenter)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20passwd%20hacluster%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157220i45DE92B1391EBFEF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_5.png%22%20title%3D%22clipboard_image_5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E18.%20To%20allow%20nodes%20to%20rejoin%20the%20cluster%20after%20the%20reboot%2C%20enable%20and%20start%20pcsd%20service%20and%20Pacemaker.%20Run%20the%20following%20commands%20on%20all%20servers%20in%20datacenter1.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20systemctl%20enable%20pcsd%0A%24%20sudo%20systemctl%20start%20pcsd%0A%24%20sudo%20systemctl%20enable%20pacemaker%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E19.%20Create%20a%20cluster%20in%20datacenter1.%20Run%20following%20commands%20on%20any%20one%20of%20the%20nodes%20in%20datacenter1(just%20on%20one%20server%2C%20and%20just%20one%20time).%20The%20'%3CFONT%20size%3D%222%22%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2013.33px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3EYourStrongPassword%3C%2FSTRONG%3E%3C%2FFONT%3E'%20highlighted%20should%20be%20replaced%20with%20the%20password%20specified%20in%20step%2017.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20pcs%20cluster%20auth%20cen1%20cen2%20cen3%20-u%20hacluster%20-p%20%3CSTRONG%3EYourStrongPassword%3C%2FSTRONG%3E%3CBR%20%2F%3E%24%20sudo%20pcs%20cluster%20setup%20--name%20cluster1%20cen1%20cen2%20cen3%0A%24%20sudo%20pcs%20cluster%20start%20--all%0A%24%20sudo%20pcs%20cluster%20enable%20--all%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157224iC10B0575F28D14DA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_7.png%22%20title%3D%22clipboard_image_7.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E20.%20Check%20the%20cluster%20status%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20pcs%20cluster%20status%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157223i58FBCDEFBD9A236C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_8.png%22%20title%3D%22clipboard_image_8.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E21.%20Because%20the%20node%20level%20fencing%20configuration%20depends%20heavily%20on%20your%20environment%2C%20disable%20it%20for%20this%20tutorial%20(%3CSTRONG%3Eit%20can%20be%20configured%20later.%20In%20my%20test%2C%20if%20this%20value%20is%20not%20set%20to%20false%2C%20you%20can't%20failover%2C%20and%20IP%20resource%20may%20fail%3C%2FSTRONG%3E).%20The%20following%20script%20disables%20node%20level%20fencing(run%20it%20on%20any%20nodes%20in%20datacenter1)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20property%20set%20stonith-enabled%3Dfalse%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CDIV%3E22.%20On%20all%20SQL%20Server%20Linux%20servers%20in%20datacenter1%20%2C%20save%20the%20credentials%20for%20the%20SQL%20Server%20login.(Use%20the%20same%20user%20name%20and%20password%20created%20in%20step14)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%3CFONT%20face%3D%22Courier%20New%22%3E%24%20echo%20'pacemakerLogin'%20%26gt%3B%26gt%3B%20~%2Fpacemaker-passwd%20%0A%24%20echo%20'ComplexP%40%24%24w0rd!'%20%26gt%3B%26gt%3B%20~%2Fpacemaker-passwd%20%0A%24%20sudo%20mv%20~%2Fpacemaker-passwd%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2Fpasswd%20%0A%24%20sudo%20chown%20root%3Aroot%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2Fpasswd%20%0A%24%20sudo%20chmod%20400%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2Fpasswd%20%23%20Only%20readable%20by%20root%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FPRE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E23.%20Create%20availability%20group%20resource%20at%20cluster%20level%20in%20datacenter1%2C%20run%20following%20command%20on%20any%20one%20of%20the%20nodes%20in%20datacenter1(just%20on%20one%20node%2C%20and%20just%20one%20time)%3C%2FP%3E%0A%3CP%3EHere%20are%20the%20resource%20and%20constraint%20before%20creating%20the%20resource(no%20resource%2Fconstraint)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157225iEBE5742AFC787AE3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_9.png%22%20title%3D%22clipboard_image_9.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20command%20to%20create%20cluster%20resource.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20resource%20create%20ag_cluster1%20ocf%3Amssql%3Aag%20ag_name%3Dag1%20meta%20failure-timeout%3D60s%20master%20notify%3Dtrue%3C%2FFONT%3E%0A%3C%2FPRE%3E%0A%3CP%3E-------------note%20begin------------------%3C%2FP%3E%0A%3CP%3EIt%20creates%20a%20resource%20called%20ag_cluster1-master(it%20add%20the%20'-master'%20keyword%20automatically%20to%20the%20name)%3C%2FP%3E%0A%3CP%3EAnd%20bind%20the%20resource%20to%20availability%20group%20'ag1'%3C%2FP%3E%0A%3CP%3EAnd%20it%20set%20the%20current%20primary%20replica%20as%20Master%2C%20the%20rest%20of%20nodes%20are%20Slaves(it%20check%20the%20status%20of%20AG%20group%20and%20make%20the%20decision%3C%2FP%3E%0A%3CP%3EPlease%20note%2C%20it%20does%20not%20return%20any%20error%20if%20you%20put%20an%20incorrect%20Availability%20group%20name%20(in%20this%20case%2C%20the%20%3CSTRONG%3Eag1%3C%2FSTRONG%3E%20is%20used%2C%20make%20sure%20you%20put%20correct%20name)%3C%2FP%3E%0A%3CP%3EInternally%2C%20pacemaker%20issues%20T-SQL%20queries%20to%20SQL%20Server%20to%20check%20if%20the%20Ag%20exists%3C%2FP%3E%0A%3CP%3E-----------note%20end------------------%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157227i80A8C5898A52167D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_10.png%22%20title%3D%22clipboard_image_10.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EPlease%20note.%20Pacemaker%20starts%20to%20connect%20the%20SQL%20Server%20instance%20using%20the%20login%20'pacemakerLogin'%20created%20in%20step%2014.%20If%20the%20step%2014%20or%20step%2022%20is%20skipped%2C%20the%20AG%20resource%20in%20both%20SQL%20and%20Cluster%20will%20fail.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E24.%20Create%20virtual%20IP%20resource.%20The%20resource%20name%20is%20'virtualip1'%2C%20and%20IP%20Address%20is%20192.168.1.65%20in%20this%20demonstration.%20This%20is%20the%20IP%20Address%20for%20the%20AG%20Listener.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20resource%20create%20virtualip1%20ocf%3Aheartbeat%3AIPaddr2%20ip%3D192.168.1.65%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3EThe%20resource%20is%20will%20start%20automatically%20after%20it'%20created%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157226i43BE44E086F60369%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_11.png%22%20title%3D%22clipboard_image_11.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E25.%20Create%20Availability%20group%20listener%20for%20Availability%20group%20ag1.%20Run%20following%20T-SQL%20query%20in%20primary%20replica(cen1%20in%20this%20case).%20The%20ip%20address%20should%20be%20as%20same%20as%20the%20IP%20in%20step%2024.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag1%5D%20%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EADD%20LISTENER%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'ag1-listener'%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20IP%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E((%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'192.168.1.65'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'255.255.255.0'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E))%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EPORT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E1433%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3Ea)It's%20fine%20if%20you%20run%20step%2025%20before%20the%20step%2024%2C%20however%2C%3CSTRONG%3E%20you%20can't%20use%20the%20IP%20Address%20to%20connect%20SQL%20Server%20until%20the%20resource%20in%20Pacemaker%20is%20created.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Eb)There%20is%20a%20bug%20in%20DMV%20sys.availability_group_listener_ip_addresses%20showing%20the%20state%20is%3CSTRONG%3E%20offline%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E26.%20Add%20colocation%20constraint.%20Please%20note%2C%20without%20the%20constraint%2C%20the%20ip%20resource%20does%20not%20failover%20when%20the%20AG%20resource%20failover.%20Hence%20we%20need%20to%20create%20a%20constraint%20to%20guarantee%20that%20the%20IP%20will%20failover%20when%20cluster%20resource%20failover.%20Run%20following%20command%20on%20any%20servers%20in%20datacenter1(just%20on%20one%20server%2C%20and%20just%20one%20time)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20constraint%20colocation%20add%20virtualip1%20ag_cluster1-master%20INFINITY%20with-rsc-role%3DMaster%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157228i53DBA79E35D0AEA2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_12.png%22%20title%3D%22clipboard_image_12.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E27.%20Add%20ordering%20constraint%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20%3C%2FFONT%3E%3CFONT%20size%3D%222%22%3Esudo%20pcs%20constraint%20order%20promote%20ag_cluster1-master%20then%20start%20virtualip1%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157229iDFAC40DF92D2CFF0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_13.png%22%20title%3D%22clipboard_image_13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E28.%20To%20update%20the%20property%20value%20to%202%20minutes%20run.%20Run%20it%20on%20any%20node%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20property%20set%20cluster-recheck-interval%3D2min%0A%3CFONT%20size%3D%222%22%3E%24%20%3C%2FFONT%3Esudo%20pcs%20property%20set%20start-failure-is-fatal%3Dtrue%0A%3CFONT%20size%3D%222%22%3E%24%20%3C%2FFONT%3Epcs%20resource%20update%20ag_cluster1%20meta%20failure-timeout%3D60s%3C%2FFONT%3E%20%20%3C%2FPRE%3E%0A%3CP%3E29.%20-----note--%20The%20order%20of%20'%3CSTRONG%3Eordering%20constraint%3C%2FSTRONG%3E'%20matters-----------%3C%2FP%3E%0A%3CP%3EI%20have%20one%20customer%20who%20had%20a%20wrong%20order%20constraint.%20In%20his%20case%2C%20he%20had%20following%20constraint%20that%20promoted%20virtualip%20first%2C%20then%20start%20node.%20This%20will%20make%20the%20resource%20fail(may%20happen%2C%20and%20may%20caused%20failover%20issue)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20constraint%20order%20promote%20virtualip1%20then%20start%20ag_cluster1-master%20%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3E%23%23wrong%20one%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20availability%20group%20ag1%20and%20the%20listener%20in%20Datacenter%20are%20fully%20functional%20right%20now.%20The%20following%20section%20will%20cover%20the%20forwarder.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-1743752942%22%20id%3D%22toc-hId-1743752942%22%3E%3CSTRONG%3ESection%202%3ACreate%20Availability%20group%20AG2%20in%20datacenter2%3C%2FSTRONG%3E%3C%2FH4%3E%0A%3CP%3E1.%20Updates%20%2Fetc%2Fhosts%20on%20all%20the%20nodes%20Datacenter2%2C%20add%20IP%20%2C%20replica%20name%20and%20AG%20Listener%20name.%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20screenshot.%20All%20servers%20in%20the%20datacenter2%20should%20have%20same%20setting.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157230iA324BA3D2DAF1ECE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_14.png%22%20title%3D%22clipboard_image_14.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E2.%20Run%20following%20commands%20on%20all%20servers%20in%20datacenter2%20to%20enable%20Alwayson%20Feature%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20%2Fopt%2Fmssql%2Fbin%2Fmssql-conf%20set%20hadr.hadrenabled%201%0A%24%20sudo%20systemctl%20restart%20mssql-server%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157231i615BBB9C34BA1608%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_15.png%22%20title%3D%22clipboard_image_15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E3.%20Run%20following%20T-SQL%20to%20Enable%20Always%20On%20Health%20check%20event%20session%20on%20all%20the%20servers%20in%20datacenter2%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20EVENT%20SESSION%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EAlwaysOn_health%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EON%20SERVER%20WITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3ESTARTUP_STATE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EON%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E4.%20Remember%20the%20certificate%20backup%20files%20created%20in%20step4%20in%20section%201%3F%20Copy%20them%20to%20all%20the%20servers%20in%20datacenter2(cen4%2Ccen5%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20cd%20%2Fvar%2Fopt%2Fmssql%2Fdata%0A%24%20scp%20dbm_certificate.*%20%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3Eroot%40**%3CNODEIPADDRESS%3E**%3C%2FNODEIPADDRESS%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3A%2Fvar%2Fopt%2Fmssql%2Fdata%2F%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157233i3232379EE6600CA4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_16.png%22%20title%3D%22clipboard_image_16.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E5.%20Give%20permission%20to%20the%20mssql%20user%20to%20access%20the%20certificate%20files%20in%20all%20servers%20in%20datacenter2(cen4%2Ccen5%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20cd%20%2Fvar%2Fopt%2Fmssql%2Fdata%0A%24%20chown%20mssql%3Amssql%20dbm_certificate.*%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157232i38604DCF96F0549A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_17.png%22%20title%3D%22clipboard_image_17.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E6.%20Create%20the%20certificate%20in%20all%20the%20servers%20in%20datacenter2%2C%20by%20restoring%20the%20certificate%20backup(cen4%2Ccen5%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EUSE%20master%0AGO%0ACREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'**%3CMASTER_KEY_PASSWORD%3E**'%3C%2FMASTER_KEY_PASSWORD%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%0ACREATE%20CERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFROM%20FILE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdbm_certificate.cer'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20PRIVATE%20KEY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFILE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'%2Fvar%2Fopt%2Fmssql%2Fdata%2Fdbm_certificate.pvk'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDECRYPTION%20BY%20PASSWORD%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'**%3CPRIVATE_KEY_PASSWORD%3E**'%0A%20%20%20%20%20%20%20%20%3C%2FPRIVATE_KEY_PASSWORD%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E7.%20Create%20endpoint%20on%20all%20servers%20in%20datacenter2(cen4%2Ccen5%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20ENDPOINT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BHadr_endpoint%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAS%20TCP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ELISTENER_PORT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E5022%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFOR%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20lime%3B%22%3EDATABASE_MIRRORING%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EROLE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITNESS%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTHENTICATION%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECERTIFICATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3Edbm_certificate%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENCRYPTION%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EREQUIRED%20ALGORITHM%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3EAES%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%0AALTER%20ENDPOINT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5BHadr_endpoint%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESTATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESTARTED%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E8.%20Create%20AG%20with%202%20synchronous%20replicas%20in%20datacenter2.%20Run%20following%20T-SQL%20query%20on%20primary%20replica%20(cen4%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag2%5D%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDB_FAILOVER%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EON%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECLUSTER_TYPE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFOR%20REPLICA%20ON%20%0A%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'cen4'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENDPOINT_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'TCP%3A%2F%2Fcen4%3A5022'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESYNCHRONOUS_COMMIT%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'cen5'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EENDPOINT_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3EN'TCP%3A%2F%2Fcen5%3A5022'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESYNCHRONOUS_COMMIT%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%0A%0AALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag2%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGRANT%20CREATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EANY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATABASE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E9.%20Join%20the%20AG%20group%2C%20run%20the%20following%20queries%20on%20%3CSTRONG%3Eall%20the%20secondary%20servers%20in%20datacenter2%3C%2FSTRONG%3E(%20cen5%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20AVAILABILITY%0AGROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag2%5D%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EJOIN%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECLUSTER_TYPE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EEXTERNAL%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%0AALTER%20AVAILABILITY%20GROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag2%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGRANT%20CREATE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EANY%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDATABASE%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3B%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EGO%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EThe%20Availability%20group%20has%20been%20created.%20I'll%20create%20the%20resources%20in%20Linux%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E10.%20Install%20SQL%20Server%20resource%20agent%20for%20SQL%20Server.%20Run%20the%20following%20commands%20on%20all%20servers%20in%20datacenter2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20yum%20install%20mssql-server-ha%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E11.%20Install%20pacemaker.%20Run%20the%20following%20commands%20on%20all%20servers%20in%20datacenter2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20yum%20install%20pacemaker%20pcs%20fence-agents-all%20resource-agents%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E12.%20Set%20the%20password%20for%20the%20default%20user%20that%20is%20created%20when%20installing%20Pacemaker%20and%20Corosync%20packages.%20Run%20following%20command%20on%20all%20servers%20in%20datacenter2(This%20command%20asks%20for%20password.%20The%20password%20should%20be%20same%20for%20the%20servers%20in%20same%20datacenter)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20passwd%20hacluster%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157234iD4F4CF277E86AF8C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_18.png%22%20title%3D%22clipboard_image_18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E13.%20To%20allow%20nodes%20to%20rejoin%20the%20cluster%20after%20the%20reboot%2C%20enable%20and%20start%20pcsd%20service%20and%20Pacemaker.%20Run%20the%20following%20commands%20on%20all%20servers%20in%20datacenter2(cen4%2Ccen5).%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20systemctl%20enable%20pcsd%0A%24%20sudo%20systemctl%20start%20pcsd%0A%24%20sudo%20systemctl%20enable%20pacemaker%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157235i3A6BFB6A3BA05ED7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_19.png%22%20title%3D%22clipboard_image_19.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E14.%20Create%20cluster%2C%20run%20it%20on%20one%20of%20the%20nodes(just%20on%20one%20node).%20%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20font-family%3A%20'SegoeUI'%2C'Lato'%2C'Helvetica%20Neue'%2CHelvetica%2CArial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EThe%20'%3C%2FSPAN%3E%3CFONT%20size%3D%222%22%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2013.33px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3EYourStrongPassword%3C%2FSTRONG%3E%3C%2FFONT%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20font-family%3A%20'SegoeUI'%2C'Lato'%2C'Helvetica%20Neue'%2CHelvetica%2CArial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E'%20highlighted%20should%20be%20replaced%20with%20the%20password%20specified%20in%20step%2012.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20cluster%20auth%20cen4%20cen5%20-u%20hacluster%20-p%20%3CSTRONG%3EYourStrongPassword%3C%2FSTRONG%3E%3CBR%20%2F%3E%24%20sudo%20pcs%20cluster%20setup%20--name%20cluster2%20cen4%20cen5%0A%24%20sudo%20pcs%20cluster%20start%20--all%0A%24%20sudo%20pcs%20cluster%20enable%20--all%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157236iD912BDAFC337D715%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_20.png%22%20title%3D%22clipboard_image_20.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E15.%20Because%20the%20node%20level%20fencing%20configuration%20depends%20heavily%20on%20your%20environment%2C%20disable%20it%20for%20this%20tutorial%20(%3CSTRONG%3Eit%20can%20be%20configured%20later.%20In%20my%20test%2C%20if%20this%20value%20is%20not%20set%2C%20you%20can't%20failover%2C%20and%20IP%20resource%20may%20fail).%20The%20following%20script%20disables%20node%20level%20fencing%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20property%20set%20stonith-enabled%3Dfalse%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CEM%3EFrom%20%26lt%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fsql-server-linux-availability-group-cluster-rhel%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fsql-server-linux-availability-group-cluster-rhel%3Fview%3Dsql-server-2017%3C%2FA%3E%26gt%3B%20%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E16.%20Create%20availability%20group%20resource%20at%20cluster%20level%2C%20on%20one%20of%20nodes%20in%20DC2(only%20one%20time).%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20resource%20create%20ag_cluster2%20ocf%3Amssql%3Aag%20ag_name%3Dag2%20meta%20failure-timeout%3D60s%20master%20notify%3Dtrue%3C%2FFONT%3E%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157239i9F9A69C7F5B6DCD9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_21.png%22%20title%3D%22clipboard_image_21.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E17.%20Create%20virtual%20IP%20resource.%20The%20resource%20name%20is%20'virtualip2'.%20Please%20replace%20with%20valid%20value%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20resource%20create%20virtualip2%20ocf%3Aheartbeat%3AIPaddr2%20ip%3D192.168.1.75%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157238i05BB775BA5DB698A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_22.png%22%20title%3D%22clipboard_image_22.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E18.%20Create%20Availability%20group%20listener%20for%20Availability%20group%20ag2.%20Run%20following%20T-SQL%20query%20in%20primary%20replica(cen1%20in%20this%20case).%20The%20ip%20address%20should%20be%20as%20same%20as%20the%20IP%20in%20step%2017.%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20AVAILABILITY%0AGROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bag2%5D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EADD%20LISTENER%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'ag2-listener'%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20IP%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E((%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'192.168.1.75'%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'255.255.255.0'%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E))%0A%20%20%20%20%20%20%20%20%20%20%20%20%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EPORT%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E1433%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E19.%20Add%20colocation%20constraint.%20Please%20note%2C%20without%20the%20constraint%2C%20the%20ip%20resource%20does%20not%20failover%20when%20the%20AG%20resource%20failover.%20Hence%20we%20need%20to%20create%20a%20constraint%20to%20guarantee%20that%20the%20IP%20will%20failover%20when%20cluster%20resource%20failover.%20Run%20following%20command%20on%20any%20servers%20in%20datacenter2(just%20on%20one%20server%2C%20and%20just%20one%20time)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20constraint%20colocation%20add%20virtualip2%20ag_cluster2-master%20INFINITY%20with-rsc-role%3DMaster%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157237iF36C9F31C287A3B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_23.png%22%20title%3D%22clipboard_image_23.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E20.%20Add%20ordering%20constraint.%20Run%20following%20command%20on%20any%20servers%20in%20datacenter2(just%20on%20one%20server%2C%20and%20just%20one%20time)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20constraint%20order%20promote%20ag_cluster2-master%20then%20start%20virtualip2%0A%3C%2FFONT%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157240i82F964CB5EF017C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_24.png%22%20title%3D%22clipboard_image_24.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E21.%20To%20update%20the%20property%20value%20to%202%20minutes%20run.%20Run%20it%20on%20any%20node%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CFONT%20size%3D%222%22%3E%24%20sudo%20pcs%20property%20set%20cluster-recheck-interval%3D2min%0A%24%20sudo%20pcs%20property%20set%20start-failure-is-fatal%3Dtrue%0A%24%20pcs%20resource%20update%20ag_cluster1%20meta%20failure-timeout%3D60s%3C%2FFONT%3E%20%20%3C%2FPRE%3E%0A%3CH4%20id%3D%22toc-hId--63701521%22%20id%3D%22toc-hId--63701521%22%3E%3CSTRONG%3ESection%203%3A%20Create%20Distributed%20Availability%20group%20%3C%2FSTRONG%3E%3C%2FH4%3E%0A%3CP%3EI%20suggest%20to%20test%20the%20Listener%20connections%20on%20both%20primary%20replica%20and%20forwarder.%20Do%20not%20proceed%20until%20the%20connections%20are%20good%20on%20both%20sides.%20If%20you%20proceed%20when%20the%20connection%20fails%2C%20the%20following%20steps%20will%20be%20good%2C%20but%20the%20DAG%20will%20be%20%3CSTRONG%3E'Not%20Healthy'%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.Create%20distributed%20availability%20group%20in%20datacenter1.%20Run%20following%20query%20in%20primary%20replica%20in%20datacenter1(%20cen1%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ECREATE%20AVAILABILITY%0AGROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdistributedag%5D%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EDISTRIBUTED%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY%0AGROUP%20ON%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'ag1'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ELISTENER_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'tcp%3A%2F%2Fag1-listener%3A5022'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EASYNCHRONOUS_COMMIT%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EMANUAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'ag2'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ELISTENER_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'tcp%3A%2F%2Fag2-listener%3A5022'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EASYNCHRONOUS_COMMIT%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EMANUAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E2.%20Join%20the%20distributed%20availability%20group%20.%20Run%20following%20query%20in%20the%20forwarder%20server(cen4%20in%20this%20case)%3C%2FP%3E%0A%3CPRE%20class%3D%22code%22%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EALTER%20AVAILABILITY%0AGROUP%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20black%3B%22%3E%5Bdistributedag%5D%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3EJOIN%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY%0AGROUP%20ON%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'ag1'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ELISTENER_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'tcp%3A%2F%2Fag1-listener%3A5022'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EASYNCHRONOUS_COMMIT%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EMANUAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%0A%20%20%20%20%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'ag2'%0A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EWITH%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E(%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ELISTENER_URL%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20red%3B%22%3E'tcp%3A%2F%2Fag2-listener%3A5022'%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAVAILABILITY_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EASYNCHRONOUS_COMMIT%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EFAILOVER_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EMANUAL%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3ESEEDING_MODE%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20blue%3B%22%3EAUTOMATIC%0A%20%20%20%20%20%20%20%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20gray%3B%22%3E)%3B%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EThe%20databases%20in%20AG%20group%20in%20datacenter1%20will%20be%20propagated%20to%20servers%20in%20datacenter2%20by%20auto-seeding%20after%20step%202%20is%20complete.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20483px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F161297iD5FA5486D20572F7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22dagview.png%22%20title%3D%22dagview.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1003471%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158790i324FBA8B07D221C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20title%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Microsoft

This article describes how to create a SQL Server Distributed Availability Group (AG) for high availability on Linux(Redhat/Centos).

Here is the topology of the DAG environment

Data Center 1

Data Center 2

  • Cen1:192.168.1.61 (Primary replica)
  • Cen2:192.168.1.62
  • Cen3:192.168.1.63
  • Availability group name:AG1
  • ag1-listener:192.168.1.65
  • SQL Server: 2017 CU17
  • Cen4:192.168.1.71 (Forwarder)
  • Cen5:192.168.1.72
  • Availability group name:AG2
  • ag2-listener:192.168.1.75
  • SQL Server: 2017 CU17

Please make use following ports are opened on all the servers in both datacenters.

TCP:5022,2224,3123,21064

UDP:5405

 

For RHEL, you need to have the 'high availability subscription' enabled. Please refer https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql... for detail.

 

Section 1:Create Availability group AG1 in datacenter1

1. Updates /etc/hosts on all the nodes Datacenter1, add IP , replica name and AG Listener name.

Here is a screenshot. All servers in the datacenter1 should have same setting.(Please note, the IP resources of Listeners will be created in later steps, it's ok to add at the very beginning)

clipboard_image_0.png

2. Run following bash commands on all servers in datacenter1 to enable Alwayson Feature

$ sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
$ sudo systemctl restart mssql-server    

clipboard_image_1.png

3. Run following T-SQL to Enable Always On Health check event session on all the servers in datacenter1

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);

4. Create a certificate, run the query on primary replica in datacenter1(cen1 in this case)

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
go
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
go
BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);

5. Copy the certificate to rest of the servers in datacenter1(cen2,cen3 in this case)

$ cd /var/opt/mssql/data
$ scp dbm_certificate.* root@**<nodeName>**:/var/opt/mssql/data/    
clipboard_image_2.png

6. Give permission to the mssql user to access the certificate files in rest of the servers(cen2,cen3 in this case)

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*    

7. Create the certificate on rest of the servers by restoring the certificate backup(cen2,cen3 in this case)

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
go
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
go
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);

8. Create endpoint on all servers in datacenter1. The port 5022 is used.

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
    ROLE = WITNESS,
    AUTHENTICATION = CERTIFICATE dbm_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

9. Create AG with 3 synchronous replicas in datacenter1. Run following query on primary replica (cen1 in this case)

CREATE AVAILABILITY GROUP [ag1]
    WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
    FOR REPLICA ON
    N'cen1'
        WITH (
        ENDPOINT_URL = N'tcp://cen1:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC) ,
    N'cen2'
        WITH (
        ENDPOINT_URL = N'tcp://cen2:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC),
    N'cen3'
        WITH (
        ENDPOINT_URL = N'tcp://cen3:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = EXTERNAL,
        SEEDING_MODE = AUTOMATIC)
GO

ALTER AVAILABILITY GROUP
[ag1] GRANT CREATE ANY DATABASE;--grant create any database permission

Please make sure the port 5022 is in the firewall exception list, else you may run into following error messages

2019-09-11 16:10:18.560        spid40s        Error: 8471, Severity: 16, State: 2.

2019-09-11 16:10:18.560        spid40s        An SNI call failed during a Service Broker/Database Mirroring transport operation. SNI error '10022(An invalid argument was supplied.)'.

2019-09-11 16:10:18.570        spid40s        Error: 9642, Severity: 16, State: 1.

2019-09-11 16:10:18.570        spid40s        An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8471, State: 2. (Near endpoint role: Initiator, far endpoint address: '1')

10. You will see below message in errorlog file of primary replica. The timeout happens because the secondary replicas have not joined the AG group

2019-09-05 20:00:44.530 spid62       The state of the local availability replica in availability group 'ag1' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'. The state changed because the local availability replica is joining the availability group. For more information, see the SQL

2019-09-05 20:00:44.570 spid62       Always On: The local replica of availability group 'ag1' is preparing to transition to the primary role. This is an informational message only. No user action is required.

2019-09-05 20:00:44.610 spid62       The state of the local availability replica in availability group 'ag1' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'. The state changed because the availability group is coming online. For more information, see the SQL Server error log or clu

2019-09-05 20:00:44.620 Server       The state of the local availability replica in availability group 'ag1' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'. The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WS

2019-09-05 20:00:54.630 spid39s     A connection timeout has occurred while attempting to establish a connection to availability replica 'cen2' with id [6C1CD374-CB97-477A-B8DB-589D94A0FCB6]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is no

2019-09-05 20:00:54.650 spid40s     A connection timeout has occurred while attempting to establish a connection to availability replica 'cen3' with id [D8E1CE2A-10CF-409B-98B1-9D3B35FEE91A]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is no

11. Join the AG group, run the following T-SQL queries on all the secondary servers in datacenter1(cen2,cen3 in this case)

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

clipboard_image_3.png

12. You will see below message in errorlog file of primary replica. Connection established with two secondary replicas.

2019-09-05 20:01:23.820 spid11s     A connection for availability group 'ag1' from availability replica 'cen1' with id [03D58956-AAC2-4A1D-975E-3F3B788752A2] to 'cen2' with id [6C1CD374-CB97-477A-B8DB-589D94A0FCB6] has been successfully established. This is an informational message only. N

2019-09-05 20:02:04.950 spid31s     A connection for availability group 'ag1' from availability replica 'cen1' with id [03D58956-AAC2-4A1D-975E-3F3B788752A2] to 'cen3' with id [D8E1CE2A-10CF-409B-98B1-9D3B35FEE91A] has been successfully established. This is an informational message only. N

13. Create database and add to AG group on primary replica.(cen1 in this case).

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1] TO DISK = N'/var/opt/mssql/data/db1.bak';
BACKUP log [db1] TO DISK = N'/var/opt/mssql/data/db1.trn';
GO
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

The database db1 will created in the rest of the servers by auto-seeding.

clipboard_image_4.png

14. Create SQL login pacemaker on all servers in datacenter1

CREATE LOGIN [pacemakerLogin] with PASSWORD= N'ComplexP@$$w0rd!'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]
GO

The Availability group has been created. Unlike SQL Server in Windows, SQL Server Linux does not create any resource in Linux, you need to create these resource manually. Step 15~29 will cover the detail:

15. Install SQL Server resource agent for SQL Server. Run the following bash command on all servers in datacenter1.

$ sudo yum install mssql-server-ha

16. Install pacemaker.Run the following bash command on all servers in datacenter1.

$ sudo yum install pacemaker pcs fence-agents-all resource-agents

17. Set the password for the default user that is created when installing Pacemaker and Corosync packages. Run following command on all servers in datacenter1(This command asks for password. The password should be same for the servers in same datacenter)

$ sudo passwd hacluster

clipboard_image_5.png

18. To allow nodes to rejoin the cluster after the reboot, enable and start pcsd service and Pacemaker. Run the following commands on all servers in datacenter1.

$ sudo systemctl enable pcsd
$ sudo systemctl start pcsd
$ sudo systemctl enable pacemaker

19. Create a cluster in datacenter1. Run following commands on any one of the nodes in datacenter1(just on one server, and just one time). The 'YourStrongPassword' highlighted should be replaced with the password specified in step 17.

$ pcs cluster auth cen1 cen2 cen3 -u hacluster -p YourStrongPassword
$ sudo pcs cluster setup --name cluster1 cen1 cen2 cen3 $ sudo pcs cluster start --all $ sudo pcs cluster enable --all

clipboard_image_7.png

20. Check the cluster status

$ pcs cluster status

 

clipboard_image_8.png

21. Because the node level fencing configuration depends heavily on your environment, disable it for this tutorial (it can be configured later. In my test, if this value is not set to false, you can't failover, and IP resource may fail). The following script disables node level fencing(run it on any nodes in datacenter1)

$ sudo pcs property set stonith-enabled=false
22. On all SQL Server Linux servers in datacenter1 , save the credentials for the SQL Server login.(Use the same user name and password created in step14)

$ echo 'pacemakerLogin' >> ~/pacemaker-passwd 
$ echo 'ComplexP@$$w0rd!' >> ~/pacemaker-passwd 
$ sudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/passwd 
$ sudo chown root:root /var/opt/mssql/secrets/passwd 
$ sudo chmod 400 /var/opt/mssql/secrets/passwd # Only readable by root

 

23. Create availability group resource at cluster level in datacenter1, run following command on any one of the nodes in datacenter1(just on one node, and just one time)

Here are the resource and constraint before creating the resource(no resource/constraint)

clipboard_image_9.png

Here is the command to create cluster resource.

$ sudo pcs resource create ag_cluster1 ocf:mssql:ag ag_name=ag1 meta failure-timeout=60s master notify=true

-------------note begin------------------

It creates a resource called ag_cluster1-master(it add the '-master' keyword automatically to the name)

And bind the resource to availability group 'ag1'

And it set the current primary replica as Master, the rest of nodes are Slaves(it check the status of AG group and make the decision

Please note, it does not return any error if you put an incorrect Availability group name (in this case, the ag1 is used, make sure you put correct name)

Internally, pacemaker issues T-SQL queries to SQL Server to check if the Ag exists

-----------note end------------------

clipboard_image_10.png

Please note. Pacemaker starts to connect the SQL Server instance using the login 'pacemakerLogin' created in step 14. If the step 14 or step 22 is skipped, the AG resource in both SQL and Cluster will fail.

24. Create virtual IP resource. The resource name is 'virtualip1', and IP Address is 192.168.1.65 in this demonstration. This is the IP Address for the AG Listener.

$ sudo pcs resource create virtualip1 ocf:heartbeat:IPaddr2 ip=192.168.1.65

The resource is will start automatically after it' created

clipboard_image_11.png

25. Create Availability group listener for Availability group ag1. Run following T-SQL query in primary replica(cen1 in this case). The ip address should be as same as the IP in step 24.

ALTER AVAILABILITY GROUP [ag1] 
ADD LISTENER 'ag1-listener' (WITH IP(('192.168.1.65','255.255.255.0')),PORT = 1433);

a)It's fine if you run step 25 before the step 24, however, you can't use the IP Address to connect SQL Server until the resource in Pacemaker is created.

b)There is a bug in DMV sys.availability_group_listener_ip_addresses showing the state is offline

 

26. Add colocation constraint. Please note, without the constraint, the ip resource does not failover when the AG resource failover. Hence we need to create a constraint to guarantee that the IP will failover when cluster resource failover. Run following command on any servers in datacenter1(just on one server, and just one time)

$ sudo pcs constraint colocation add virtualip1 ag_cluster1-master INFINITY with-rsc-role=Master

clipboard_image_12.png

27. Add ordering constraint

$ sudo pcs constraint order promote ag_cluster1-master then start virtualip1

clipboard_image_13.png

28. To update the property value to 2 minutes run. Run it on any node

$ sudo pcs property set cluster-recheck-interval=2min
$ sudo pcs property set start-failure-is-fatal=true
$ pcs resource update ag_cluster1 meta failure-timeout=60s  

29. -----note-- The order of 'ordering constraint' matters-----------

I have one customer who had a wrong order constraint. In his case, he had following constraint that promoted virtualip first, then start node. This will make the resource fail(may happen, and may caused failover issue)

$ sudo pcs constraint order promote virtualip1 then start ag_cluster1-master ##wrong one

 

The availability group ag1 and the listener in Datacenter are fully functional right now. The following section will cover the forwarder.

 

Section 2:Create Availability group AG2 in datacenter2

1. Updates /etc/hosts on all the nodes Datacenter2, add IP , replica name and AG Listener name.

Here is an screenshot. All servers in the datacenter2 should have same setting.

clipboard_image_14.png

2. Run following commands on all servers in datacenter2 to enable Alwayson Feature

 

$ sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
$ sudo systemctl restart mssql-server

clipboard_image_15.png

3. Run following T-SQL to Enable Always On Health check event session on all the servers in datacenter2

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);

4. Remember the certificate backup files created in step4 in section 1? Copy them to all the servers in datacenter2(cen4,cen5 in this case)

$ cd /var/opt/mssql/data
$ scp dbm_certificate.* root@**<nodeIPAddress>**:/var/opt/mssql/data/

clipboard_image_16.png

5. Give permission to the mssql user to access the certificate files in all servers in datacenter2(cen4,cen5 in this case)

$ cd /var/opt/mssql/data
$ chown mssql:mssql dbm_certificate.*

clipboard_image_17.png

6. Create the certificate in all the servers in datacenter2, by restoring the certificate backup(cen4,cen5 in this case)

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
GO
CREATE CERTIFICATE dbm_certificate
FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk'
        ,DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
        );

7. Create endpoint on all servers in datacenter2(cen4,cen5 in this case)

CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING(ROLE = WITNESS, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES);
GO
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GO

8. Create AG with 2 synchronous replicas in datacenter2. Run following T-SQL query on primary replica (cen4 in this case)

CREATE AVAILABILITY GROUP [ag2]
WITH (
        DB_FAILOVER = ON
        ,CLUSTER_TYPE = EXTERNAL
        )
FOR REPLICA ON 
     N'cen4'
WITH (
        ENDPOINT_URL = N'TCP://cen4:5022'
        ,FAILOVER_MODE = EXTERNAL
        ,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
        ,SEEDING_MODE = AUTOMATIC
        )
    ,N'cen5'
WITH (
        ENDPOINT_URL = N'TCP://cen5:5022'
        ,FAILOVER_MODE = EXTERNAL
        ,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
        ,SEEDING_MODE = AUTOMATIC
        );
GO

ALTER AVAILABILITY GROUP [ag2] GRANT CREATE ANY DATABASE;

9. Join the AG group, run the following queries on all the secondary servers in datacenter2( cen5 in this case)

ALTER AVAILABILITY
GROUP [ag2]
JOIN WITH (CLUSTER_TYPE = EXTERNAL);
GO
ALTER AVAILABILITY GROUP [ag2] GRANT CREATE ANY DATABASE;
GO

The Availability group has been created. I'll create the resources in Linux

 

10. Install SQL Server resource agent for SQL Server. Run the following commands on all servers in datacenter2.

 

$ sudo yum install mssql-server-ha

11. Install pacemaker. Run the following commands on all servers in datacenter2.

 

$ sudo yum install pacemaker pcs fence-agents-all resource-agents

12. Set the password for the default user that is created when installing Pacemaker and Corosync packages. Run following command on all servers in datacenter2(This command asks for password. The password should be same for the servers in same datacenter)

$ sudo passwd hacluster

clipboard_image_18.png

13. To allow nodes to rejoin the cluster after the reboot, enable and start pcsd service and Pacemaker. Run the following commands on all servers in datacenter2(cen4,cen5).

$ sudo systemctl enable pcsd
$ sudo systemctl start pcsd
$ sudo systemctl enable pacemaker

clipboard_image_19.png

14. Create cluster, run it on one of the nodes(just on one node). The 'YourStrongPassword' highlighted should be replaced with the password specified in step 12.

$ sudo pcs cluster auth cen4 cen5 -u hacluster -p YourStrongPassword
$ sudo pcs cluster setup --name cluster2 cen4 cen5 $ sudo pcs cluster start --all $ sudo pcs cluster enable --all

clipboard_image_20.png

15. Because the node level fencing configuration depends heavily on your environment, disable it for this tutorial (it can be configured later. In my test, if this value is not set, you can't failover, and IP resource may fail). The following script disables node level fencing:

$ sudo pcs property set stonith-enabled=false

From <https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel?view=sql...>

16. Create availability group resource at cluster level, on one of nodes in DC2(only one time).

$ sudo pcs resource create ag_cluster2 ocf:mssql:ag ag_name=ag2 meta failure-timeout=60s master notify=true

clipboard_image_21.png

17. Create virtual IP resource. The resource name is 'virtualip2'. Please replace with valid value

$ sudo pcs resource create virtualip2 ocf:heartbeat:IPaddr2 ip=192.168.1.75

clipboard_image_22.png

18. Create Availability group listener for Availability group ag2. Run following T-SQL query in primary replica(cen1 in this case). The ip address should be as same as the IP in step 17.

ALTER AVAILABILITY
GROUP [ag2] ADD LISTENER 'ag2-listener' (
        WITH IP(('192.168.1.75'    ,'255.255.255.0'))
            ,PORT = 1433
        );

19. Add colocation constraint. Please note, without the constraint, the ip resource does not failover when the AG resource failover. Hence we need to create a constraint to guarantee that the IP will failover when cluster resource failover. Run following command on any servers in datacenter2(just on one server, and just one time)

$ sudo pcs constraint colocation add virtualip2 ag_cluster2-master INFINITY with-rsc-role=Master

clipboard_image_23.png

20. Add ordering constraint. Run following command on any servers in datacenter2(just on one server, and just one time)

$ sudo pcs constraint order promote ag_cluster2-master then start virtualip2

clipboard_image_24.png

21. To update the property value to 2 minutes run. Run it on any node

$ sudo pcs property set cluster-recheck-interval=2min
$ sudo pcs property set start-failure-is-fatal=true
$ pcs resource update ag_cluster1 meta failure-timeout=60s  

Section 3: Create Distributed Availability group

I suggest to test the Listener connections on both primary replica and forwarder. Do not proceed until the connections are good on both sides. If you proceed when the connection fails, the following steps will be good, but the DAG will be 'Not Healthy'

 

1.Create distributed availability group in datacenter1. Run following query in primary replica in datacenter1( cen1 in this case)

CREATE AVAILABILITY
GROUP [distributedag]
WITH (DISTRIBUTED) AVAILABILITY
GROUP ON 'ag1'
WITH (
        LISTENER_URL = 'tcp://ag1-listener:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        )
    ,'ag2'
WITH (
        LISTENER_URL = 'tcp://ag2-listener:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        );

2. Join the distributed availability group . Run following query in the forwarder server(cen4 in this case)

ALTER AVAILABILITY
GROUP [distributedag]
JOIN AVAILABILITY
GROUP ON 'ag1'
WITH (
        LISTENER_URL = 'tcp://ag1-listener:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        )
    ,'ag2'
WITH (
        LISTENER_URL = 'tcp://ag2-listener:5022'
        ,AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
        ,FAILOVER_MODE = MANUAL
        ,SEEDING_MODE = AUTOMATIC
        );

The databases in AG group in datacenter1 will be propagated to servers in datacenter2 by auto-seeding after step 2 is complete.

dagview.png