Learn to take advantage of the opportunities offered by SQL
Server 2014

Microsoft's SQL Server 2014 update means big changes for
database administrators, and you need to get up to speed quickly
because your methods, workflow, and favorite techniques will be
different from here on out. The update's enhanced support of
large-scale enterprise databases and significant price advantage
mean that SQL Server 2014 will become even more widely adopted
across the industry. The update includes new backup and recovery
tools, new AlwaysOn features, and enhanced cloud capabilities.
In-memory OLTP, Buffer Pool Extensions for SSDs, and a new
Cardinality Estimator can improve functionality and smooth out the
workflow, but only if you understand their full capabilities.
Professional Microsoft SQL Server 2014 is your comprehensive
guide to working with the new environment. Authors Adam Jorgensen,
Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the
dream team of the SQL Server community, and they put their
expertise to work guiding you through the changes.

* Improve oversight with better management and monitoring

* Protect your work with enhanced security features

* Upgrade performance tuning, scaling, replication, and
clustering

* Learn new options for backup and recovery

Professional Microsoft SQL Server 2014 includes a
companion website with sample code and efficient automation
utilities, plus a host of tips, tricks, and workarounds that will
make your job as a DBA or database architect much easier. Stop
getting frustrated with administrative issues and start taking
control. Professional Microsoft SQL Server 2014 is your
roadmap to mastering the update and creating solutions that
work.

Adam Jorgensen is the President of Pragmatic Works and
the Executive Vice President of PASS with more than 15 years
experience.

Bradley Ball is a MCITP 2005, 2008, and 2012 MCSE DBA
with more than a decade of experience who is currently the Data
Platform Management Lead with Pragmatic Works specializing in Data
Platform solutions.

Steven Wort has been working with SQL Server since 1993,
who joined Microsoft in 2000, and now works as an Architect in the
CRM Service Engineering team.

Ross LoForte is a Technology Architect at the Microsoft
Technology Center Chicago focused on Microsoft SQL Server
solutions, with more than 20 years of business development, project
management, and SQL experience.

Brian Knight is the founder of Pragmatic Works and
co-founder of SQLServerCentral.com and JumpStartTV.com

Wrox Professional guides are planned and written by
working programmers to meet the real-world needs of programmers,
developers, and IT professionals. Focused and relevant, they
address the issues technology professionals face every day. They
provide examples, practical solutions, and expert education in new
technologies, all designed to help programmers do a better job.

Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Authors Adam Jorgensen, Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the dream team of the SQL Server community, and they put their expertise to work guiding you through the changes.

• Improve oversight with better management and monitoring
• Protect your work with enhanced security features
• Upgrade performance tuning, scaling, replication, and clustering
• Learn new options for backup and recovery

Professional Microsoft SQL Server 2014 includes a companion website with sample code and efficient automation utilities, plus a host of tips, tricks, and workarounds that will make your job as a DBA or database architect much easier. Stop getting frustrated with administrative issues and start taking control. Professional Microsoft SQL Server 2014 is your roadmap to mastering the update and creating solutions that work.

INTRODUCTION xxxvii

CHAPTER 1: SQL SERVER 2014 ARCHITECTURE 1

SQL Server 2014 Ecosystem 1

New Important Features in 2014 2

Production DBA 2

Development DBA 3

Business Intelligence DBA and Developer 4

SQL Server Architecture 5

Database Files and Transaction Log 5

SQL Server Native Client 6

Standard System Databases 7

Schemas 9

Synonyms 10

Dynamic Management Objects 10

SQL Server 2014 Data Types 12

Editions of SQL Server 18

Edition Overview 18

Licensing 21

Summary 22

CHAPTER 2: INSTALLING SQL SERVER 2014 BEST PRACTICES 23

Planning the System 24

Hardware Options 24

Software and Install Options 29

Installing SQL Server 32

New Installs 32

Side-by-Side Installs 32

Upgrades 33

Attended Installations 33

Unattended Installs 37

Installing Analysis Services 43

Multidimensional and Data Mining Mode (UDM Mode) 44

Tabular Mode 46

Installing PowerPivot for SharePoint 47

Burning in the System 48

Post-Install Configuration 49

Configuring SQL Server Settings for Performance 49

tempdb 51

Configuring SQL Server Settings for Security 52

SQL Server Configuration Manager 54

Back It Up 54

Uninstalling SQL Server 54

Uninstalling Reporting Services 55

Uninstalling Analysis Services 55

Uninstalling the SQL Server Database Engine 55

Troubleshooting a Failed Install 55

Summary 56

CHAPTER 3: UPGRADING SQL SERVER 2014 BEST PRACTICES 57

Why Upgrade to SQL Server 2014? 57

Risk Mitigationthe Microsoft Contribution 58

Independent Software Vendors and SQL Community Contributions 59

Upgrading to SQL Server 2014 59

In-Place Upgrading 60

Side-by-Side Upgrade 62

In-Place Upgrade versus Side-by-Side Upgrade Considerations 62

Pre-Upgrade Steps and Tools 63

Pre-Upgrade Steps 63

Pre-Upgrade Tools 64

Backward Compatibility 72

Unsupported and Discontinued Features in SQL Server 2014 72

SQL Server 2014 Deprecated Database Features 74

Other SQL Server 2014 Changes Affecting Behavior 74

SQL Server Component Considerations 75

Upgrading Full-Text Catalog 75

Upgrading Reporting Services 75

Upgrading to 64-Bit 76

Post-Upgrade Checks 76

Summary 77

CHAPTER 4: MANAGING AND TROUBLESHOOTING THE DATABASE ENGINE 79

Configuration and Administration Tools 80

SQL Server Configuration Manager 80

Startup Parameters 82

Startup Stored Procedures 84

Partially Contained Databases 86

Troubleshooting Tools 87

Dedicated Administrator Connection 87

Rebuilding the System Databases 89

SQL Server Management Studio 90

Reports 90

Configuring SQL Server in SQL Ser…