March 15, 2021 at 8:52 pm
Back in 2015 (and in SQL 2012) we hired some consultants to help us create a data warehouse and a separate transactional database. The consultants set up the SQL server and did the configuration for us. They suggested 2 different servers for the different databases because of the nature of the work done by each. Since then, the consulting company has changed hands (it's been 6 years) and none of the people on the original project are there anymore. One difference I found was that the reporting database had a simple recovery model and the transactional database had a full recovery model.
With SQL 2019, is there any reason to separate a database used primary for reporting from a database used primarily transactionally? If so, what would differ in the setup? We're on-prem and can put both databases on a single instance of SQL if that won't reduce our throughput or response time.
Thoughts?
March 15, 2021 at 9:37 pm
TLDR: Analyze server specs, compare to actual workload, & test consolidation.
It's not really about the version of SQL Server, but about the size/rate of transactions (inserts, updates, deletes), the size/rate of selects (particularly for reporting), sufficient memory (memory is orders of magnitude faster than even SSDs), I/O speed (how fast can one read & write data to disk), proper indexing that optimizes select queries without significantly slowing transactions, well-written queries/stored procedures that allow SQL Server to select execution plans that best use indexes, sufficient # and speed of CPU cores, and finally, to a much lesser extent, the volume of data.
SQL 2016 SP2 + through SQL 2019 add some features (including features that used to be enterprise-only but are now available in standard edition) that might offer performance improvements in some scenarios (with implementation changes), but don't count on that.
Does your transactional server have the CPU, memory, and I/O/throughput to support reporting queries on top of transactions without blocking transactions? And can it return reporting results in acceptable times without timing out or blocking reporting queries?
You can get some idea by looking at the specs of the existing servers and seeing how much CPU, I/O, memory, & user connections are consumed on average and at maximum, and therefore how much are available if you wanted to combine. For maximum, it's important to determine of the maximums overlap, or occur at different times of the day where they might be compatible. Then, given those numbers, unless there is a lot of spare "horsepower", it would be prudent to set up a test server with the proposed specs/hardware for the combined server where you can test combined workloads (e.g., by replacing trace files, and maybe also doing simulated load testing with maximum # of users), and see if expectations are supported by tests.
March 15, 2021 at 10:00 pm
The basic reason for separating reporting onto another server is to move that processing off the production system. If someone creates a query that needs to load a lot of data into memory - that will force data needed for the other database out of the buffer cache.
Someone could also write a query that needs all of the CPUs available - causing performance issues for the application.
Much better to isolate that to a separate system. If someone writes a bad query on the reporting system - at least your application users will not be affected and your business not impacted.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 15, 2021 at 10:06 pm
Thanks ratbak,
Sounds like it's more the normal things to worry about in terms of sizing and less any specific settings in SQL. Our primary CPU & memory use are definitely time offset for these two machines. So, it would be just adding the max daytime use together, in case they happened at the same time. Because they would still be in different databases, but on the same virtual server and the same SQL instance, transaction blocking won't be a problem, it's only a total CPU and total memory limit question.
We'll definitely be testing this with development servers before deploying in production.
Thanks
March 15, 2021 at 10:11 pm
@Jeffrey,
Good points. In our case, the reporting system queries are severely constrained, so we have less opportunity for runaway processes. That said, it's important to take that possibility into consideration, because situations can change. Thanks for bringing it up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply