Skip to content

Instantly share code, notes, and snippets.

@IPvPho
Last active September 18, 2021 12:33
Show Gist options
  • Save IPvPho/26729c374403dccdd208cd00e0080710 to your computer and use it in GitHub Desktop.
Save IPvPho/26729c374403dccdd208cd00e0080710 to your computer and use it in GitHub Desktop.
Azure DB & Analytics

Azure Cosmos DB

  • A globally distributed, multi-model database service.
  • Can elastically and independently scale throughput and storage across any number of Azure regions . worldwide.
  • Take advantage of fast, single-digit-millisecond data access by using any one of the API's.
  • Provides comprehensive service level agreements for throughput, latency, availability, and consistency guarantees.
  • Supports schema-less data, which you build highly responsive and "Always On" applications to support constantly changing data.
  • At the lowest level, Azure Cosmos DB stores in atom-record-sequences (ARS) format.
  • Data is then abstracted and projected as an API, which you can specify when you're creating your DB.
  • Choices include SQL, MongoDB, Cassandra, Tables, and Gremlin.
  • This level of flexibility means that as you migrate your company's DB to Azure Cosmos DB, your devs > can stick with the API that they're most comfortable with.

Azure SQL DB

  • A relational DB based on the latest stable version of the Microsoft SQL Server DB engine.
  • SQL DB is a high-performance, reliable, fully managed, and secure DB.
  • Can be used to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure.

Features

  • Azure SQL DB is a PaaS DB engine. It handles most of the DB mgmt functions, such as upgrading, patching, backups, and monitoring, without user involvement.
  • Provides 99.99% availability.
  • PaaS capabilities that are built into SQL DB enable you to focus on the domain-specific DB administration and optimization activities that are critical for you business.
  • SQL DB is a fully managed service that has built-in high availability, backups, and other common > maintenance operations.
  • Microsoft handles all updates to the SQL and OS, you don't have to manage the underlying infrastructure.

Migration

  • Azure DB Migration Service can be used to migrate existing SQL Server DBs with minimal downtime.
  • The Microsoft Data Migration Assistant can generate assessment reports that provide recommendations to help you guide you through required changes prior to migration.
  • Azure DB Migration Service performs all of the required steps, you just change the connection strings in your apps.

Create a SQL DB

  1. Sign in to Azure Portal
  2. Create A Resource > Databases > SQL Databases
    • Fill in the following:
      • Subscription
      • Resource Group
      • Database Name
      • Server
  3. Select 'Create New"
  4. New server panel appears
    • Fill in the following:
      • Server Name
      • Server Admin Login
      • Password
      • Location
  5. 'Next: Networking'
    • Fill in the following:
      • Connectivity Method
  6. 'Next: Security'
    • Select 'Not Now' for Azure Defender for SQL
  7. 'Next: Additional Settings'
    • Fill in the following:
      • Data Resources
      • Use Existing Data (Sample)
      • Collation (Default)
  8. 'Review + Create'
  9. After validation success, on the 'Create SQL Database window, select 'Create' to deploy the server and database. (2-5 mins)
  10. Select 'Go to Resource'
  11. Select 'Set Server Firewall', select 'Yes' to 'Allow Azure services and resources to access this server
  12. Save
  13. Select 'Ok'

Test the SQL DB

  1. From 'All Resources' pane, search and select 'SQL Databases', ensuring your new DB was created (may need to refresh).
  2. Select 'db1' representing the SQL db you created, and then select 'Query Editor (preview)'
  3. Sign in (You shouldn't be able to)
  4. Select Overview > Set Server Firewall
  5. In 'Client IP Address', you IP will be shown (verify). Click on '+ Add Client IP', which will add a 'Rule Name' and put your IP in both the 'Start IP' and 'End IP' fields, then 'Save'.
  6. Return to SQL DB and the Query Editor sign-in page. Sign in should succeed this time.
  7. Repeat Step 7 if you still cannot login.
  8. Enter the following in the editor pan:
    • SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
    • FROM SalesLT.ProductCategory pc
    • JOIN SalesLT.Product p
    • ON pc.productcategoryid = p.productcategoryid; 9 'Run', then review query results in the 'Results' pane.

Azure DB for MySQL

  • Azure Db for MySQL is a relational db service in the cloud, and it is currently based on the MySQL Community Edition db engine, versions 5.6, 5.7, and 8.0.
  • 99.99% availability SLA form Azure.
  • Built-in security, fault tolerance and data protection.

Azure Database for MySQL delivers:

  - Built-in high availability with no additional cost.
  - Predictable performance and inclusive, pay-as-you-go pricing.
  - Scale as needed, within seconds.
  - Ability to protect sensitive data at-rest and in-motion.
  - Automatic backups.
  - Enterprise-grade security and compliance.
  - This is all provided at no additional cost with almost no required administration.

Azure DB for PostgreSQL

  • Azure DB for PostgreSQL is a relational db service in the cloud.
  • Based on the community version of the open-source PostgreSQL db engine.

Benefits

  • Built-in high-av compare to on-prem resources. No additional config, replication or cost required.
  • Simple/Flexible pricing, predictable performance based on selected pricing tier choice that includes software patching, automatic backups, monitoring, and security.
  • Scale up/down as needed in seconds, adapting services to match usage.
  • Adjustable automatic backups and point-in-time-restore for up to 35 days.
  • Enterprise-grade security and compliance to protect sensitive data at-rest and in-motion.
  • Security covers data encryption on disk and SSL encryption between client and server communication.

Azure DB for PostgreSQL is available in 2 deployment options: Single Server & Hyperscale Citus).

Single Server

  • Built-in high availability with no additional cost (99.99% SLA)
  • Predictable performance and inclusive, pay-as-you-go pricing
  • Vertical scale as needed, within seconds
  • Monitoring and alerting to assess your server
  • Enterprise-grade security and compliance.
  • Ability to protect sensitive dat at-rest and in-motion
  • Automatic backups and point-in-time-restore for up to 35 days.
  • Three pricing tiers: Basic, General Purpose, and Memory Optimized. Each offers different resource capabilities to support your db workloads. You can build your first app on a small db for a few > dollars a month, and then scale to meet your needs.

Hyperscale (Citus)

  • Horizontally scales queries across multiple machines by using sharding.
  • Its query engine parallelizes incoming SQL queries across these servers for faster responses on large datasets.
  • Serves applications that require greater scale and performance, generally workloads.
  • Apps built for PostgreSQL can run distributed queries on Hyperscale (Citus) with standard connection libraries and minimal changes.

Azure SQL Managed Instance

Features

  • Platform as a Service (PaaS) db engine
  • 99.99% uptime SLA
  • Automated backups and configurable backup retention period
  • Many of the same features as Azure SQL DB, but offers several options that might no be available on Azure SQL DB.

Migration

  • Migrate on-prem data from an SQL Server to the cloud using the Azure DB Migration Service (DMS) or native backup and restore.
  • Make sure there are no blocking issues when migrating.
  • Once any issues are resolved, you can migrate the data, then cutover from the on-prem SQL Server to the Azure SQL Managed Instance by changing the connection string in your apps.

Migration Process Flow

  1. Discover
  2. Assess
  3. Migrate
  4. Cutover
  5. Optimize

Big Data & Analytics

Azure Synapse Analytics

  • Formerly 'Azure SQL Data Warehouse'
  • A limitless analytics service that brings together enterprise data warehousing and big data analytics.
  • Data can be queried on your terms by using either serverless or provisioned resources at scale.
  • Brings a unified experience to ingest, prepare, manage, and serve data for immediate BL and ML needs.

Azure HDInsight

  • Fully managed, open-source analytics service for enterprises.
  • Makes it easier, faster and more cost-effective to process massive amounts of data.
  • Can run popular open-source frameworks and create cluster types such as:
    • Apache Spark
    • Apache Hadoop
    • Apache Kafka
    • Apache Storm
    • Machine Learning Services
  • Supports a broad range of scenarios such as:
    • Extraction, transformation, and loading (ETL)
    • Data Warehousing
    • Machine Learning
    • IoT

Azure Databricks

  • Unlocks insights from all of your data and helps build AI solutions.
  • Can set up Apache Spark env in minutes, and autoscale/collaborate on shared projects in an interactive workspace.
  • Supports:
    • Python
    • Scala
    • R
    • Java
    • SQL
    • TensorFlow
    • PyTorch
    • Scikit-learn

Azure Data Lake Analytics

  • On-demand analytics job service that simplifies big data.
  • Instead of deploying, configuring, and tuning hardware, you write queries to transform your data and extract valuable insights.
  • The analytics service can handle jobs of any scale instantly by setting the dial for how much power you need.
  • You only pay for your job when it is running, making it more cost-effective.

Network Security Groups

  • Enables you to filter network traffic to and from Azure resources within an Azure Virtual Network.
  • Similar to an internal firewall.
  • Can contain multiple inbound and outbound security rules that enable you to filter traffic to and from resources by Source and Destination IP address, port, and protocol.

How Do I Specify NSG Rules?

  1. Name: A Unique name for the NSG.>

  2. Priority: A number between 100 - 4096. Rules are processed in priority order, with lower numbers processed before higher numbers.

  3. Source or Destination: A single IP or range of IPs, service tag, or application security group.

  4. Protocol: TCP, UDP, or Any.

  5. Direction: Whether the rule applies to inbound or outbound traffic.

  6. Port Range: A single port or range of ports.

  7. Action: Allow or Deny.

Combining Azure Services to Create a Complete Network Security Solution

Secure the Perimeter Layer

  • The Perimeter Layer is about protecting your org's resources form network-based attacks.
  • Identifying these attacks, alerting the appropriate security teams, and eliminating their impact are important to keeping your network secure.
  • How to secure:
    • Use Azure DDoS Protection to filter large-scale attacks before they can cause a denial of service for users.
    • Use perimeter firewalls with Azure Firewall to identify and alert on malicious attacks against your network.

Secure the Network Layer

  • At this layer, the focus is on limiting network connectivity across all of your resources to allow only what's required.
  • Segment your resources and use network-level controls to restrict communication to only what is needed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment