Unlike Hadoop and NoSql Databases, MPP is not a new technology. Yet, it is a strong contender in the “Big Data” space. Sql Server PDW appliance boasts up to 100x performance gains over legacy data warehouses. Moreover, it is a fault tolerant, horizontally scalable, high capacity RDBMS. Simply put, it is an excellent solution for companies that are wholly vested in RDBMS but need to break free of its constraining factors. We will narrow down our comparison by juxtaposing Sql Server SMP with Sql Server MPP.
Scalability: Throwing more hardware at Sql Server SMP will eventually hit a point of diminishing return as the size of the data sets grow. By contrast, Sql Server MPP architecture is horizontally scalable and performance grows linearly as we add more nodes (Physical Servers) to the appliance– Up to 100x performance gains over legacy data warehouses.
CPU Utilization: A Database task in Sql Server SMP is bound to only one Cpu whereas a task runs on multiple Cpu’s in Sql Server MPP
Resource Sharing: Sql Server MPP has a shared nothing architecture which allows each node to dedicate its resources to processing queries thereby avoiding resource contention and I/O bottlenecks that are caused by resource sharing.
Distributed Queries: Query execution time is reduced significantly. Each query is broken down into pieces and fed to different nodes enabling parallel processing.
Data Distribution: Sql Server MPP automatically distributes the data among different nodes. Each node processes its own data set before sending the output to the control process which in turn merges the results.
Parallel Load: Data is automatically loaded in parallel.
In-Memory Operations and Columnar Data Store : Both Sql Server SMP and Sql Server MPP support In-Memory Operations and Columnar Data Stores.
Sql Server SMP can handle a few Terabytes whereas Sql Server MPP can linearly scale-out to 6 Petabytes.
Sql Server MPP is fault tolerant. Redundancy is applied to all hardware and software components of the appliance. Moreover, the appliance runs on Microsoft Hyper-V which gives the nodes failover capabilities.
Analytics Capabilities (Variety and Variability)
PDW is part of Microsoft Analytics Platform System, which supports connectivity and query access to Hadoop and unstructured data via PolyBase data querying technology.
Sql Server SMP Databases are simpler and less costly to maintain.
Sql Server MPP: Upgrades are not seamless and may require down time. Patches are applied by Microsoft.