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:
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:
LVL 27
SteveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kevinhsiehCommented:
Please define what you mean by large databases, and reasonable spec hardware.

I would use only flash storage. Possibly NVMe based for the ultimate low latency storage. If you license SQL Server Enterprise, you have all the best options for running SQL across multiple servers for HA, even across data centers. I think that you can also run as many instances of SQL as you would like. Not sure if you can run in as many VMs as you would like on a given host. I would run in VMs.
0
kevinhsiehCommented:
Your current jobs, are they IO bound? CPU bound? What type of storage do you currently have? How much RAM?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Multiple large (enterprise level) databases across multiple SQL instances on the same server.
How large?


some processing/queries can take several days to complete (even on reasonable spec hardware)
OLTP or OLAP processes/queries?


a) How would you protect the majority of databases/instances and maintain performance when a single instance/database is under particularly HEAVY load?
By limiting the resources for each instance.


b) how would you recommend planning high-availability, while keeping 'a' in mind, above when load is significant?
Windows clustering and SQL Server AlwaysOn.


•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?
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.


•What throttling is available? does it require manual intervention? Does it work in active/active clusters?
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.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

SteveAuthor Commented:
Thanks for the contributions guys. Great to discuss it with you both.

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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should try to get the information about OLTP and OLAP processes and it takes days to be processed.


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)?
I'm thinking in limiting by instance, since you have more than one instance in each server, so you can limit for each one how many processors and how much RAM they can handle.
If you want to limit by database you need to have a database per instance.


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?
With AlwaysOn you can only write on the Principal and read on all Secondary Replicas. Listeners are specific for a Replica, so you just need to use the listener that fits you better depending on the work task.


are you referring to resource governor? does that still work well when you have multiple instances?
Yes and Yes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kevinhsiehCommented:
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.
0
SteveAuthor Commented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, Resource Governor is somehow blind on the way it works, since you set percentage of CPU and Memory from the server but if you play it with the configuration of the resources for the instance, like limiting the number of processors and memory, then you can do it. Even is somehow complex.

Limiting the resources by instance is static. You set the number of processors the instance can use and the minimum and maximum memory that an instance can use.
0
SteveAuthor Commented:
Thanks. That's pretty much what I was expecting.

That's great info guys. Always worth bouncing ideas around to check understanding.
0
SteveAuthor Commented:
Great info from Vitor. Appreciate Kevin's contribution too but he didn't really deal with any of my main queries.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.