June 17, 2015 at 6:34 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 18, 2015 at 4:40 am
SQL!$@w$0ME (6/17/2015)
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.
Sounds good so far
SQL!$@w$0ME (6/17/2015)
The database that needs to have alwayson setup has column level encryption enabled.
Can you provide a little more detail on the encryption implementation
SQL!$@w$0ME (6/17/2015)
* 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?
No, the service master key is an instance level encryptor
SQL!$@w$0ME (6/17/2015)
* What would be preferred Quorum settings?
with 3 nodes in the cluster you would be looking to use Majority node set which does not use a separate witness (disk or fileshare). Are the servers same site or not?
SQL!$@w$0ME (6/17/2015)
* What is the setting for 'readable secondary' for primary and replica db?
Related to the listener setup, see below
SQL!$@w$0ME (6/17/2015)
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
Depends on how you setup the group, generally it would be left to All
SQL!$@w$0ME (6/17/2015)
* 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.
Only if the connections in secondary role are set to Yes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 18, 2015 at 9:53 am
Thanks Perry.
*Can you provide a little more detail on the encryption implementation
Column level encryption is enabled for one of the tables in this database (Symmetric key protected by a certificate)
*with 3 nodes in the cluster you would be looking to use Majority node set which does not use a separate witness (disk or fileshare). Are the servers same site or not?
Servers are on the same site. But the 3rd node will not be configured for high availability, just for reporting (no vote for quorum?)
June 18, 2015 at 10:05 am
SQL!$@w$0ME (6/18/2015)
Servers are on the same site. But the 3rd node will not be configured for high availability, just for reporting (no vote for quorum?)
If the servers are same site then you'd leave all nodes with an active vote to maintain the recommended Majority Node Set (odd number of nodes).
What Operating system are your nodes using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 18, 2015 at 10:48 am
Server 2012 R2.
In our scenario, SQL is clustered(2 node) and we don't want windows cluster to be down even if the replica server- 3rd node (reporting) goes down.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply