SQL - heavy load & resilience options - Quick Opinions
Hi Everyone.
I'd like to ask for some quick opinions on a potential SQL installation.
Requirements:
Microsoft OS (preferably 2012 R2)
MS SQL (2012 or 2014 preferred)
Background:
Multiple large (enterprise level) databases across multiple SQL instances on the same server.
processing occurs 24/7, with data flowing both in and out of SQL.
some processing/queries can take several days to complete (even on reasonable spec hardware)
multiple sites are available with high capacity links between them
budget is significant so please consider all options within reason
Questions:
a) How would you protect the majority of databases/instances and maintain performance when a single instance/database is under particularly HEAVY load?
b) how would you recommend planning high-availability, while keeping 'a' in mind, above when load is significant?
Thoughts:
Discuss:
I'd like to ask for some quick opinions on a potential SQL installation.
Requirements:
Microsoft OS (preferably 2012 R2)
MS SQL (2012 or 2014 preferred)
Background:
Multiple large (enterprise level) databases across multiple SQL instances on the same server.
processing occurs 24/7, with data flowing both in and out of SQL.
some processing/queries can take several days to complete (even on reasonable spec hardware)
multiple sites are available with high capacity links between them
budget is significant so please consider all options within reason
Questions:
a) How would you protect the majority of databases/instances and maintain performance when a single instance/database is under particularly HEAVY load?
b) how would you recommend planning high-availability, while keeping 'a' in mind, above when load is significant?
Thoughts:
In HA environments, can one server handle the heavy load while other server(s) handle the normal load? how would you achieve this? is it automatic or manual?
What throttling is available? does it require manual intervention? Does it work in active/active clusters?
A single instance/database per server wouldn't be practical
Discuss:
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
kevinhsieh
Your current jobs, are they IO bound? CPU bound? What type of storage do you currently have? How much RAM?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the contributions guys. Great to discuss it with you both.
Hi kevinhsieh,
and for hardware let's say a good, 64Gb mem, 6core Xeon, SAS 15K HDDs (multiple logical disks)physical server.
Hi Vitor Montalvão,
Hi kevinhsieh,
Please define what you mean by large databases, and reasonable spec hardware.lets say around 30 DBs of varying size from 1GB - 500GB spread randomly across 3 SQL servers
and for hardware let's say a good, 64Gb mem, 6core Xeon, SAS 15K HDDs (multiple logical disks)physical server.
Hi Vitor Montalvão,
OLTP or OLAP processes/queries?I don't have that information yet I'm afraid.
By limiting the resources for each instance.is there a way to do it by database or is instance the only option? Are the limits dynamic (ie only if fighting with another instance) or static (limited even if no other instances are demanding resources)?
Yes. With AlwaysOn Availabilty Listeners. Manual: You create a listener for read/write connection pointing to the Primary Replica and a read only listener to the Secondary Replica for Reporting.so it's read/write or read. you're not aware of any way of forcing a known heavy load to a specific member of the cluster?
What are your understanding of "throttling"? It's sharing and limiting the resources? If so, then the answer is yes. You can configure the usage of the available resources. The configuration is manual but then the SQL Server engine will take care of it automatically.are you referring to resource governor? does that still work well when you have multiple instances?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You didn't mention how many physical CPU or drives, but those specs are for a 7 year old server.
I recently bought a server from Dell with dual 10 core Xeon, 384 GB RAM, and 1.6 TB of NVMe SSD. Less than $20K USD with 5 years of support.
These days, only trivial, mostly idle databases should be considered for spinning disk. Going to SSD could cut SQL processing time by 90% or more if you are currently doing a lot of waiting on the disks. RAM is cheap up to about 384 GB per server, and will dramatically improve SQL performance.
I recently bought a server from Dell with dual 10 core Xeon, 384 GB RAM, and 1.6 TB of NVMe SSD. Less than $20K USD with 5 years of support.
These days, only trivial, mostly idle databases should be considered for spinning disk. Going to SSD could cut SQL processing time by 90% or more if you are currently doing a lot of waiting on the disks. RAM is cheap up to about 384 GB per server, and will dramatically improve SQL performance.
ASKER
That's guys. Good info.
Vitor,
Does using Governor across multiple instances work correctly, as I've heard that the governor processing doesn't apply cross-instance, so the processing for each instance applies as if it is the only instance on the server. What's your take on this?
Also, you mention 'limiting by instance' and I'd like to check if this is dynamic or not as I'm concerned this would limit an instance even when more resources are actually available.
Vitor,
Does using Governor across multiple instances work correctly, as I've heard that the governor processing doesn't apply cross-instance, so the processing for each instance applies as if it is the only instance on the server. What's your take on this?
Also, you mention 'limiting by instance' and I'd like to check if this is dynamic or not as I'm concerned this would limit an instance even when more resources are actually available.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks. That's pretty much what I was expecting.
That's great info guys. Always worth bouncing ideas around to check understanding.
That's great info guys. Always worth bouncing ideas around to check understanding.
ASKER
Great info from Vitor. Appreciate Kevin's contribution too but he didn't really deal with any of my main queries.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
