Chapter 1
SQL Server 2014 Architecture

WHAT'S IN THIS CHAPTER?

• Getting to know the new important features in SQL Server 2014
• Understanding how new features relate to data professionals based on their roles
• Understanding SQL Server architecture
• Getting to know the editions of SQL Server and how they affect the data professional

SQL Server 2012 offered a fresh look at how organizations and their developers, information workers, and executives use and integrate data. SQL Server 2014 continues that story and pushes forward into the future with the integration of cloud and in-memory technologies. Exciting new features and improvements provide breakthrough performance, availability, and manageability for mission-critical workloads on premises or in the cloud. This chapter provides enough information to give you an understanding of how SQL Server operates.

SQL SERVER 2014 ECOSYSTEM

The SQL Server data platform has become quite large over the past few releases. This first section provides a review of the overall SQL Server ecosystem, which is now referred to as less of a product and more of a data platform, because many interactions with other products and features drive increased performance, scale, and usability. SQL Server 2014 focuses on the following areas:

• Mission-critical performance—New In-Memory Online Transaction Processing (OLTP) features that allow faster performance with no application changes, updatable columnstore indexes, buffer pool extensions using solid-state drives (SSDs), and AlwaysOn enhancements (including support of up to eight replicas) make this the most powerful, available release of SQL Server.
• Faster insights—With new Office-based Business Intelligence (BI) tools such as Power Query and Power Map, as well as enhancements on Power View and PowerPivot, getting insights on your data has never been easier. Also, enterprise options such as Parallel Data Warehouse with Polybase allow an organization to easily explore its Big Data and gain never-before-seen insights on its data by leveraging the full power of the Microsoft BI stack.
• Platform for hybrid cloud—Whether your environment is pure on-premises, virtualized, or all in the cloud, SQL Server 2014 has options for you. New features such as the Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage, and encrypts and compresses SQL Server backups stored locally or in the cloud. You can also now choose a Windows Azure virtual machine (VM) as a replica in an AlwaysOn Availability Group configuration.

NEW IMPORTANT FEATURES IN 2014

You will be excited about a number of new things, depending on your role and how you use SQL Server. This section touches on the features you should be checking out and getting your hands on. Many of these features are quick to get up and running, which is exciting for those readers who want to begin delivering impact right away.

Production DBA

Production database administrators (DBAs) are a company's insurance policy that the production database won't go down. If the database does go down, the company cashes in its insurance policy in exchange for a recovered database. The Production DBA also ensures that the server performs optimally, and promotes database changes from development to quality assurance (QA) to production. New features include the following:

• In-Memory OLTP—This release offers a new memory-optimized OLTP database engine for SQL Server. Experience significant performance and scalability gains to your application with little to no code changes.
• AlwaysOn Availability Groups—Availability functionality includes Availability Groups and the ability to fail over databases in groups that mimic applications. Though not a new feature in 2014, enhancements in this release include support of up to eight secondaries (up from four in the 2012 release), as well as performance and management improvements.
• SQL Server Backup to Windows Azure tool—This free tool enables backing up to Windows Azure Blob Storage. It encrypts and compresses backups stored locally or in the cloud. This tool supports SQL Server 2005 and later.
• SQL Server Backup to URL—Originally released in SQL Server 2012 SP1 CU2, this feature is now fully integrated into the Management Studio interface so that you can back up or restore from the Windows Azure Blob Storage service.
• SQL Server Managed Backup to Windows Azure—This new feature fully automates SQL Server backups (full and transaction log) to the Windows Azure Blob Storage service based on retention period and transaction workload on the database.
• Columnstore indexes—The 2014 release includes updatable clustered columnstore indexes for optimizing large data volumes.
• Encrypted backups—Support is now included for encrypted backups, including algorithm support for Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple Data Encryption Standard (DES). You must use either a certificate or an asymmetric key to perform encryption during backup.
• Delayed durability—This includes the ability to reduce latency by designating some or all transactions as delayed durable. This asynchronous process reports a COMMIT as successful before log records are written to disk. Delayed durable transactions become durable when the transaction log entries are flushed to disk in chunks. This feature is available for In-Memory OLTP only.
• Compression and partitioning—Improved compression and partitioning include the capability to rebuild single partitions.
• Resource Governor—This now enables you to specify limits on physical I/O within a resource pool.

Development DBA

Since the release of SQL Server 2000, there has been a trend away from full-time Production DBAs, and the role has merged with that of the Development DBA. The trend may have slowed, though, with laws such as Sarbanes-Oxley, which require a separation of power between the person developing the change and the person implementing the change. In a large organization, a Production DBA may fall into the operations department, which consists of the network of administrators and Windows-support administrators. In other instances, a Production DBA may be placed in a development group. This removes the separation of power that is sometimes needed for regulatory reasons.

Development DBAs play a traditional role in an organization. They wear more of a developer's hat, and serve as the development staff's database experts and representatives. This administrator ensures that all stored procedures are optimally written and that the database is modeled correctly, both physically and logically. The Development DBA also may be the person who writes the migration processes to upgrade the database from one release to the next. The Development DBA typically does not receive calls at 2:00 A.M like the Production DBA might for failed backups or similar problems. Following are some of the things Development DBAs should be excited about in this new release:

• Transact SQL (T-SQL) enhancements include features such as the inline specification of indexes for disk-based tables. The SELECT…INTO statement can now operate in parallel with databases, in compatibility mode set to at least 110.
• Updates for SQL Server Data Tools for Business Intelligence (SSDT-BI) include support for creating Power View reports against multidimensional models. Additional improvements to SSDT-BI include support to create projects for older versions (2005+) of Analysis Services and Reporting Services. Backward compatibility for Integration Services projects is not yet supported.

The Development DBA typically reports to the development group, and receives requests from a business analyst or another developer. In a traditional sense, Development DBAs should never have modification access to a production database. However, they should have read-only access to the production database to debug in a time of escalation.

Business Intelligence DBA and Developer

The BI DBA is a role that has evolved as a result of the increased capabilities of SQL Server. In SQL Server 2012, BI grew to be an incredibly important feature set that many businesses could not live without. The BI DBA (or developer) is an expert at these features. SQL Server 2014 improves upon the end-user BI experience started in SQL Server 2012, with all new extensibility through the Power BI solution with Office365, as well as the existing BI offerings.

Development BI DBAs specialize in the best practices, optimization, and use of the BI toolset. In a small organization, a Development BI DBA may create your SQL Server Integration Services (SSIS) packages to perform extract, transform, and load (ETL) processes or reports for users. In a large organization, developers create the SSIS packages and SQL Server Reporting Services (SSRS) reports. The Development BI DBA is consulted regarding the physical implementation of the SSIS packages and SQL Server Analysis Services (SSAS) cubes, or SSAS tabular models.

Development BI DBAs may be responsible for the following types of functions:

• Modeling/consulting regarding Analysis Services cubes/tabular models and solutions
• Creating reports using Reporting...