Pro SQL Server on Linux Including Container-Based Deployment with Docker and Kubernetes — Bob Ward Foreword by Slava Oks Pro SQL Server on Linux Including Container-Based Deployment with Docker and Kubernetes Bob Ward Foreword by Slava Oks Pro SQL Server on Linux: Including Container-Based Deployment with Docker and Kubernetes Bob Ward North Richland Hills, TX, USA ISBN-13 (pbk): 978-1-4842-4127-1 ISBN-13 (electronic): 978-1-4842-4128-8 https://doi.org/10.1007/978-1-4842-4128-8 Library of Congress Control Number: 2018962264 Copyright © 2018 by Bob Ward This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover image designed by Freepik (www.freepik.com) Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer- sbm.com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail [email protected], or visit www.apress.com/ rights-permissions. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the book's product page, located at www.apress.com/9781484241271. For more detailed information, please visit www.apress.com/source-code. Printed on acid-free paper This book is dedicated to all the people who have worked at Microsoft over the last 25 years to build, ship, support, and market the SQL Server product. Table of Contents About the Author �����������������������������������������������������������������������������������������������������xv About the Technical Reviewer �������������������������������������������������������������������������������xvii Acknowledgments ��������������������������������������������������������������������������������������������������xix Foreword ����������������������������������������������������������������������������������������������������������������xxi Introduction �����������������������������������������������������������������������������������������������������������xxv Chapter 1: Why SQL Server on Linux? ����������������������������������������������������������������������1 Platform of Choice ������������������������������������������������������������������������������������������������������������������������2 How We Built It ������������������������������������������������������������������������������������������������������������������������������3 Drawbridge ������������������������������������������������������������������������������������������������������������������������������4 SQLOS, SQLPAL, and Helsinki ��������������������������������������������������������������������������������������������������6 The SQL Server on Linux Architecture �������������������������������������������������������������������������������������8 SQL Server on Windows vs� Linux� Is it the Same? ���������������������������������������������������������������������14 SQL Server on Linux Capabilities�������������������������������������������������������������������������������������������14 What Features Are Not Available �������������������������������������������������������������������������������������������17 Should I Use Windows or Linux? �������������������������������������������������������������������������������������������18 Containers Are the New Virtual Machine �������������������������������������������������������������������������������������19 Database Containers �������������������������������������������������������������������������������������������������������������20 Platform Independence, Portability, and Consistency ������������������������������������������������������������20 Continuous Integration/Continuous Deployment �������������������������������������������������������������������21 Kubernetes ����������������������������������������������������������������������������������������������������������������������������21 Summary�������������������������������������������������������������������������������������������������������������������������������������22 v Table of ConTenTs Chapter 2: Install and Configuration �����������������������������������������������������������������������23 Preparing for Install ��������������������������������������������������������������������������������������������������������������������23 Linux Distributions �����������������������������������������������������������������������������������������������������������������24 System Requirements �����������������������������������������������������������������������������������������������������������26 Testing for SQL Server �����������������������������������������������������������������������������������������������������������27 Linux Tips ������������������������������������������������������������������������������������������������������������������������������28 Just Install It! ������������������������������������������������������������������������������������������������������������������������������34 Deploy in 60 Seconds ������������������������������������������������������������������������������������������������������������35 Download a Repository Config File ����������������������������������������������������������������������������������������36 Do the Install of the SQL Server Engine ���������������������������������������������������������������������������������37 Complete the Setup of SQL Server ����������������������������������������������������������������������������������������39 The Complete Installation Experience �����������������������������������������������������������������������������������������42 Installing Other Versions ��������������������������������������������������������������������������������������������������������42 Verifying Install ����������������������������������������������������������������������������������������������������������������������44 Unattended Install �����������������������������������������������������������������������������������������������������������������52 Offline Install �������������������������������������������������������������������������������������������������������������������������53 Installing Other Packages ������������������������������������������������������������������������������������������������������55 Installing in Azure ������������������������������������������������������������������������������������������������������������������56 Troubleshooting Install ����������������������������������������������������������������������������������������������������������58 Exploring SQL Server on Linux ����������������������������������������������������������������������������������������������������61 What Is Installed ��������������������������������������������������������������������������������������������������������������������61 Using Log Files ����������������������������������������������������������������������������������������������������������������������63 Post-Install Configuration �����������������������������������������������������������������������������������������������������������64 Using mssql-conf�������������������������������������������������������������������������������������������������������������������64 SQL Server Instance Configuration ����������������������������������������������������������������������������������������69 Windows Configuration Options on Linux ������������������������������������������������������������������������������70 Updates and Uninstall �����������������������������������������������������������������������������������������������������������������71 Updating SQL Server �������������������������������������������������������������������������������������������������������������71 Rolling Back to a Previous Update �����������������������������������������������������������������������������������������73 Removing SQL Server ������������������������������������������������������������������������������������������������������������74 Summary�������������������������������������������������������������������������������������������������������������������������������������75 vi Table of ConTenTs Chapter 3: Building a Database and T-SQL Fundamentals ��������������������������������������77 Setting Up Your Environment ������������������������������������������������������������������������������������������������������77 Creating a Database ��������������������������������������������������������������������������������������������������������������������79 System Databases �����������������������������������������������������������������������������������������������������������������80 Creating a Login and User �����������������������������������������������������������������������������������������������������82 Creating a User Database ������������������������������������������������������������������������������������������������������84 Creating Tables����������������������������������������������������������������������������������������������������������������������������89 Creating Schemas �����������������������������������������������������������������������������������������������������������������90 Creating Sequences ��������������������������������������������������������������������������������������������������������������91 Finally Creating the Tables�����������������������������������������������������������������������������������������������������92 Creating the Complete Database ����������������������������������������������������������������������������������������������100 Building and Running Queries ���������������������������������������������������������������������������������������������������102 Inserting and Reading Data �������������������������������������������������������������������������������������������������102 Updating and Deleting data �������������������������������������������������������������������������������������������������105 Building Views and Stored Procedures ��������������������������������������������������������������������������������106 Summary�����������������������������������������������������������������������������������������������������������������������������������109 Chapter 4: Building an Application and Advanced T-SQL �������������������������������������111 Setting Up Your Environment ����������������������������������������������������������������������������������������������������112 Building and Running a Data Application for SQL Server ����������������������������������������������������������113 Using node�js with SQL Server ��������������������������������������������������������������������������������������������114 Connecting to SQL Server with node�js �������������������������������������������������������������������������������115 Inserting and Reading Data �������������������������������������������������������������������������������������������������118 Executing Stored Procedures ����������������������������������������������������������������������������������������������123 Enhancing Your Application �������������������������������������������������������������������������������������������������125 Go Big on T-SQL ������������������������������������������������������������������������������������������������������������������������126 Creating and Using Temporary Objects �������������������������������������������������������������������������������126 Triggers ��������������������������������������������������������������������������������������������������������������������������������132 Analytic Queries�������������������������������������������������������������������������������������������������������������������132 Complex Datatypes ��������������������������������������������������������������������������������������������������������������133 String Functions ������������������������������������������������������������������������������������������������������������������135 Other T-SQL Commands ������������������������������������������������������������������������������������������������������135 vii Table of ConTenTs Exploring New SQL Server Capabilities ������������������������������������������������������������������������������������135 JSON ������������������������������������������������������������������������������������������������������������������������������������136 Temporal Tables �������������������������������������������������������������������������������������������������������������������136 Graph Database �������������������������������������������������������������������������������������������������������������������139 Native Scoring ���������������������������������������������������������������������������������������������������������������������142 Summary�����������������������������������������������������������������������������������������������������������������������������������144 Chapter 5: SQL Server Tools ���������������������������������������������������������������������������������145 Command Line Tools �����������������������������������������������������������������������������������������������������������������146 sqlcmd ���������������������������������������������������������������������������������������������������������������������������������147 bcp ���������������������������������������������������������������������������������������������������������������������������������������151 mssql-cli ������������������������������������������������������������������������������������������������������������������������������153 mssql-scripter ���������������������������������������������������������������������������������������������������������������������156 sqlservr Command Line Options ������������������������������������������������������������������������������������������158 SQL Operations Studio ��������������������������������������������������������������������������������������������������������������159 Installation ���������������������������������������������������������������������������������������������������������������������������160 Configuration �����������������������������������������������������������������������������������������������������������������������163 Object Explorer ��������������������������������������������������������������������������������������������������������������������167 Dashboards, Insights, and Extensions ���������������������������������������������������������������������������������169 T-SQL Query Editor ��������������������������������������������������������������������������������������������������������������173 Other Features ���������������������������������������������������������������������������������������������������������������������174 SQL Server Management Studio �����������������������������������������������������������������������������������������������175 Object Explorer ��������������������������������������������������������������������������������������������������������������������176 T-SQL Query Editor ��������������������������������������������������������������������������������������������������������������179 Reports ��������������������������������������������������������������������������������������������������������������������������������180 Tools Built into the Engine���������������������������������������������������������������������������������������������������������181 System Tables and Catalog Views ���������������������������������������������������������������������������������������182 System Stored Procedures ��������������������������������������������������������������������������������������������������184 Dynamic Management Views ����������������������������������������������������������������������������������������������184 Extended Events ������������������������������������������������������������������������������������������������������������������196 T-SQL Performance Features�����������������������������������������������������������������������������������������������204 Query Store ��������������������������������������������������������������������������������������������������������������������������211 viii Table of ConTenTs DBCC Commands �����������������������������������������������������������������������������������������������������������������214 Trace Flags ��������������������������������������������������������������������������������������������������������������������������216 SSIS for ETL ������������������������������������������������������������������������������������������������������������������������������218 Creating a Package �������������������������������������������������������������������������������������������������������������219 Executing a Package �����������������������������������������������������������������������������������������������������������226 Go Further with SSIS �����������������������������������������������������������������������������������������������������������227 Summary�����������������������������������������������������������������������������������������������������������������������������������228 Chapter 6: Performance Capabilities ��������������������������������������������������������������������229 Performance Built In �����������������������������������������������������������������������������������������������������������������230 SQL Server Built-in Scalability ��������������������������������������������������������������������������������������������231 Dynamic Memory and Cache Management �������������������������������������������������������������������������235 Efficient I/O Processing �������������������������������������������������������������������������������������������������������240 Parallel Processing ��������������������������������������������������������������������������������������������������������������247 Configuration for Maximum Performance ���������������������������������������������������������������������������������249 SQL Server Instance Configuration ��������������������������������������������������������������������������������������250 Database Options ����������������������������������������������������������������������������������������������������������������255 Linux Kernel Configuration ��������������������������������������������������������������������������������������������������258 Tuning for Success ��������������������������������������������������������������������������������������������������������������������259 Files and File Groups �����������������������������������������������������������������������������������������������������������260 Indexes ��������������������������������������������������������������������������������������������������������������������������������270 Statistics ������������������������������������������������������������������������������������������������������������������������������280 Tips for Developers ��������������������������������������������������������������������������������������������������������������284 Accelerating Performance ��������������������������������������������������������������������������������������������������������288 Partitioned Tables and Indexes ��������������������������������������������������������������������������������������������288 Columnstore Indexes �����������������������������������������������������������������������������������������������������������294 In-Memory OLTP ������������������������������������������������������������������������������������������������������������������302 The Intelligent SQL Server Engine ��������������������������������������������������������������������������������������������311 Adaptive Query Processing ��������������������������������������������������������������������������������������������������311 Automatic Tuning �����������������������������������������������������������������������������������������������������������������313 Summary�����������������������������������������������������������������������������������������������������������������������������������316 ix Table of ConTenTs Chapter 7: Security in SQL Server ������������������������������������������������������������������������317 Logins and Users ����������������������������������������������������������������������������������������������������������������������318 Active Directory Authentication �������������������������������������������������������������������������������������������������323 How it Works �����������������������������������������������������������������������������������������������������������������������324 Setting it Up �������������������������������������������������������������������������������������������������������������������������326 Using AD Authentication ������������������������������������������������������������������������������������������������������328 Permissions and Access �����������������������������������������������������������������������������������������������������������328 Grant and Revoking Access �������������������������������������������������������������������������������������������������328 Roles and Permissions ��������������������������������������������������������������������������������������������������������329 Row Level Security ��������������������������������������������������������������������������������������������������������������336 Dynamic Data Masking ��������������������������������������������������������������������������������������������������������340 SQL Server and Encryption �������������������������������������������������������������������������������������������������������345 SQL Server Keys and Certificates ����������������������������������������������������������������������������������������346 Transparent Data Encryption �����������������������������������������������������������������������������������������������347 Encrypting Database Backups ���������������������������������������������������������������������������������������������348 Encrypting Connections �������������������������������������������������������������������������������������������������������349 Always Encrypted ����������������������������������������������������������������������������������������������������������������352 Encryption Summary �����������������������������������������������������������������������������������������������������������355 Data Classification and Auditing �����������������������������������������������������������������������������������������������355 Data Classification ���������������������������������������������������������������������������������������������������������������356 Vulnerability Assessment�����������������������������������������������������������������������������������������������������360 SQL Server Audit������������������������������������������������������������������������������������������������������������������362 Summary�����������������������������������������������������������������������������������������������������������������������������������367 Chapter 8: High Availability and Disaster Recovery for SQL Server ���������������������369 Backup and Restore ������������������������������������������������������������������������������������������������������������������370 Database Backup �����������������������������������������������������������������������������������������������������������������370 Database Restore and Recovery������������������������������������������������������������������������������������������381 Always On Failover Cluster Instance �����������������������������������������������������������������������������������������397 How It Works �����������������������������������������������������������������������������������������������������������������������398 Setup and Configuration ������������������������������������������������������������������������������������������������������401 sp_server_diagnostics and failover ������������������������������������������������������������������������������������403 x
Description: