June 12, 2019 at 1:55 pm
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.
June 13, 2019 at 1:39 pm
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
Change is inevitable... Change for the better is not.
June 17, 2019 at 12:43 pm
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
June 18, 2019 at 4:06 pm
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.
June 19, 2019 at 6:40 pm
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.
June 20, 2019 at 2:44 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply