SQL server performance

Posted on 2013-10-18
Medium Priority
Last Modified: 2016-02-11

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?

Question by:Sonali P
LVL 29

Accepted Solution

Bernard S. earned 1500 total points
ID: 39596545
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?

Author Comment

by:Sonali P
ID: 39598838
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 .


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question