AlwaysOn: Setting up Readable Seconary Replica
The easiest way to create an availability group and adding replicas is to use the availability group wizard. You can invoke the wizard by right-clicking on 'Availability Groups'. The picture of the explorer window below shows that I have created 1 availability group 'StockPro' with two replicas 'SUNILA03-6PLHSI' (primary) and 'SUNILA03-YLZO1U' (secondary). The availability group StockPro contains one database 'hadr_test' but you can have more databases if you want. For now, let us work with this simple example
While working with the wizard, you will be presented a choice to enable one or more secondary replicas for read work load as shown below in the picture. You can choose either ‘Yes’ or ‘Read-intent only’ to get the ability to run the read workload on the secondary replica. In the picture below, I have chosen ‘Yes’ option for replica running on instance ‘SUNILA03-YLZO1U’. Please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/2011/12/22/alwayson-why-there-are-two-options-to-enable-a-secondary-replica-for-read-workload/ understand the difference between the two options.
Once you have configured the availability group, you can connect to the secondary replica either explicitly or using VNN and run your reporting workload. I will talk about VNN in a later blog.