Manage non-relational data stores in Azure

28 Sep 2021

Manage CosmoDB

• NoSQL database management system
• NoSQL systems, including MongoDB and Cassandra.
• Cosmos DB manages data as set of documents.
• JSON
• Key-Value pair
• Cosmos DB database are organized into containers.
• Query COSMOS DB

Manage Azure Blob Storage

Blob Storage

Manage Azure File storage

File Storage

<- Back to Main Menu

Azure Relational Data Services

28 Sep 2021

Introduction

• Database -> Collection of Data
• Shredsheet -> PB of data -> store data in a tabular format, with rows and columns.
• Relationships between tables -> Relational Databases
• Semi-structured or unstructured, comprising of semi-processed or unprocessed data ->non-relational databases.
• DBMS -> physical aspects of a database, such as where and how it’s stored, who can access it, and how to ensure that it’s available when required.
• A stored procedure is a block of code that runs inside your database. Applications often use stored procedures because they are optimized to run in the database environment, and can access data very quickly.
• A linked server is a connection from one database server to another.
• SQL Server can use linked servers to run queries on one server that can include data retrieved from other servers; these are known as distributed queries

Relational Azure Data Services

• Migrate your on-premises systems to a collection of Azure virtual machines.

IaaS(Infrastructure-as-a-Service)

• Create a virtual infrastructure in the cloud that mirrors the way an on-premises data center might work.
• Create a set of virtual machines, connect them together using a virtual network, and add a range of virtual devices. You take responsibility for installing and configuring the software, such as the DBMS, on these virtual machines.
• An Azure Virtual Network is a representation of your own network in the cloud.
• A virtual network enables you to connect virtual machines and Azure services together, in much the same way that you might use a physical network on-premises. Azure ensures that each virtual network is isolated from other virtual networks created by other users, and from the Internet. Azure enables you to specify which machines (real and virtual), and services, are allowed to access resources on the virtual network, and which ports they can use.

PaaS(Platform-as-a-service)

• Creating a virtual infrastructure, and installing and managing the database software yourself, a PaaS solution does this for you.
• Azure automatically creates the necessary virtual machines, networks, and other devices for you
• Scale up or down (increase or decrease the size and number of resources) quickly.

SaaS(Software-as-a-Service)

• Specific software packages that are installed and run on virtual hardware in the cloud.
• Hosted Applications -> Microsoft 365

Azure Data Services

• PaaS category
• Configuration, day-to-day management, software updates, and security of the databases that it hosts.
• Azure SQL Database -> Azure Database for MySQL servers, Azure Database for MariaDB servers, and Azure Database for PostgreSQL servers.
• Available for at least 99.99% of the time.
• Cost -> 1. Base price of each service covers underlying infrastructure and licensing, together with the administration charges. 2. Additionally, these services are designed to be always on. This means that you can’t shut down a database and restart it later.

#### SQL Server on Azure Virtual Machines

• IaaS
• Use full versions of SQL Server in the Cloud without having to manage any on-premises hardware.
• SQL Server running on an Azure virtual machine effectively replicates the database running on real on-premises hardware.
• Migrating from the system running on-premises to an Azure virtual machine is no different than moving the databases from one on-premises server to another.
• The database runs stored procedures and scripts as part of the database workload.
• Stored procedures and scripts depend on features that are restricted by following a PaaS approach, then running SQL Server on your own virtual machines might be a good option.
• migrations and applications requiring access to operating system features that might be unsupported at the PaaS level.
• SQL virtual machines are lift-and-shift ready for existing applications that require fast migration to the cloud with minimal changes.
• lift-and-shift refers to the way in which you can move a database directly from an on-premises server to an Azure virtual machine without requiring that you make any changes to it. Applications that previously connected to the on-premises database can be quickly reconfigured to connect to the database running on the virtual machine, but should otherwise remain unchanged.
• A hybrid deployment is a system where part of the operation runs on-premises, and part in the cloud. Your database might be part of a larger system that runs on-premises, although the database elements might be hosted in the cloud.
• #### Azure SQL Database
• PaaS
• SQL Server on a virtual machine ->Management overhead(Disadvantage)
• A SQL Database server is a logical construct that acts as a central administrative point for multiple single or pooled databases, logins, firewall rules, auditing rules, threat detection policies, and failover groups.
• Azure SQL Database options -> Single Database, Elastic Pool, and Managed Instance

##### Single Database : set up and run a single SQL Server database

• You create and run a database server in the cloud
• You access your database through this server.
• Microsoft manages the server( one should configure the database, create your tables, and populate them with your data.)
• By default, resources are pre-allocated, and you’re charged per hour for the resources you’ve requested.
• A serverless configuration -> Microsoft creates its own server, which might be shared by a number of databases belonging to other Azure subscribers. Microsoft ensures the privacy of your database. Your database automatically scales and resources are allocated or deallocated as required.

Elastic Pool

• Similar to Single Database, except that by default multiple databases can share the same resources, such as memory, data storage space, and processing power through multiple-tenancy.
• resources ->pool
• create the pool, and only your databases can use the pool
• databases with resource requirements that vary over time, and can help you to reduce costs.
• Elastic Pool enables you to use the resources available in the pool, and then release the resources once processing has completed.
• 

Advantages:

• Azure SQL Database is often used for:

      * Modern cloud applications that need to use the latest stable SQL Server features.
      * Applications that require high availability.
      * Systems with a variable load, that need the database server to scale up and down quickly
  

• Advanced threat protection
• At least 99.99% of the time
• Auditing tracks database events and writes them to an audit log
• Secure your data by providing encryption.
• Linked servers are used to perform distributed queries(Single or Elastic pool).

Azure SQL Database Managed Instance

• The Single Database and Elastic Pool options restrict some of the administrative features available to SQL Server.
• Managed instance effectively runs a fully controllable instance of SQL Server in the cloud.
• Automates backups, software patching, database monitoring, and other general tasks, but you have full control over security and resource allocation for your databases.
• All communications are encrypted and signed using certificates. To check the trustworthiness of communicating parties, managed instances constantly verify these certificates through certificate revocation lists.
• If the certificates are revoked, the managed instance closes the connections to protect the data.

PostgreSQL, MariaDB, and MySQL

High availabilityAutomatic backups and point-in-time restore for the last 35 days.Enterprise-level security and compliance with legislationpay-as-you-go pricing

MySQL	MariaDB	PostgreSQL
	High availability	similar benefits as Azure Database for MySQL
Predictable performance.	Predictable performance	three pricing tiers: Basic, General Purpose, and Memory Optimized
Easy scaling	Easy scaling	Hyperscale (Citus) is a deployment option that scales queries across multiple server nodes to support large database loads. Your database is split across nodes. Data is split into chunks based on the value of a partition key or sharding key.
Secure data, both at rest and in motion.	Secure data, both at rest and in motion.	highly available service
	Automatic backups and point-in-time restore for the last 35 days.	pgAdmin tool
	Enterprise-level security and compliance with legislation	queries run against databases on the server, and saves them in a database named azure_sys.
	-----	monitor the queries that users are running

Migrate data to Azure

• Azure Database Migration Service (DMS) -> MySQL, MariaDB, or PostgreSQL databases running on premises on Cloud.
• Restore a backup of your on-premises databases directly to databases running in Azure Data Services.
• Configure replication from an on-premises database, so that any changes made to data in that database are copied to the database running in Azure Data Services.
• Reconfigure users and applications to connect to the database in the cloud.
• don’t have to shut down the on-premises system while you transfer users to the cloud

<- Back to Main Menu

Describe provisioning relational data services

28 Sep 2021

What is provisioning?

• Provisioning is the act of running series of tasks that a service provider, such as Azure SQL Database, performs to create and configure a service.
• specify parameters that determine the size of the resources required (how much disk space, memory, computing power, and network bandwidth).
• The act of increasing (or decreasing) the resources used by a service is called scaling.

Tools

• The Azure portal
• The Azure command-line interface (CLI)
• Azure PowerShell

• Azure Resource Manager templates

• Send the template to Azure using the az deployment group create command in the Azure CLI, or New-AzResourceGroupDeployment command in Azure PowerShell. Stpes for provisioning

Configuring relational data services

• The default connectivity for Azure relational data services is to disable access to the world.
• To enable connectivity, use the Firewalls and virtual networks page for a service.
• Selected Networks : Virtual network, Firewall, and Exceptions
• An Azure Virtual Network is a representation of your own network in the cloud.
• In the Virtual networks section, you can specify which virtual networks are allowed to route traffic to the service.
• Firewall section, add the IP address of the computer.
• Exceptions setting allows you to enable access to any other services that cannot be uniquely isolated through virtual network or IP address rules.
• Firewalls and virtual networks page for an Azure SQL database.
• Azure SQL Database communicates over port 1433
• PostgreSQL port number 5432
• MySQl port number 3306
• A firewall rule of 0.0.0.0 enables all Azure services to pass through the server-level firewall rule and attempt to connect to a single or pooled database through the server.
• Azure Private Endpoint is a network interface that connects you privately and securely to a service powered by Azure Private Link
• The Private endpoint connections page for a service allows you to specify which private endpoints, if any, are permitted access to your service. You can use the settings on this page, together with the
• Firewalls and virtual networks page, to completely lock down users and applications from accessing public endpoints to connect to your Azure SQL Database account.

Configure authentication

• With Azure Active Directory (AD) authentication ->centrally manage the identities of database users and other Microsoft services in one central location.
• Central ID management provides a single place to manage database users and simplifies permission management.

Configure access control

• Access control defines what a user or application can do with your resources once they’ve been authenticated.
• Azure role-based access control (Azure RBAC) helps you manage who has access to Azure resources, and what they can do with those resources.
  • Allow one user to manage virtual machines in a subscription and another user to manage virtual networks.
  • Allow a database administrator group to manage SQL databases in a subscription.
  • Allow a user to manage all resources in a resource group, such as virtual machines, websites, and subnets.
  • Allow an application to access all resources in a resource group.
• Roles
  • Owner ->Full Access
  • Contributor -> create and manage but not grant
  • Reader -> View existing resources
  • User Access Administrator -> manage user access to Azure resources.
• A scope lists the set of resources that the access applies to.
• Add role assignments to a resource in the Azure portal using the Access control (IAM) page.
• Add role assignments to a resource in the Azure portal using the Access control (IAM) page.

Configure advanced data security

• Authentication
• Authorization
• Advanced data security implements threat protection and assessment.

Describe configuring Azure SQL Database, Azure Database for PostgreSQL, and Azure Database for MySQL

• An ACL contains a list of resources, and the objects (users, computers, and applications) that are allowed to access those resources.
• When an object attempts to use a resource that is protected by an ACL, if it’s not in the list, it won’t be given access.
• Items that implement network-based ACLs include routers and load balancers.
• Connection established Gateway (IP listening to port 1433) -> either redirects traffic to the database cluster, or acts as a proxy for the database cluster ->Inside the database cluster, traffic is forwarded to the appropriate Azure SQL database.

<- Back to Main Menu

Query relational data in Azure

28 Sep 2021

Introduction to SQL

• SQL -> Structured Query Language
• Used to communicate with a relational database
• update data in a database, or retrieve data from a database
• SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle.
• SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP
• Security management and programmability
• Microsoft SQL Server -> uses Transact-SQL -> writing stored procedures and triggers and managing user accounts
• Popular dialects of SQL include:
  • Transact-SQL (T-SQL) -> Microsoft SQL Server and Azure SQL Database.
  • pgSQL -> PostgreSQL.
  • PL/SQL -> Oracle -> Procedural Language/SQL.

SQL statement types

• SQL statements are grouped into two main logical groups, and they are:
• Data Manipulation Language (DML)

Statement	Description	Applied
SELECT	Select/Read rows from a table	By Default, Every Row at a time
INSERT	Insert new rows into a table	One Row at a time
UPDATE	Edit/Update existing rows	By Default, Every Row at a time
DELETE	Delete existing rows in a table	By Default, Every Row at a time
DROP	all the rows in that table are lost.	By Default, Every Row at a time

#### Data Types : 1) INT 2) VARCHAR 3) NOT NULL

• Data Definition Language (DDL)

Retrieve connection information for Azure SQL Database

Tools to query data held in Azure SQL Database:

• The query editor in the Azure portal
• The sqlcmd utility from the command line or the Azure Cloud Shell
• SQL Server Management Studio
• Azure Data Studio
• SQL Server Data Tools

Find the details about using the tools

Retrieve connection information for Azure Database for PostgreSQL

• psql psql utility is available in the Azure Cloud Shell -> to query a database

Retrieve connection information for Azure Database for MySql

• Use MySql Benchmark.

<- Back to Main Menu

Data Visualization

27 Sep 2021

Reporting

• The process of organizing data into informational summaries to monitor how different areas of an organization are performing.

Business Intelligence (BI)

• Technologies, applications, and practices for the collection, integration, analysis, and presentation of business information.
• The purpose of business intelligence is to support better decision making.
• This process of comparison with other companies in the same industry is known as benchmarking.

Data Vizualization

• Data visualization is the graphical representation of information and data.
• Power BI -> can connect to multiple different sources of data, and combine them into a data model.

Visualization options to represent data

• Bar and column charts
• Line charts
• Matrix
• Key influencers
• Treemap
• Dot plot chart or Bubble chart or scatter chart -> along X-axis
• Filled map

Data analytics