部署SQL Server 2016基于工作组的AlwaysOn AG

部署说明：

以下部署基于工作组环境、不用证书、启动账号为Users组成员、各节点位于同一网段。

前期环境准备参考之前的文章：

部署Windows Server 2016基于工作组的集群

安装和配置SQL Server 2016 With SP1

Part1:创建测试数据库

  CREATE  DATABASE  Chapter5App1Customers ; 
 GO 
 ALTER  DATABASE  Chapter5App1Customers  SET  RECOVERY  FULL  ; 
 GO 
 USE Chapter5App1Customers 
 GO 
 CREATE  TABLE  App1Customers 
 ( 
 ID  INT  PRIMARY  KEY  IDENTITY, 
 FirstName NVARCHAR(30), 
 LastName NVARCHAR(30), 
 CreditCardNumber VARBINARY(8000) 
 ) ; 
 GO 
 --Populate the table 
 DECLARE  @Numbers  TABLE 
 ( 
 Number  INT 
 ) 
 ; WITH  CTE(Number) 
 AS 
 ( 
 SELECT  1 Number 
 UNION  ALL 
 SELECT  Number + 1 
 FROM  CTE 
 WHERE  Number < 100 
 ) 
 INSERT  INTO  @Numbers 
 SELECT  Number  FROM  CTE 
 DECLARE  @Names  TABLE 
 ( 
 FirstName  VARCHAR (30), 
 LastName  VARCHAR (30) 
 ) ; 
 INSERT  INTO  @Names 
 VALUES ( 'Peter' ,  'Carter' ), 
 ( 'Michael' ,  'Smith' ), 
 ( 'Danielle' ,  'Mead' ), 
 ( 'Reuben' ,  'Roberts' ), 
 ( 'Iris' ,  'Jones' ), 
 ( 'Sylvia' ,  'Davies' ), 
 ( 'Finola' ,  'Wright' ), 
 ( 'Edward' ,  'James' ), 
 ( 'Marie' ,  'Andrews' ), 
 ( 'Jennifer' ,  'Abraham' ), 
 ( 'Margaret' ,  'Jones' ) 
 INSERT  INTO  App1Customers(Firstname, LastName, CreditCardNumber) 
 SELECT  FirstName, LastName, CreditCardNumber  FROM 
 ( SELECT 
 ( SELECT  TOP  1 FirstName  FROM  @Names  ORDER  BY  NEWID()) FirstName 
 ,( SELECT  TOP  1 LastName  FROM  @Names  ORDER  BY  NEWID()) LastName 
 ,( SELECT  CONVERT (VARBINARY(8000) 
 ,( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()) +  '-'  + 
 ( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()) +  '-'  + 
 ( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()) +  '-'  + 
 ( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()))) CreditCardNumber 
 FROM  @Numbers a 
 CROSS  JOIN  @Numbers b 
 CROSS  JOIN  @Numbers c 
 ) d ; 
 CREATE  DATABASE  Chapter5App1Sales ; 
 GO 
 ALTER  DATABASE  Chapter5App1Sales  SET  RECOVERY  FULL  ; 
 GO 
 USE Chapter5App1Sales 
 GO 
 CREATE  TABLE  dbo.Orders( 
 OrderNumber  int  NOT  NULL  IDENTITY(1,1)  PRIMARY  KEY  CLUSTERED, 
 OrderDate  date  NOT  NULL , 
 CustomerID  int  NOT  NULL , 
 ProductID  int  NOT  NULL , 
 Quantity  int  NOT  NULL , 
 NetAmount money  NOT  NULL , 
 TaxAmount money  NOT  NULL , 
 InvoiceAddressID  int  NOT  NULL , 
 DeliveryAddressID  int  NOT  NULL , 
 DeliveryDate  date  NULL , 
 ) ; 
 DECLARE  @Numbers  TABLE 
 ( 
 Number  INT 
 ) 
 ; WITH  CTE(Number) 
 AS 
 ( 
 SELECT  1 Number 
 UNION  ALL 
 SELECT  Number + 1 
 FROM  CTE 
 WHERE  Number < 100 
 ) 
 INSERT  INTO  @Numbers 
 SELECT  Number  FROM  CTE 
 --Populate ExistingOrders with data 
 INSERT  INTO  Orders 
 SELECT 
 ( SELECT  CAST (DATEADD(dd,( SELECT  TOP  1 Number 
 FROM  @Numbers 
 ORDER  BY  NEWID()),getdate()) as  DATE )), 
 ( SELECT  TOP  1 Number -10  FROM  @Numbers  ORDER  BY  NEWID()), 
 ( SELECT  TOP  1 Number  FROM  @Numbers  ORDER  BY  NEWID()), 
 ( SELECT  TOP  1 Number  FROM  @Numbers  ORDER  BY  NEWID()), 
 500, 
 100, 
 ( SELECT  TOP  1 Number  FROM  @Numbers  ORDER  BY  NEWID()), 
 ( SELECT  TOP  1 Number  FROM  @Numbers  ORDER  BY  NEWID()), 
 ( SELECT  CAST (DATEADD(dd,( SELECT  TOP  1 Number - 10 
 FROM  @Numbers 
 ORDER  BY  NEWID()),getdate())  as  DATE )) 
 FROM  @Numbers a 
 CROSS  JOIN  @Numbers b 
 CROSS  JOIN  @Numbers c ; 
 CREATE  DATABASE  Chapter5App2Customers ; 
 GO 
 ALTER  DATABASE  Chapter5App2Customers  SET  RECOVERY  FULL  ; 
 GO 
 USE Chapter5App2Customers 
 GO 
 CREATE  TABLE  App2Customers 
 ( 
 ID  INT  PRIMARY  KEY  IDENTITY, 
 FirstName NVARCHAR(30), 
 LastName NVARCHAR(30), 
 CreditCardNumber VARBINARY(8000) 
 ) ; 
 GO 
 --Populate the table 
 DECLARE  @Numbers  TABLE 
 ( 
 Number  INT 
 ) ; 
 ; WITH  CTE(Number) 
 AS 
 ( 
 SELECT  1 Number 
 UNION  ALL 
 SELECT  Number + 1 
 FROM  CTE 
 WHERE  Number < 100 
 ) 
 INSERT  INTO  @Numbers 
 SELECT  Number  FROM  CTE ; 
 DECLARE  @Names  TABLE 
 ( 
 FirstName  VARCHAR (30), 
 LastName  VARCHAR (30) 
 ) ; 
 INSERT  INTO  @Names 
 VALUES ( 'Peter' ,  'Carter' ), 
 ( 'Michael' ,  'Smith' ), 
 ( 'Danielle' ,  'Mead' ), 
 ( 'Reuben' ,  'Roberts' ), 
 ( 'Iris' ,  'Jones' ), 
 ( 'Sylvia' ,  'Davies' ), 
 ( 'Finola' ,  'Wright' ), 
 ( 'Edward' ,  'James' ), 
 ( 'Marie' ,  'Andrews' ), 
 ( 'Jennifer' ,  'Abraham' ), 
 ( 'Margaret' ,  'Jones' ) 
 INSERT  INTO  App2Customers(Firstname, LastName, CreditCardNumber) 
 SELECT  FirstName, LastName, CreditCardNumber  FROM 
 ( SELECT 
 ( SELECT  TOP  1 FirstName  FROM  @Names  ORDER  BY  NEWID()) FirstName 
 ,( SELECT  TOP  1 LastName  FROM  @Names  ORDER  BY  NEWID()) LastName 
 ,( SELECT  CONVERT (VARBINARY(8000) 
 ,( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()) +  '-'  + 
 ( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()) +  '-'  + 
 ( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()) +  '-'  + 
 ( SELECT  TOP  1  CAST (Number * 100  AS  CHAR (4)) 
 FROM  @Numbers 
 WHERE  Number  BETWEEN  10  AND  99  ORDER  BY  NEWID()))) CreditCardNumber 
 FROM  @Numbers a 
 CROSS  JOIN  @Numbers b 
 CROSS  JOIN  @Numbers c 
 ) d ; 
 

Part2:配置SQL Server

clip_image001

Part4:创建AG内数据库的完整备份

  BACKUP  DATABASE  Chapter5App1Customers 
 TO  DISK = N 'C:\Backups\Chapter5App1Customers.bak' 
 WITH  NAME  = N 'Chapter5App1Customers-Full Database Backup'  ; 
 GO 
 BACKUP  DATABASE  Chapter5App1Sales 
 TO  DISK = N 'C:\Backups\Chapter5App1Sales.bak' 
 WITH  NAME  = N 'Chapter5App1Sales-Full Database Backup'  ; 
 GO 
 

Part5:将备份在AG内的其他副本上恢复为NORECOVERY

clip_image002

clip_image003

Part6:将启动账号添加到Logins

  --提前创建端点 
 USE [master] 
 GO 
 CREATE  ENDPOINT [Hadr_endpoint]  
      STATE=STARTED 
      AS  TCP (LISTENER_PORT = 5022, LISTENER_IP =  ALL ) 
      FOR  DATA_MIRRORING (ROLE =  ALL , AUTHENTICATION = WINDOWS NEGOTIATE 
 , ENCRYPTION = REQUIRED ALGORITHM AES) 
 GO 
 DECLARE  @Server sysname, 
 @HostName sysname, 
 @Login sysname, 
 @SQL nvarchar( max ) 
 ; 
 --获取实例名 
 SET  @Server =  CAST (SERVERPROPERTY( 'ServerName' )  AS  sysname); 
 --获取机器名（远程执行时HOST_NAME()函数不代表远程主机） 
 IF CHARINDEX( '\\' ,@Server,1) <> 0 
      SET  @HostName =  SUBSTRING (@Server,1,CHARINDEX( '\\' ,@Server,1)-1); 
 ELSE 
      SET  @HostName = @Server; 
       
 --用到SQL Server Database Engine服务启动账号 
 SET  @Login = @HostName +  '\SQLService' ; 
 IF  NOT  EXISTS( SELECT  name  FROM  sys.syslogins  WHERE  isntuser=1  AND  name  = @Login) 
 BEGIN 
      --SELECT @Login 
      EXEC  sp_grantlogin @Login; 
      --EXEC sp_addsrvrolemember @Login, 'sysadmin'; 
      --端点访问授权 
      SET  @SQL = N 'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO ['  + @Login +  ']' ; 
      --SELECT @SQL 
      EXEC  sp_executesql @SQL; 
 END 
 