June 17, 2015 at 8:01 am
Hello Guys,
I would like to setup replica for one of the databases for reporting. The current environment is a 2 node cluster(active/passive). I would like to add a 3rd node that can server as a secondary replica. The secondary replica will be on asynchronous commit mode.
The database that needs to have alwayson setup has column level encryption enabled.
Could you please provide your suggestions on setting this up.
Other Questions,
* Do I need to backup and restore the service master key on secondary server in order to have the column level encryption to work on secondary server?
* What would be preferred Quorum settings?
* What is the setting for 'readable secondary' for primary and replica db?
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
* We are trying to setup without a Listner. Do I need to setup AG Listner? Can the application exclusively use the [secondary instance name].[replica DB name] without a listner?
Many thanks in advance.
June 17, 2015 at 2:32 pm
* Do I need to backup and restore the service master key on secondary server in order to have the column level encryption to work on secondary server?
Service master keys are created and managed by Windows. Is this column-level encryption or TDE?
* What would be preferred Quorum settings?
That probably depends on your configuration and preferences for failover. With 3 nodes in the cluster you could do node majority set.
* What is the setting for 'readable secondary' for primary and replica db?
If you want to be able to report off of a secondary replica you need to set the setting to make the database readable. Otherwise queries to it will fail.
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
This is only applicable for the primary. The default of "Allow all connections" should be fine.
* We are trying to setup without a Listner. Do I need to setup AG Listner? Can the application exclusively use the [secondary instance name].[replica DB name] without a listner
If you are going to use AGs you need a listener. If not for the reporting functionality then for the main application. Otherwise you do not get any of the HA benefits of AlwaysOn.
Have you followed the Books Online article for configuring read-only access?
Configure Read-Only Access on an Availability Replica (SQL Server)
Joie Andrew
"Since 1982"
June 18, 2015 at 4:55 am
answered here http://www.sqlservercentral.com/Forums/Topic1695309-3411-1.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply