Authors: Ross Mistry and Stacia Misner
Publisher: Microsoft Press
Pages: 144
ISBN: 978-0735684751
Audience: DBAs, Architects, Developers
Rating: 2.0 or 4.0 (see review for reason)
Reviewer: Ian Stirk

Microsoft’s newly released SQL Server 2014 database is closely followed by its first book, how does it fare?

This is a small book, consisting of 125 pages (as a PDF), spread over six chapters. As explained in its introduction, it doesn’t cover everything, but rather provides a high-level overview of SQL Server 2014 functionality. It’s deemed suitable for DBAs, database developers, business intelligence users, power users and technical decision makers.

The book can be downloaded for free, in various formats, at: www.microsoftvirtualacademy.com/ebooks although it will also be available commercially in print format.

The first three chapters relate to database administration, and the last three relate to business intelligence development.

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1 SQL Server 2014 editions and engine enhancements

The chapter opens with an outline of availability enhancements, namely the AlwaysOn Availability Groups have had the maximum number of secondary replicas increased from 4 to 8. This provides a greater ability to offload reporting/backups/extraction from the primary, as well as providing improved High Availability (HA) and Disaster Recovery (DR) options. The new Azure Replica wizard allows you to store a secondary in Windows Azure (Microsoft’s public cloud), providing additional DR safety.

Backup and restore have been enhanced, and despite the attention given to HA secondaries, backups and restores are still the cornerstone of recovery. It is now possible to backup to a URL destination (i.e. to Windows Azure), additionally, there is now native encryption for backups.

We now enter the area that has in many ways defined the release, scalability and performance enhancements. The major enhancements being:

• Use of In-Memory OLTP [Online Transaction Processing] (For most people this will be the most important feature of SQL Server 2014. Tables can now be stored in memory, reducing I/O, this together with a new lock/latch free concurrency model should ensure significantly improved throughput. Case studies have shown improvements in the range of x30)

• More computing resources (Microsoft’s Cloud using Windows Server 2012 R2, supports 2,048 logical processors for a Hyper-V host. A virtual environment can support 64 virtual CPUs and 1TB of memory)

• Resource Governor enhancements (I/O has been added to the resources to limit/prioritise)

• Buffer pool extension (This allows integration of non-volatile RAM [e.g. SSDs] with the database engine buffer pool, which can significantly improve I/O, giving less latency, faster throughput, more caching and faster queries)

• Columnstore enhancements (These are now updateable)

• Scale networking and storage (Features of Windows Server 2012 R2 provide improved scale and storage functionality for SQL Server 2014)

Security enhancements include new permissions to: connect any database, impersonate any login, select all user securables, and alter any database event session. Also included in this section is a diagram to remind readers that Microsoft has the best track record for security amongst the major database vendors (i.e. SQL Server, Oracle, MySQL, and DB2).

There’s a relatively large section that discusses SQL Server as a platform for hybrid cloud usage. Various scenarios are considered including:

• Deploy a database to SQL Server in a Windows Azure Virtual Machine (has wizard)

• Deploy a database to Windows Azure SQL database (has wizard, also reversible)

• Cloud backup of on-premise SQL Server, can also restore from cloud (good for DR)

• AlwaysOn and Windows Azure together can provide a stronger DR solution. If you have no secondary (or it’s close to primary), you can use Windows Azure as a secondary

• Extend on-premises applications. Windows Azure provides scale-out functionality to support irregular demand, so you don’t need to overprovision on-premise SQL Server

• Enhancing backups with Windows Azure blob storage

The main features in the various editions of SQL Server 2014 are outlined, namely:

• Enterprise (Premium edition, for the most demanding and mission-critical environments, it contains all the functionality)

• Business Intelligence (has all the BI functionality, can use up to 16 cores for database engine usage, maximum number of cores for BI processing, alerting, Power View, Power Pivot, In-memory tabular BI etc)

• Standard (for small/medium class organization, includes AlwaysOn 2-node failover, and limited to 4 CPUs and 64GB RAM)

• Developer (same specification as enterprise edition, but for non-production usage)

• Web (no database size restrictions, limited to 4 CPUs and 64GB RAM)

• Express (free version, limited to 1 CPU, 1GB RAM, maximum database size 10GB)

There’s a small section on licensing, emphasising this has been simplified, and optimised for virtualisation and cloud deployment. This is followed by a list of hardware and software requirements, which depend on the components installed – there’s a useful table of typical requirements together with links for other scenarios.

Lastly, installation, upgrade and migration strategies are briefly examined. For both in-place and side-by-side migration, there’s a helpful list of pros and cons, migration steps, and use of the upgrade advisor.

Overall, this is a very useful chapter, outlining the major new features in SQL Server 2014. There is a degree of marketing included, and the potential use of cloud computing infiltrates many sections – that said, there are some good arguments included as to why you might want to use the cloud in your solutions.