Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

What Happens If...

Now that you've seen how to set up and monitor replication you might be asking, "This looks cool, but what if one of my servers becomes unavailable? What happens then?" Well, fortunately replication is quite capable of resolving most types of issues on its own. Transactional replication involves three distinct server roles: publisher, distributor, and subscriber. If a publisher becomes unavailable (read unreachable perhaps due to network outage), neither distributor nor database users will be able to connect to the publisher. Thus, there is no transactional activity going on in the published database, and therefore, there is nothing to replicate—no problem. When the publisher becomes available again and users begin to add, modify, or remove rows, replication will start working again, too. The Log Reader agent will pick up where it left off.

If a distributor server is unavailable, the Log Reader agent can't connect to the publisher, and the Distribution agent can't connect to the subscriber(s). Replication marks the last replicated transaction on the publisher as well as the last transaction that was delivered to subscriber(s). When the distribution server comes back online, the agents will pick up where they left off. If the distributor server stays offline more than a few minutes, you have to restart the replication agents (the Distribution and Log Reader agents)—you have to restart corresponding jobs. In most cases, that's all you have to do. Similarly, if the subscribing server(s) happen to be unreachable the Distribution agent might have to be restarted. Unless you have a long network outage, the replication can pick up where it left off, and all will be well.

Keep in mind, however, that problems do occur from time to time. For example, a faulty disk drive might cause one of the databases (whether the published, distribution, or subscriber database) to become corrupt, and you'll have to restore it from a backup. Such problems aren't specific to replication. If a published or distribution database is restored from the backup, you'll have to re-create the publication and subscription databases. If a subscription database is restored from a backup, you might have to populate it with a snapshot from the publisher and then re-create the subscription database.

Planning for disaster recovery for replicated systems is slightly different from non-replicated counterparts because it involves regular backups of the distribution database. In addition, you might want to back up the publication, subscription, and distribution databases on the same schedule. You should always have replication scripts saved in an easily accessible location in case you have to rebuild the replicated site.

  • + Share This
  • 🔖 Save To Your Account