{"id":589,"date":"2013-06-23T11:34:22","date_gmt":"2013-06-23T18:34:22","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=589"},"modified":"2017-04-13T09:19:19","modified_gmt":"2017-04-13T16:19:19","slug":"sql-server-hadr-features","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-hadr-features\/","title":{"rendered":"The Accidental DBA (Day 23 of 30): SQL Server HA\/DR Features"},"content":{"rendered":"

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0several times each year<\/a>. You can find all the other posts in this series at\u00a0http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n

Two of the most important responsibilities for any DBA are protecting the data in a database and keeping that data available.\u00a0 As such, a DBA may be responsible for creating and testing a disaster recovery plan, and creating and supporting a high availability solution.\u00a0 Before you create either, you have to know your RPO and RTO, as Paul talked about<\/a> a couple weeks ago.\u00a0 Paul also discussed what you need to consider when developing a recovery strategy, and yesterday Jon covered considerations for implementing a high availability solution<\/a>.<\/p>\n

In today\u2019s post, I want to provide some basic information about disaster recovery and high availability solutions used most often.\u00a0 This overview will give you an idea of what options might be a fit for your database(s), but you\u2019ll want to understand each technology in more detail before you make a final decision.<\/p>\n

Backup\/Restore<\/span><\/strong><\/p>\n

No matter what type of implementation you support, you need a disaster recovery plan.\u00a0 Your database may not need to be highly available, and you may not have the budget to create a HA solution even if the business wants one.\u00a0 But you must have a method to recover from a disaster.\u00a0 Every version, and every edition, of SQL Server supports backup and restore.\u00a0 A bare bones DR plan requires a restore of the most recent database backups available \u2013 this is where backup retention<\/a> comes in to play.\u00a0 Ideally you have a location to which you can restore.\u00a0 You may have a server and storage ready to go, 500 miles away, just waiting for you to restore the files.\u00a0 Or you may have to purchase that server, install it from the ground up, and then restore the backups.\u00a0 While the plan itself is important, what matters most is that you have<\/em> a plan.<\/p>\n

Log Shipping<\/span><\/strong><\/p>\n

Log shipping exists on a per-user-database level and requires the database recovery model to use either full or bulk-logged recovery (see Paul\u2019s post<\/a> for a primer on the differences).\u00a0 Log shipping is easy to understand \u2013 it\u2019s backup from one server and restore on another \u2013 but the process is automated through jobs.\u00a0 Log shipping is fairly straight forward to configure and you can use the UI or script it out (prior to SQL Server 2000 there was no UI).\u00a0 Log shipping is available in all currently supported versions of SQL Server, and all editions.<\/p>\n

You can log ship to multiple locations, creating additional redundancy, and you can configure a database for log shipping if it\u2019s the primary database in a database mirroring or availability group configuration.\u00a0 You can also use log shipping when replication is in use.<\/p>\n

With log shipping you can allow limited read-only access on secondary databases for reporting purposes (make sure you understand the licensing impact), and you can take advantage of backup compression<\/a> to reduce the size of the log backups and therefore decrease the amount of data sent between locations.\u00a0 Note: backup compression was first available only in SQL Server 2008 Enterprise, but starting in SQL Server 2008 R2 it was available in Standard Edition.<\/em><\/p>\n

While Log Shipping is often used for disaster recovery, you can use it as a high availability solution, as long as you can accept some amount of data loss and some amount of downtime.\u00a0 Alternatively, in a DR scenario, if you implement a longer delay between backup and restore, then if data is changed or removed from the primary database \u2013 either purposefully or accidentally \u2013 you can possibly recover it from the secondary.<\/p>\n

Failover Cluster Instance<\/span><\/strong><\/p>\n

A Failover Cluster Instance (also referred to as FCI or SQL FCI) exists at the instance level and can seem scary to newer DBA because it requires a Windows Server Failover Cluster (WSFC).\u00a0 A SQL FCI usually requires more coordination with other teams (e.g. server, storage) than other configurations.\u00a0 But clustering is not incredibly difficult once you understand the different parts involved.\u00a0 A Cluster Validation Tool was made available in Windows Server 2008, and you should ensure the supporting hardware successfully passes its configuration tests before you install SQL Server, otherwise you may not be able to get your instance and up and running.<\/p>\n

SQL FCIs are available in all currently supported versions of SQL Server, and can be used with Standard Edition (2 nodes only), Business Intelligence Edition in SQL Server 2012 (2 nodes only), and Enterprise Edition.\u00a0 The nodes in the cluster share the same storage, so there is only one copy of the data.\u00a0 If a failure occurs for a node, SQL Server fails over to another available node.<\/p>\n

If you have a two-node WSFC with only one instance of SQL Server, one of the nodes is always unused, basically sitting idle.\u00a0 Management may view this as a waste of resources, but understand that it is there as insurance (that second node is there to keep SQL Server available if the first node fails).\u00a0 You can install a second SQL Server instance and use log shipping or mirroring with snapshots to create a secondary copy of the database for reporting (again, pay attention to licensing costs).\u00a0 Or, those two instances can both support production databases, creating a better use of the hardware.\u00a0 However, be aware of resource utilization when a node fails and both instances run on the same node.<\/p>\n

Finally, a SQL FCI can provide intra-data center high availability, but because it uses shared storage, you do have a single point of failure.\u00a0 A SQL FCI can be used for cross-data center disaster recovery if you use multi-site SQL FCIs in conjunction with storage replication.\u00a0 This does require a bit more work and configuration, because you have more moving parts, and it can become quite costly.<\/p>\n

Database Mirroring<\/span><\/strong><\/p>\n

Database mirroring is configured on a per-user-database basis and the database must use the Full recovery model.\u00a0 Database mirroring was introduced in SQL Server 2005 SP1 and is available in Standard Edition (synchronous only) and Enterprise Edition (synchronous and asynchronous).\u00a0 A database can be mirrored to only one secondary server, unlike log shipping.<\/p>\n

Database mirroring is extremely easy to configure using the UI or scripting.\u00a0 A third instance of SQL Server, configured as a witness, can detect the availability of the primary and mirror servers.\u00a0 In synchronous mode with automatic failover, if the primary server becomes unavailable and the witness can still see the mirror, failover will occur automatically if the database is synchronized.<\/p>\n

Note that you cannot mirror a database that contains FILESTREAM data, and mirroring is not appropriate if you need multiple databases to failover simultaneously, or if you use cross-database transactions or distributed transactions.\u00a0 Database mirroring is considered a high availability solution, but it can also be used for disaster recovery, assuming the lag between the primary and mirror sites is not so great that the mirror database is too far behind the primary for RPO to be met.\u00a0 If you\u2019re running Enterprise Edition, snapshots can be used on the mirror server for point-in-time reporting, but there\u2019s a licensing cost that comes with reading off the mirror server (as opposed to if it\u2019s used only when a failover occurs).<\/p>\n

Availability Groups<\/span><\/strong><\/p>\n

Availability groups (AGs) were introduced in SQL Server 2012 and require Enterprise Edition.\u00a0 AGs are configured for one or more databases, and if a failover occurs, the databases in a group failover together.\u00a0 They allow three synchronous replicas (the primary and two secondaries), whereas database mirroring allowed only one synchronous secondary, and up to four asynchronous replicas.\u00a0 Failover in an Availability Group can be automatic or manual.\u00a0 Availability Groups do require a Windows Failover Clustering Server (WFCS), but do not require a SQL FCI.\u00a0 An AG can be hosted on SQL FCIs, or on standalone servers within the WFCS.<\/p>\n

Availability Groups allow read-only replicas that allow for lower latency streaming updates, so you can offload reporting to another server and have it be near real-time.\u00a0 Availability Groups offer some fantastic functionality, but just as with a SQL FCI, there are many moving parts and the DBA cannot work in a vacuum for this solution, it requires a group effort.\u00a0 Make friends with the server team, the storage team, the network folks, and the application team.<\/p>\n

Transactional Replication<\/span><\/strong><\/p>\n

Transactional Replication gets a shout out here, even though it is not always considered a high availability solution as Paul discusses in his post, In defense of transaction replication as an HA technology<\/a>.\u00a0 But it can work as a high availability solution provided you can accept its limitations.\u00a0 For example, there is no easy way to fail back to the primary site\u2026however, I would argue this is true for log shipping as well because log shipping requires you to backup and restore (easy but time consuming).\u00a0 In addition, with transactional replication you don\u2019t have a byte-for-byte copy of the publisher database, as you do with log shipping, database mirroring or availability groups.\u00a0 This may be a deal-breaker for some, but it may be quite acceptable for your database(s).<\/p>\n

Transactional Replication is available in all currently supported versions and in Standard and Enterprise Editions, and may also be a viable option for you for disaster recovery.\u00a0 It\u2019s important that you clearly understand what it can do, and what it cannot, before you decide to use it.\u00a0 Finally, replication in general isn\u2019t for the faint of heart.\u00a0 It has many moving parts and can be overwhelming for an Accidental DBA.\u00a0 Joe has a great article<\/a> on SQL Server Pro that covers how to get started with transactional replication.<\/p>\n

Summary<\/span><\/strong><\/p>\n

As we\u2019ve seen, there are many options available that a DBA can use to create a highly available solution and\/or a system that can be recovered in the event of a disaster.\u00a0 It all starts with understanding how much data you can lose (RPO) and how long the system can be unavailable (RTO), and you work from there.\u00a0 Remember that the business needs to provide RPO and RTO to you, and then you create the solution based on that information.\u00a0 When you present the solution back to the business, or to management, make sure it is a solution that YOU can support.\u00a0 As an Accidental DBA, whatever technology you choose must be one with which you\u2019re comfortable, because when a problem occurs, you<\/em> will be the one to respond and that\u2019s not a responsibility to ignore.\u00a0 For more information on HA and DR solutions I recommend the following:<\/p>\n