SQL server performance


Currently we have OLTP databases running on a physical server with SQL 2008 Version.
We are planning to migrate the databases to SQL 2012 and implement Always ON
Availaibility feature for fault tolerance .
We also have an SSIS server which is our ETL staging box and  an OLAP server for users to run complex queries  for reporting  , which are running on seperate vms . We are facing performance problems due to resource contention and increase in the volume of data.
Our management is planning to buy 2 high end physical server with similar hardware configuration and would like to move the Oltp,Olap and ETL staging(SSIS) on the same server on different instances.

My question is if it would be a good idea to have all the three environments on same server and implement always on feature for all the three instances as a fault tolerance solution..will it work?

SP_2018 .IT ConsultantAsked:
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.

Bernard S.CTOCommented:
As a precaution, I would run OLTP alone on its physical machine (or with a minimum GUARANTEED allocated pool of resources) so that however crazzy the query might be on OLAP, however big is the data volume to be trafserred by the ETL, OLTP runs untouched.

Depending on your business area, if OLTP slows or stops, then your physical business will have to stop, products cannot monve in and out of the warehouse, your web site does not take orders, etc. Do you really want to tame that risk, or do you prefer that the OLAP persons are delayed some seconds or even hours?

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
SP_2018 .IT ConsultantAuthor Commented:
Thanks for your reply.

Also please advise me about  implementing Always Availability feature
for multiple instances on a server, ie.  if i create 3 different instances
Instance 1. OLTP databases
Instance 2. Staging databases
Instance 3. OLAP database

In case of any of these  instances fail will all the instances on the server failover to the secondary server or would just the failed instance switch to the secondary server.

And could all these 3 instances exist on the same server if each instance is allocated
adequate pool of resources .

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 Development

From novice to tech pro — start learning today.