{"id":650,"date":"2010-11-03T10:28:00","date_gmt":"2010-11-03T10:28:00","guid":{"rendered":"\/blogs\/paul\/post\/In-defense-of-transactional-replication-as-an-HA-technology.aspx"},"modified":"2017-04-13T09:51:26","modified_gmt":"2017-04-13T16:51:26","slug":"in-defense-of-transactional-replication-as-an-ha-technology","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/in-defense-of-transactional-replication-as-an-ha-technology\/","title":{"rendered":"In defense of transactional replication as an HA technology"},"content":{"rendered":"

\nYesterday on Twitter a few people expressed disgust at a conference slide listing transactional replication as a high-availability (HA) technology. I took exception to that and argued. I think the discussion merits a blog post so here it is. This point of view is controversial and I'm expecting some dissenting comments – bring them on! <\/font>\n<\/p>\n

\nI teach high-availability inside Microsoft and at the MCM level and I absolutely always teach that transactional replication, and it's more advanced brother – peer-to-peer replication, have their place as a high-availability technology. The two whitepapers on high availability I've written for Microsoft over the last two years also discuss transactional replication as a high-availability technology (links later). <\/font>\n<\/p>\n

\nTo start with, let's define a high-availability technology as one which makes data more highly available in the event of a disaster. That definition does not imply any limits on the amount of data loss or downtime experienced – and to do so would be incorrect. <\/font>\n<\/p>\n

\nAs some background, many people approach HA planning the wrong way – either by trying to shoe-horn a new strategy onto an incumbent and unsuitable technology or just picking the technology they've heard of – which is most commonly failover clustering. Planning an HA strategy means carefully laying out the business requirements, considering technical and non-technical limitations, reaching a compromise between the technologists and the business managers, and THEN starting to evaluate technologies. Once you start evaluating technologies you'll find that no single HA technology that SQL Server provides is a one-size-fits-all solution – they all have pros and cons. The resulting strategy will likely utilize multiple technologies to meet the strategy's needs. <\/font>\n<\/p>\n

\nYou can read in more depth about my HA planning methodology in the whitepaper I wrote for Microsoft last year: <\/font>High Availability with SQL Server 2008<\/font><\/a>. This also has a good overview of transactional and peer-to-peer replication. <\/font>\n<\/p>\n

\nThere are seven main arguments I hear against transactional replication as an HA technology – I'll take them on one at a time. <\/font>\n<\/p>\n

\nArgument 1: It's a scale-out technology, not an HA technology<\/strong> <\/font><\/font>\n<\/p>\n

\nSo where's the actual argument here? Sure, transactional replication and it's more advanced brother, peer-to-peer replication, are primarily query scale-out technologies. But both provide extra copies of some data, so both can be used to provide higher availability of that data in the event of a disaster. <\/font>\n<\/p>\n

\nArgument 2: There's too much latency involved – how can that be HA?<\/strong> <\/font><\/font>\n<\/p>\n

\nHigh availability does not mean zero data loss and zero downtime. High availability means the data is more available in the event of a disaster than if you had no redundant copy. Sure, there is some latency involved with transaction replication but there's also latency involved with log shipping, and with asynchronous database mirroring.<\/font>\n<\/p>\n

\nThe key is making sure that the availability provided by the technology fits within your desired downtime and data loss SLAs (service level agreements) – commonly known as RTO (recovery time objective) and RPO (recovery point objective), respectively. If you're fine with potentially losing 1\/2 hour of data, and the transactional replication latency between the Publisher and Subscriber is only 10 minutes, then transactional replication can meet your data loss requirements. <\/font>\n<\/p>\n

\nArgument 3: It's really hard to know what you're missing if the Publisher crashes<\/strong> <\/font><\/font>\n<\/p>\n

\nCorrect. But it's just the same as the other asynchronous technologies – log shipping and asynchronous database mirroring – so this is not a valid argument against transactional replication. <\/font>\n<\/p>\n

\nArgument 4: Replication is too complicated to set up <\/font><\/strong>\n<\/p>\n

\nThis argument is asinine IMHO. A technology may be complicated to use or troubleshoot, but that does not make it unsuitable – it just means you have to have someone who knows what they're doing. I've seen people mess up configuring log shipping – does that mean it's not an HA technology? No. Failover clustering can be fiendishly difficult to set up at the Windows level – does that mean it's not an HA technology? No. This argument is also invalid. <\/font>\n<\/p>\n

\nArgument 5: It only replicates some of the data<\/strong> <\/font><\/font>\n<\/p>\n

\nCorrect. This means it's unsuitable if you'd like to protect an entire database or several databases – but if its restrictions work for you, no problem. <\/font>\n<\/p>\n

\nArgument 6: You can't make certain schema changes on a published table<\/strong> <\/font><\/font>\n<\/p>\n

\nCorrect. But that doesn't mean it's not suitable for providing HA – as long as you can live with its restrictions. <\/font>\n<\/p>\n

\nArgument 7: There's no automatic failure detection or automatic failover<\/strong> <\/font><\/font>\n<\/p>\n

\nCorrect. But neither does log shipping. This just means you have to provide the mechanisms for this. Transactional replication still means your data is available if the Publisher crashes. <\/font>\n<\/p>\n

\n[Edit: Argument 8: There's no easy way to fail back again<\/font><\/strong>\n<\/p>\n

\nCorrect – for transactional replication – that can be a PITA. With peer-to-peer replication that problem disappears because the changes that happened while one peer node was down are automatically pushed to it when it comes back online.]<\/font>\n<\/p>\n

\nArguments FOR using transactional replication<\/strong> <\/font><\/font>\n<\/p>\n

\nSo these arguments are really saying that transactional replication has some restrictions. Sure it does. As I said above, no single HA technology is a one-size-fits-all. Now, what about all the good things about transactional replication? <\/font>\n<\/p>\n