Four-Project Series

Migrate SQL Server to Azure you own this product

prerequisites
basic SQL scripting skills • working knowledge of SQL Server Management Studio, the Azure portal, Azure Data Studio, and PowerShell
skills learned
use dbatools, dbachecks, and Pester to help configure and migrate a database server • use Azure Database Migration Service • create a migration checklist with a text or Markdown editor • create an Azure Managed Instance • configure auditing and alerting • review Center for Internet Security (CIS) benchmarks
Josephine Bush & Gilda Alvarez
4 weeks · 5-7 hours per week average · INTERMEDIATE

pro $24.99 per month

  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose one free eBook per month to keep
  • exclusive 50% discount on all purchases

lite $19.99 per month

  • access to all Manning books, including MEAPs!

team

5, 10 or 20 seats+ for your team - learn more


As the database administrator for Magic Wand Services Corporation it’s your job to migrate an on-premises SQL Server database to an Azure SQL Managed Instance, a task mandated by the CTO’s plan to save money by eliminating the company’s in-house data center.

You’ll prepare for the move by determining which objects should and shouldn’t be migrated, using SQL Server Management Studio. You’ll set up your database by building a SQL Managed Instance Database using Azure portal and migrate server-level objects to the cloud using dbatools and SQL scripts. To achieve optimal performance with minimal downtime, you’ll configure your database migration and proceed with migrating your on-premises database to the cloud. You’ll finish by using the Azure portal and SQL Server Management Studio to verify that your database settings were implemented—and that your migration was successful.

These projects are designed for learning purposes and are not complete, production-ready applications or solutions.

I definitely learned what I expected to learn, and became far more comfortable with Azure because of it. I’ll apply what I’ve learned to other tasks, like metrics, alerts, and networking.

Mitch Fox, software engineer, Aditi Staffing

here's what's included

Project 1 Prepare for Migration

You’re a database administrator at Magic Wand Services Corporation. Your CTO has mandated the migration of all on-premises resources to the cloud in an effort to save money on resources. As a database administrator, it’s your job to make the necessary preparations for this migration.

Using SQL Server Management Studio (SSMS) and SQL Server Auditing, you’ll determine usage of your on-premises databases to help decide what should (and shouldn’t) be moved to the cloud. Using SSMS, you’ll remove all objects that aren’t supported in Azure, any unused or unnecessary agent jobs and server objects, as well as any unused database objects. Then, to ensure you migrate everything you need to Azure, you’ll make a checklist in the text editor of your choice. When you’re finished, you’ll be prepared to smoothly migrate your SQL Server database to Azure SQL Managed Instance.

Project 2 Set Up Cloud Database

The CTO of Magic Wand Services Corporation has mandated the migration of all on-premises resources to the cloud in order to save money. As a database administrator, your job is to set up your cloud database, an Azure SQL Managed Instance, for optimal performance.

You’ll create a performance and configuration baseline using SQL Server Management Studio to ensure that you configure your Azure SQL Managed Instance properly to support your workload. With your baseline as a guide, you’ll use the Azure portal to build a SQL Managed Instance Database. Using dbatools and SQL scripts, you’ll migrate server-level objects from your on-premises SQL Server to the cloud, verifying that your Azure SQL Managed Instance is properly configured. When you’re done, you’ll have a managed instance with maximized performance to which you can confidently migrate your databases.

Project 3 Migrate Databases to Azure

As a database administrator for Magic Wand Services Corporation, your task is to migrate an on-premises SQL Server database to an Azure SQL Managed Instance, a task mandated by the company’s CTO in an effort to save money on resources.

You’ll use the Azure portal and Azure Data Studio to configure your database migration service for a smooth migration with no data loss and minimal downtime, then you'll perform the migration. Using the Azure portal, Azure Data Studio, and SQL Server Management Studio, you’ll verify that your database settings were implemented—and that your migration was successful.

Project 4 Monitor and Audit

You’re a database administrator at Magic Wand Services Corporation, where the on-premises SQL Server database has recently been migrated to an Azure SQL Managed Instance. Your task is to audit, monitor, and configure your Azure SQL Managed Instance to help troubleshoot issues and maximize security.

Using SQL Server Audit and Azure Blob storage with SQL Server Management Studio and the Azure portal, you’ll configure and perform auditing to monitor changes made by other applications or users. Next, using the Azure portal, you’ll configure alerting to monitor for issues related to storage and CPU usage. Lastly, you’ll run dbachecks against your managed instance to ensure it’s compliant with Center for Internet Security (CIS) benchmarks. When you’re done, your Azure SQL Managed Instance will be properly configured for troubleshooting and as secure as it can be.

book resources

When you start each of the projects in this series, you'll get full access to the following book for 90 days.

choose your plan

team

monthly
annual
$49.99
$499.99
only $41.67 per month
  • five seats for your team
  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose another free product every time you renew
  • choose twelve free products per year
  • exclusive 50% discount on all purchases
  • Migrate SQL Server to Azure project for free

project authors

Josephine Bush

Josephine Bush is a Microsoft Data Platform MVP. She has over ten years of broad-based experience as a database administrator in the financial, business, and energy data sectors using SQL Server, MySQL, Oracle, and PostgreSQL along with Azure and AWS. She holds a BS in information technology, an MBA in IT management, and an MS in data analytics. She presents at SQL conferences and user groups around the world, and she’s the leader of the Boulder SQL user group. She is the author of Practical Database Auditing published by Apress. You can read her blog posts on sqlkitty.com and contact her on Twitter @hellosqlkitty.

Gilda Alvarez

Gilda Alvarez graduated from the University of Central Florida with management information system (MIS) and computer science (CS) degrees. She’s currently a senior manager at Skytouch Technology and has over a decade of experience in enterprise information technology for several Fortune 500 companies. She’s worked on multi-million dollar projects for IBM, Disney, the IRS, the U.S. Federal Defense Agency, Marriott, Deloitte, and others. She has specialized in database design, implementation, and optimization, emphasizing high availability, business continuity, and migrations. She’s passionate about promoting technical and leadership education in her community. In addition to leading the information technology committee of ALPFA (Association of Latino Professionals for America in Orlando), she’s a chapter leader for PASS (Professional Association for SQL Server). These associations have empowered her to bridge the gap in IT skills in the Latino community and mentor many Latino women in technology.

Prerequisites

These liveProjects are for database administrators, DevOps engineers, and developers who want to migrate their on-premises SQL Server databases to Azure. To begin these liveProjects you’ll need to be familiar with the following:

TOOLS
  • SQL Server
  • SQL Server Management Studio (SSMS)
  • A text or Markdown editor
  • The Azure portal (you’ll need full admin rights)
  • PowerShell
  • A previously configured VPN or Azure ExpressRoute
TECHNIQUES
  • Use SSMS to manage databases, such as in restoring a database and managing objects in the database and at the SQL Server level
  • Basic SQL scripting skills such as how to query tables
  • Basic PowerShell skills such as how to add a module

features

Self-paced
You choose the schedule and decide how much time to invest as you build your project.
Project roadmap
Each project is divided into several achievable steps.
Get Help
While within the liveProject platform, get help from other participants and our expert mentors.
Compare with others
For each step, compare your deliverable to the solutions by the author and other participants.
book resources
Get full access to select books for 90 days. Permanent access to excerpts from Manning products are also included, as well as references to other resources.