Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 50 of 212

39 C H A P T E R 3 | Higher availability Note You can also use Windows PowerShell to configure a read-only routing list, as described in "Configure Read-Only Routing for an Availability Group (SQL Server)," at In SQL Server 2012 and SQL Server 2014, the read traffic is directed to the first available replica, without consideration for load balancing. An alternative solution requires the use of third-party hardware or software load balancers to route traffic equally across the secondary copies of the databases. In SQL Server 2016, you can now balance loads across replicas by using nested parentheses in the read-only routing list, as shown in Example 3-3. In this example, connection requests first try the load-balanced set containing Server1 and Server2. If neither replica in that set is available, the request continues by sequentially trying other replicas defined in the list, Server3 and Server4 in this example. Only one level of nested parentheses is supported at this time. Example 3-3: Defining a load-balanced replica list READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4') Defining automatic failover targets In SQL Server 2012 and SQL Server 2014, you can define a maximum of two replicas running in an automatic failover set, but now SQL Server 2016 allows for a third replica to support a topology such as is shown in Figure 3-4. In this example, the replicas on Node01, Node02, and Node03 are configured as an automatic failover set. As long as data is synchronized between the primary replica and one of the secondary replicas, failover can take place in an automatic fashion with no data loss. Figure 3-4: Availability Group topology with three automatic failover targets. When configuring availability group failover, you can choose from among the following failover modes: Automatic Failover A failover that occurs automatically on the failure of the primary replica, which is supported only when both the primary replica and at least one secondary replica are configured with AUTOMATIC failover mode and the secondary replica is currently synchronized. Planned Manual Failover (without data loss) A failover that is typically initiated by an administrator for maintenance purposes. This requires synchronous-commit mode, and the databases must currently be synchronized.

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016