# SQL Server & Azure Data Platform SQL Server & Azure Data Platform

Level-500-Referenz zu SQL Server, Azure SQL Database, Managed Instance, Synapse, Data Factory, Databricks, Microsoft Fabric, Azure Data Explorer und Purview. Level 500 reference for SQL Server, Azure SQL Database, Managed Instance, Synapse, Data Factory, Databricks, Microsoft Fabric, Azure Data Explorer, and Purview.

SQL Server SQL Server
OLTP, Security, HA/DR OLTP, security, HA/DR
Azure SQL Azure SQL
Managed PaaS-Modelle Managed PaaS models
Fabric Fabric
OneLake und Analytics-Suite OneLake and analytics suite
KQL KQL
ADX und Telemetriedaten ADX and telemetry data
💡 Datenplattformen lassen sich nur über Workload-Muster sauber auswählen 💡 Data platforms can only be chosen cleanly through workload patterns

Editionen, Service Tiers und Werkzeuge sind nur die sichtbare Oberfläche. Entscheidend sind RPO/RTO, Kompatibilitätsanforderungen, Query-Muster, Governance, Kostenmodell und Betriebsverantwortung. Editions, service tiers, and tools are only the visible surface. What matters are RPO/RTO, compatibility requirements, query patterns, governance, cost model, and operating responsibility.

SQL Server SQL Server

Installation, Security, HA, Tuning Installation, security, HA, tuning

Azure SQL Azure SQL

DB, MI, geo-replication, tiers DB, MI, geo-replication, tiers

Synapse, ADF, Databricks Synapse, ADF, Databricks

Pipelines, Spark, Delta Pipelines, Spark, Delta

Fabric, ADX, Purview Fabric, ADX, Purview

Lakehouse, KQL, data catalog Lakehouse, KQL, data catalog

## SQL Server on-premises: Editions, Security, HA/DR und Performance SQL Server on-premises: editions, security, HA/DR, and performance

Bereich Area | Wichtige Punkte Key points | Werkzeuge Tools | Hinweis Note
--- | --- | --- | ---
Editions Editions | Express, Standard, Enterprise, Developer mit klaren Limits Express, Standard, Enterprise, Developer with clear limits | Setup, edition comparison Setup, edition comparison | Developer nie produktiv einsetzen Never use Developer in production
Security Security | Logins, Users, Roles, TDE, Always Encrypted, auditing Logins, users, roles, TDE, Always Encrypted, auditing | SSMS, T-SQL, Azure Key Vault integration SSMS, T-SQL, Azure Key Vault integration | Schlüsselverwaltung und Anwendungskompatibilität früh prüfen Review key management and app compatibility early
Always On AG Always On AG | Mehrere replizierte Datenbanken mit Listener Multiple replicated databases with listener | WSFC, synchronous/asynchronous commit WSFC, synchronous/asynchronous commit | Read-only routing und quorum design berücksichtigen Consider read-only routing and quorum design
FCI FCI | Instanzweite Hochverfügbarkeit Instance-level high availability | Shared storage, WSFC Shared storage, WSFC | Schützt nicht vor Storage-Ausfall Does not protect from storage failure
Log Shipping Log shipping | Einfaches DR über Log-Backups Simple DR via log backups | Agent jobs, backup/copy/restore Agent jobs, backup/copy/restore | Geringer Funktionsumfang, aber robust Limited functionality, but robust
Replication Replication | Transactional, merge, snapshot Transactional, merge, snapshot | Publisher, distributor, subscriber Publisher, distributor, subscriber | Topologie und Schemaänderungen exakt planen Plan topology and schema changes precisely
Query Store Query Store | Persistiert Plan- und Laufzeithistorie Persists plan and runtime history | SSMS, ALTER DATABASE SSMS, ALTER DATABASE | Pflichtwerkzeug für Regressionen Mandatory tool for regressions
Indexes & Statistics Indexes and statistics | Clustered, nonclustered, filtered, columnstore Clustered, nonclustered, filtered, columnstore | sp_BlitzIndex, DMVs, dbatools sp_BlitzIndex, DMVs, dbatools | Workload-getrieben optimieren, nicht dogmatisch Optimize by workload, not dogma
Backup/Restore Backup/restore | Full, differential, log, copy-only, checksums Full, differential, log, copy-only, checksums | SQL Agent, maintenance jobs, dbatools SQL Agent, maintenance jobs, dbatools | Restore-Tests sind Teil der Sicherung Restore tests are part of backup

T-SQL T-SQL

    BACKUP DATABASE SalesDB
      TO DISK = 'E:\SqlBackups\SalesDB_full.bak'
      WITH COMPRESSION, CHECKSUM, STATS = 5;

    ALTER DATABASE SalesDB SET QUERY_STORE = ON;
    ALTER DATABASE SalesDB
      SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

## Azure SQL Database und Azure SQL Managed Instance Azure SQL Database and Azure SQL Managed Instance

Service Service | Stärken Strengths | Wann wählen When to choose | Hinweis Note
--- | --- | --- | ---
Azure SQL Database Azure SQL Database | Voll gemanagter PaaS-Dienst Fully managed PaaS service | Cloud-native Applikationen Cloud-native applications | DTU versus vCore und Serverless bewusst wählen Choose DTU versus vCore and serverless deliberately
General Purpose General Purpose | Ausgewogenes Kosten-/Leistungsmodell Balanced cost/performance model | Standard-OLTP Standard OLTP | Remote storage mit anderen Latenzeigenschaften Remote storage has different latency characteristics
Business Critical Business Critical | Lokale SSD und mehrere Replikas Local SSD and multiple replicas | Niedrige Latenz, hohe IOPS Low latency, high IOPS | Kosten höher, dafür bessere HA Higher cost, better HA
Hyperscale Hyperscale | Große Datenbanken und schnelles Scale-out Large databases and fast scale-out | Very large OLTP/HTAP Very large OLTP/HTAP | Architektur von storage and compute entkoppelt Architecture decouples storage and compute
Elastic Pool Elastic pool | Ressourcen-Sharing über viele Datenbanken Resource sharing across many databases | SaaS multi-tenant patterns SaaS multi-tenant patterns | Noisy neighbor mit Telemetrie beobachten Watch noisy neighbors with telemetry
Serverless Serverless | Auto-pause und automatische Skalierung Auto-pause and auto-scale | Intermittierende Last Intermittent workloads | Cold start und Resume-Zeit berücksichtigen Consider cold start and resume time
Managed Instance Managed Instance | Nahezu vollständige SQL-Server-Kompatibilität Near-complete SQL Server compatibility | Lift-and-shift oder Vendor-App-Migration Lift-and-shift or vendor app migration | VNet- und DNS-Design entscheidend VNet and DNS design are critical
MI Link MI link | Replikation zwischen SQL Server und MI Replication between SQL Server and MI | Nahtlose Migration oder DR Seamless migration or DR | Latenz- und Cutover-Plan testen Test latency and cutover plan
Failover Groups Failover groups | Geo-failover auf Anwendungsebene Geo failover at the application layer | Regionale Resilienz Regional resilience | Anwendungsverbindungsstrings sauber entkoppeln Decouple application connection strings cleanly

## Azure Synapse Analytics, Azure Data Factory und Databricks Azure Synapse Analytics, Azure Data Factory, and Databricks

Dienst Service | Kernfunktion Core function | Typische Komponenten Typical components | Hinweis Note
--- | --- | --- | ---
Synapse Dedicated SQL Pool Synapse dedicated SQL pool | MPP Warehouse MPP warehouse | Distributions, resource classes, columnstore Distributions, resource classes, columnstore | Nicht wie klassische SQL-DB modellieren Do not model like a classic SQL DB
Synapse Serverless SQL Synapse serverless SQL | Ad-hoc Analyse über Dateien Ad-hoc analysis over files | OPENROWSET, external tables OPENROWSET, external tables | Kosten nach Datenvolumen im Scan Costs depend on scanned data volume
Synapse Spark Synapse Spark | Big Data und ETL Big data and ETL | Notebooks, Spark pools Notebooks, Spark pools | Governance und package management definieren Define governance and package management
ADF Pipeline ADF pipeline | Orchestriert Datenbewegung und Transformation Orchestrates data movement and transformation | Copy, Lookup, Web, Stored Procedure Copy, Lookup, Web, Stored Procedure | Parameterisierung und IR-Wahl beachten Mind parameterization and IR choice
Integration Runtime Integration runtime | Ausführungsumgebung für ADF Execution runtime for ADF | Azure, Self-hosted, SSIS IR Azure, self-hosted, SSIS IR | Netzwerkpfade und Secret-Zugriff sauber absichern Secure network paths and secret access cleanly
Azure Databricks Azure Databricks | Managed Apache Spark mit Delta Lake Managed Apache Spark with Delta Lake | Clusters, jobs, notebooks, Delta tables Clusters, jobs, notebooks, Delta tables | Unity Catalog und cluster policies operationalisieren Operationalize Unity Catalog and cluster policies
MLflow MLflow | Experiment- und Modellmanagement Experiment and model management | Runs, artifacts, model registry Runs, artifacts, model registry | MLOps-Prozess an Datenplattform koppeln Tie MLOps to the data platform process

Azure CLI Azure CLI

    az sql db create `
      --resource-group rg-data-prod `
      --server sql-prod-01 `
      --name appdb-prod `
      --service-objective GP_Gen5_4

    az sql failover-group create `
      --name fog-prod `
      --partner-server sql-prod-dr `
      --server sql-prod-01 `
      --resource-group rg-data-prod `
      --add-db appdb-prod

## Microsoft Fabric, Azure Data Explorer und Purview Microsoft Fabric, Azure Data Explorer, and Purview

Plattform Platform | Wofür What for | Bausteine Components | Hinweis Note
--- | --- | --- | ---
Microsoft Fabric Microsoft Fabric | Einheitliche Analytics-Suite über OneLake Unified analytics suite on top of OneLake | Lakehouse, Warehouse, Data Pipeline, Dataflow Gen2, Real-Time Intelligence Lakehouse, warehouse, data pipeline, Dataflow Gen2, Real-Time Intelligence | Kapazität und Domänenmodell zentral planen Plan capacity and domain model centrally
OneLake OneLake | Einheitlicher Storage-Layer Unified storage layer | Shortcuts, domains, security Shortcuts, domains, security | Datei- und Tabellen-Governance vereinheitlichen Unify file and table governance
Azure Data Explorer Azure Data Explorer | KQL-basierte Telemetrie- und Log-Analytik KQL-based telemetry and log analytics | Ingestion, update policies, dashboards Ingestion, update policies, dashboards | Ideal für Zeitreihen und hochvolumige Events Ideal for time series and high-volume events
Purview Data Map Purview data map | Metadateninventar Metadata inventory | Collections, scans, classifications Collections, scans, classifications | Nur wertvoll mit konsequenter Scan-Strategie Valuable only with a consistent scan strategy
Lineage Lineage | Nachverfolgt Datenherkunft und Abhängigkeiten Tracks data origins and dependencies | ADF, Power BI, Fabric integrations ADF, Power BI, Fabric integrations | Hilft bei Change Impact und Audit Helps with change impact and audit
Business Glossary Business glossary | Geschäftsdefinitionen zentralisieren Centralizes business definitions | Terms, stewards, mappings Terms, stewards, mappings | Nur mit Data Ownership sinnvoll Useful only with clear data ownership

KQL KQL

    StormEvents
    | where StartTime > ago(30d)
    | summarize Events = count(), Fatal = countif(DeathsDirect > 0) by State, EventType
    | top 20 by Events desc