Getting Started with SQL Server 2014 Administration
By Gethyn Ellis
()
About this ebook
If you want to create an highly efficient database server then this book is for you. This book is for database professionals and system administrators who want to use the added features of SQL Server 2014 to create a hybrid environment, which is both highly available and allows you to get the best performance from your databases.
Related to Getting Started with SQL Server 2014 Administration
Related ebooks
Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code Rating: 0 out of 5 stars0 ratingsUltimate Azure Data Engineering Rating: 0 out of 5 stars0 ratingsMicrosoft System Center Configuration Manager High availability and performance tuning Rating: 0 out of 5 stars0 ratingsActive Directory with PowerShell Rating: 4 out of 5 stars4/5Microsoft Azure IaaS Essentials Rating: 4 out of 5 stars4/5VMware vSphere Design Essentials Rating: 0 out of 5 stars0 ratingsPowerShell for SQL Server Essentials Rating: 0 out of 5 stars0 ratingsProfessional Microsoft SQL Server 2016 Reporting Services and Mobile Reports Rating: 0 out of 5 stars0 ratingsHigh Availability MySQL Cookbook Rating: 0 out of 5 stars0 ratingsSQL Server: Tips and Tricks - 2 Rating: 4 out of 5 stars4/5Learn SQL Server Administration in a Month of Lunches Rating: 3 out of 5 stars3/5SQL Server 2016 Developer's Guide Rating: 0 out of 5 stars0 ratingsSQL Server 2016 Reporting Services Cookbook Rating: 5 out of 5 stars5/5Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability Rating: 5 out of 5 stars5/5Mastering Cloud Development using Microsoft Azure Rating: 0 out of 5 stars0 ratingsMicrosoft IIS 10.0 Cookbook Rating: 0 out of 5 stars0 ratingsMicrosoft SQL Server 2014 Business Intelligence Development Beginner’s Guide Rating: 0 out of 5 stars0 ratingsMastering Windows Server 2016 Rating: 0 out of 5 stars0 ratingsMicrosoft SQL Server 2012 Integration Services: An Expert Cookbook Rating: 5 out of 5 stars5/5SQL Server 2017 Integration Services Cookbook Rating: 0 out of 5 stars0 ratingsOCA Oracle Database 11g Database Administration I: A Real-World Certification Guide Rating: 4 out of 5 stars4/5SQL Server Interview Questions You'll Most Likely Be Asked Rating: 0 out of 5 stars0 ratingsLearning Oracle 12c: A PL/SQL Approach Rating: 0 out of 5 stars0 ratingsSQL Server MVP Deep Dives, Volume 2 Rating: 0 out of 5 stars0 ratingsAzure SQL Revealed: A Guide to the Cloud for SQL Server Professionals Rating: 0 out of 5 stars0 ratingsSQL Server Management Studio Second Edition Rating: 1 out of 5 stars1/5Pro Power BI Architecture: Sharing, Security, and Deployment Options for Microsoft Power BI Solutions Rating: 0 out of 5 stars0 ratingsMicrosoft Windows Azure Development Cookbook Rating: 5 out of 5 stars5/5
Programming For You
SQL QuickStart Guide: The Simplified Beginner's Guide to Managing, Analyzing, and Manipulating Data With SQL Rating: 4 out of 5 stars4/5Coding All-in-One For Dummies Rating: 4 out of 5 stars4/5Excel : The Ultimate Comprehensive Step-By-Step Guide to the Basics of Excel Programming: 1 Rating: 5 out of 5 stars5/5Python Programming : How to Code Python Fast In Just 24 Hours With 7 Simple Steps Rating: 4 out of 5 stars4/5Learn to Code. Get a Job. The Ultimate Guide to Learning and Getting Hired as a Developer. Rating: 5 out of 5 stars5/5SQL: For Beginners: Your Guide To Easily Learn SQL Programming in 7 Days Rating: 5 out of 5 stars5/5Python QuickStart Guide: The Simplified Beginner's Guide to Python Programming Using Hands-On Projects and Real-World Applications Rating: 0 out of 5 stars0 ratingsC Programming For Beginners: The Simple Guide to Learning C Programming Language Fast! Rating: 5 out of 5 stars5/5HTML in 30 Pages Rating: 5 out of 5 stars5/5Tiny Python Projects: Learn coding and testing with puzzles and games Rating: 5 out of 5 stars5/5Learn PowerShell in a Month of Lunches, Fourth Edition: Covers Windows, Linux, and macOS Rating: 5 out of 5 stars5/5HTML & CSS: Learn the Fundaments in 7 Days Rating: 4 out of 5 stars4/5Linux: Learn in 24 Hours Rating: 5 out of 5 stars5/5C# Programming from Zero to Proficiency (Beginner): C# from Zero to Proficiency, #2 Rating: 0 out of 5 stars0 ratingsAssembly Programming:Simple, Short, And Straightforward Way Of Learning Assembly Language Rating: 5 out of 5 stars5/5Grokking Algorithms: An illustrated guide for programmers and other curious people Rating: 4 out of 5 stars4/5Spies, Lies, and Algorithms: The History and Future of American Intelligence Rating: 4 out of 5 stars4/5Lua Game Development Cookbook Rating: 0 out of 5 stars0 ratingsExcel 101: A Beginner's & Intermediate's Guide for Mastering the Quintessence of Microsoft Excel (2010-2019 & 365) in no time! Rating: 0 out of 5 stars0 ratingsA Slackers Guide to Coding with Python: Ultimate Beginners Guide to Learning Python Quick Rating: 0 out of 5 stars0 ratingsThe Ultimate Python Programming Guide For Beginner To Intermediate Rating: 5 out of 5 stars5/5
Reviews for Getting Started with SQL Server 2014 Administration
0 ratings0 reviews
Book preview
Getting Started with SQL Server 2014 Administration - Gethyn Ellis
Table of Contents
Getting Started with SQL Server 2014 Administration
Credits
About the Author
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers and more
Why Subscribe?
Free Access for Packt account holders
Instant Updates on New Packt Books
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. SQL Server 2014 and Cloud
Windows Azure SQL Database
Creating a Windows Azure SQL database
Connecting to a Windows Azure SQL database with Management Studio
Migrating an existing database to Microsoft Azure
Integrating Microsoft Azure Storage
Creating a database that uses the Microsoft Azure Storage integration
Creating a Microsoft Azure Storage account and container
The container policy and Shared Access Signature
Creating a credential
Creating a database using Microsoft Azure Storage
Microsoft Azure Virtual Machines
Creating a Microsoft Azure Virtual Machine
Summary
2. Backup and Restore Improvements
Database backups to a URL and Microsoft Azure Storage
Backing up a SQL Server database to a URL
Restoring a backup stored on Windows Azure Storage
SQL Server Managed Backup to Microsoft Azure
Configuring SQL Server-managed backups to Microsoft Azure
Encryption
Creating an encrypted backup
Creating a database master key for the master database
Creating a certificate or asymmetric key
Creating an encrypted database backup
Summary
3. In-Memory Optimized Tables
Requirements
Virtualized environments
Memory-optimized tables
Memory-optimized tables and indexes
Transact-SQL and natively compiled stored procedures
Concurrency
Some concurrency terminologies
Summary
4. Delayed Durability
Understanding the full transaction durability
Understanding the delayed transaction durability
Full Durability versus Delayed Durability
Using delayed transaction durability
Transaction level Delayed Durability – Transact-SQL
Delayed Durability and Natively Compiled Stored Procedures
Delayed Durability and transaction log flush
Delayed Durability and potential data loss
Delayed Durability and other SQL Server components
Summary
5. AlwaysOn Availability Groups
Introducing AlwaysOn Availability Groups
Enhancements in AlwaysOn Availability Groups
Using Microsoft Azure Virtual Machines as replicas
The commit mode
Reporting and administrating on secondary replicas
Building AlwaysOn Availability Groups
Windows Server Failover Cluster
Configuring the instance service
The Availability Group listener
Creating Availability Group
Troubleshooting Availability Groups
Creating a cloud-based AlwaysOn Availability Group
Creating an AlwaysOn Availability Group in Microsoft Azure
Creating a Hybrid Availability Group
Summary
6. Performance Improvements
Partition switching and indexing
Columnstore indexes
Creating a clustered columnstore index
Updating a table with a clustered columnstore index
Buffer pool extensions
Cardinality estimator and query plans
Statistics
Resource Governor
Summary
Index
Getting Started with SQL Server 2014 Administration
Getting Started with SQL Server 2014 Administration
Copyright © 2014 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: April 2014
Production Reference: 1180414
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78217-241-3
www.packtpub.com
Cover Image by Karl Moore (<karl@karlmoore.co.uk>)
Credits
Author
Gethyn Ellis
Reviewers
Brenner Grudka Lira
David Loo
Richard Louie
Commissioning Editor
Antony Lowe
Acquisition Editors
Richard Harvey
Antony Lowe
Content Development Editor
Arun Nadar
Technical Editors
Aman Preet Singh
Nachiket Vartak
Copy Editors
Mradula Hegde
Gladson Monteiro
Adithi Shetty
Project Coordinator
Lima Danti
Proofreader
Maria Gould
Indexers
Mehreen Deshmukh
Mariammal Chettiyar
Graphics
Ronak Dhruv
Production Coordinator
Aparna Bhagat
Cover Work
Aparna Bhagat
About the Author
Gethyn Ellis is a SQL Server consultant and trainer with clients in Europe and North America. His client base includes both public and private sector clients that range from large financial firms to departments of the UK government. Specialized in database administration, virtualization, and performance, he has been involved in several large projects that involve migration of large SQL Server estates to the latest version of SQL Server and also migrated that infrastructure to a virtualized platform. He is also an instructor with Learning Tree and teaches several courses on the Learning Tree SQL Server curriculum. You can follow Gethyn's blog at www.gethynellis.com.
I would like to mention a few people here. My Mum, Lynda, and dad, Ron Ellis for encouraging me to write this book. My sister, Katheryn, and brother-in-law, Gareth Lewis, who have just given birth to twin boys. I would also like to mention my niece and two new nephews, Carys Gwen, Iwan, and Efan Lewis. Last but not least, I also wish to mention my two golden retriever dogs, Seth and Jake. While I wrote this book, these two helped me dearly by providing necessary distractions from the writing process by demanding regular long walks.
About the Reviewers
Brenner Grudka Lira has been a data analyst and DBA at NeuroTech since 2012. He has a bachelor's degree in Computer Science and a post graduate degree in Project Management, both from the Catholic University of Pernambuco, Recife, Brazil.
He also has experience in building and modeling data warehouses and has knowledge of SQL Server and MySQL database management. At present, he is dedicated to the study of project management and database tuning.
He has already reviewed the books Microsoft SQL Server Denali Integration Services: An Expert Handbook and Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting, both published by Packt Publishing.
David Loo is a senior software development professional with over 25 years of experience in both software development and people management. He is respected for his ability to focus teams on service excellence and for designing and implementing practical process improvements and solutions. He is always on the lookout for ways to contribute his knowledge and experience of software development, team building, and developing best practices.
Richard Louie is a Business Intelligence developer at Redwood Trust, a residential and commercial mortgage investment firm. He has extensive experience in Oracle and Microsoft SQL for ETL, SSIS, SSRS,