html5-img
1 / 21

SQL 2012 AlwaysOn

SQL 2012 AlwaysOn. High Availability/Disaster Recovery Solution. Dilip Nayak .

tymon
Download Presentation

SQL 2012 AlwaysOn

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 2012 AlwaysOn High Availability/Disaster Recovery Solution

  2. Dilip Nayak • I work as a DBA at CUDirect Corporation. My expertise is on performance tuning/troubleshooting, high availability. I have about 17 years of experience working as a .NET developer. I am a pretty active member of PASS, SQL Saturday and anything SQL related. • Email: d_nayak@hotmail.com • Twitter:@d_nayak

  3. Why do we need another solution? • The current High Availability and Disaster Recovery Solutions from Microsoft are scattered. • High Availability /Disaster Recovery Solutions: • Windows Failover Clustering • Database Mirroring • Log Shipping • Database Replication is not a viable HA/DR solution.

  4. SQL Failover Clustering • A failover cluster is a combination of two or more servers(nodes) connected to a shared storage. WindowsCluster Node 1 client App Server Database Node 2

  5. SQL Failover Clustering Issues • Protects against hardware loss and not disk loss. • Multi site cluster across data centers is tedious using VLAN etc. • Passive node is not used thus not achieving maximum utilization of resources.

  6. Database Mirroring Primary DB Primary Server Client App Server Mirrored DB Primary Server Primary DB Mirrored Server

  7. Database Mirroring Issues • Can have only one mirror. No multiple copies of data. • Mirrored database is not readable, though snapshots can be taken from the mirrored database. Maximize utilization of resources is not met. • Need to be setup per database. • Per database failover, no automatic failover of multiple databases. • No sql logins/jobs are replicated to the mirrored instance. • Either synchronous or asynchronous mirroring is supported but not both.

  8. Log Shipping Issues • Cannot have synchronized databases. • To be setup per database. • In case of failover, the connection string needs to change to point it to the failover server. • No logins/jobs are replicated to the mirrored instance.

  9. What if we had all these features in a single solution? • Protects against hardware loss. • Protects against disk loss. • Multiple copies of replicated databases. • Readable mirrored databases thus maximizing resources. • Multiple database failover at once. • No need to change the connection string in case of failover, you can use the same abstract name. • Failover can occur across datacenters without extra effort.

  10. The answer is……. SQL 2012 AlwaysOn

  11. Modes of SQL2012 AlwaysOn 1. AlwaysOn Failover Clustering Instance(FCI). 2. AlwaysOn Availability Groups.

  12. SQL 2012 AlwaysOn Requirements: • Needs to have Windows failover cluster. • Supported in Enterprise Edition Only. • Install individual SQL servers on each machines, not cluster aware. • All servers should be in a single windows cluster. • Matching hardware not required. • Database should have Full Recovery model with at least one full successful backup. • All nodes must be in the same AD domain.

  13. AlwaysOn Vocabulary • Availability Group : A set of databases working together in a group. • Availability Replicas: A set of Mirrored databases in a availability group. • Availability Group Listener: Virtual network name.

  14. Demo Architecture SQL2012-HostA Local Data Center Synchronous SQL2012-HostB DR Site Asynchronous SQL2012-HostC

  15. SQL 2012 AlwaysOn Demo Setup Domain: SQL2012.Demos Cluster Name: SQL2012ALwaysOn Availability Group:SacAG Listener: SACListener – 192.168.81.30

  16. Demo • Readable secondary. • DBCC on secondary. • Backup on secondary. • Synchronous Failover. • Asynchronous Failover. • Listener. • Read-only routing.

  17. SQL 2012 AlwaysOn Features • Combines the best features of mirroring and clustering. • Can have up to 2 mirrored synchronous databases(high safety) - automatic failover • Can have up to 4 mirrored asynchronous databases(high performance) – manual failover. • Can have read-only secondary databases. • Can have one or more databases in an availability group so in case of a failover, all databases failover at once. • SQL Logins can be failed over using contained databases. • Failover can be scripted using Windows PowerShell.

  18. SQL 2012 AlwaysOn features contd.. • Backups can occur on the replica databases. • DBCC commands can be run on secondary. • Flexible failover policy– sp_server_diagnostics. • Simpler deployment. • Unified Solution. • Unified Dashboard.

  19. Some disadvantages • Needs Enterprise Edition of SQL 2012. • Needs Windows Cluster which in turn needs Windows Enterprise. • If read-only feature on replica is used, you need to take care of extra licensing. • System databases cannot be replicated- users(contained databases) and jobs are not part of replication. • Differential backups are not supported on secondary.

  20. References • AlwaysOn Whitepaper http://msdn.microsoft.com/en-us/library/jj191711 • Creating Virtual machines using Virtual Box http://bifuture.blogspot.com/2012/04/creating-sql-server-2012-playground.html • Setting up Availability Groups http://www.brentozar.com/archive/2011/07/how-set-up-sql-server-denali-availability-groups/ • Creating virtual machines on Hyper-V and AlwaysOn Features http://www.sqlfeatures.com

  21. Q&A

More Related