2

I am using SQL Server 2016 Standard Edition.

I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02. Users should be able to read from database in both server at any time and transactional replication allows that.

I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).

I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.

Current failover strategy for a patch/maintenance in SQL01:

  • Transactional Replication on from SQL01 to SQL02.
  • So point the application to SQL02. Now new data comes into SQL02.
  • To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.
  • Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:

  • My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.
  • Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?

Please add your suggestion(s).

Improve this question
3
  • @LowlyDBA : Basic Availability Groups won't allow read in the secondary server.
    – user9516827
    Commented Aug 13, 2019 at 19:46
  • @LowlyDBA:Added that to question.Thanks for pointing out.
    – user9516827
    Commented Aug 13, 2019 at 19:54
  • I would suggest adding another server to the mix and use either FCI or Basic Availability Groups for your actual HA, while keeping the transnational replication for reporting. With FCI/BAG the cold (emphasis on cold) spare is free. But anything you read from (such as your transnational replication recipient has to be licensed.
    – Jonathan Fite
    Commented Aug 14, 2019 at 2:43

2 Answers 2

Reset to default
4

Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher.

Or, if you have a stomach for obscure replication solutions, you could implement Bidirectional Transactional Replication, which is similar to Peer-to-Peer replciation, but older and available on Standard Edition.

Improve this answer
3

Is there a reason, other than tradition, that you need SQL01 to return to being the "primary"? You could do just a single flip, and skip the confusing mess of short-term log shipping:

  1. Original state: Primary database on SQL01, set up to replicate all tables to SQL02.
  2. When you do maintenance on SQL02, replication will queue up changes in the distribution database while SQL02 is down, and catch up when it is back online.
  3. To do maintenance on SQL01, do the following:
    • Pause/stop the application
    • Break replication
    • Redirect application to SQL02
    • Do maintenance on SQL01
    • When SQL01 is back up, re-establish replication from SQL02 to SQL01, since SQL02 is now the "primary"

Your final state is now: Primary database on SQL02, set up to replicate all tables to SQL01

Now for a 2TB database, re-establishing replication (either direction) won't be fast or trivial; I'd recommend starting with a backup instead of trying to push a full snapshot.

Improve this answer
1
  • For some reason my client wants SQL01 as primary and SQL02 and secondary.Currently i am using backup instead of snapshot in the replication process.The reason i was looking at log shipping is that ,i can setup/configure log shipping earlier itself.With replication,i should configure replication after redirecting the application and also i may have to manually synch some tables once replication is set up.
    – user9516827
    Commented Aug 13, 2019 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.