Packt+ | Advance your knowledge in tech

You're reading from Professional Azure SQL Database Administration

Product typeBook

Published inJul 2018

Reading LevelBeginner

PublisherPackt

ISBN-139781789538854

Edition1st Edition

Languages

SQL

Tools

Azure

Concepts

Database Administration

Author (1)

Ahmad Osama

Chapter 2. Migrating a SQL Server Database to an Azure SQL Database

Migrating an on-premises SQL Server Database is an important task and should be planned to perfection. An ideal migration methodology should be as shown in the following diagram:

Figure 2.1: Migration Methodology

By the end of this chapter, you will be able to:

Select a service tier for your migrated Azure SQL Database based on your needs
Explain Database Transaction Units
Identify and fix SQL Server to Azure SQL Database compatibility issues
Migrate from on-premises database to Azure SQL Database using different tools

Finding the Migration Benefits

You should first analyze and find out the benefits of migrating an on-premises SQL Database to an Azure SQL Database. Migration involves a lot of time, effort, and cost, and it shouldn't be done just for the sake of having a cloud database.

Finding the Blockers

The next step is to find out the compatibility issues which may stop you from migrating to Azure SQL Database.

Selecting a Service Model

The next step is to find out whether the database will be deployed individually or whether it will be part of an Elastic Pool. This is important as the service model will affect the overall pricing, service tier, performance, and management of the Azure SQL Database.

Selecting a Service Tier

The next step is to find an appropriate service tier and performance level for Azure SQL Database. This is important as it will directly affect the performance of an Azure SQL Database. A lower service tier will result in bad performance, and a higher service tier will result in higher cost.

Selecting the Main and Disaster Recovery Region

The next step is to find the main region and the disaster recovery region for your Azure SQL Database. It's advisable to have the database in a region that would provide fast connectivity to your users.

Selecting a Migration Tool

Microsoft provides various tools to automate database migration. You can also write PowerShell or C# script to automate the database migration process. Tool selection largely depends on the database's size and the downtime SLA.

Azure SQL Database Service Tiers

Azure SQL Database has four different service tiers which define the size, performance, features, and, most importantly, the cost of an Azure SQL Database. These service tiers are Basic, Standard, Premium, and Premium RS:

Note

The Database Transaction Unit is the measure of Azure SQL Database Performance. This is discussed later in the chapter.

Basic Service Tier

The Basic tier is the lowest tier available and is applicable to small, infrequently used applications, usually supporting one single active transaction at any given point in time.

The Basic tier has a size limit of 2 GB, a performance of 5 DTU, and costs $5/month:

Standard Service Tier

This is the most commonly used service tier and is best for web applications or workgroups with low to medium I/O performance requirements. Unlike the Basic service tier, it has four different performance levels: S0, S1, S2, and S3. Each performance level offers the same size (250 GB), however, they differ in terms...

Determining an Appropriate Service Tier

As a SQL Server DBA, when migrating to an Azure SQL Database, you will need to have an initial estimate of DTUs so as to assign an appropriate Service tier to an Azure SQL Database. An appropriate Service Tier will ensure that you met most of your application performance goals. Estimating a lower or a higher Service tier will result in decreased performance or increased cost, respectively.

This chapter teaches you how to use the DTU calculator to make an appropriate initial estimate of the Service tier. You can, at any time, change your service tier by monitoring the Azure SQL Database performance once it's up and running.

Azure SQL Database DTU Calculator

Developed by Justin Henriksen, an Azure Solution Architect at Microsoft, the DTU Calculator can be used to find out the initial Service tier for an Azure SQL Database. The calculator is available at https://dtucalculator.azurewebsites.net.

DTU Calculator Work Flow

The DTU Calculator works as shown...

Determining Compatibility Issues

Once you have finalized the Service tier, the next step is to migrate both schema and data from the on-premises SQL Database to the Azure SQL Database. As we learned in Chapter 1 Microsoft Azure SQL Database Primer, not all features are the same and supported on the Azure SQL Server. Therefore, you will first have to do a compatibility test or assessment to find and fix the compatibility issues.

The following are the available tools to detect compatibility issues. However, these tools can be used to migrate the database as well; in this section, though, we'll talk about assessing compatibility.

Data Migration Assistant

Data Migration Assistant (DMA) is a standalone tool for detecting compatibility issues and to migrate on-premises SQL Server Databases to Azure SQL Databases. It provides a wizard-type easy-to-use graphical user interface for compatibility assessment and migration.

It detects and highlights compatibility issues. Once all of the compatibility...

Determining the Migration Method

Once you find and fix compatibility issues, the next step is to select a migration tool or method and perform the actual migration. There are different methods available for various scenarios. The selection largely depends on downtime, database size, and network speed/quality.

Here's a comparison of various migration methods to help you correctly choose a migration method:

Summary

Migrating to an Azure SQL Database is an important task and should be planned to perfection. This chapter talked about a migration strategy that you should follow when migrating from an on-premises database to an Azure SQL Database:

Find the migration benefits.
Find the blockers.
Select a service model.
Select a service tier.
Select the main and disaster recovery region.
Select a migration tool.

This chapter also talked about how to choose a service tier and covered different migration tools to migrate the data and the schema from an On-Premies SQL Server Database to an Azure SQL Database.

The rest of the chapter is locked

You have been reading a chapter from

Professional Azure SQL Database Administration

Published in: Jul 2018Publisher: PacktISBN-13: 9781789538854

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Author (1)

Ahmad Osama

Ahmad Osama works for Pitney Bowes Pvt. Ltd. as a technical architect and is a former Microsoft Data Platform MVP. In his day job, he works on developing and maintaining high performant, on-premises and cloud SQL Server OLTP environments as well as deployment and automating tasks using PowerShell. When not working, Ahmad blogs at DataPlatformLabs and can be found glued to his Xbox.
Read more about Ahmad Osama

Other recommended products

Related to this chapter

Professional Azure SQL Database Administration

This book is your one-stop solution to learning all that is needed to 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.

BookJul 2019562 pages

Professional Azure SQL Managed Database Administration

Whether it is learning different techniques to monitor and tune an Azure SQL database or improving performance using in-memory technology, this book will enable you to make the most out of Azure SQL database features and functionality for data management solutions.

BookMar 2021724 pages

Azure Data Engineering Cookbook

This book will help you design and implement modern ETL workflows along with data management, monitoring, and security aspects to meet the current organization's needs. You will use various services such as Azure Data Factory, Azure Databricks, Azure Stream Analytics, and Azure Data Explorer to design efficient data processing solutions.

BookApr 2021454 pages

Professional SQL Server High Availability and Disaster Recovery

The Professional SQL Server High Availability and Disaster Recovery book explains the high availability and the disaster recovery technologies, their technical implementation, and different topologies that you can use when creating a highly available infrastructure with hybrid topologies.

BookJan 2019564 pages

Learning Microsoft Azure Storage

Microsoft Azure Storage is the bedrock of Microsoft's core storage solution offering in Azure. No matter what solution you are building for the cloud, you'll find a compelling use for Azure Storage. This book will help you get up-to-speed quickly on Microsoft Azure Storage by teaching you how to use the different storage services. You will be able to leverage secure design patterns based on real-world scenarios and develop a strong storage foundation for Azure virtual machines.

BookNov 2017276 pages

Learn T-SQL Querying

T-SQL is an extension of the SQL language which allows you to tackle advanced querying and query-tuning challenges in SQL Server and Azure SQL Database. This book will be a perfect reference for you to write more efficient T-SQL code to perform simple-to-advanced tasks for data management and data analysis.

BookMay 2019484 pages

Learn Microsoft Azure

Microsoft Azure is a cloud computing platform that helps you build, deploy, and manage applications to overcome your business challenges. This book covers the commonly used Azure services and also explains how you effectively integrate and utilize them.

BookDec 2018354 pages

Hands-On Cloud Administration in Azure

Cloud offers new opportunities and more and more features every day. All services hosted in local Data Centers are now available in Azure. In this book, we’ll show you how to work in Azure and how to use Azure resources to your advantage.

BookOct 2018390 pages

SQL Server 2019 Administrator's Guide

This book will give you all the information you need to become an expert database administrator and master the administrative aspects of SQL Server 2019. From setting up and configuring your SQL Server instance to fine-tuning your database, this extensive guide will teach you the nitty-gritty of SQL Server 2019 administration.

BookSep 2020522 pages

Implementing Microsoft Azure Architect Technologies: AZ-303 Exam Prep and Beyond

Microsoft Azure Architect - Exam Guide AZ-303 is a complete guide to mastering topics related to Azure Architect practices. With detailed coverage of concepts and techniques, you’ll gain the knowledge and skills required to take and pass the AZ-303 exam and become a Microsoft Azure Architect expert.

BookDec 2020548 pages

SQL Server 2017 Administrator's Guide

This book will give you all the information you need to become an expert database administrator, and master the administrative aspects of SQL Server 2017. From setting up and configuring your SQL Server instance to fine-tuning your database, this extensive guide will teach you the nitty-gritty of SQL Server 2017 administration.

BookDec 2017434 pages

SQL Server on Azure Virtual Machines

This book will teach you how to harness the capabilities of SQL Server and the cloud database by taking you through the steps to deploy the server in Azure virtual machine effectively.

BookJun 2020200 pages

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages

Migration Method	Description	Downtime	Database Size
SQL Server Management Studio – Deploy Database to Azure SQL Database	Wizard-based GUI to export on-premises database to `bacpac` and import the `bacpac` onto Azure SQL Database.	Yes (depends on database size)	Small to Medium databases
Sqlpackage.exe	Command-line utility to export on-premises databases to `bacpac` and import the `bacpac` on to Azure SQL Database.	Yes (Depends on database size)	Small to Medium databases
Manual (Dacpac and BCP)	Use `sqlpackage.exe` to export `dacpac` (only schema) and bcp out data in a folder...