Microsoft Azure SQL Database Step By Step
Microsoft Azure SQLDatabase Step by Step Leonard G. LobelEric D. Boyd
PUBLISHED BYMicrosoft PressA Division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399Copyright 2014 by Leonard G. Lobel and Eric D. BoydAll rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by anymeans without the written permission of the publisher.Library of Congress Control Number: 2014940679ISBN: 978-0-7356-7942-9Printed and bound in the United States of America.First PrintingMicrosoft Press books are available through booksellers and distributors worldwide. If you need support relatedto this book, email Microsoft Press Book Support at mspinput@microsoft.com. Please tell us what you think ofthis book at http://aka.ms/tellpress.Microsoft and the trademarks listed at roperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respectiveowners.The example companies, organizations, products, domain names, email addresses, logos, people, places, andevents depicted herein are fictitious. No association with any real company, organization, product, domain name,email address, logo, person, place, or event is intended or should be inferred.This book expresses the author’s views and opinions. The information contained in this book is provided withoutany express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, ordistributors will be held liable for any damages caused or alleged to be caused either directly or indirectly bythis book.Acquisitions Editor: Devon MusgraveProject Editor: Rosemary CapertonEditorial Production: Waypoint Press, www.waypointpress.comTechnical Reviewer: Scott Klein; Technical Review services provided by Content Master, a member ofCM Group, Ltd.Copyeditor: Roger LeBlancIndexer: Christina YeagerCover: Twist Creative Seattle and Joel Panchot
To my partner of 20 years, Mark, and our children, Adam, Jacqueline, Joshua,and Sonny. With all my love, I thank you guys, for all of yours.—Leonard LobelFor my loving wife, Shelly, and our wonderful boys, Jaxon and Xander.—Eric Boyd
This page intentionally left blank
Contents at a glanceIntroductionxiiiCHAPTER 1Getting started with Microsoft Azure SQL Database1CHAPTER 2Configuration and pricing31CHAPTER 3Differences between SQL Server and MicrosoftAzure SQL Database57CHAPTER 4Migrating databases63CHAPTER 5Security and backup97CHAPTER 6Cloud reporting123CHAPTER 7Microsoft Azure SQL Data Sync173CHAPTER 8Designing and tuning for scalability and highperformance217CHAPTER 9Monitoring and management261CHAPTER 10Building cloud solutions289Index357
This page intentionally left blank
ContentsIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiiiChapter 1Getting started with Microsoft Azure SQL Database1Cloud computing: The concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Instant dynamic provisioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2The Microsoft Azure cloud. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Getting signed up for SQL Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Creating a Microsoft account. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Creating a Microsoft Azure subscription. . . . . . . . . . . . . . . . . . . . . . . . 7Creating a server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8Creating a SQL Database instance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13Using the SQL Database management portal . . . . . . . . . . . . . . . . . . 15Designing tables and relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Inserting data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21Querying the database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24Exploring additional portal capabilities. . . . . . . . . . . . . . . . . . . . . . . . 27Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30Chapter 2Configuration and pricing31Using the Microsoft Azure platform management portal. . . . . . . . . . . . . . 31Creating a new database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Setting firewall rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Obtaining connection strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Deleting a database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Using SQL Server Management Studio. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Connecting to SQL Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Creating a new database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43What do you think of this book? We want to hear from you!Microsoft is interested in hearing your feedback so we can continually improve ourbooks and learning resources for you. To participate in a brief online survey, please visit:microsoft.com/learning/booksurveyvii
Changing the database edition and maximum size. . . . . . . . . . . . . . 44Deleting a database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44Using PowerShell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44Installing the Microsoft Azure PowerShell cmdlets. . . . . . . . . . . . . . 44Using the PowerShell Integrated Scripting Environment. . . . . . . . . 46Configuring PowerShell for your Microsoft account. . . . . . . . . . . . . 46Creating a new server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Creating a new database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48Deleting a database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50Budgeting for SQL Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50SQL storage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50Client bandwidth. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Backup storage space. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51Backup storage bandwidth. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53Optimizing your costs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54Configuring the database edition and size. . . . . . . . . . . . . . . . . . . . . 55Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56Chapter 3Differences between SQL Server andMicrosoft Azure SQL Database57Size limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Connection limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Unsupported features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62Chapter 4Migrating databases63Making the case for data migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Migrating data using Transact-SQL scripts. . . . . . . . . . . . . . . . . . . . . . . . . . . 64Setting up a local SQL Server database. . . . . . . . . . . . . . . . . . . . . . . . 64Creating the T-SQL scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67Generating T-SQL scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68SQL Data-Tier Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Creating a Microsoft Azure Storage account. . . . . . . . . . . . . . . . . . . 71Exporting a BACPAC to Microsoft Azure Storage . . . . . . . . . . . . . . . 74Importing a BACPAC to Microsoft Azure SQL Database. . . . . . . . . . 77viiiContents
SQL Server Bulk Copy (bcp). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80Migrating Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81Exporting data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Importing data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84SQL Database Migration Wizard. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Downloading the tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87Migrating a database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95Chapter 5Security and backup97Addressing major cloud concerns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97Security responsibilities of the public cloud vendor. . . . . . . . . . . . . 98Shared security responsibilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98Security in Microsoft Azure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Securing SQL Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Creating a SQL Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Configuring SQL Database Firewall. . . . . . . . . . . . . . . . . . . . . . . . . . . 101Authenticating and authorizing users . . . . . . . . . . . . . . . . . . . . . . . . 105Backing up SQL Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112Copying a database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112Monitoring the progress of a database copy operation. . . . . . . . . 113Exporting a BACPAC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Importing a BACPAC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117Scheduling BACPAC exports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122Chapter 6Cloud reporting123Creating a SQL Server Reporting services virtual machine. . . . . . . . . . . . 125Creating the virtual machine from the image gallery. . . . . . . . . . . 126Configuring SSRS in the virtual machine. . . . . . . . . . . . . . . . . . . . . . 128Opening firewall access to the report server . . . . . . . . . . . . . . . . . . 130Creating the sample database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Using Report Builder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135Installing Report Builder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135Creating a report using Report Builder. . . . . . . . . . . . . . . . . . . . . . . 137Contentsix
Using Visual Studio Report Server projects. . . . . . . . . . . . . . . . . . . . . . . . . 150Installing AdventureWorks2012 for SQL Database . . . . . . . . . . . . . 152Installing SSDT Business Intelligence for Visual Studio 2012. . . . . 154Creating a report using Visual Studio. . . . . . . . . . . . . . . . . . . . . . . . . 156Implementing report security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170Shutting down the SSRS virtual machine. . . . . . . . . . . . . . . . . . . . . . . . . . . 171Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .171Chapter 7Microsoft Azure SQL Data Sync173Getting to know SQL Data Sync. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Exporting data from SQL Server to SQL Database. . . . . . . . . . . . . . 175Importing data from SQL Database to SQL Server . . . . . . . . . . . . . 175Sharing data between multiple locations . . . . . . . . . . . . . . . . . . . . . 176Scaling out. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178Creating the SQL Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180Working with SQL Data Sync . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182Creating a sync group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182Creating sync rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188Running a manual sync. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191Establishing conflict resolution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196Creating an automated sync schedule. . . . . . . . . . . . . . . . . . . . . . . . 200Creating a local SQL Server database. . . . . . . . . . . . . . . . . . . . . . . . . 202Creating a sync agent. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204Pitfalls and best practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215Summary. . . . . . . . . . . . . . . . . . . .
Microsoft Azure SQL Database Step by Step Leonard G. Lobel Eric D. Boyd Cloud computing: The concept 1 Instant dynamic provisioning 2 The Microsoft Azure cloud 2 Getting signed up for SQL Database 5 Creating a Microsoft account 5 Creating a Microsoft Azure subscription 7 Creating a server 8 Creating a SQL Database instance 13
The Microsoft Azure SQL Data Sync plug-in on the Microsoft Azure Silverlight portal has been decommissioned. Going forward, use the Microsoft Azure Management portal, for Azure SQL Data Sync. You access SQL Data Sync (Preview) via the SYNC tab under SQL Database at the Microsoft Azure Management portal. The SYNC tab is only available when you .
Get the full name of the Azure Portal's Azure SQL Database Server. Use the full name of the server to connect to your Azure SQL server using client tools, including Data Migration Assistance and SQL Server Management Studio 1. Log in to the Azure Portal. 2. Select SQL Database from the menu on the left and click "Database" in the SQL .
D. SQL Server Agent job E. Azure SQL Data Sync Answer: E Explanation: SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi- directionally across multiple SQL databases and SQL Server instances. With Data Sync, you can keep data synchronized between your on-premises databases and Azure SQL
1 The Total Economic Impact Of Microsoft Azure SQL Database Managed Instance Executive Summary Microsoft Azure SQL Database Managed Instance is a new deployment option that provides organizations the ability to migrate their on-premises SQL Server applications and databases onto a fully managed SQL Server in the Azure cloud.
You will get a notice in the Windows Azure Portal once your database is ready. In order to connect to this database from outside Windows Azure (such as your workstation) additional steps will be required. See the StackOverflow documentation for Managing Windows Azure SQL Database Firewall Settings. Create a Windows Azure SQL Database with .
are supported on Azure HDInsight cluster. Furthermore, the DATA step and PROC SQL data preparation with bulk load are handled to save data efficient on the cloud. SAS AND AZURE SQL DATABASE Organizations can connect and access data from an Azure SQL Database using SAS/Access to Microsoft SQL Server from SAS.
Microsoft Azure interview questions and answers pdf that covers top questions to crack the Azure interview. This type of general questions are very common among Azure interview so don't miss the latest updates of SQL Azure services while preparing for Microsoft Azure interview. 19. When will the workload on SQL schedule get throttled?
Alex Rider: Never say Die by Anthony Horowitz Below are the complete reviews, written by the Lovereading4kids members. George Hutton - Dormston Secondary School Alex Rider receives a suspicious email from who could be Jack Starbright who was kidnapped on his previous mission. However, whilst trying to locate Jack, he accidentally manages to get tangled up in another MI6 Mission which could put .
