Share via

Presentation Transcript: Introducing High Availability and Disaster Recovery with SQL Server 2012 AlwaysOn

  • Article

SQL11UPD01-TSCRIPT-01

This wiki is a transcript of a previously recorded video.

Related content assets: 

  • Presentation: Introducing High Availability and Disaster Recovery with SQL Server 2012 AlwaysOn (SQL11UPD01-DECK-01) 
  • Video: Introducing High Availability and Disaster Recovery with SQL Server 2012 AlwaysOn (SQL11UPD01-REC-01)

Slide: Introduction to SQL Server 2012 AlwaysOn Availability Groups

In this video we’ll look at the introduction to SQL Server 2012 AlwaysOn Availability Groups. My name is Jonathan Kehayias - I’m a SQL Server MVP and a Microsoft Certified Master for SQL Server 2008 as well as a Principal Consultant for SQLskills.com.

Slide: Improved High Availability and Disaster Recovery

In SQL Server 2012 we get improved high availability and disaster recovery with the use of AlwaysOn Availability Groups which is an extension of Windows Server failover Clustering on top of what used to essentially be database mirroring and the two have been merged together to provide a more robust solution as far as providing high availability and remote disaster recovery in a single solution that is very easy to implement and maintain.

Slide: What Happens When…

So in the past when business requirements required that you have automatic failover of multiple databases together and that there be no single point of failure within the environment and redundancy across multiple data centers, things were really complex to actually get configured and implemented inside of the environment.

In 2008 R2 and prior, if you were using database mirroring you got automatic failover of a single database. If you wanted to have multiple databases fail over at the exact same time it required some custom implementation and there was no guarantee that you were actually going to get same time failover associated with those databases inside database mirroring.

With database mirroring you’ve got redundant copies of the database so you had no single point of failure. With Windows Server Failover Clustering the SAN is typically your single point of failure because all of your storage is shared inside of the SAN so if it disappears your failover cluster is not going to be viable. With database mirroring you have application failover that’s accomplished through the use of the failover partner connection string parameter so you can get automatic failover between the principal and the mirror but if you went to a remote data center with something like log shipping to provide a warm standby server in another environment you weren’t able to get that same automatic failover.

In SQL Server 2012 the answer to this is much easier to implement using Availability Groups, so we get redundant copies of our database (up to four) that exist on nodes participating inside of the Availability Group and these nodes can either be synchronously replicated to or asynchronously replicated to.

We get multiple database failover so all the databases defined within the same Availability Group are going to automatically fail over as the Availability Group fails over from node to node, and we get easier application failover through the use of the Availability Group Listener and the application virtual name that exists inside of the Windows Server Failover Cluster resource group for the Availability Group – so as the Availability Group is failed from one node to the other, all the resources associated with that, including the application virtual name, failover simultaneously and you get instant redirection of applications for your Availability Group to the new node that the group is running on.

Slide: AlwaysOn Availability Groups

So we get advanced enhancements to the capabilities that are already provided by database mirroring, the biggest one being the multiple database coordinated failover for the applications that required multiple databases within a single instance; an example may be SharePoint, but there are plenty of applications that are developed in line-of-business to have multiple databases, and the database code actually talks across databases in some instances.

The simplified application connectivity and automatic redirection through the implementation of the Availability Group Listener actually can allow some of you legacy applications that weren’t able to leverage database mirroring because they didn’t support the failover partner connection string setting; those can actually be moved into an Availability Group because all you connect to is the application virtual name and the failover of the virtual name inside of the Windows Server Failover Cluster on top of the Availability Group is actually what provides the redirection. So the legacy applications that weren’t compatible with database mirroring are now available to leverage Availability Groups to get the high availability that they need without having to be inside a failover cluster.

For the log application between the primary and the replicas, there’s built in compression and there’s encryption as well, so there’s no real need to be concerned about the security of the data that’s flowing between the primary and the replicas (or the secondaries) inside of the environment. And we get the ability to have synchronous and asynchronous data movement if we are looking for a high-availability solution with inside of our single data center. Synchronous data movement along with automated failover will provide us that, and if we’re looking to implement this as a possible DR solution or maybe for a scale-out reporting environment, we can set up a couple of asynchronous replicas inside of the environment as well, and that gives us the ability to not have our production workload impacted by a higher latency associated with network traffic that may be occurring for our remote data center.

We do have automatic or manual failover modes and we have a configurable failover trigger level that we can set as well. Just like database mirroring in SQL 2008, we do have automatic repair of page level corruptions, so if the primary is running and it gets a page checksum failure, it can request that page from the mirror and automatically repair that page associated with the corruption event.

One of the new features is we get readable secondary replicas and it doesn’t require any kind of snapshots that you used to have to do with database mirroring or managing snapshots, you can actually define a secondary replica as being a read-only secondary and the applications, depending on how they’re connecting to the virtual network name associated with the Availability Group, can actually be re-routed into a readable secondary to allow offloading of your read-only workloads.

There’s also full support for FILESTREAM, FileTable, Remote Blob Store and Service Broker inside of Availability Groups, which was one of the limitations that previously existed associated with database mirroring – these are all entirely supported now with AlwaysOn Availability Groups.

The configuration of an AG inside of 2012 is really simply by comparison to database mirroring in 2008 R2 and beyond because of the new configuration wizards, the PowerShell integration, and the new Availability Groups Dashboard for monitoring the performance and the health of the Availability Groups in the environment.

Slide: Availability Group Architecture (1)

So the Availability Group architecture… Availability Groups actually leverage Windows Server Failover Clustering but they’re not a failover cluster instance of SQL Server – there’s is a big difference of distinction there. The reason that Availability Groups leverage Windows Server Failover Clustering is for inter-node health detection inside of the actual clustered environment, for failover coordination so the Availability Group is only owned by one cluster node at a time, and for health detection of the actual nodes. It provides a distributed data store that allows storage of settings and the state of the Availability Group inside of the environment and we get distributed change notification between nodes, so when one node is being failed over to another it actually notifies all of the nodes inside of the failover cluster so that they know how to respond to that event.

What’s really great about this is Windows Server Failover Clustering provides a common availability platform inside of the Microsoft product stack; Hyper-V uses it, Exchange uses it, it’s used for AlwaysOn failover-clustered instances, and now it’s used for AlwaysOn Availablity Groups as well.

Slide: Availability Group Architecture (2)

With the Availability Group architecture you can have a mix of synchronous and asynchronous topologies within the same Availability Group. This replaces the need for multiple technology implementations, like database mirroring with a log sipping secondary. This is all provided in a single solution.

You can have up to three replicas – one primary and two secondaries inside of the environment and there’s support for separate failure paths where you have multiple Availability Groups within the same Windows Server Failover Cluster node set. So you can have three servers that have an Availability Group defined across nodes A, B, and C; and you can have separate Availability Group for a different set of databases defined across C, B, and A; and the failure paths can be configured so that A will fail over to B, and C will fail over to B primarily. And then as an alternative, if a worst case scenario occurs and B’s not available but you need to fail over C for some maintenance, it can also fail over to A as well and you’ll be running all the databases inside of a single instance or it could be multiple instances depending on how you’ve configured things.

Slide: Topology Examples (1)

So an example of the topology: if we’re looking at a map of the United States, typically on the West Coast we have problems with earthquakes, in the center of the country we have problems with tornados, and then on the east coast we have a problem with hurricanes – although in recent years we’ve had issues with earthquakes on the East Coast as well, so the typical layout of what your disaster planning might have been two years ago may be very different today.

So if your primary data center is on the East Coast, for example in New York City, maybe you want to have a synchronous, automatic-failover secondary inside of your data center so you don’t have to take downtimes for maintenance and other tasks, you can actually just fail over the Availability Group from the primary server to the secondary, reversing their roles. And then maybe in the middle of the country you have a disaster-recovery center, an alternate center that you have a good enough connection to that you can still maintain a synchronous connection to, and you can do synchronous replication of the log records to the secondary. And then maybe you have a third data center for the worst case scenario, that’s out in California, and the latency associated with that’s going to be way too high for you to be able to do synchronous data movement across the wire, so you’ll set that one up as asynchronous in the environment.

Slide: Topology Examples (2)

Or, an alternative would be if you can’t get the synchronous data movement to one of the remote data centers, you can also have it set up so your that remote data centers are getting asynchronous data movement, and your local data center is configured with an AlwaysOn failover cluster instance, which we’ll talk about, and what the considerations are for geo-secondaries in the environment.

Slide: Architecture – Availability Group Failover

So with the Availability Group failover, one of the things associated with Availability Groups is that you have the Availability Group Listener and an application virtual name that you’re going to connect to, and the clients connect to the primary replica through the Availability Group Listener virtual name.

So if you have a failover associated with your Availability Group, or it’s initiated manually through the Windows Server Failover Clustering Manager or through the Availability Group failover functionality, if the active primary right now is SQLAG1, it’s going to confirm the failover and inform Windows Server Failover Clustering that it’s going to fail over the activity to SQLAG2.

As a part of that, it’s going to take the cluster resource group associated with the Availability Group Listener offline, taking the Availability Listener, the IP address associated with it, and the Availability Group offline temporarily while it performs the failover. All the clients are going to be disconnected at this point, so there is a little bit of development consideration that you have to make associated with you Availability Group configuration, so that you have a retry technology or retry logic built in so you can either provide the error message that the connection failed back to the client, or if the application gracefully retries, it’ll be transparent to the client.

The Windows failover cluster’s going to tell the Availability Group resource DLL to bring the Availability Group online on SQLAG2. The Availability Group Listener and virtual name resources along with the IP are going to be brought online by Windows Server Failover Clustering on SQLAG2. The applications will be able to reconnect to SQLAG2 through the application virtual name and once the databases are online on SQLAG2, the Windows Server Failover Cluster is going to provide notification of the new primary to all of the secondaries in the environment and they’re going to resynchronize with the primary and the log data movement will continue inside of the environment.

Slide: What Happens When…

One of the things associated with failover of databases is if you need to move a database to a different server, in SQL 2008 the server logins are mapped to database users through the login SID. If you have incorrectly mapped logins you’ll end up with failures associated with your client connections to the new server in the environment when they try to actually access the database.

Simply creating a login on a different server with the same password doesn’t resolve the problem because the login will be created with a different SID hash associated with it. You can manually transfer logins from server to server using sp_helprevlogin (which is a stored-procedure provided by Microsoft), or you can use the SSIS Transfer Logins task to maintain identical SIDs on both servers.

Nothing tracks additional external dependencies outside of logins that might exist inside of your database code though, so there could still be other problems associated with moving a database from one server to another.

In SQL 2012 we have what are called Partially Contained Databases. Partially Contained Databases can actually contain database login credentials as a part of the database so when you create a user, the credential for that user is maintained inside of the database and it moves with the database as the database moves from server to server. This makes it much easier to plan for failover inside of an environment by allowing the information to be integrated as a part of the database.

In 2012, if you’re using a Partially Contained Database, it can track all the instance-level impacts and what are called ‘uncontained dependencies’ using sys.dm_db_uncontained_entities inside of SQL Server 2012.

Slide: Contained Database Authentication (1)

Partially Contained Databases allow you to contain the login inside of the database and when a connection is made directly to the database, using the database name in the connection string, the authentication actually occurs through the database rather than the instance of SQL Server.

There are two types of users that can be contained. The first one is a contained database user that actually has a password that’s similar to your SQL login but it’s at the database level, not the instance level. The second one is that Windows Principals can connect directly to the database without having a login in the master database, so you create a user from the Windows Principal, and that’s also self-contained inside of the database, inside of the contained database.

Slide: Contained Database Authentication (2)

To configure contained database authentication, the first thing you have to do is enable the ‘contained database authentication’ sp_configure option. Then you need to alter the database and set containment to partial. If you’re using Availability Groups, enabling the sp_configure option has to be performed on each of the instances inside of the Availability Group: it’s not a group-wide setting, its per-instance, so you have to go make that change per instance.

If you have an existing SQL Server login, you can convert that to a contained database user sp_migrate_user_to_contained, which is a system stored procedure. If you want to create new users, there’s a modified DDL for CREATE USER that now includes the WITH PASSWORD option, so that is how you create a contained database user that uses database authentication.

Slide: Partial Contained Database

If you’re developing against a partially contained database, there are a number of considerations. Containment actually provides a functional boundary between the database and the instance that it’s running on, so when you’re making development changes, or you’re developing a new database that’s going to be partially contained, you have to make a number of considerations about what that boundary actually is and make sure you that don’t cross the boundary, or you get uncontained features inside of your application. If you’re using temporary tables, they are allowed but they use the collation of the contained database, not the collation of tempdb. This can solve a number of problems associated with environments where SQL Server was installed with a binary collation and the database doesn’t use a binary collation. Or SQL Server was installed with a case-sensitive collation and the database was created on an instance that previously used a binary collation so it’s got a binary collection. Instead of having the collation mismatch problems that were associated with that, temporary objects inside of a partially-contained environment will now use the database collation instead of tempdb’s.

You can’t use named constraints for any of your temporary tables and you can’t refer to user-defined data types, XML schema collections, or user-defined functions – that’s a limitation of being in a partially-contained environment.

If you use dynamic SQL, dynamic SQL will always be flagged as an uncontained entity if you’re querying dm_db_uncontained_entities, and the reason for that is it’s actual containment can’t be determined until the run-time for the dynamic SQL and it would require execution of the SQL to know if it’s contained or uncontained.

And additional features like replication, change data capture, change tracking, and SQL Agent jobs are going to be an uncontained portion that isn’t tracked. Linked servers and SSIS if you have packages that touch the database – those aren’t going to be things that are contained as a part of partial containment for the database.

Slide: Summary

In summary, SQL 2012 offers a great advance in high availability and disaster recovery with the Availability Groups. You get up to four secondary replicas. You can combine synchronous and asynchronous replicas in the same topology. You have a choice between automatic and manual failover, and you get a number of additional offerings associated with the flexibility in configuring your topology, including the ability to read from secondary replicas, to fail groups of databases over automatically together without having to need complex logic. And you get much broader cross-feature support, including FILESTREAM, Remote Blob Store, FileTable, and Service Broker. And the advances with partial containment for databases make actual designing and implementation of database failover significantly easier inside of the environment.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]