37,19 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Sprache: Englisch
Bring the performance and security of SQL Server to Linux
About This Book
- Design and administer your SQL Server solution on the open source Linux platform
- Install, configure, and fine-tune your database application for maximum performance
- An easy-to-follow guide teaching you how to implement various SQL Server CTP 2.x offerings on Linux—from installation to administration
Who This Book Is For
This book is for the Linux users who want to learn SQL Server on their favorite Linux distributions. It is not important if you are experienced database user or a beginner as we are starting from scratch. However, it is recommended that you have basic knowledge about relational models. More advanced readers can pick the chapters of their interest and study specific topics immediately. Users from Windows platform can also benefit from this book to expand their frontiers and become equally efficient on both platforms.
What You Will Learn
- Install and set up SQL Server CTP 2.x on Linux
- Create and work with database objects using SQL Server on Linux
- Configure and administer SQL Server on Linux-based systems
- Create and restore database back-ups
- Protect sensitive data using the built-in cryptographic features
- Optimize query execution using indexes
- Improve query execution time by more than 10x using in-memory OLTP
- Track row-versioning using temporal tables
In Detail
Microsoft's launch of SQL Server on Linux has made SQL Server a truly versatile platform across different operating systems and data-types, both on-premise and on-cloud.
This book is your handy guide to setting up and implementing your SQL Server solution on the open source Linux platform. You will start by understanding how SQL Server can be installed on supported and unsupported Linux distributions. Then you will brush up your SQL Server skills by creating and querying database objects and implementing basic administration tasks to support business continuity, including security and performance optimization. This book will also take you beyond the basics and highlight some advanced topics such as in-memory OLTP and temporal tables.
By the end of this book, you will be able to recognize and utilize the full potential of setting up an efficient SQL Server database solution in your Linux environment.
Style and approach
This book follows a step-by-step approach to teach readers the concepts of SQL Server on Linux using the bash command line and SQL programming language trough examples which can easily be adapted and applied in your own solutions.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 203
Veröffentlichungsjahr: 2017
Ähnliche
BIRMINGHAM - MUMBAI
SQL Server on Linux
Copyright © 2017 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: August 2017
Production reference: 1100817
ISBN 978-1-78829-180-4
www.packtpub.com
Credits
Author
Jasmin Azemović
Copy Editor
Safis Editing
Reviewer
Marek Chmel
Project Coordinator
Nidhi Joshi
Commissioning Editor
Amey Varangaonkar
Proofreader
Safis Editing
Acquisition Editor
Tushar Gupta
Indexer
Pratik Shirodkar
Content Development Editor
Cheryl Dsa
Graphics
Tania Dutta
Technical Editor
Prasad Ramesh
Production Coordinator
Melwyn Dsa
About the Author
Jasmin Azemović is a university professor active in the database systems, information security, data privacy, forensic analysis, and fraud detection fields. His PhD degree was in modeling design and developing an environment for the preservation of privacy inside database systems. He is the author of many scientific research papers and two books: Writing T-SQL Queries for Beginners Using Microsoft SQL Server 2012 and Securing SQL Server 2012. He has been a Microsoft MVP (Data Platform) for the last 10 years and an information security consultant. He is an active speaker at many IT professional and community conferences.
About the Reviewer
Marek Chmel is an IT consultant and trainer with more than 10 years' experience. He is a frequent speaker, with a focus on Microsoft SQL Server, Azure, and security topics. Marek writes for Microsoft's TechnetCZSK blog and since 2012 he has been an MVP (Data Platform). He has also been recognized as a Microsoft Certified Trainer: Regional Lead for the Czech Republic for a few years in a row. He holds many MCSE certifications, he's also an EC Council Certified Ethical Hacker and holder of several eLearnSecurity certifications. Marek earned his MSc in business and informatics from Nottingham Trent University. He started his career as a trainer for Microsoft server courses. Later, he joined AT&T as a senior database administrator, with a specialization in MSSQL Server, data platforms, and machine learning.
www.PacktPub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Fully searchable across every book published by Packt
Copy and paste, print, and bookmark content
On demand and accessible via a web browser
Customer Feedback
Thanks for purchasing this Packt book. At Packt, quality is at the heart of our editorial process. To help us improve, please leave us an honest review on this book's Amazon page at https://www.amazon.com/dp/1788291808.
If you'd like to join our team of regular reviewers, you can e-mail us at [email protected]. We award our regular reviewers with free eBooks and videos in exchange for their valuable feedback. Help us be relentless in improving our products!
Table of Contents
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
Linux Distributions
Supported Linux distributions
openSUSE
Installation procedure
Kubuntu
Installation procedure
Summary
Installation and Configuration
Bash, really quick start
SQL Server installation on openSUSE
SQL Server installation on Kubuntu
Summary
SQL Server Basics
Overview of SQL Server
Client-server architecture concepts
SQL Server components
How it works on Linux
SQL Server objects
System databases
Database objects
SQL Server data types
SQL/T-SQL basics
History of SQL/TSQL
Types of SQL statements
DDL - Data Definition Language
DCL - Data Control Language
DML - Data Manipulation Language
Working environments and tools
sqlcmd
bcp
Visual Studio code
SQL Server Management Studio (SSMS)
Summary
Database in the Sandbox
DDL statements
Creating a new database
Creating new tables
DML statements
Data manipulation
Changing table definition
Dropping a table
Creating other database objects
Creating views
Creating stored procedures
Creating triggers
Summary
Sample Databases
Relational database concepts
Normalization
First normal form
Second normal form
Third normal form
Northwind database
Pubs database
AdventureWorks database
Installing AdventureWorks
WideWorldImporters database
Summary
A Crash Course in Querying
Retrieving and filtering data
Retrieving data from a table
String functions
Exercise
Filtering data
Comparison operators
String comparison
Logical operators
Working with NULL values
Manipulating query output
Overview of aggregate functions
Aggregate functions and NULL values
GROUP BY clause
HAVING clause
JOIN operators
INNER JOIN
OUTER JOIN
Multiple joins
Summary
Backup and Recovery Operations
SQL Server recovery models
Simple recovery model
Full recovery model
Bulk-logged recovery model
How transaction log works
Elements of backup strategy
Who can create backups?
Backup media
Types of backup
Full database backups
Transaction log backups
Differential backups
Backup and restore
Summary
User Management
Authentication process
Authorization process
Accessing SQL Server resources
Server-level permissions
Database-level permissions
Schema separation
Summary
Implementing Data Protection
Crash course in cryptography
Symmetric cryptography
Asymmetric cryptography
What is a key?
SQL Server cryptographic elements
T-SQL functions
Certificates
Service Master Key
Database master key
Transparent Data Encryption
Backup encryption
Symmetric encryption
Row-level security
Dynamic data masking
Summary
Indexing
Indexing concepts
Accessing the data
Index structure
Single and composite indexes
Ascending and descending indexes
Clustered index
What is a heap
Non-clustered index
Unique indexes
Columnstore index
Summary
In-Memory OLTP
Elements of performance
The good
The bad
The ugly
What is In-Memory OLTP?
In-Memory OLTP quick start
How to create memory-optimized tables?
What is natively compiled stored procedure?
Summary
Beyond SQL Server
Query store
Temporal tables
Mssql-scripter tool
DBFS tool
DBeaver – third party tool
Summary
Conclusion
Preface
Microsoft now loves Linux and part of its new exciting strategy is the availability of SQL Server as one of the best database platforms on all major Linux distributions. This book will be your quick-start guide to this new uncharted space. You will recognize and understand the full potential of a new database environment in the Linux world. At this point, the technology is pretty fresh and getting to know things now in this early phase will be to your strategic advantage.
What this book covers
Chapter 1, Linux Distributions, covers the basics of supported Linux distributions. You will learn about openSUSE and Ubuntu distributions and will be prepared for the installation procedure.
Chapter 2, Installation and Configuration, covers key points about the installation and initial configuration of SQL Server on Linux. You will learn how to install SQL Server on Linux on the openSUSE and Ubuntu distributions.
Chapter 3, SQL Server Basics, is about the SQL Server architecture and concepts, and how they differ from other database platforms.
Chapter 4, Database in the Sandbox, is about using DDL and DML sets of SQL statements in the SQL Server environment. You will learn how to create, modify, and delete a database and its objects, and how to implement INSERT, SELECT, DELETE, and update statements.
Chapter 5, Sample Databases, teaches you how to find and install SQL Server samples. You will learn how to restore a sample database and browse through its structure and objects.
Chapter 6, A Crash Course in Querying, shows you how to write efficient queries on SQL Server samples. The readers will learn to write SELECT statements on one or more tables in combination with different sets of operators and functions.
Chapter 7, Backup and Recovery Operations, is about understanding and creating backup procedures inside your database environment. The process of restoring is equally as important as backing up. This chapter will also teach you how to create and implement an efficient restore procedure.
Chapter 8, User Management, focusses on the user management process using the security features of SQL Server. You will learn how to implement new logins and map procedures to specific assets on SQL Server.
Chapter 9, Implementing Data Protection, is focused on data protection using the built-in support for encryption. If you want to prevent and minimize the consequences of data breaches, then this is the right chapter for you.
Chapter 10, Indexing, explains how to use different types of indexing to achieve better performance for your SQL code. The reader will learn how to recognize bottlenecks inside databases and apply the appropriate indexing methods.
Chapter 11, In-Memory OLTP, shows the readers how to implement different types of in-memory OLTP features.
Chapter 12, Beyond SQL Server, takes you on a beautiful journey through the rest of the features and tools that can be used in SQL Server development.
What you need for this book
This book is a highly practical guide for SQL Server on Linux. We focus on how to get things up-and-running, whether or not you have any prior SQL Server or Linux experience. To achieve the full potential and get the maximum benefits from this book, you will need one of these Linux distributions: Ubuntu 16.04 or Ubuntu 17.04, openSUSE Leap 42.2 or openSUSE Tumbleweed, Kubuntu 16.04 or 17.04, Red Hat Enterprise Linux 7.3, SUSE, or Linux Enterprise Server v12 SP2. Last but not least, you will need to install SQL Server on Linux. At the time of writing, the last actual version was SQL Server on Linux RC1. I recommend the Developer edition. We have tried to keep all the code as user-friendly and readable as possible. We feel that this will enable our readers to easily understand it, and readily use it, in different scenarios.
Who this book is for
This book is for the Linux users who want to learn SQL Server on their favorite Linux distributions. It is not important if you are experienced database user or a beginner as we are starting from scratch. However, it is recommended that you have basic knowledge about relational models. More advanced readers can pick the chapters of their interest and study specific topics immediately. Users from Windows platform can also benefit from this book to expand their frontiers and become equally efficient on both platforms..
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book-what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of. To send us general feedback, simply e-mail [email protected], and mention the book's title in the subject of your message. If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for this book from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files e-mailed directly to you. You can download the code files by following these steps:
Log in or register to our website using your e-mail address and password.
Hover the mouse pointer on the
SUPPORT
tab at the top.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box.
Select the book for which you're looking to download the code files.
Choose from the drop-down menu where you purchased this book from.
Click on
Code Download
.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR / 7-Zip for Windows
Zipeg / iZip / UnRarX for Mac
7-Zip / PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/SQL-Server-on-Linux. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title. To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy. Please contact us at [email protected] with a link to the suspected pirated material. We appreciate your help in protecting our authors and our ability to bring you valuable content.
Questions
If you have a problem with any aspect of this book, you can contact us at [email protected], and we will do our best to address the problem.
Linux Distributions
Welcome to the exciting new journey of our old friend in a new environment. Yes, we are talking about one of the best database platforms in the world. SQL Server is well known in the domain of the Windows operating system, whether we speak of small, medium, or enterprise-size businesses. Cloud computing pushes those limits even further in the directions of big data analytics and data science. SQL Server has it all.
Linux, or to be precise GNU/Linux, is one of the best alternatives to Windows and, in many cases, it is the first choice of environment for daily tasks such as system administration, running different kinds of services, or just a tool for desktop application. Linux, which is the actual name for a kernel, was originally developed in 1991 by Linus Torvalds as his response to the MINIX operating system, which was limited to educational use. It was quickly recognized by the open source community, adopted, and packaged in many distributions. It's fascinating how large that number is. I believe that you've heard or read at least one name in the following list (the list is arranged by date (1993 - 2013)): Debian, Slackware, SUSE Linux, Red Hat Linux, CentOS, Fedora, Ubuntu, Tails, Kali Linux, and many more. Today, GNU/Linux does not hold a large chunk of the desktop operating system market. That fact is changing and many people, organizations, businesses, and even states are embracing this technology. But, if we talk about the server market, the situation is quite the opposite. Linux is holding a large share of the market.
However, Linux territory was a no-go for Microsoft products for a long time to be precise, from the beginning. Now, Microsoft loves Linux. A couple of years ago, this sentence would've sounded like a bad science fiction scenario. Fortunately, Microsoft has changed and become friendly to open source and free software philosophies. One of the reasons for this 180 degree change is cloud computing. New paradigms simply don't push old platform limitations to the background. Everything is a service now and it is not important where that service (Windows/Linux/Unix) is. The fact that Microsoft is pushing its core product on a couple of major Linux distributions speaks for itself.
However, one segment of the database ecosystem was out of Microsoft's reach. Linux was mostly reserved for open source representatives such as MySQL, PostgreSQL, and MariaDB, or proprietary ones such as IBM's DB2 and Oracle. But now it is time to change those facts. We can say that Microsoft is officially offering their data platform flagship as options and choice for database professionals in the Linux world. There are varied kinds of responses to this subject, from totally negative to positive comments. My opinion is that this is good thing. SQL Server is one of the best database environments; let's give them a chance to prove it on the ground. The Linux ecosystem has become richer and end users have more choices to pick the right solution for their needs.
This is not a book about Linux distributions, nor is it about Linux internals. Here, you will not find any Linux command line reference, but you will find a lot of useful information on how to deal with SQL Server on Linux.
This chapter targets DBAs, developers, and everyone else from the Windows playground. If you are from this group of users and you want to try out SQL Server in Linux land but don't know how to start, this is the chapter for you. If you are a Linux user with experience in your favorite distribution or different kinds of distributions, you can skip this chapter and go straight to the installation part.
In this chapter we will cover the following topics:
Supported Linux distributions
Installation of Linux operating system
Supported Linux distributions
When this book was written, SQL Server on Linux supported all major distributions:
Commercial
:
Red Hat Enterprise Linux 7.3 Workstation, Server, and Desktop
Commercial
: SUSE Enterprise Linux Server v12 SP2
Free
: Ubuntu 16.04 LTS and 16.10
Red Hat Enterprise Linux and SUSE Enterprise Linux are commercial versions of popular open source and free distributions. So, this list can be easily expanded to include the following distributions:
openSUSE Leap/Tumbleweed
Fedora
The two are not officially listed in the documentation but they work just fine, the same as the commercial versions. If you don't have any Linux experience whatsoever, I recommend Ubuntu or openSUSE. These two distributions will be covered in this chapter, specifically how to install them and prepare for SQL Server installation.
openSUSE
This distribution has a long history (https://www.suse.com/company/history/). The journey started under the name of SUSE Linux in 1994. Later, in 2003, Novel bought SUSE and its brand and trademark. The company recognized the importance of the community and created openSUSE, preserving the open source philosophy.
Novel was acquired by The Attachmate Group in 2011 and SUSE became an independent part of the company business. In a nutshell, SUSE has two product lines:
Commercial
:
SUSE Enterprise Server
(
SLES
)
openSUSE
: an independent community project based on SLES source code
Microsoft officially supports SLES, but openSUSE is in that train also.
Installation procedure
Let's start with the installation of openSUSE Leap 42.2, stable release. I will use a Hyper-V virtualization environment, but any other, such as VMWare or Oracle Virtual Box, will be just fine. If you are planning to install a native Linux environment without virtualization, the steps are the same. The installation of ISO can be found at https://www.opensuse.org/. You can mount ISO directly inside your virtual machine, create a bootable USB drive, or burn a DVD. The choice is yours.
The steps for the installation of openSUSE Leap 42.2 are as follows:
The welcome screen will show up after the initial boot procedure from USB drive, DVD ROM, or ISO image directly as shown in the following screenshot.
In the next step, you can choose to book from the hard drive, start a fresh installation of openSUSE, run an upgrade process of an old installation, or initialize some advanced steps. We will choose
Installation
.
Now, we pick the installation language and keyboard layout as shown in the following screenshot. You can test specific language characters if you come from non-English region. The license agreement is also there. Click
Next
after you are satisfied with your choices.
