MS SQL Server 2014 “Hybrid” to attack MongoDB

Databases

On April 1st 2014 MS SQL Server 2014 is internally released for small group of big customers such as bwin and others. The biggest improvement is in-memory OLTP engine to deliver breakthrough performance to their mission critical applications. Company bwin scale its applications to 250K requests a second, a 16x increase from before, and provide an overall faster and smoother customer playing experience.

The new In-Memory OLTP engine (formerly code named Hekaton), provides significantly improved OLTP performance by moving selected tables and stored procedures into memory. Hekaton enables you to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures.

The In-memory OTLP engine works with standard x64 hardware. The new In-Memory OLTP engine uses a completely new optimistic locking design that’s optimized for in-memory data operations. In addition, stored procedures are compiled into native Win64 code. The end result is far faster application performance.

Microsoft recommends that you provide an amount of memory that’s two times the on-disk size of the memory-optimized tables and indexes.

Why similarity to MongoDB? Because we were using it also and if you have enough RAM memory it keeps all indexes in RAM and is very quick plus it uses the advantages of SSD disks. Where SQL Server shines comparing to Mongo is when you need to group large amount of data and analyze them. Until this version of MS SQL 2014 (we didn’t test it) MongoDB was superior when it comes to large number of insert request into database. For analyzing the large amount of data MS SQL was better. Now, with new SQL Server 2014 it comes closer to MongoDB main advantage (process data in memory and use SSD advantages – if specified).

in-memory-oltp

(Source: sqlmag.com)

The following data types aren’t supported by memory-optimized tables:

  • datetimeoffset
  • geography
  • hierarchyid
  • image
  • ntext
  • sql_variant
  • text
  • varchar(max)
  • User data types (UDTs)

In addition, there are a number of database features that aren’t supported. Here are some of the most important database and table limitations:

  • Database mirroring isn’t supported.
  • The AUTO_CLOSE database option isn’t supported.
  • Database snapshots aren’t supported.
  • DBCC CHECKDB and DBCC CHECKTABLE don’t work.
  • Computed columns aren’t supported.
  • Triggers aren’t supported.
  • FOREIGN KEY, CHECK, and UNIQUE constraints aren’t supported.
  • IDENTITY columns aren’t supported.
  • FILESTREAM storage isn’t supported.
  • ROWGUIDCOL isn’t supported.
  • Clustered indexes aren’t supported.
  • Memory-optimized tables support a maximum of eight indexes.
  • COLUMNSTORE indexes aren’t supported.
  • ALTER TABLE isn’t supported. In-Memory OLTP tables must be dropped and re-created.
  • Data compression isn’t supported.
  • Multiple Active Result Sets (MARS) aren’t supported.
  • Change Data Capture (CDC) isn’t supported.

Other SQL Server 2014 improvements

  • Using advantages of SSD disks technology (new buffer pool enhancements increase performance by extending SQL Server’s in-memory buffer pool to SSDs for faster paging)
  • AlwaysOn Availability Groups now support up to eight secondary replicas
  • Business Intelligence Enhancements (Power View can work against multidimensional cube data, new data visualization tool named Power Query, new visual data mapping feature named Power Maps)
  • SQL Server 2014 also supports encrypted backups
  • SQL Server 2014 will have the ability to scale up to 640 logical processors and 4 TB of memory in a physical environment. Plus, it has the ability to use up to 64 virtual processors and 1 TB of memory when running in a VM

More reading about this: