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:
Explore the different configuration and pricing options available for Azure SQL Database
Provision a new Azure SQL Database or Migrate an existing on-premise SQL Server database to Azure SQL Database
Backup and restore Azure SQL Database
Secure and scale an Azure SQL Database
Monitor and tune an Azure SQL Database
Implement High Availability and Disaster Recovery with Azure SQL Database
Automate common management tasks with PowerShell
Develop a scalable cloud solution with Azure SQL Database
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 Database to 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.
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.
For successful completion of this book, you will require the following:
Processor: 1.8 GHz or higher Pentium 4 (or equivalent)
Memory: 2 GB RAM
Hard disk: 10 GB free space
Internet connection
This book is for database administrators, database developers, and application developers interested in developing new applications or migrating existing applications with Azure SQL Database.
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "We will create
src/app/about.component.ts as a simple component that does not have any interactions."
Folder names, filenames, file extensions, pathnames, include file names in text are shown as follows: "To remove an element from an array, use the
unset function."
A block of code is set as follows:
// x.ts
import { one, add, Lorem } from './y';
console.log(add(one, 2));
var lorem = new Lorem();
console.log(lorem.name);New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "So, when we click on the Submit button, the data will be submitted."
Important new programming terms are shown in bold. Conceptual terms are shown in italics.
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:
Log in or register at www.packtpub.com.
Select the SUPPORT tab.
Click on Code Downloads & Errata.
Enter the name of the book in the Search box and follow the onscreen instructions.
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 LinuxPreface
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!
Before you start with this book, we'll sign up to Azure. You will find the steps to sign up here:
Open the following link in a browser: https://azure.microsoft.com/en-us/free/
Select Start Free. In the sign-up page, login using your Microsoft account and follow the steps to create a free Azure account.
If you don't have a Microsoft account, you can create a new one by selecting the Create one!
An Azure account requires you to provide credit card details. However, no money is charged even if you have exhausted your free credit or free month. The resources you created will be stopped and can only be started once you sign-up for a paid account.
Follow the instructions to download the latest version of SQL Server Management studio provided here: - https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
Open the following URL in the browser to get to open the download page - https://www.microsoft.com/en-us/download/details.aspx?id=54616
Click on the download button and select Win8.1AndW2K12R2-KB3191564-x64.msu. Click on next to download and the install the Windows Management Framework 5.1 to upgrade to PowerShell 5.1
Install the Web Platform installer
Open the following link in a browser https://www.microsoft.com/web/downloads/platform.aspx
Scroll to the end and select x64 under Download WebPI 5.0 to download the web platform installer.
Double click on the downloaded exe file and follow the steps to install Web PI 5.0
Open Web PI and type Microsoft Azure PowerShell in the search box. Follow the steps to download the latest version.
Open the following link in a browser - https://www.microsoft.com/en-in/download/details.aspx?id=4511
Click on download to download the installer. Click on the downloaded file and follow the instructions to install RML Utilities.
Visual Studio command prompt is required to generate self-signed certificates. As there's no easy way to install only the Visual Studio command prompt it's advised to install the Visual Studio 2013 or above community edition.
Open the following link in a browser - https://www.visualstudio.com/downloads/
Download and install the Visual Studio 2017 community edition and follow the instructions to install it.
You may get a different version to download. The latest Visual Studio version available at the time of the book is 2017.
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.
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.