SQL Database in Microsoft Fabric

Key Concepts

• Microsoft Fabric
• OneLake
• Delta Parquet format
• SQL database in Microsoft Fabric
• Serverless compute
• HTAP (Hybrid Transactional Analytical Processing)
• SQL endpoint
• Analytics endpoint
• Near real-time mirroring
• Fabric compute units (Capacity Units)
• AI-ready database
• Vector search
• Retrieval Augmented Generation (RAG)

Microsoft Fabric and OneLake

• Microsoft Fabric is built on OneLake, which uses the Delta Parquet format.
• Problem: Previously, different data services had proprietary formats, leading to data silos, transformation overhead, data drift, and multiple copies of data.
• Solution: Fabric re-engineered data engines (Data Factory, Data Engineering, Data Science, Data Warehouse, Real-Time Intelligence, Power BI, Lakehouse) to use the Delta Parquet format on OneLake.
• This eliminates the need for data movement and copies, enabling seamless data access across different tools.
• Workspaces allow for organizing data views.
• Billing is unified under a common compute unit (Capacity Units).
• Data in OneLake can be discovered, classified, and protected using technologies like Purview.
• Co-pilots and AI capabilities are integrated, including Fabric AI data agents.
• Shortcuts: Allow accessing data in other locations (e.g., other clouds) without copying it. Data remains in its original location but appears as if it's in OneLake.
• Mirroring: Used to copy data from traditional databases into OneLake.

The Need for SQL Database in Fabric

• HTAP (Hybrid Transactional Analytical Processing): The need to perform both transactional processing and analytics on the same data.
• Traditional databases are separate entities, requiring complex integration steps for analytics.
• App teams want a SQL instance that is pre-integrated with analytical tools and easy to set up and manage.

SQL Database in Microsoft Fabric

• The SQL database in Microsoft Fabric is based on the Azure SQL database engine running in serverless mode.
• Serverless Benefits:
  • Autoscaling: Scales between 16 and 32 vCores automatically.
  • Auto-pausing: Pauses compute after 15 minutes of inactivity (storage costs still apply).
• The SQL database writes MDF (primary data files) and LDF (transaction logs), but these are not stored in OneLake directly.
• Database Size Limit: 4 TB maximum database size.
• AI-Ready: Built-in vector capabilities for semantic search and integration with generative AI.
  • Supports vector search for high-dimensional representations of data meaning.
  • Enables Retrieval Augmented Generation (RAG) for AI applications.
• Auto-Optimization: Automatically adjusts indexes based on query patterns.
• High Availability (HA) and Disaster Recovery (DR): Handled automatically.
• Native Backups: Based on zone-redundant storage (if the region supports it).
• Region Dependency: The SQL database lives in the same Azure region as the Fabric instance.
• Permissions: Fabric workspace roles (administrator, contributor, viewer) map directly to the SQL database.
• New database users can also be created.

Creating a SQL Database in Fabric: Step-by-Step

1. In Fabric, select "New" and then "SQL database" (currently in preview).
2. Give the database a name.
3. Click "Create."
4. The database is created with default settings (autoscaling, auto-sleep, HA/DR).

Under the Hood: Mirroring and Endpoints

• The SQL database is created in a Microsoft-managed Azure subscription (not visible in your own Azure subscription).
• Data written to the SQL database is automatically mirrored to OneLake in near real-time using a special mirroring technology.
• Two Endpoints:
  • SQL Endpoint (Transactional): Used for regular transactional interactions (writing data).
  • Analytics Endpoint: Used for read-only analytics against the mirrored data in OneLake (Delta Parquet format).
• Changes made through the SQL endpoint are reflected in the analytics endpoint in near real-time.
• Private endpoints at the workspace level for SQL endpoints are on the roadmap (currently only tenant-level private endpoints are supported).
• Co-pilot is integrated for help with queries and table operations.

Pricing

• Pricing is based on Fabric compute units (Capacity Units).
• SQL vCore usage is converted to Fabric compute units.
• The cost includes auto-management, autoscaling, HA/DR, and other built-in features.
• Billing examples and mapping of capacity units to vCores per second are available.

Feature Comparison and Decision Guide

• The SQL database in Fabric is a simplified offering compared to Azure SQL database.
• It is designed for app developers who need a SQL database with built-in analytics integration.
• Feature comparison documentation is available to understand the differences in functionality.
• The Fabric SQL database is auto-managed, so some configuration options available in Azure SQL database are not present.
• A decision guide helps determine whether the Fabric SQL database or another option (e.g., Azure SQL database Hyperscale) is the right choice.
• Hyperscale is not only for massive requirements; it can be the right solution in many scenarios.

Conclusion

The SQL database in Microsoft Fabric provides a simple, integrated solution for transactional processing and analytics. It offers automatic scaling, management, and AI capabilities, making it a good choice for app developers who need a SQL database with built-in analytics. However, it is a simplified offering, so it's important to review the feature comparison and decision guide to determine if it meets specific requirements.