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?
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…