Availability Groups vs. Transactional Replication

  • robin.pryor

    SSCrazy

    Points: 2508

    I'm getting ready to rebuild our entire SQL farm, upgrading everything to Win 2016 and SQL 2017. A huge motivation for getting this approval is that I'll be separating the reporting layer from the rest. I'm slightly experienced with implementing Availability Groups once the creation of the Windows server is already done, but I don't know much about what goes on prior to that hand-off from the system/storage admin.

    We do 3 lines of business that have to be completely separate from each other from a security standpoint. I cannot emphasize this enough. Client 1 isn't as concerned, but Clients 2 & 3 are adamant about their data not touching each other in any way. So, I'm going to have to sell them on my idea.... I want to build a server with some serious specs and then install 3 separate instances of SQL on the same server. Each line of business will then have their own SQL server, each with its own security model.

    Anyway, my question for you all is can this really be done using Availability Groups and is that even the best performance for my scenario?

    Here is a crude representation:

    Server1

    Node A

    db1

    db2

    Node B

    db1

    db2

    Server2

    Node A

    db3

    db4

    Node B

    db3

    db4

    Server3

    Node A

    db5

    db6

    Node B

    db5

    db6

    RPTServer - STANDALONE - NO NODES, BUT MULTIPLE INSTANCES

    Instance1

    read-only db1

    ReportServer

    ReportServerTempDB

    Instance2

    read-only db3

    ReportServer

    ReportServerTempDB

    Instance3

    read-only db5

    ReportServer

    ReportServerTempDB

    Am I thinking along good lines, or am I missing something. Is AG possible for that reporting server or will that have to be old-school transactional replication? Would the reporting server have to be a 3rd node to each of those 1st 3 clusters? That doesn't even sound like something that's possible while I'm asking it.

    Please let me know your thoughts and/or advice. This is 100% in the planning stage right now, so I am still free to completely scrap this and redesign it if I'm too far off-base - including going with 3 separate reporting servers if necessary. I'd love to avoid that just in the name of cutting down the overall number of servers I have to admin. Whatever design I go with, I have to implement it twice - once for production, once for test.

    • This topic was modified 1 month, 1 week ago by  robin.pryor.
  • Jeff Moden

    SSC Guru

    Points: 993905

    This post was previously mistaken for SPAM and removed.  It may not have even appeared on the "Active Threads" list.  With that in mind, I'm bumping this post for the OP so that it does.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • andreas.kreuzberg

    SSCertifiable

    Points: 5981

    Hi,

    it depends, always on synchronized the complete database, and with a transactional replication, you are able to replicate the objects that you need to create your report.

    If you use always on, you can configure read only routing for reports, and your primary node is less used, which meas a better performace.

    But, it depends on your setup.

    Kind regards,

    Andreas

  • Rohan Joackhim

    SSC Veteran

    Points: 291

    After looking at your setup, I would make Instance 2 and 3 reporting databases as Subscriber db's for transactional replication. You have more flexibility with what to replicate or not. I.e. If instance 2 reporting databases don't  need all the tables, procedures and triggers for the reports,  you can only replicate the required objects. Same story with the instance 3 and this will have some performance gains I believe  compared to Always on read only replicas. Also you can pick and choose which databases will be your publishing databases.The downside  will be, you  need a standalone Distributor server; if this will be an OLTP setup to remove any resource hogs.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714673

    I'm torn. I like AGs because I think they're more robust and reliable than replication. Repl works great until it doesn't, and then it's a pain. However, this is reporting, so rebuilding repl is probably not the end of the world.

    With any of this multi-instance stuff, be careful of load, especially memory. This can be hard to balance and the noisy neighbor issue is tough. The one good thing about repl is that you can tune indexes (and schema) on the reporting dbs to better serve reports, without impacting the source db.

  • BrownCoat42

    SSC Journeyman

    Points: 85

    Personally I would strongly recommend against transactional replication. It works 95% of the time, but the 5% of the time that it is broken, it is just an unbelievable hassle to deal with. In the environment I work in now, virtually every problem we have has been created as a bi-product of using transactional replications, some of the problems technical in nature, while others are a product of workarounds to deal with some of the constraints created by replication, primarily by developers and report writers.

    Depending on your organization, you may also have problems keeping the environment as under control as you would like to with three distinct groups with different interests in the SQL environment being able to call some of the shots, according to their interests only. When it comes to replications, being able to  saying "no" to certain requests is of paramount importance, but once you add one, I have experience it has usually been like a drug to report writers who want more and more and they just grow like cancer.

    Does the reporting data really all need to be done in real time?

    If it doesn't need to be live I would use stored procedures or SSIS to copy data from the source to a reporting database, then run that on a schedule.

    I really despise named instances, so I would recommend against stacking them from that point, but would also be pretty leery about stacking three application facing instances on one server. The performance expectation of end users in an application is for fast reads and writes and when you stack up more than one database instance SQL has very little ability to intelligently control how the resources are shared. You can set a limit on memory, but its pretty difficult to limit disk i/o and CPU usage.

    If you wanted to run three instances on one box, I'd virtualize three machines on it instead and then equally divide up the CPUs and memory between each guest, that way SQL isn't in charge of dividing resources, the hypervisor is. This adds a lot more complexity however.

    Using an AAG can be a really good option for reporting if all your report writers are very good, and the data isn't that complex, but at least where I have been, that hasn't always been the case. The amount of babysitting needed could run away beyond your reach. Keeping a separate copy of reporting data to me is more about keeping the mess out of the live data. User expectation of reporting is also much lower than in the application. A user expects a report to take time to render, so a badly written report can get isolated away from the live data and then also not get complained about. I dont mind stacking SSRS, SSIS and the database instance on the reporting box either.

     

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply