azSql - klagan/learning GitHub Wiki

Overview

Azure SQL offer a family of managed, secure and intelligent SQL database solutions in the cloud.

Database Types

  • Azure SQL Database
  • Azure SQL Managed instance
  • SQL server in Azure VM

Azure SQL Database

Single databases are optimized for modern application development of new cloud-born applications. Databases provide a fully managed SQL experience with extensive and easy to use manageability features.

  • relational DBaaS
  • built modern cloud applications
  • multi tenanted SaaS
  • elastic pools
  • scale compute and storage
  • integrate other azure services
  • hyperscale
  • can implement serverless
  • automatic tuning
  • virtual network
  • private link
  • 99.995% availability
  • backups included - both LTR and PITR (long term retention and point in time recovery)
  • backups stored in globally redundant storage

Includes: single databases, elastic pools, and database servers

Azure SQL Managed instances

Managed instances provide the PaaS benefits of SQL databases with added capabilities that were previously only available in SQL virtual machines. This includes a native virtual network and near 100% compatibility with on-premises SQL Server.

  • client want to migrate to azure and delegate management but keep "instance scoped features" e.g. service broker, sql service agent, clr, machine learning services
  • originally called 'cloud lifter project' then 'managed instance'
  • sql server instance with PaaS managed capabilities
  • online migration, (on prem. to cloud migration) - with log shipping, database backup and restores
  • management and cost savings from on-prem.
  • native virtual network support
  • AAD enabled

Includes: single instances, instance pools

SQL Server on Azure VMs

Lift-and-shift your SQL Server workloads with ease and maintain 100% SQL Server compatibility and operating system-level access.

Deployment options

  • large number of databases and share resources/cost across multiple database
  • DTU and vCore options (preferred)
  • all other azure sql options only provide "provisioned compute"
  • azure sql database offers "provisioned compute" or "serverless compute"

Serverless

  • good for intermittent or unpredictable workloads
  • per second cost basis max memory, processor

Hyperscale

  • large scale clients
  • different architecture behind the scenes
  • unlimited storage

Resilience

Geo replication

  • multiple replicas in multiple - including same - region
  • multiple replicas is useful for read-scale
  • not available in managed instance

Failover groups

  • cannot be in same region
  • can only choose one other region for replica
  • manual and automatic failover - connection strings stay the same

Link 1

Security

Data at rest

  • data at rest is protected with encryption using transparent data encryption (tde)
  • don't store data in master database - master is not encrypted

Data in transit

  • protected with ssl
  • on connection string use:
    • encrypt: true
      • demand connection encryption
    • trustServerCertificate: false
      • force verification of server certificate
# sample connection string
Server=tcp:my.database.windows.net,1433;Initial Catalog=kaml;Persist Security Info=False;User ID=dbadmin;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Always Encrypted is another feature to protect sensitive data, but has disadvantages like prevents certain types of queries. Read further information for details:

source

Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (for example, U.S. social security numbers), stored in Azure SQL Database or SQL Server databases.

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server).

As a result, Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access.

By ensuring on-premises database administrators, cloud database operators, or other high-privileged unauthorized users, can't access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to store their data in Azure, and enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

⚠️ **GitHub.com Fallback** ⚠️