Due to the exponential growth of data in the datawarehousing environment, the customers are facing the constant challenges in the scalability, efficiency and high availability for their Data in their respective environment. Also, due to this high increase in the growth of data the warehousing infrastructure, which was originally built for OLTP purposes, is becoming increasingly complex, ineffective from the data warehousing infrastructure design perspective. Finally, with this data growth comes the problem of fast and effective methodologies of data mining which dictates/contributes to the critical business decisions. As a matter of fact all the above factors create tough challenges and nightmares for the senior executives in any organization who is using data warehousing environment.
Here comes a solution which will address the above business challenges and pain points and also will help the customers to be able to design and implement balanced configurations especially for Microsoft SQL Server data warehouse environments with the goal of providing hardware-balanced approach and predictable out-of-box performance.
This solution is designed to enable organizations to quickly and reliably deploy a total system solution stack, including server, storage and connectivity, for data warehouses based on Microsoft SQL Server 2014. It also helps dramatically to reduce the burden on end users in selecting, sizing, and testing the combination of products and technologies while minimizing the risk of failures. Lastly, this solution is a balanced and optimized hardware and software design on SQL Server data warehouse deployments for common data warehouse, decision support and business analytics deployments that can:
- Eliminate the complexity of data warehouse design.
- Reduce total cost of ownership (TCO)
- Offer better performance to users
Now let us discuss the reference architecture of this solution.
Data Warehouse Fast Track for Microsoft SQL Server 2014 has the following components (as depicted in the below diagram):-
- XtremIO Storage System
- Cisco UCS C-Series Rack Server
- Microsoft SQL Server 2014
The above configuration has been designed keeping in mind about the optimization of the warehouse workload and has been tested by EMC and Microsoft for up to 45 TB user data capacity. This proves the fact that this architecture has been tested under extreme conditions to derive the superior performance. The purpose of this architecture is to build and demonstrate the functionality, performance, and scalability of the Data Warehouse Fast Track for Microsoft SQL Server 2014 solution as enabled by XtremIO and Cisco Unified Computing System (UCS) servers.Below are the exhaustive lists of the hardware and software components that were integrated together to build the solution.
This solution provides the following benefits for the customers:-
- Predetermined allocation of resources (like CPU and storage) so that we can limit the excess consumption of CPU and storage.
- Predictable performance and it matches SQL Server application capacity for a target workload.
- Aligns itself with different workloads. In short, it maintains constant latency no matter what the loads for the applications are.
- Provides superior data compression, reducing storage and I/O bandwidth requirement as only columns referenced in the query need to be brought into memory. (Due to SQL Server clustered columnstore index)
- Reduces the need for secondary indexes, simplifies the overhead on incremental data load, and enables customers to load data in the maintenance window. (Due to SQL Server clustered columnstore index)
- Executes ad hoc queries , complex analytic queries efficiently because it uses predicate pushdown and partition elimination to reduce the data processing of large data sets. (Due to SQL Server clustered columnstore index)
- Helps to reduce the burden of customers in selecting, sizing, and testing the combination of product and technologies while minimizing the risk of failures.
For more detailed description on the technicalities of solution , please consult the
below references:- The following documents, located on emc.com , provide more information:
The following document, located on msdn.microsoft.com, provides more information about the Microsoft SQL Server Data Warehouse Fast Track solution:
- Data Warehouse Fast Track Reference Guide for SQL Server 2014