Introducing Microsoft SQL Server 2014

144 Pages·2014·10.881 MB·English

by  Ross Mistry, Stacia Misner

I
n
Introducing Windows 8.1 t
r
o
d
u
c
for IT Professionals i
n
g

W
i
n
d
Get a head start evaluating Windows 8.1—with early technical Note o
w
insights from award-winning journalist and Windows expert Ed This title is also available as a free s

Bott. This guide introduces new features and capabilities, providing eBook from Microsoft at: 8
.
a practical, high-level overview for IT professionals ready to begin http://aka.ms/mspressfree 1

f
deployment planning now. o
About the Author r
I
Preview new features and enhanced capabilities, T
Ed Bott is an award-winning journalist
P
including: known to millions of readers through
r
two decades of writing for leading o
• The Windows 8.1 user experience industry publications and more than 25 fe
• Deployment tools and technologies books on Microsoft Office and Windows, ss
i
• Security features including Windows 7 Inside Out and o
n
• Internet Explorer 11 Microsoft Office Inside Out: 2013 Edition. a
Introducing
• Delivering Windows Store apps ls
• Recovery options
• Networking and remote access
• Virtualization
• Windows RT 8.1 Microsoft
• Managing mobile devices
Also see
SQL Server 2014
Technical Overview
Windows 8.1 Administration Microsoft Office
Pocket Consultant Inside Out
Essentials & Configuration 2013 Edition
ISBN: 9780735682658 ISBN: 9780735669062
Storage, Security, & Networking
ISBN: 9780735682610
U.S.A. $9.99
Canada $10.99
[Recommended]
Celebrating 30 years! ROSS MISTRY n STACIA MISNER
Operating Systems/Windows
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2014 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any
means without the written permission of the publisher.
Library of Congress Control Number: 2014934033
ISBN: 978-0-7356-8475-1
Printed and bound in the United States of America.
First Printing
Microsoft Press books are available through booksellers and distributors worldwide. If you need support related
to this book, email Microsoft Press Book Support at [email protected]. Please tell us what you think of
this book at http://www.microsoft.com/learning/booksurvey.
Microsoft and the trademarks listed at http://www.microsoft.com/en-us/legal/intellectualproperty/Trademarks/
EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective
owners.
The example companies, organizations, products, domain names, email addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain name,
email address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without
any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or
distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by
this book.
Acquisitions Editor: Devon Musgrave
Developmental Editor: Devon Musgrave
Project Editor: Carol Dillingham
Editorial Production: Flyingspress and Rob Nance
Technical Reviewer: Stevo Smocilac; Technical Review services provided by Content Master, a member of CM
Group, Ltd.
Copyeditor: John Pierce
Indexer: Lucie Haskins
Cover: Twist Creative • Seattle
I dedicate this book to my Kyanna and Kaden. Follow your
passions, and never let anyone hold you back. And to the next
chapter in the game of life, “Return of the Autumn Wind.”
—Ross MistRy
I dedicate this book to my SQL family all around the world. You
all bring a lot of enthusiasm and passion of your own to this pro-
fession, which inspires me and spurs me on to learn more so that
I can share more. Thanks to each of you for what you give
to me.
—stacia MisneR

Contents at a glance
PART I DATABASE ADMINISTRATION
CHAPTER 1 SQL Server 2014 editions and engine enhancements 3
CHAPTER 2 In-Memory OLTP investments 23
CHAPTER 3 High-availability, hybrid-cloud, and backup enhancements 39
PART II BUSINESS INTELLIGENCE DEVELOPMENT
CHAPTER 4 Exploring self-service BI in Microsoft Excel 2013 63
CHAPTER 5 Introducing Power BI for Office 365 89
CHAPTER 6 Big data solutions 101

Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
PART I DATABASE ADMINISTRATION
Chapter 1 SQL Server 2014 editions and engine enhancements 3
SQL Server 2014 enhancements for database administrators . . . . . . . . . . . 4
Availability enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Backup and restore enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Scalability and performance enhancements . . . . . . . . . . . . . . . . . . . . . 8
Security enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Platform for hybrid cloud . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
SQL Server 2014 editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Enterprise edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Standard edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Business Intelligence edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Specialized editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
SQL Server 2014 licensing overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Hardware and software requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Installation, upgrade, and migration strategies . . . . . . . . . . . . . . . . . . . . . . . 18
In-place upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Side-by-side migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Chapter 2 In-Memory OLTP investments 23
In-Memory OLTP overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
In-Memory OLTP fundamentals and architecture . . . . . . . . . . . . . . . . . . . . 25
Four In-Memory OLTP architecture pillars . . . . . . . . . . . . . . . . . . . . . 26
In-Memory OLTP concepts and terminology . . . . . . . . . . . . . . . . . . . 27
Hardware and software requirements for memory-optimized
tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
In-Memory OLTP use cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Myths about In-Memory OLTP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
vii
In-Memory OLTP integration and application migration . . . . . . . . . . . . . . 29
Will In-Memory OLTP improve performance? . . . . . . . . . . . . . . . . . . 31
Using the Memory Optimization Advisor to migrate disk-based
tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Analyzing In-Memory OLTP behavior after memory-optimized
table migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Using In-Memory OLTP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Enabling In-Memory OLTP in a database . . . . . . . . . . . . . . . . . . . . . . 34
Create memory-optimized tables and natively compile stored
procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Execute queries to demonstrate performance when using
memory-optimized tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Appendix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Memory-optimized table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Natively compiled stored procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Chapter 3 High-availability, hybrid-cloud, and backup
enhancements 39
SQL Server high-availability and disaster-recovery enhancements . . . . . . 40
AlwaysOn Availability Groups enhancements . . . . . . . . . . . . . . . . . . . 40
AlwaysOn Failover Cluster Instances enhancements . . . . . . . . . . . . . 43
Platform for hybrid cloud . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Cloud disaster recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Deploy a database to a Windows Azure Virtual Machine . . . . . . . . 50
Storing SQL Server data files in Windows Azure . . . . . . . . . . . . . . . . 51
Extending on-premises apps to the cloud . . . . . . . . . . . . . . . . . . . . . . 52
Backup and restore enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
SQL Server backup to a URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Encryption for backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
SQL Server Managed Backup to Windows Azure . . . . . . . . . . . . . . . .59
viii Contents
PART II BUSINESS INTELLIGENCE DEVELOPMENT
Chapter 4 Exploring self-service BI in Microsoft Excel 2013 63
Excel Data Model and Power Pivot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Working with the Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Managing data as a Power Pivot model . . . . . . . . . . . . . . . . . . . . . . . 65
Power Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Searching for data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Importing data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Loading the worksheet or Data Model . . . . . . . . . . . . . . . . . . . . . . . . 71
Shaping data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Combining data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Power View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Creating a Power View report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Working with visualizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Sharing a Power View report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Power Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Creating a Power Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Visualizing geographic data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Exploring the 3-D map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Displaying values over time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Enhancing a map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Working with tours, scenes, and layers . . . . . . . . . . . . . . . . . . . . . . . . 86
Sharing Power Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Chapter 5 Introducing Power BI for Office 365 89
Power BI sites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Configuring featured workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Opening Power View in HTML5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Adding favorite reports to My Power BI . . . . . . . . . . . . . . . . . . . . . . . 92
Contents ix
Shared queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Creating a shared query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Using a shared query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Managing query metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Reviewing usage analytics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Power BI Q&A . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Power BI for Mobile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Preparing a workbook for mobile viewing . . . . . . . . . . . . . . . . . . . . . 97
Using Power BI for Mobile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Sharing a report from Power BI for Mobile . . . . . . . . . . . . . . . . . . . . . 99
Power BI administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Chapter 6 Big data solutions 101
Big data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Describing big data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Exploring the history of Hadoop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
HDInsight . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Creating a storage account for HDInsight . . . . . . . . . . . . . . . . . . . . . 105
Provisioning an HDInsight cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Loading data into a storage account . . . . . . . . . . . . . . . . . . . . . . . . . 106
Working with Hive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Analyzing data from Hive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
PolyBase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Exploring the benefits of PolyBase . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Accessing HDFS data from PDW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Index 119
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
microsoft.com/learning/booksurvey
x Contents