46,44 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Discover how you can migrate a traditional on-premise SQL server database to a cloud-based solution with Microsoft Azure. Built with database administrators in mind, this book emulates different scenarios you might come across while working with large, complex SQL database migrations and provides solutions for effectively managing the migrated databases.
Key Features
- Implement backup, restore, and recovery of Azure SQL databases
- Create shards and elastic pools to scale Azure SQL databases
- Automate common management tasks with PowerShell
- Implement over 40 practical activities and exercises across 24 topics to reinforce your learning
Book Description
As the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. It's important to know how to administer SQL Database to fully benefit from all of the features and functionality that it provides. This book addresses important aspects of an Azure SQL Database instance such as migration, backup restorations, pricing policies, security, scalability, monitoring, performance optimization, high availability, and disaster recovery. It is a complete guide for database administrators, and ideal for those who are planning to migrate from on premise SQL Server database to an Azure SQL Server database.
What you will learn
- Learn how to provision a new database or migrate an existing on-premise solution
- Understand how to backup, restore, secure, and scale your own Azure SQL Database
- Optimize the performance by monitoring and tuning your cloud-based SQL instance
- Implement high availability and disaster recovery procedures with SQL Database
- Develop a roadmap for your own scalable cloud solution with Azure SQL Database
Who this book is for
This book is ideal for database administrators, database developers, or application developers who are interested in developing or migrating existing applications with Azure SQL Database. Prior experience of working with an on-premise SQL Server deployment and brief knowledge of PowerShell and C# are recommended prerequisites.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 288
Veröffentlichungsjahr: 2018
Ähnliche
Table of Contents
Professional Azure SQL Database Administration
Professional Azure SQL Database Administration
Copyright © 2018 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 endeavoured 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.
Acquisitions Editors: Aditya Date, Bridget Neale
Content Development Editors: Naveenkumar Jain, Manasa Kumar
Production Coordinator: Samita Warang
First published: July 2018
Production reference: 1250718
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78953-885-4
www.packtpub.com
https://mapt.packtpub.com/
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit https://mapt.packtpub.com/ website.
Why Subscribe?
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.
Contributors
About the Author
Ahmad Osama works for Pitney Bowes India Pvt Ltd as a database engineer and is a Microsoft Data Platform Reconnect MVP. In his day to day job at Pitney Bowes, he works on developing and maintaining high performance on-premise and cloud SQL Server OLTP environments, building CI/CD environments for databases and automation.
Other than his day to day work, Ahmad blogs at "dataplatformlabs" and has written over 100 blogs on various topics, including SQL Server Administration/Development, Azure SQL Database, and Azure Data Factory. He regularly speaks at user group events and webinars conducted by the Dataplatformlabs community. You can reach him on LinkedIn at "ahmadosama3" or follow his twitter handle @_ahmadosama.
About the Reviewer
Mohit Gupta works as a Solutions Architect with Pitney Bowes India Pvt Ltd and is focused on Cloud technologies and DevOps methodologies. Mohit has more than 12 years of experience in Software Product Development and has worked on AWS and Azure technologies since 2012. Mohit has helped customers of all sizes design, architect, build, migrate, and manage their workloads and applications on various cloud-based products including AWS and Azure. He received his Bachelors of Technology from Kurukshetra University. Additionally, he holds many Industry-leading IT Certifications. Learn more about Mohit's work at his blogspot page "mogupta".
Packt is Searching for Authors Like You
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Preface
Azure SQL Database is a scalable, high performing SQL Server based Managed Cloud Database Service offering from Microsoft.
Though Azure SQL Database is the cloud version of SQL Server, it differs in terms of management, maintenance and administration. It's important to know how to administer SQL Database so that you can get the most out of the features that it provides. The book addresses different management aspects of an Azure SQL Database such as Migration, Backup and Restore, Pricing, Security, Scalability, Monitoring and Performance Optimization, High Availability, and Disaster Recovery.
After completing this book, you will be able to:
What This Book Covers
Chapter 1, Microsoft Azure SQL Database Primer, covers the fundamental architecture of Microsoft Azure SQL Database. We will provision an Azure SQL Database and learn to connect and query it. We will also discuss the differences between the on-premises SQL Server and Azure SQL Database.
Chapter 2, Migrating a SQL Server Databaseto an Azure SQL Database, discusses the features of the various Azure SQL Database service tiers that are available and the need for migration. We will talk about selecting the most suitable service tier, finding and fixing compatibility issues originating as a result of data migration, and selecting the most appropriate migration tool or method based on the scenario.
Chapter 3, Backing Up the Azure SQL Database, explains how to perform automatic and manual backups for Azure SQL Databases and discusses the benefits and strategies for each.
Chapter 4, Restoring an Azure SQL Database, covers the various restore options available on the Azure SQL Database. We will understand the features of each restore type, when and where they can be used, and how they are performed.
Chapter 5, Securing an Azure SQL Database, discusses various security mechanisms employable on the Azure SQL Database. We will talk about access control measures such as Firewall and Authentication and also cover proactive security measures, including Dynamic Data Masking, Auditing, and Threat Detection.
Chapter 6, Scaling Out Azure SQL Database, explains how to scale Azure SQL Databases, either vertically or horizontally, and how to shard a database. The lesson further talks about how to run cross-database queries.
Chapter 7, Elastic Pools, introduces the Elastic Pool and its benefits. We will understand how to effectively size an Elastic Pool, and will talk about the uses of Elastic Jobs, their architecture, workflow, and how to create them.
Chapter 8, High Availability and Disaster Recovery, covers the built-in High Availability feature in Azure SQL Database. This lesson also teaches you how to implement a Disaster Recovery solution using Standard and Active Geo-replication. We will also learn how to automate auto-failover groups using PowerShell.
Chapter 9, Monitoring and Tuning Azure SQL Database, describes different techniques for monitoring and tuning an Azure SQL database. We will learn how to monitor an Azure SQL Database using the Azure portal, Dynamic Management Views, and Extended Events. We will also talk about Query performance insight and about tuning an Azure SQL Database using Automatic tuning. Finally, we will learn to implement in-memory features to improve workload performance.
What You Need For This Book
You should have prior experience working on on-premise SQL Server. The know-how of PowerShell and C# is the only prerequisite for this book.
Hardware
For successful completion of this book, you will require the following:
Software
Download the example code files
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
The code bundle for the book is also hosted on GitHub at https://github.com/TrainingByPackt/Professional-Azure-SQL-Database-Administration. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Installation and Setup
Before you start with this book, we'll sign up to Azure. You will find the steps to sign up here:
Sign up for a Free Azure Account
Install SQL Server Management Studio
Installing Microsoft PowerShell 5.1
Installing Microsoft Azure PowerShell
Installing Microsoft RML Utilities
Installing Visual Studio Community Edition
Get in touch
Feedback from our readers is always welcome.
General feedback: Email <[email protected]> and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at <[email protected]>.
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at <[email protected]> with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
Chapter 1. Microsoft Azure SQL Database Primer
There are very few relational database systems as established and widespread as Microsoft's SQL Server. Azure SQL Database, released on February 1, 2010, is a cloud database service that is based on Microsoft's SQL Server.
It is compatible with most SQL Server features and is optimized for Software-as-a-Service (SaaS) applications.
As organizations are adopting cloud computing and moving their applications into the cloud, Azure SQL Database offers everything that a Database-as-a-Service can offer. Azure SQL Database becomes a de facto DBaaS option for any organization with applications built on the SQL Server Database.
By the end of this chapter, you will be able to:
Azure SQL Database uses familiar T-SQL programming and a user interface which is well known and is also easier to adopt. It is therefore important for SQL Server database administrators and developers to learn Azure SQL Database.
Note
Azure SQL Database is also known as SQL Azure or SQL Database instance.
This chapter covers the Azure SQL Database architecture in detail. After familiarizing yourself with the architecture, you'll learn how to provision Azure SQL Database by means of activities, explore pricing, settings, and its properties. You'll also identify the key differences between Azure SQL Database and SQL Server, mainly the SQL Server features that are not supported by Azure SQL Database.
Azure SQL Database Architecture
Azure SQL Database is a highly scalable multi-tenant and a highly available Platform-as-a-Service (PaaS) or DBaaS offering from Microsoft.
Microsoft takes care of the operating system (OS), storage, networking, virtualization, servers, installation, upgrades, infrastructure management, and maintenance.
Azure SQL Database allows users to focus only on managing the data, and is divided into four layers which work together to provide relational database functionality to the users, as shown in the following diagram:
Figure 1.1 The four layers of Azure SQL Database
Note
If you were to compare it to on-premises SQL Server architecture, other than the Service Layer, the rest of the architecture is pretty similar.
Note
Dynamic routing allows for moving the SQL Database to different physical servers in case of any hardware failures or load distribution.
Azure SQL Database Request Flow
Figure 1.2: Platform layer - nodes
The application sends a TDS request (login, DML, or DDL queries) to the SQL Database. The TDS request is not directly sent to the Platform layer. The request is first validated by the SQL Gateway Service at the Service layer.
The Gateway Service validates the login and firewall rules, and checks for denial of service attacks. It then dynamically determines the physical server on which the SQL database is hosted and routes the request to that physical server in the Platform layer. The dynamic routing allows SQL Database to be moved across physical servers or SQL instances in case of hardware failures.
Note
Here, a node is a physical server. A single database is replicated across three physical servers internally by Microsoft to help the system recover from physical server failures. The Azure SQL Server user connects to just a logical name.
Dynamic routing refers to routing the database request to the physical server which hosts an Azure SQL Database. This routing is done internally and is transparent to the user. If one physical server hosting the database fails, the dynamic routing will route the requests to the next available physical server hosting the Azure SQL Database.
Internals about dynamic routing are out of the scope of this book.
As shown in Figure 1.2, the Platform layer has three nodes: Node 1, Node 2, and Node 3. Each node has a primary replica of a SQL database and two secondary replicas of two different SQL databases from two different physical servers. The SQL database can fail over to the secondary replicas if the primary replica fails. This ensures high availability of the SQL Database.
Provisioning an Azure SQL Database
Provisioning an Azure SQL Database refers to creating a new and blank Azure SQL Database.
In this section, we'll create a new SQL database in Azure using the Azure portal:
Note
A Resource group is a logical container that is used to group Azure resources required to run an application. For example, say, toystore retail web application uses different Azure resources such as Azure SQL Database, Azure VMs, and Azure Storage Account. All of these resources can be grouped in a single Resource group, say, toystore.
The SQL Database name should be unique across Microsoft Azure and should be as per the following naming rules and conventions: https://docs.microsoft.com/en-us/azure/architecture/best-practices/naming-conventions
Select the Server option to create an Azure SQL Server. You can also opt to create the database in an existing Azure SQL Server:In the Server pane, select Create a new server. In the New server pane, provide the following details and click Select at the bottom of the New server pane:The server name should be unique across Microsoft Azure and should be as per the following naming rules and conventions:
https://docs.microsoft.com/en-us/azure/architecture/best-practices/naming-conventions
Under the Want to use SQL elastic pool? option, select Not now.Under the pricing tier option, select Standard:Leave the Collation as default. Select the Create button to provision a:Provisioning may take 2-5 minutes. Once the resources are provisioned, you'll get a notification, as shown in the following screenshot:
Select Go to resource to go to the newly created SQL Database.Connecting and Querying the SQL Database from the Azure Portal
In this section, we'll learn to connect and query the SQL Database from Azure portal:
Connecting and Querying the SQL Database from SQL Server Management Studio
In this section, we'll connect and query an Azure SQL Database from SQL Server Management Studio (SSMS):
Note
The virtual network can be used to add a SQL database in Azure to a given network. A detailed explanation of virtual networks is out of the scope of this book.
Note
To open a new query window in the master database context, in Object Explorer, expand Databases then expand System Databases. Right-click on master database and select New Query.
You should get the following output:
Deleting Resources
To delete Azure SQL Database, Azure SQL Server, and Azure Resource group, complete the following steps:
Note
All resources must be deleted to successfully complete the activity at the end of this chapter.
Note
To only delete Azure SQL Database, check the Azure SQL Database checkbox.
Differences between Azure SQL Database and SQL Server
Azure SQL Database is a PaaS offering and therefore some of the features differ from the on-premises SQL Server. Some of the important features which differ from on-premises SQL Server are:
Backup and Restore
Conventional database backup and restore statements aren't supported. The backups are automatically scheduled and start within a few minutes of the database provisioning. The backups are transactionally consistent, which means that you can do a point-in-time restore.
There is no additional cost for backup storage until the backup storage goes beyond 200% of the provisioned database storage.
You can reduce the backup retention period to manage the backup storage cost. You can also use the long-term retention period feature to store the backup in the Azure vault for a much smaller cost for a longer duration.
Other than the automatic backups, you can export the Azure SQL Database bacpac or dacpac file to Azure storage.
Recovery Model
The default recovery model of an Azure SQL Database is Full and it can't be modified to any other recovery models as in on-premises recovery models.
The recovery model is set when the master database is created, meaning when an Azure SQL Server is provisioned. The recovery model can't be modified because the master database is read-only.
To view the recovery model of an Azure SQL Database, execute the following query:
Note
You can use any of the two methods discussed earlier in the chapter to run the query – the Azure portal or SSMS.
You should get the following output:
SQL Server Agent
Azure SQL Server doesn't have SQL Server Agent, which is used to schedule jobs and send success/failure notifications. However, you can use the following workarounds:
