Configuring SQL Server with AlwaysOn AGs

I would now like to briefly discuss the steps required to enable AlwaysOn AGs on SQL Server Enterprise machines. You can use these steps to extend the standalone SQL Server installation that we discussed in the main How to do it... section:

  1. Copy the SQL configuration file (step 3 of How to do it...) to the second SQL Server machine (for example, w2k16-sql02) and install a new SQL Server instance using the same steps as we discussed earlier.
  2. On each SQL Server, add the failover clustering feature:
Install-WindowsFeature Failover-Clustering -IncludeManagementTools
  1. Next, you need to create a failover cluster and set its witness settings. In the following example, a shared folder on a file server is used:
    New-Cluster -Name SQLFCI -Node w2k16-sql,w2k16-sql02 -StaticAddress 10.10.0.200 -NoStorage
    Set-ClusterQuorum -NodeAndFileShareMajority\\filesrv\sqlcl
If your SQL Server nodes have unrestricted external access, you can use a new witness type in Windows Server 2016 - cloud witness. For more information, check out this link: https://rlevchenko.com/2017/01/26/whats-new-in-failover-clustering-in-windows-server-2016/.
  1. When the failover cluster comes online, you will be ready to enable the AlwaysOn feature on each SQL Server. Run the following command on a SQL machine (for example, w2k16-sql):
 Enable-SqlAlwaysOn -ServerInstance $env:computername -Force
  1. Repeat the previous step on the second SQL machine.
  2. Open up SQL Server Management Studio, connect to the SQL Server (w2k16-sql, for example), right-click on Databases and select New Database..., define the Database Name (for example, Temp) and ensure that Recovery Model is set to Full, then click OK to create the database.
  3. Expand Databases and highlight the created database (Temp); right-click on it, select Tasks and then Backup... ; in the Backup Database dialog window, set Backup type to Full; and click OK to start the backup job.
  4. To set up AlwaysOn for the database, launch the New Availability Group wizard from the context menu of the AlwaysOn High Availability (HA), specify availability group name (for example, rl-ag01) and select databases as shown in the following screenshot:
  1. Specify instances of SQL Servers to host primary and secondary replicas. Refer to the settings shown in the following screenshot (Automatic Failover and Synchronous Commit are enabled; Readable Secondary is set to Yes):
  1. Now click on the Listener tab to configure the listener for this availability group. A static IP address is preferable. Also ensure that the DNS name and IP address are not in use:
  1. On the Select Data Synchronization page, you need to specify a network share that will be used to store database and log backups. The SQL Server service account must have read and write permissions on this network share (for example, \\filesrv\sqlshare). Then click on Next.
  2. Review the validation results and summary then click on Finish to start the initial replication of the database and the creation of the availability group.
上一章目录下一章