SQL Server 2012 AlwaysOn

Introduction

One of the greatest new features introduced in SQL Server 2012 in terms of high availability is the AlwaysOn technology.
This is divided into two types:

1. AlwaysOn Failover Cluster Instance: Protection at single instance level, improvement of the traditional  SQL Server Cluster installation, mainly in the health monitoring  and Multi-Subnet implementation.
2. AlwaysOn Availability Group: Database-level security, evolution of the  earlier SQL Server versions DB mirroring.

Source: Microsoft Learning

First solution, in terms of architecture, just adds elimination of the need to create a VLAN for cluster nodes, while Availability groups are a true novelty, perhaps one of that particular for which it would be worth to migrate to SQL 2012.

AlwaysOn Availability Group


General


This technology takes advantage of the Windows Server Failover Cluster (WSFC) to provide a high availability solution for SQL Server databases. It 'an evolution of DB mirroring technology, introduced with SQL Server 2005 version, but offers these fundamental additional advantages:

1.  Nodes  number is no longer limited to 2
2.  Failover is no longer handled by the client, but it's a server side event done in a transparent way to the client
3. And' possible to perform read-only access to a passive node of the availability group
4. And' possible to perform the backup operations on a passive node of the availability group
   
   

Although this technology is based on the Failover Cluster is not necessary:

1. Have a storage shared among cluster nodes
2. Perform an SQL Server Cluster installation
   

Then each node will have a standalone SQL Server installation with data on local node storage.


Note:

In Windows Server 2012 Failover Cluster feature is available also on the standard version

There is no problem in creating virtualized nodes, if not the usual recommendations for SQL Server running on a virtual machine


Terminology

Source: Microsoft Learning

AlwaysOn Availability Group: a group of servers (cluster) that provides high availability to one or more databases

Primary Replica: SQL Server instance that hosts the read and write Database copy (only one among group)

Secondary Replica: node that hosts a Database copy on which you can enable read-only access

Listener: consists of a DNS name, TCP port, and one or more IP addresses through which clients connect to the availability group


Availability Modes: how data is replicated between the primary and a secondary member, can be:

1. Commit-Synchronous: the primary replica waits for confirmation that the changes have been made on the secondary member before closing the transaction. Reliable but dangerous in terms of  performances.
2. Commit-Asynchronous: the primary member sends changes to the secondary and close the transaction without waiting for any confirmation of the outcome. Not reliable but very performing.

Failover: process through which the role of the Primary Replica is passed to a Secondary Replica, called failover target. There are 3 types of failover:

1. Automatic failover (without data loss) available when both the primary  that the secondary replica are operating in synchronous mode (Commit-Synchronous). In this case the failover mode in the group is set to automatic and the passage of roles takes place without any administrative intervention.
2. Planned manual failover (without data loss): as in the previous case, the two members operate in synchronous mode, but, since the Group was not set to Automatic failover. requires an administrative intervention to send the failover command.
3. Forced manual failover (with possible data loss): the two replica members operate asynchronously and then the administrator can force the failover knowing that any transactions that have not yet been synchronized from the primary member will be lost.

Active Secondary Replica: to improve performance and manage more efficiently the availability group resources you can route  read-only and backup requests to a secondary replica; secondary replica(s) acting in this way is/are called 'active'




Installation

Note: the objective of this article is centered on the Availability Group and not on the Failover Cluster configuration, so I will not go into detail on this part.


1) Install the Failover Cluster feature on each Server

2) Create a Failover Cluster that includes all servers

in our example we have created a cluster called  MIA-CLUSTER including the servers:


MIA-CLUST1

MIA-CLUST2

MIA-CLUST3


Note that there is no shared storage in the cluster.

3) Install a Standalone instance of SQL Server 2012 on each server

Note:


Use a domain account for the SQL Service

You can install only the Database Engine feature, optionally also management tools


4) Trough the SQL Server Configuration Manager enabled AlwaysOn Availability Group on each server

Note: Restart the SQL Server service


5)  Create a network share for the backup files used to synchronize the replicas

6) Create Database(s) we want to protect on the Server that will become your Primary Replica

In our case the Server that will act as Primary Replica is MIA-CLUST1 and we'll put in High Availability the  Sales database


Check that the Database Recovery Model is Full

7) Now you can create your AlwaysOn Avalaibilty Group

 Launch the Wizard on the Primary Server

  

Specify your AG name (in our case MIA-SQL-AG)

 We select the Sales database

Add the Secondary Replicas: Add Replica ...

Configure the Listener

We set the synchronization folder

 Then let's go Next

 Control the outcome

Note: the warning I received about the WSFC is not significant



Final Results:


The Sales database is replicated on all three Servers

 

 In the cluster was created a service whose onwer is our Primary Replica

Configuration

Now let's do a configuration example.


We enter our Group Availability properties

We set MIA-CLUST2 in synchronous mode and we enable automatic failover
Let MIA-CLUST3 in asynchronous mode, and then the failover can only be manual
We make both Secondary Replicas readable 

Let's analyze deeper the available options.

Remember that each server can potentially act in two modes: Primary and Secondary, here we have options available for each server based on the role it plays (state is intended to be dynamic):


Connections in primary role: when acting as a primary replica server can accept all or only write connections

Readable Secondary: when acting as a secondary a server can: don't accept connections (passive), accept any connection  (but operation will fail if you try to change data) or accept only connections that explicitly define Intent = Readonly

Then we go in the Backup Preferences



We leave the default to Prefer Secondary, in this way the backup will always be performed on a secondary replica, if available.
We also change the backup priority by putting up MIA-CLUST3 Server.

Our settings results are also visible from the AG Dashboard

READ-ONLY ROUTING


After making our secondary replicas readable it's already possible to access them pointing directly to the server name.
Access example to MIA-CLUST2

Access example to MIA-CLUST3 (set to read-only intent, note the error in the first attempt)

But if we want to create a mechanism that automatically addresses read-only requests to Secondary Replicas we have to implement Read-Only Routing.

For details about the configuration you can refer to this article, but let's do it togheter:

• You must have configured a Listener and we did it in the AG configuration wizard:

            DNS: MIA-SQL-CLUSTER
            IP: 10.10.0.40
           TCP Port: 1433

• Identify each AG server member connection URLs, in our case:

           TCP://MIA-CLUST1.AdventureWorks.msft:1433
           TCP://MIA-CLUST2.AdventureWorks.msft:1433
           TCP://MIA-CLUST3.AdventureWorks.msft:1433
    As you can see URLs contain the Listener Configuration (TCP 1433) and the server FQDN


    However to obtain URLs you can use this script created by Matt Neerincx that provides an output like :

• Configure Read_only_routing using these TSQL commands on your Primary Replica:

__________________________ Start _________________________________________


-- Configure Read_Only_Routing

-- This script needs to be paramatrized with your deployment data!!!!

-- by Ruggiero Lauria


-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it

-- trough AG propriety. But for completeness I preferred to include it in the script


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


-- Define the Server Read_only_routing URL


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST1.AdventureWorks.msft:1433'));


-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it

-- trough AG propriety. But for completeness I preferred to include it in the script


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST2' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


-- Define Server Read_only_routing URL


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST2.AdventureWorks.msft:1433'));


-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it

-- trough AG propriety. But for completeness I preferred to include it in the script


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST3' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


-- Define Server Read_only_routing URL


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST3' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST3.AdventureWorks.msft:1433'));


-- Define Read Routing List when Server is acting as Primary Replica


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));


-- Define Read Routing List when Server is acting as Primary Replica


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST2' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST3')));


-- Define Read Routing List when Server is acting as Primary Replica


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST3' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST2')));


GO

_______________________________End _________________________________________



Note: Servers indicated in the routing lists will be used in sequence based on availability, there is no form of load balancing or round robin.



Connectivity Test


Here is the process used by the Read Only Routing:

1. Clients connect to our Availability Group Listener
2. Client request is redirected (always!) to the Primary Replica
3. Client has specified in the connection string ApplicationIntent=ReadOnly
4.  Sever checks if the target database is member of an Availability Group
5. If it is true step 4, server checks if it is set a read_only_routing_list on the Primary Replica
6. If it is true step 5 server checks, in order, that the servers listed in Routing_List are synchronizing and accepting connections (allow_connections = read_only or all)
7. Server reads from the read_only_routing_url the first secondary replica ready to accept connections and passes it's URL to the client
8. The client reads the URL and redirects itself to the secondary readable instance

To test connectivity we'll now use SQL Server Reporting Services, in this example I'll make a simple report (the example is trivial!) on the Sales.dbo.orders table , including the name of the responding server,  using the following query:


Select OrderDate, OrderTotal, @@ServerName as Server from Orders


First Test: we access with ApplicationIntent = ReadWrite

And we receive answer from the Primary Replica

And now let's change the connection type with ApplicationIntent=ReadOnly

 And resubmitting our report we obtain

We have been routed to the first server in the routing list defined for Primary Replica, remember:


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));




 Useful Commands

Connection string example:
Server=tcp:MIA-SQL-CLUST,1433;Database=Sales;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True


Listener Restart: 


ALTER AVAILABILITY GROUP [MIA-SQL-AG] RESTART LISTENER 'MIA-SQL-CLUST';


Related System Views


SELECT * FROM sys.dm_tcp_listener_states

SELECT * from sys.availability_replicas


SELECT * from sys.availability_read_only_routing_lists



Ending

I hope that this step by step guide will help you to take full advantages of this wonderful opportunity offered by SQL Server 2012.
Any corrections and comments are welcome
  
That's it, have fun!

SQL Server 2012 AlwaysOn

english version


Introduzione

Una delle più grandi novità introdotte da SQL Server 2012 sotto il profilo dell' alta disponibilità è la tecnologia AlwaysOn.
Questa si suddivide in due tipologie:

1. AlwaysOn  Failover Cluster Instance: Protezione a livello di singola istanza, miglioramento della tradizionale installazione in Cluster di SQL Server nel monitoraggio dello stato di salute e nelle implementazioni Multi-Subnet
2. AlwaysOn Availability Group: Protezione a livello di Database, evoluzione del DB mirroring delle versioni precedenti di SQL Server 

Source: Microsoft Learning

 La prima soluzione in termini archietturali poco aggiunge alle precedenti versioni se non l'eliminazione della necessità di creazione di una VLAN per i nodi del cluster; invece i gruppi di diponibilità sono una vera novità, forse uno di quei particolari per cui varrebbe la pena di migrare ad SQL 2012.

 AlwaysOn Availability Group

Generale

Questa tecnologia sfrutta i vantaggi offerti dal Windows Server Failover Cluster (WSFC) per fornire una soluzione di alta disponibilità per i Database SQL Server. E' un evoluzione della tecnologia di DB mirroring, introdotta con la versione SQL Server 2005, ma offre questi fondamentali vantaggi aggiuntivi:

1. Il numero dei nodi non è più limitato a 2
2. Il failover non è più fatto dal client, ma avviene lato server in maniera trasparente per i client
3. E' possibile accedere in sola lettura ad un nodo passivo del gruppo di disponibilità
4. E' possibile eseguire le operazioni di backup su un nodo passivo  del gruppo di disponibilità

 Nonostante questa tecnologia si basi sul Failover Cluster non è necessario:

1. Avere uno storage condiviso fra i nodi del cluster
2. Fare un installazione in Cluster di SQL Server

 Quindi ogni nodo avrà un'installazione autonoma di SQL Server con i dati su uno storage locale del nodo.

Note:

•  In Windows Server 2012 la funzionalità di Failover Cluster è disponibile anche sulla versione Standard 
• Non esiste nessun problema nel creare nodi virtualizzati, se non le solite raccomandazioni relative al fare girare SQL Server su una macchina virtuale

Terminologia

Source: Microsoft Learning

  
AlwaysOn Availability Group: gruppo di  server (in cluster) che mette in alta disponibilità uno o più Database
Primary Replica:  istanza che ospita la copia in lettura e scrittura del Database

Secondary Replica: host che ospita una copia del database su cui è possibile abilitare accessi in sola lettura

Listener: consiste di un nome DNS, una porta TCP ed uno o più indirizzi IP attraverso cui i client si connettono al gruppo di disponibilità
Availability Modes:  modalità di replica dei dati fra il membro primario ed i secondari, può essere:

1. Commit-Synchronous: la replica primaria attende conferma che le modifiche siano state apportate sul membro secondario prima di chiudere la transazione. Affidabile ma pericoloso per le performances
   
2. Commit-Asynchronous: il membro primario invia le modifiche al secondario e chiude la transazione senza aspettare conferma sull'esito.Non affidabile ma performante.

Failover: processo attraverso il quale il ruolo di replica primaria viene passato ad una replica secondaria, chiamata failover target. Sono disponibili 3 tipi di failover:

1. Failover automatico (senza perdita di dati): disponibile quando sia la replica primaria che la secondaria operano in modalità sincrona (Commit-Synchronous). In questo caso la modalità di failover nel gruppo è impostata su automatica ed  il passaggio di ruoli avviene senza alcun intervento amministrativo.
2. Failover manuale pianificato (senza perdita di dati): come nel caso precedente i due membri operano in modalità sincrona, ma non essendo stato impostato il failover automatico è necessario l'intervento amministrativo  per inviare il comando di failover.
3. Failover manuale forzato (con possibile perdita di dati): i due membri di replica operano in modalità asincrona e quindi l'amministratore può forzare il failover sapendo che eventuali transazioni del membro primario non ancora sincronizzate andranno perse.

Active Secondary Replicas: per migliorare le performance e gestire in maniera più efficiente le risorse del gruppo di disponibilità è possibile indirizzare le richieste di sola lettura e backup verso repliche secondarie


Installazione

L'obiettivo di questo articolo è centrato sugli Availability Group e non sulla configurazione del Cluster di Failover, quindi non entrerò nei dettagli su questa parte.

1) Installare la funzionalità di Failover Cluster sui Server

2) Creare un Cluster di Failover che comprenda tutti i Server

nel nostro esempio abbiamo creato un cluster MIA-CLUSTER a cui partecipano i server:
MIA-CLUST1
MIA-CLUST2
MIA-CLUST3

Notare che nel Cluster non c'è Storage condiviso.  
 

3) Installare un'istanza Standalone di SQL Server 2012 su ogni Server

 

Note:

• Usate un account di dominio per il Servizio SQL
• Potete installare solo il Motore di Database, opzionalmente anche gli strumenti di gestione

4) Attaverso il Configuration Manager abilitate AlwaysOn Availability Group su ogni Server

Riavviate i servizi di SQL Server

5) Create una condivisione di rete per i file di Backup utilizzati per sincronizzare le repliche

6) Creiamo i DataBase che vogliamo proteggere sul Server che diventerà la Replica Primaria

Nel nostro caso il Server che svolgerà il ruolo di Replica Primaria è MIA-CLUST1 e mettiamo in alta disponibilità il Database Sales
Controllate che il Recovery Model sia Full

 

7) Creiamo un AlwaysOn Avalaibilty Group

Lanciamo il Wizard dal Server Primario

 

 Specifichiamo il nome del nostro AG

 

 Selezioniamo il database Sales

Aggiungiamo le Repliche Secondarie: Add Replica...

 

 

Configuriamo il Listener

 

Impostiamo il Folder per la sincronizzazione

Controlliamo l'esito

  Il warning ricevuto relativo al WSFC è trascurabile

Risultato Finale:

Il Database Sales è replicato su tutti e tre i Server   

 

 Nel cluster è stato creato un servizio il cui Onwer è il nostro Primary Replica

Configurazione

Adesso facciamo un esempio di configurazione.
Entriamo nelle proprietà del nostro Availability Group

Impostiamo MIA-CLUST2 in modalità sincrona ed abilitiamo il failover automatico
Lasciamo MIA-CLUST3 in modalità asincrona, e quindi il failover può essere solo manuale
Rendiamo ambedue le Repliche Secondarie leggibili ed analizziamo meglio le opzioni disponibili.
Ricordiamo che ogni server può potenzialmente agire in due modalità: Primario e Secondario, qui abbiamo le opzioni disponibili per ogni server in base al ruolo che svolge (la situazione può essere dinamica):

Connections in primary role: quando agisce da primario, può accettare tutte le connessioni o solo quelle in scrittura.

Readable Secondary: quando agisce da secondario può non accettare connessioni, accettarle tutte (ma dare errore se si prova a modificare i dati) o accettare solo le connessioni che abbiano esplicitamente Intent=Readonly

 Andiamo poi nella sezione Backup Preferences
Lasciamo il default a Prefer Secondary, in questo modo i backup verrano sempre fatti sui secondari, se disponibili.
Cambiamo la priorità del backup mettendo avanti il Server MIA-CLUST3

I risultati delle nostre impostazioni sono anche visibili dalla Dashboard dell' AG

READ-ONLY ROUTING

Dopo aver reso le nostre repliche secondarie leggibili  già possible accedervi puntando direttamente al nome del Server.
Esempio di accesso al MIA-CLUST2

Esempio di accesso a MIA-CLUST3 (impostato read-intent only, notare l'errore nel primo tentativo)

Ma se vogliamo creare un meccanismo che indirizzi automaticamente le richieste di sola lettura verso le Repliche Secondarie dobbiamo implementare in Read-Only Routing.

Per la configurazione potete fare riferimento a questo articolo ma riassumendo:

• Dobbiamo avere configurato un Listener e noi lo abbiamo fatto nel wizard:

          DNS: MIA-SQL-CLUSTER
          IP: 10.10.0.40
          TCP Port: 1433

• Identifichiamo la URL per connessione ad ogni Server membro del nostro AG

           Nel nostro caso: TCP://MIA-CLUST1.AdventureWorks.msft:1433
                                     TCP://MIA-CLUST2.AdventureWorks.msft:1433
                                     TCP://MIA-CLUST3.AdventureWorks.msft:1433
          Potete utilizzare questo script  realizzato da Matt Neerincx che fornisce un output del tipo

• Configuriamo i nostri Read_only_routing dando questi comandi sulla nostra Replica Primaria:

___________________ Start ____________________________
-- Configure Read_Only_Routing
-- This script need to be paramatrized with your deployment data!
--  Ruggiero Lauria


-- Enable Server as Readable when acting as Secondary Replica  
-- This command is not strictly necessary because we have already configured it
-- trough AG propriety. But for completeness I preferred to include it in the script
ALTER AVAILABILITY GROUP [MIA-SQL-AG]
 MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Define Server Read_only_routing URL

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
 MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST1.AdventureWorks.msft:1433'));

-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it
-- trough AG propriety. But for completeness I preferred to include it in the script
ALTER AVAILABILITY GROUP [MIA-SQL-AG]
 MODIFY REPLICA ON
N'MIA-CLUST2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Define Server Read_only_routing URL

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
 MODIFY REPLICA ON
N'MIA-CLUST2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST2.AdventureWorks.msft:1433'));



-- Enable Server as Readable when acting as Secondary Replica   
-- This command is not strictly necessary because we have already configured it
-- trough AG propriety. But for completeness I preferred to include it in the script

 
ALTER AVAILABILITY GROUP [MIA-SQL-AG]
 MODIFY REPLICA ON
N'MIA-CLUST3' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Define Server Read_only_routing URL 

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
 MODIFY REPLICA ON
N'MIA-CLUST3' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST3.AdventureWorks.msft:1433'));



-- Define Read Routing List when Server is acting as Primary Replica

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));



-- Define Read Routing List when Server is acting as Primary Replica 

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST3')));


 -- Define Read Routing List when Server is acting as Primary Replica

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST3' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST2')));
GO
_________________End ________________________

Nota: I Server indicati nella routing list verranno usati in sequenza in base alla disponibilità, non esiste alcuna forma di Load Balancing o round robin.

Test di connettività


Di seguito il processo utilizzato dal Read Only Routing:

1. Il client si connettono al Listener del nostro Availability Group
2. Il client viene reindirizzato (sempre!) verso la Primary Replica
3. Il client ha specificato nella stringa di connessione ApplicationIntent=ReadOnly
4. Il sever controlla che il database di destinazione faccia parte di un Availability Group
5. Se è vero il punto 4, il server controlla se è impostata una read_only_routing_list sulla Primary Replica
6. Se è vero il punto 5 il server controlla in ordine che i server elencati nella Routing_List stiano sincronizzando ed accettino connessioni (allow_connections=read_only or all)
7. Il server legge la read_only_routing_url della prima replica secondaria pronta ad accettare connessioni e la passa al client
8. Il client legge la URL e si ridireziona verso l'istanza secondaria leggibile

Per eseguire un test di connettività si possono utilizzare i Reporting Services, quindi nel nostro esempio farò un semplice report (l'esempio è banale!) sulla tabella sales.dbo.orders includendo il nome del server che sta rispondendo con la seguente query:

Select OrderDate, OrderTotal, @@ServerName as Server from Orders

Primo Test: accediamo con ApplicationIntent=ReadWrite

 E riceviamo risposta dalla Primary Replica

Adesso cambiamo il tipo di connessione in ApplicationIntent=ReadOnly

 E rilanciando il report

 Siamo stati reindirizzati verso il primo server della routing list definita per Primary Replica:

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));

Comandi Utili

Esempio di stringa di connessione:

Server=tcp:MIA-SQL-CLUST,1433;Database=Sales;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

Restart del listener

ALTER AVAILABILITY GROUP [MIA-SQL-AG] RESTART LISTENER 'MIA-SQL-CLUST';

Related  System Views 

SELECT * FROM sys.dm_tcp_listener_states

SELECT * from sys.availability_replicas

SELECT * from sys.availability_read_only_routing_lists

Conclusione

Spero che questa guida passo passo possa esservi di aiuto per sfruttare appieno questa opportunità che ci viene offerta da SQL Server 2012.
Tutte le correzioni ed i commenti sono i benvenuti
Buon divertimento!