Jorgensen A. et al. - Professional Microsoft SQL Server 2014 Administration

• 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.

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 performanceNew 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 insightsWith 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 cloudWhether 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.

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 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 OLTPThis 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 GroupsAvailability 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 toolThis 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 URLOriginally 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 AzureThis 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 indexesThe 2014 release includes updatable clustered columnstore indexes for optimizing large data volumes.
• Encrypted backupsSupport 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 durabilityThis 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 partitioningImproved compression and partitioning include the capability to rebuild single partitions.
• Resource GovernorThis now enables you to specify limits on physical I/O within a resource pool.

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 SELECTINTO 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.