Best Practices While Migrating your SQL Server to Azure Benjamin Moskovits NetCom Learning
Š 1998-2020 NetCom Learning Š 1998-2020 NetCom Learning
www.netcomlearning.com | info@netcomlearning.com | 1-888-563-8266 www.netcomlearning.com | info@netcomlearning.com | 1-888-563-8266
AGENDA The top factors for leaving the SQL server Comparing SQL Server on Azure Environments Migration Azure SQL Managed Instance Moving SSIS Jobs into Azure
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
WHY MIGRATE • Much Lower TCO • Reliability • Ease of Use
• Speed of Setup
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
CHOOSE AN AZURE SQL SERVER ENVIRONMENT • SQL Server on Azure VM • Azure SQL Managed Instance • Azure SQL Database
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
AZURE SQL DATABASE 1. Fully managed Platform as a Service (Paas) 2. Database Oriented 3. All maintenance is done by Azure 4. Single Database vs Elastic Pool 5. 1-80 vCores /32 GB to 4TB 6. Service Tiers – General Purpose, Business Critical, Hyperscale
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
AZURE SQL MANAGED INSTANCE • Server Level Access • Almost Equivalent to Data Center Features • Azure maintains install and operating system
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
CREATE SQL SERVER ON AZURE VM 1. Images from 2008 R2 through SQL Server 2019 2. Use built in license vs Bring Your Own 3. Pick Region, Image, Size, Administrator Account, Port Number
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
COMPARING SQL SERVER ON AZURE ENVIRONMENTS
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
CREATE A MANAGED INSTANCE 1. Region 2. Machine Type – Use sliders to choose storage and number of cores 3. Assign Admin/Password 4. Set up networking
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
CREATE A SQL DATABASE 1. Create unique server name 2. Admin Login/Password 3. Location 4. Networking
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
CHOOSING A MANAGEMENT TOOL •
Azure Data Studio
•
SQL Server Management Studio (SSMS)
•
Azure Data Studio Vs. SSMS
•
Azure Data Studio Unique Features Schema Compare macOS Linux
•
SQL Server Data Tools
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
DATA MIGRATION SQL DATABASE •
Dacpac/Bacpac
•
Data Migration Assistant 1) Assess using Database Migration Assistant (DMA) 2) Prepare Fixes 3) Copy Production Database to Staging 4) Deploy Fixes on Copy 5) Migrate the Database Copy
•
Transactional Replication
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
MIGRATION AZURE SQL MANAGED INSTANCE •
Native restore from URL
•
Data Migration Assistant 1) Assess using Database Migration Assistant (DMA) 2) Prepare Fixes 3) Copy Production Database to Staging 4) Deploy Fixes on Copy 5) Migrate the Database Copy
•
DACPAC/BACPAC
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
AZURE IMPORT EXPORT SERVICE •
Use your own disks
•
Azure Data Box Disk - SSDs with 40 TB total capacity
•
Charges
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
INCOMPATIBILITIES
• • • • • • • • • • • •
Feature
SQL Database
Change Data Capture CLR Three Part Queries DbMail Mirroring Triggers Filestream Linked Servrs Recovery Model Temp Tables SQL Server Agent DBCC
No No No No No Database Only No No Full Only Local No Limited
© 1998-2020 NetCom Learning
Azure SQL Managed Instance Yes Yes Yes Yes No Yes No Limited Full Only Local and Global Yes (No proxies or Alerts) Limited
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
BACKUP • • • • • • • •
Built – In Full every week Differential 12 – 24 hours Transaction Log every 5 – 10 minutes Data stored in geo-redundant storage blobs Replicated to a paired region Configure how long Weekly/Monthly/Yearly backups are kept. SQL Server file-snapshot backup
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
SQL SERVER AGENT REPLACEMENT •
Azure Automation Runbooks
•
Using Elastic Database Jobs in Azure
•
Using Azure Data factory.
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
MOVING SSIS JOBS INTO AZURE • • • • • •
•
Install an instance of SQL Database to host SSISDB. Deploy Packages to Azure (only Project Deployment Model) using: SSMS TSQL Powershell Run Packages: >> Run a package directly - Run with SSMS - Run with stored procedures - Run with script or code Run a package as part of an Azure Data Factory pipeline - Run with the Execute SSIS Package activity - Run with the Stored Procedure activity
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
AZURE DATA FACTORY •
Create and schedule data-driven workflows (called pipelines) that can ingest data from disparate data stores.
•
Process or transform the data by using compute services such as Azure HDInsight Hadoop, Spark, Azure Data Lake Analytics, and Azure Machine Learning.
•
Publish output data to data stores such as Azure Synapse Analytics for business intelligence (BI) applications to consume.
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
IMPLEMENTING SECURITY ON AZURE •
Azure SQL Database - Create server-level and database-level firewall rules - Configure an Azure Active Directory (Azure AD) administrator - Manage user access with SQL authentication, Azure AD authentication, and secure connection strings - Enable security features, such as Azure Defender for SQL, auditing, data masking, and encryption
•
Azure SQL Managed Instance - Limit access in an isolated environment - Use authentication mechanisms that require identity: Azure Active Directory (Azure AD) and SQL Authentication - Use authorization with role-based memberships and permissions - Enable security features
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
HIGH AVAILABILITY – SINGLE REGION
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
HIGH AVAILABILITY – SINGLE REGION
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
MONITORING CHARGES
Other Views – Daily Costs, Cost by Service, Cost by Resource Budget and Credit Alerts Spending Limit © 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
MONITORING PERFORMANCE •
Azure SQL Database Performance Tool - Resource consuming queries - Long running queries - Based on Query Store
•
DTU – Database Throughput Unit
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
RECORDED WEBINAR VIDEO To watch the recorded webinar video for live demos, please access the link: https://bit.ly/3erLP2B
© 1998-2020 NetCom Learning
www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
ABOUT NETCOM LEARNING
150K+
14K+
3500
Professionals trained
Corporate clients
IT, Business & Soft Skills courses
96%
8.6/9
20+
Of customers recommend us to others
Instructor evaluations
Leadingvendors recognitions
Microsoft’s
80%
Top 20
Worldwide training partner of the year
Trained of the Fortune 1000
ITTraining Company
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
NetCom Learning is an award-winning global leader in managed learning services, training and talent development.
www.netcomlearning.com www.netcomlearning.com
Founded
: 1998
Headquarters
: New YorkCity
Delivery Capability
: Worldwide
CEO
: RussellSarder
|| info@netcomlearning.com || 1-888-563-8266
RECOMMENDED COURSES AND MARKETING ASSETS NetCom Learning offers a comprehensive portfolio for Data and AI Courses » 20764: Administering A SQL Database Infrastructure (SQL Server 2017) - Class Scheduled on Nov 16 » DP-300: Administering Relational Databases On Microsoft Azure - Class Scheduled on Nov 16 » DP-200: Implementing An Azure Data Solution Associate (Data Engineer) - Class Scheduled on Nov 18 » DP-201: Designing An Azure Data Solution Associate (Data Engineer) - Class Scheduled on Nov 30 » DP-900T00: Microsoft Azure Data Fundamentals - Class Scheduled on Dec 04 » DP-070T00: Migrate Open Source Data Workloads To Azure - Class Scheduled on Dec 07 » 20768: Developing SQL Data Models SQL Server (SQL Server 2017) - Class Scheduled on Dec 07 » DP-050T00: Migrate SQL Workloads To Azure - Class Scheduled on Dec 21
You can also access the below Marketing Assets » Free 1hr Training - Top 5 Strategies to Kickstart Your Data Analytics Resolutions Using Power BI » Free On-Demand Training - SQL Server 2017: Indexing for Higher Performance » Blog - Big Data vs. Data Science vs. Data Analytics - Find Out the Key Differences » Blog - Why Does the Industry Need SQL Server 2017?
© 1998-2019 NetCom Learning 1998-2020 NetCom Learning © 1998-2020 NetCom Learning
www.netcomlearning.com www.netcomlearning.com | |info@netcomlearning.com | |1-888-563-8266 www.netcomlearning.com| info@netcomlearning.com| 1-888-563-8266
UPCOMING WEBINARS
▪ ▪ ▪ ▪
All You Need to Know About the New PMP® Exam, 2021 Accelerating AI Adoption with Machine Learning Improve Your Business Productivity by using Microsoft 365 Cyber Attack Trends & Threat Forecast for 2021
▪ ▪ ▪ ▪ ▪ ▪ ▪
SharePoint Framework: Exploring developer toolchain All You Need to Know About ITIL® 4 How Dynamics 365 Revolutionized the Concept of CRM and ERP AWS Discovery Day - An official introduction to the core concepts of cloud and AWS Mastering Red Hat OpenShift Applications Fundamentals of Network Administration for an Organization Top 5 Strategies to Kickstart Your Data Analytics Resolutions Using Power BI
& More
© 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
PROMOTIONS
Buy More to Save More! Now fulfill all your training needs without disturbing your business funds. Choose from the bundle of our Learning Saving Pass (LSP) pre-pay plans and get up to 100% value back on your investment. Unlock Now
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
PROMOTIONS
Worry-Free Training with Price Match Guarantee Our Price Match Guarantee ensures that we'll match the offers of any other authorized training provider if you succeed at finding anyone offering the same publicly scheduled class within 30 days of our schedule at a lower regular price. Learn More
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
FOLLOW US ON
© 1998-2019 1998-2020NetCom NetCom Learning Learning
YouTube
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
BUILDING AN INNOVATIVE LEARNING ORG.
A BOOK FROM RUSSELL SARDER,
CEO AT NETCOM LEARNING
A framework to build a smarter workforce, adapt to change and drive growth.
DOWNLOAD e-book
© 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
Thank you
1998-2019 NetCom Learning Š 1998-2020
www.netcomlearning.com www.netcomlearning.com
| || info@netcomlearning.com || 1-888-563-8266