Easy lift and shift: Customers can lift and shift their on-premises SQL server to a Managed Instance that offers compatibility with SQL Server on-premises.

Fully managed PaaS: Azure SQL Database Managed Instance is designed for customers looking to migrate a large number of apps from on-premises self-built or ISV provided an environment to fully managed PaaS cloud environment.

New Business model: Competitive, transparent, and frictionless business model

Security: Managed Instance that offers compatibility with SQL Server on-premises and complete isolation of customer instances with native VNet support.

Managed Instance security isolation

Managed Instance provides additional security isolation from other tenants in the Azure cloud.

The managed instance security isolation includes:

Native virtual network implementation and connectivity to our on-premises environment using Azure Express Route or VPN Gateway.
SQL endpoint is exposed only through a private IP address, allowing safe connectivity from private Azure or hybrid networks.
It is a Single-tenant environment with dedicated underlying infrastructure (compute, storage).

Structure of Managed Instance

When we create a managed instance, a virtual network will get created. It will have front end subnet, Gateway subnet, a managed instance subnet, and the node that we can deploy as part of managed instance creation will get implemented into the MI subnet. Each node consists of the SQL engine and SQL management. Within the same network, we can deploy multiple nodes also, and these various nodes will form a virtual cluster with Gateway servers.

The entire virtual cluster will have two endpoints. The first endpoint will be for client connections, and the second endpoint is public but will be used by Microsoft to manage this environment. They need to connect to this environment using some automated script or something like that and maintain it for that purpose.

There is an endpoint also for this entire environment to work correctly. It needs to connect to Azure storage and service bus also. So, when we are trying to restrict the traffic from our MI subnet to the outside. Make sure we allow all the traffic related to Microsoft otherwise, our environment might not work correctly.

Finally, in terms of client connections and applications to connect to the database, they can reside in Frontend subnet and connect to the database, or they can live in a peered network when we peer the network which our MI subnet then all the web apps or virtual machines can be able to connect to the database because both networks have peered. We can also join our on-premises applications to the database by creating either a virtual network gateway or express gateway.

All the connections, whether it forms the web apps, or the virtual machines, or on-premises applications, all of them are communicating with the database over a private connection.

Azure Data Factory

Azure Data Factory is a data-integration service based on the Cloud that allows us to create data-driven workflows in the cloud for orchestrating and automating data movement and data transformation. Data Factory is a perfect ETL tool on Cloud. Data Factory is designed to deliver extraction, transformation, and loading processes within the cloud. The ETL process generally involves four steps:

Connect & Collect: We can use the copy activity in a data pipeline to move data from both on-premises and cloud source data stores.
Transform: Once the data is present in a centralized data store in the cloud, process or transform the collected data by using compute services such as HDInsight Hadoop, Spark, Data Lake Analytics, and Machine Learning.
Publish: After the raw data is refined into a business-ready consumable form, it loads the data into Azure Data Warehouse, Azure SQL Database, and Azure Cosmos DB, etc.
Monitor: Azure Data Factory has built-in support for pipeline monitoring via Azure Monitor, API, PowerShell, Log Analytics, and health panels on the Azure portal.

Components of Data Factory

Data Factory is composed of four key elements. All these components work together to provide the platform on which you can form a data-driven workflow with the structure to move and transform the data.

Pipeline: A data factory can have one or more pipelines. It is a logical grouping of activities that perform a unit of work. The activities in a pipeline perform the task altogether. For example – a pipeline can contain a group of activities that ingests data from an Azure blob and then runs a Hive query on an HDInsight cluster to partition the data.
Activity: It represents a processing step in a pipeline. For example – we might use a copy activity to copy data from one data store to another data store.
Datasets: It represents data structures within the data stores, which point to or reference the data we want to use in our activities as I/O.
Linked Services: It is like connection strings, which define the connection information needed for Data Factory to connect to external resources. A Linked service can be a data store and compute resource. Linked service can be a link to a data store, or a computer resource also.
Triggers: It represents the unit of processing that determines when a pipeline execution needs to be disabled. We can also schedule these activities to be performed at some point in time, and we can use the trigger to disable an activity.
Control flow: It is an orchestration of pipeline activities that include chaining activities in a sequence, branching, defining parameters at the pipeline level, and passing arguments while invoking the pipeline on-demand or from a trigger. We can use control flow to sequence certain activities and also define what parameters need to be passed for each of the activities.

Creating Azure Data-Factory using the Azure portal

Step 1: Click on create a resource and search for Data Factory then click on create.

Step 2: Provide a name for your data factory, select the resource group, and select the location where you want to deploy your data factory and the version.

Step 3: After filling all the details, click on create.

The Azure Data Factory completely had a different portal, as shown in the following figure.

Azure SQL Stretch Database & SQL Data Warehouse

SQL Stretch Database

It migrates our cold data transparently and securely to the Microsoft Azure Cloud. Stretch database divides the data into two types. One is the hot data, which is frequently accessed, and the second one is cold data, which is infrequently accessed. Also, we can define policies or criteria for hard data and cold data.

g1 1

For example – if we have a sales order table, all those open and in Program Sales orders can be hot data, and all the closed sales orders can be cold data. The cold data will be transparently migrated to Azure SQL Stretch Database. However, it doesn’t mean that we need to change our application in such a way that for open sales orders, we need to go to Azure SQL Stretch Database.

We can use the same queries in our application to fetch the data and based on the location of data, and the query will be automatically sent to Stretch Database.

Advantages of SQL Stretch Database

It provides cost-effective availability for cold data that benefits from the low cost of Azure rather than scaling expensive on-premises storage.
It doesn’t require changes to the existing queries or applications. The position of the data is transparent to the application.
It reduces the on-premises maintenance and storage for our data. Backups for our on-premises data run faster and finish within the maintenance window. Backups for the cloud portion of our data run automatically.
It keeps our data secure even during migration. It provides encryption for our data in motion. Row-level security and other advanced SQL Server security feature also work with Stretch Database to protect our data.

SQL Data Warehouse

Microsoft SQL Data Warehouse within Azure is a cloud-based at scale-out database capable of processing massive volume of data, both relational and non-relational and SQL Data Warehouse is based on massively parallel processing architecture.

In this architecture, requests are received by the control node, optimized, and passed on to the compute nodes to do work in parallel. SQL data warehouse stores the data in Premium locally redundant storage, and linked to computing nodes for query extraction.

Components of SQL Data Warehouse

Data Warehouse units: Allocation of resources to our SQL Data Warehouse is measured in Data Warehouse Units (DWUs). DWUs is a measure of underlying resources like CPU, memory, IOPS, which are allocated to our SQL Data Warehouse.

Data Warehouse units provide a measure of three precise metrics that are highly correlated with data warehouse workload performance.

Scan/Aggregation: Scan/Aggregation takes the standard data warehousing query. It scans a large number of rows and then performs a complex aggregation. It is an I/O and CPU intensive operation.
Load: This metric measures the ability to ingest data into the service. This metric is designed to stress the network and CPU aspects of the service.
Create Table As Select (CTAS): CTAS measures the ability to copy a table. It involves reading data from storage, distributing it across the nodes of the appliance, and writing it to storage again. It is a CPU, IO, and network-intensive operation.

So, this brings us to the end of blog. This Tecklearn ‘Azure SQL Managed Instance and SQL Stretch Database’ blog helps you with commonly asked questions if you are looking out for a job in Azure and Cloud Computing. If you wish to learn Microsoft Azure and build a career in Cloud Computing domain, then check out our interactive, Microsoft Azure Developer and Administrator Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/microsoft-azure-developer-associate-az-203-and-microsoft-azure-administrator-associate-az-103/

Microsoft Azure Developer Associate: AZ-203 and Microsoft Azure Administrator Associate AZ-103 Training

About the Course

This Course provides training for Microsoft Azure Administrator and Azure Developer. Tecklearn’s Azure Administrator training provides you with a deep understanding of the entire administrative lifecycle in Azure environments. This Azure course shows you how to maintain services related to computing, storage, network, and security. Enhance your Azure Administrator skills and prepare to ace the AZ-103 Azure Administrator exam. The Azure Developer certification course prepares you for Microsoft’s Azure Developer certification exam AZ-203. It covers Azure architecture, Azure technology development solutions, Azure storage services, and solutions, Cognitive Services such as Computer Vision, Q&A Maker, Azure service solutions, and API management services.

Why Should you take Azure Developer and Administrator Training?

Microsoft certified Azure Associate developer earns salary ranging from $95,000 to $135,000
Average salary of Microsoft Certified Azure Administrator is $90,000 – Indeed.com
Apple, eBay, Samsung, Citrix, UST Global, Mindtree, TCS, Wipro, Infosys & many other MNC’s worldwide use Azure across industries
According to Microsoft, more than 1,000 new sign-ups for Azure occur each day, which equates to approximately 365,000 new sign-ups each year
By 2022, 90% of enterprises will use both the IaaS and PaaS capabilities from cloud provider – Gartner

What you will Learn in this Course?

Introduction to Azure Compute Solutions and Cloud Computing

Introduction to Microsoft Azure
About Azure Certification

Overview of Azure Storage Services

Azure Storage
Azure File Use Case
Azure DNS
Azure Site Recovery

Secure and Manage Azure Storage

Security Issue
Azure Regions
Azure Services

Implementing Secure Data Solutions and Integrating Caching & CDN

Azure CDN
Azure Traffic Manager
Azure Load Balancer
Azure Scale Set

Implementing Azure App Service Web Apps and Mobile Apps

Design and Implement Azure Service Apps
Web Apps
Pricing Calculator – Azure

Managing Azure Subscriptions and Resource Groups

Create Resource Group
Create App Service Plan
Create Web App and Deploy Angular Application using SCM
Deploy .NET Application using SCM
Deploy App using Visual Studio
Web Job Types
Sendgrid

Develop Event-based and Message-based Solutions in Azure

Messaging Strategy
Design and implement Messaging Strategy
Azure Notifications
Microsoft Azure Service Bus
Queues
Topics
Create Topic
Create Subscription
Azure Relay
Using EventHubs

Implementing Azure App Service API Apps & Azure Functions

Azure PAAS Services
API Management
Function Apps
Logic Apps

Overview of Azure Virtual Machines and Configure Virtual Machines for High Availability

Virtual Machines
Create VM
PowerShell DSC and Custom Script Extension
Scale ARM VMS
VMSS
Monitoring VMs
Dev Test Labs
VM Storage

Design and implement Azure DevOps

CI/CD Pipelines
VSTS
Deployment in pass and VM’s
Scheduled deployments

Manage Azure Active Directory (AD)

Manage Identity, Application and Network Services
Overview of Azure Active Directory
Azure Active Directory B2C
Azure Active Directory B2B
Key Vault
Azure Graph API

Azure Virtual Networks and Network Security

Redis Caching
Azure search
Virtual Networks
Configure Virtual Network
Hybrid Network Connectivity
ARM VM Networking
Azure security and recovery services

Developing Solutions That Use Relational Database and Azure Blob Storage

Azure DB Services – SQL DB
Azure Notifications
Backup and Restore
Enabling Geo-Replication
Export source Database
Scale Azure SQL Databases

Developing Solutions That Use Azure Table Storage & Cosmos DB

COSMOS DB
Azure Key Vault
Azure App Insights

Azure Command Line Interface (CLI) and PowerShell

Resource management and deployments using PowerShell and CLI
ARM templates
Implement ARM templates
Control Access
PowerShell runbooks
Azure Automation
Real Time Examples

Got a question for us? Please mention it in the comments section and we will get back to you.

509