Transcript Document
What HADR Option(s) Are Right For You? Where’s The AlwaysOn? Levels of Protection Instance Database Object Availability Groups FCI Log Shipping Mirroring Replication (Merge) Replication (P2P) Options Per Version 2000 2005 2008/R2 2012 2014 Availability Groups FCI Log Shipping Mirroring Replication (Merge) Replication (P2P) SQL 2000 Options By Edition Workgroup Standard Enterprise FCI* Log Shipping Replication (Merge) * 2 nodes only in Standard SQL 2005 Options By Edition Workgroup Standard Enterprise FCI* Log Shipping Mirroring** Replication (Merge) Replication (P2P) * 2 nodes only in Standard ** High safety (synchronous) only in Standard SQL 2008 / 2008 R2 Options By Edition Web Standard Enterprise Datacenter FCI* Log Shipping Mirroring** Replication (Merge)*** Replication (P2P) * 2 nodes only in Standard ** High safety (synchronous) only in Standard *** Web only supported as subscriber SQL 2012 / 2014 Options By Edition Web Standard BI Enterprise Availability Groups**** FCI* Log Shipping Mirroring** Replication (Merge)*** Replication (P2P) • * 2 nodes only in Standard/BI ** High safety (synchronous) only in Standard/BI *** Web only supported as subscriber • **** Up to 4 secondary replicas in 2012. 8 secondary replicas in 2014 Failover Cluster Instances (FCI) You might also might know it as Clustering Common Terms: Active/Active or Active/Passive (not correct use N or N+1) MSCS (Microsoft Clustering Services) – Windows NT4.0 to 2003 WSFC (Windows Server Failover Cluster) – Windows 2008 and up Full copy of SQL installed on all servers (nodes) Transparent client redirection on failover* Jobs, logins, linked servers, etc… also failover Some special hardware requirements Shared storage or 3rd party hardware solution There are single points of failure Log Shipping Not HA technology, but critical for business continuity Scheduled backups of transaction logs get moved and restored to other servers Can delay applying of logs on other servers Perfect for those “OH @#$@()” moments Possible to use built in routines, or roll your own Great for remote DR Limited to databases only. Logins, jobs, connection strings, etc.. Have to be managed Client redirection manual or via DNS Mirroring Deprecated, but doesn’t look like it’s going away any time soon May not live past SQL 2016 with AG support in Std Edition Two different modes High Safety (2 phase commit) High Performance (Enterprise Edition only) Provides automatic failover (with Witness) Transparent client redirection (use Failover Partner in conn string) Single database in mirror Have manage failover for multiple databases Have to manually manage logins, jobs, linked servers, etc… Can only have a single mirror Has no domain requirements Merge Replication Object level, so very restrictive Requires conflict resolution which can impact performance Can develop custom resolvers Needs a GUID on each row, impacts storage Does not scale well to high transaction levels Not a good choice Peer to Peer Replication Object level, so very restrictive Need to very carefully manage ranges at each location for peer writes to prevent serious database issues Using multi-site, multi-write can allow for local scale Management can be very complicated Availability Groups Built on WSFC (to manage quorum) so requires AD Sends transactions over to secondary replicas Synchronous mode (allows for automatic failover) Asynchronous mode (great for offsite DR) Allows reading from secondary replicas Incurs a 16-byte per row overhead on Insert/Update 4 secondary replicas in 2012, 8 in 2014 Multiple databases allowed in an AG Requires manual management of logins, jobs, linked servers, etc… Availability Groups Are The New Big Shiny They can be a (reasonably) easy way to get HA going without specialized hardware, but… Multiply the storage costs, as you’ll be paying per server Reading off a secondary? That’ll cost you Multi-subnet configuration can lead to management nightmares SQLPS, SSMS do not support the MultiSubnetFailover connection option Sync commit mode can inhibit performance No cross database or DTC transactions (coming in SQL 2016!) Will only work within a single domain and WSFC Lose the WSFC or AD and lose the AG Do NOT have AGs as your sole HADR option! HADR With AGs and…? First option… Log Shipping Provides ability to delay logs application to secondary server Secondary can reside outside the WSFC Second option… FCI Provides initial instance level protection Cannot automatically failover to another node in AG Third option… Log shipping & FCI Best of both worlds, but… No automatic failover within the AG Questions?