0

We have a large (3TB) database that is a part of AOG. We now have a need for a third copy of the data. Here are some of things we need:

  • The third copy needs to be in sync with the primary in real time (or near real time); worst case we can live with a 24 hour lag.
  • The third copy cannot be PRIMARY NODE any time. (This is just a reporting server)

What are my options?

I don’t want to configure replication on top of AOG. That seems like a complex option. I can add a third node for the AOG. This could add some dependency on the primary; I’d prefer the new architecture to minimally interfere with the other 2 nodes.

4
  • Have you considered read-only routing, and then routing read-only reporting queries to the secondaries? In addition, is this DR or reporting? Those are two different concepts. Sep 13 '18 at 12:44
  • Yes. We are using 'Read Intent' connection string properties to redirect reporting queries to the secondary. This works well. Our secondary is quite busy with heavy read load. There are a couple of OLTP apps which need sub-second response from secondary. The third node (which we need) is like a OLAP/analytical load. we don't want it to interfere with secondary. That might cause lot of contention.
    – RaviLobo
    Sep 13 '18 at 12:48
  • This is beginning to feel more like a data warehouse than a replica. A reporting replica using log shipping as Brent mentioned below would work fine, you'd need to ensure your database is available for restores. In this situation maybe you only restore your logs once every 24h? Are you wanting 24/7 uptime for this server? Sep 13 '18 at 13:25
  • @Geoorge You are right. This is a DW. we can live with 24 hour restores. We probably don't need it 24/7. I think we can have a downtime during weekends.
    – RaviLobo
    Sep 13 '18 at 13:34
3

If you don't need to query the server, the classic answer here is transaction log shipping: simply restoring your backups on a different SQL Server.

It's a great fit here because:

  • It's independent of your Always On Availability Group: even if the AG fails or the cluster fails, you can go live with this DR server quickly
  • It can't become the primary node until you manually run RESTORE WITH RECOVERY and point your clients over to it (can be tuned by using a DNS CNAME that normally points to your primary, but during disaster, you change it to point to the log shipped secondary)
  • It can stay reasonably in sync - you said within 24 hours, and that's fairly easy to do with log shipping

That meets all of the requirements in your question.

Just be aware that there is a licensing gotcha: if you have Software Assurance, then for every one primary you buy, you get one free secondary as long as it's not queried. If you're using multiple secondaries (like one in your primary data center, plus adding this new one,) you may need to purchase additional licensing.

If you DO need to query the server while it continues to update the data underneath you, your only two answers are Always On Availability Groups or transactional replication. All other answers (SAN replication, log shipping, VM replication, etc) will require queries to stop each time the data is updated.

7
  • Thank you Brent! It is super cool to see you here. Are you suggesting a custom log shipping solution (since it is a deprecated feature)? The server in question is SQL 2016.
    – RaviLobo
    Sep 13 '18 at 13:01
  • why You decide LS is deprecated? (even in 2017 it not marked as deprecated)
    – a_vlad
    Sep 13 '18 at 13:10
  • @RaviLobo you're welcome. No, log shipping isn't deprecated - you might be thinking of database mirroring.
    – Brent Ozar
    Sep 13 '18 at 13:16
  • Thank you a_vlad & Brent. Somehow it's in mind that LS is deprecated. Since it is not, I'll spend some time on researching it.
    – RaviLobo
    Sep 13 '18 at 13:19
  • It looks like the log shipping target server cannot be accessed while the log is getting restored. Since this is going to be an analytical server, where I expect the queries to run for hours, how can I ensure long queries and restore process doesn't overlap?
    – RaviLobo
    Sep 13 '18 at 13:23
0

Log shipping: if you won't access the 3rd database for reporting.

Replication, transactional mode: if you need to access it.

And since you say 24hr lag is acceptable, you can use daily differencial restore (dedicated network could help since this is a big DB). Just have a maintenance timeframe for the restore as the database won't be available.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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