none
Always On Replication

    Question

  • I'm wondering if this would be a valid form of replication.

    Two local SQL Servers, with a private network between them using AlwaysOn to replicate a few databases, with the second instance configured as a read-only copy for the applications (SSRS, etc.) using sync (full safety).  Within that same Availability Group, a 3rd SQL Server in another state, with ~70ms RTT, with async replication.

    Next question is, am I required to have a disk quorum in Cluster Manager if I'm not doing an FCI?  These two local SQL instances will be virtualized on top of Hyper-V, and introducing iSCSI to have a shared disk will greatly increase the complexity of the solution.  I'd rather avoid it.

    Last question is, right now we have a column encrypted within our database.  From reading about Avail. Groups, I'll have to completely remove that encryption.  But after the Avail. Group is established, can I put it back in place?


    http://sharepoint.nauplius.net

    Thursday, April 26, 2012 11:02 PM

Answers

All replies

  • 1) Yes, if by async replication you mean async mirroring - yes. AlwaysOn has an synchronous and asynchronous mode. If by async replication you mean transactional replication. If you have a remote distributor your availability group can float between nodes. Your subscriber cannot be part of the AG.

    2)  No

    3) I think you are referring to this link - http://msdn.microsoft.com/en-us/library/hh510178.aspx This refers to TDE, but not column level encryption. You will need to decrypt your columns as you always do. You may need a certificate on the secondary to do your encryption. So it should be transparent to you.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, April 27, 2012 1:46 AM
  • 1) What I'm looking for within the same AG is "local sync, remote async" (using terms from SQL mirroring).

    For 2), just ignore the Cluster Manager warning that if one node fails, the cluster will fail?

    3) I just want to make sure I can go back and apply the column-level encryption after-the-fact.

    Thanks!


    http://sharepoint.nauplius.net

    Friday, April 27, 2012 1:54 AM
  • 2) You can't really apply clustering concepts to AG. Basically instead of a node failing over, an AG fails over - or all databases in the AG failover to the secondary. The other database running on the primary node remain running on that node.

    3) Column level encryption done on the database will migrate to the secondary.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, April 27, 2012 2:20 PM
  • So what exactly do I need to do with failover clustering?  I know it needs to be installed, and I know I have to add the AG as a resource -- can I ignore just about everything else for my scenario?

    http://sharepoint.nauplius.net

    Friday, April 27, 2012 3:43 PM
  • I am not sure what your question is. You do not require cluster hardware for Always on. There are several steps necessary to configure your servers for AlwaysOn.

    Please refer to this link for more info.

    http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, April 27, 2012 4:15 PM
  • That link specifically goes into the Windows Cluster configuration required for AlwaysOn.  Under the picture titled "What a cluster", you'll notice he is using Node Majority with no warnings.  He doesn't specifically call out that he is using a quorum disk of any sort.  This is the same case as what I'm doing, except I have the warning:

    Node Majority - Warning: Failure of a node will cause the cluster to fail.

    I'm not doing SQL FCI and don't want to.


    http://sharepoint.nauplius.net

    Friday, April 27, 2012 4:20 PM
  • Disregard, I added in a file share witness as it will be ideal for the setup anyways (3 nodes).


    Thanks!


    http://sharepoint.nauplius.net

    Friday, April 27, 2012 9:22 PM