A readable replica database is a secondary replica database that is typically used for reporting, backups or other non-production purpose. It is typically updated asynchronously and is maintained on a server that is independent from the primary and the secondary server that is designated as the failover partner.
There may be more than one readable replica database, but it must be a member of the same Windows Failover Cluster as the primary and secondary clusters.
The following sequence provides an example of adding a readable replica to an already existing AlwaysOn database cluster:
Add Replica WizardThis process should be called from the primary replica server but connected to the listener for the Availability Group. To begin the process, connect to the AlwaysOn Availability Group Listener and right-click the "AlwaysOn High Availability->Availability Groups->(name of your group)->"Availability Replicas" folder, then choose "Add Replica". Press "Next". |   |
Connect to Existing ReplicaBefore you add a new replica, you need to connect to an existing replica. The wizard will display the name of the replica that is hosted, but you will need to press the "Connect" button to connect to it. When you press the "Connect" button, you will be presented the same connection window that you would be given when initially connecting to the server. | |
Once you have connected directly to the server where the replica is hosted, this window will be updated. Press "Next" to continue. | |
Add ReplicaOnce you get to this screen, Press the "Add Replica" to add the new server to the cluster. | |
You will be presented a server connection window — select the server name and instance of the SQL Server that will join the AlwaysOn Availability Group and press "Connect". The Authentication method you use here doesn't need to be the one that is going to be used permanently for this connection — it needs to be good enough to establish the connection. | |
Set "Replicas" ParametersIn our lab, we chose the readable replica to have asynchronous updates (this will increase performance of the entire cluster) and set the "Readable Secondary" property to "Read-intent Only". This means that if an application wants to use this copy, it needs to specify "read intent only" in its connect string. NOTE: Not any application will be able to use this copy out of the box — someone with at least enough knowledge to change the connect string will need to make the deliberate decision to use it. | |
Set "EndPoints" ParametersTypically, these parameters do not need to be changed. Set "Backup Preferences" ParametersSince these were already set up when the cluster was originally created, these should not need to be changed when adding a readable replica. | |
Set "Listener" OptionSince the Availability Group should already have a listener set up, choose the first option. | |
Select Initial Data SynchronizationThe simplest way to accomplish this is to designate a directory that is accessible from both the primary server and the replica database server. Even if you need to create a temporary directory and open permissions on it so that both servers can read and write to it, this method is far simpler than the other two methods. Once the readable replica is up and running, you will not need this directory any more, so it can be removed after the replica is up and running — it is only used to transfer the contents of the database and synchronize copies. When finished, press "Next". | |
SummaryBefore creating the replica, the wizard will show you what you will be doing and give you a chance to go back and make changes. If you are satisfied with your choices, press "Finish" to create the replica. | |
ResultsWhen finished, the wizard will display your results. | |