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