SQL in Microsoft Fabric| Developer-Friendly & Seamless Integration

In the evolving field of data analytics and AI, Microsoft Fabric stands out as a unified platform designed to simplify complex tasks. Its SQL database is a developer-friendly transactional database that integrates seamlessly with operational workloads. Built on the widely adopted Azure SQL Database engine, it allows database professionals to use familiar tools, minimizing the learning curve and enhancing productivity. This blog post will explore how this familiarity helps developers and database administrators effectively manage SQL databases within Microsoft Fabric.

Getting Started and Accessing SQL Database in Fabric
Data Replication and OneLake Integration
Querying and Data Manipulation
Data Engineering and Data Science Integration
Security and Sharing
Management and Monitoring
Availability and Reliability
Database Portability and Development
Relationship with Azure SQL Database and Mirroring
Troubleshooting and Support
Learning Resources and Community

Key Takeaways

SQL database in Microsoft Fabric (preview) is a developer-friendly transactional database built on the Azure SQL Database engine, fully integrated within the Microsoft Fabric platform.
It is designed for OLTP workloads within Fabric and is easy to configure and manage.
A key feature is the automatic, near real-time replication of data into OneLake in the Delta Parquet format, making it readily available for analytics across all Fabric engines.
This automatic replication to OneLake cannot be disabled.
This integration provides a unified data estate, simplifying end-to-end analytics and AI solutions.
Connectivity is similar to Azure SQL Database, using Microsoft Entra authentication, and the only supported connection policy is Redirect.
Data within the SQL database in Fabric remains within the customer's Fabric tenant and is staged in their OneLake.
Billing for compute, storage, and backup will be tied to your Fabric capacity starting in 2025 . Currently, during the preview, there is no billing.
You can perform cross-database queries by leveraging the SQL analytics endpoint.
Fabric provides integrated data engineering and data science capabilities that can directly work with the replicated data in OneLake.
SqlPackage can be used for database portability and deployments, and there is integration with Fabric source control (git).
Mirroring in Fabric allows you to bring data from existing Azure SQL Databases into OneLake.
Microsoft Fabric aims to simplify complex analytics scenarios by offering an integrated, easy-to-use, and autonomous experience for operational databases.
SQL database in Fabric includes native AI capabilities and integration with Azure AI services and Copilot

The Power of a Familiar Engine: Built on Azure SQL Database

The core of SQL database in Fabric lies in its underlying technology: it uses the same SQL Database Engine as Azure SQL Database. This is not merely a superficial resemblance; it's a fundamental architectural decision that brings a wealth of benefits rooted in a proven and mission-critical database engine. For developers, this translates directly into familiar T-SQL syntax, data types, and core database concepts that remain consistent. You can expect the same robust query language, the same principles of relational database design, and the same transactional integrity that you have come to rely on with Azure SQL Database. This familiarity extends beyond the basics. The intelligent performance features that have made Azure SQL Database a compelling choice are also enabled by default in SQL database in Fabric, including automatic index creation with Automatic Tuning. This ensures that your databases benefit from built-in optimization without requiring extensive manual intervention, further enhancing the familiar and efficient operational experience. The decision to build upon the Azure SQL Database engine underscores Microsoft's commitment to providing a reliable and performant operational database within Fabric while simultaneously making it accessible to a broad audience of developers and database professionals who are already well-versed in its intricacies.

Connecting with Your Go-To Tools

One of the key aspects of a developer-friendly experience is the ability to use the tools you are already comfortable with. SQL database in Fabric delivers on this front by allowing you to connect to it in the same ways you connect to Azure SQL Database. This seamless connectivity eliminates the need to learn entirely new client tools or connection methodologies, streamlining your workflow and allowing you to hit the ground running. Among the most prominent tools supported are:

SQL Server Management Studio (SSMS)

For decades, SSMS has been the cornerstone of SQL Server and Azure SQL Database management. Its intuitive graphical interface provides a comprehensive suite of features for database administration, development, and querying. The fact that users can likely use their existing SSMS installations to connect to SQL database in Fabric is a significant advantage. You can leverage SSMS to browse database objects, design tables, write and execute complex T-SQL queries, manage security, and perform various administrative tasks, all within a familiar environment.

MSSQL extension with Visual Studio Code

In today's developer landscape, Visual Studio Code (VS Code) has emerged as a highly popular and versatile code editor. The mssql extension for VS Code provides a rich set of features specifically designed for working with SQL Server, Azure SQL Database, and now, SQL database in Fabric. This cross-platform extension allows developers to write and execute T-SQL queries directly within their code editor, offering features like IntelliSense, query result visualization, and even basic database object browsing. Its tight integration with development workflows makes it an invaluable tool for developers who prefer to stay within their coding environment for database interactions.

Beyond these dedicated client tools, Microsoft Fabric also provides a web-based editor within the Fabric portal. This lightweight and easily accessible editor offers a convenient way to run quick queries, explore data, and perform basic database operations directly from your web browser without the need to install any additional software. While it may not offer the full breadth of features found in SSMS or the mssql extension, it provides a valuable and readily available option for on-the-go database interaction.

The supported connection policy for SQL database in Microsoft Fabric is currently Redirect. For users needing to configure firewall rules, it's important to refer to the Azure IP Ranges and Service Tags - Public Cloud for a list of your region's IP addresses to allow. Like other Microsoft Fabric item types, SQL databases rely on Microsoft Entra authentication. To successfully connect, a Microsoft Entra user, service principal, or their group must have the Read item permission for the database in Fabric. This consistent authentication model across the Fabric ecosystem further contributes to a unified and familiar experience.

Streamlined Development Workflows

The support for familiar tooling naturally leads to streamlined development workflows. You can seamlessly integrate SQL database in Fabric into your existing development processes without significant disruptions or the need to adopt entirely new paradigms.

The availability of SqlPackage, a cross-platform command-line tool, further enhances this portability and enables various deployment scenarios. SqlPackage allows for database interactions such as import and export of entire databases (.bacpac files), facilitating migration if needed. It also enables the deployment of incremental changes to database objects by extracting and publishing .dacpac files, integrating well with SQL projects for offline and dynamic development cycles.
Microsoft Fabric also integrates SQL database with continuous integration/continuous development (CI/CD) practices through its built-in git repository. This allows developers to manage their SQL database schema and scripts under version control, fostering collaboration and ensuring a consistent and auditable development process – a familiar and essential practice for modern development teams.
For developers looking to expose their SQL database data through modern APIs, Microsoft Fabric allows you to easily create a GraphQL API from the Fabric portal. This provides another familiar and powerful way for applications to interact with your operational data.
The integration of GitHub Copilot within Fabric, including SQL in Fabric, further simplifies development tasks. This AI-powered assistant can help with code generation and query writing and even provide suggestions for database management tasks directly within your familiar development environments like VS Code.

Advanced Features with Familiar Tools

The power of familiar tools extends beyond just running basic SELECT statements. They enable you to leverage the more advanced features and insights offered by SQL databases in Fabric.

For monitoring and troubleshooting, you can use the supported tools to examine Dynamic Management Views (DMVs). For instance, you can execute queries against sys.dm_change_feed_log_scan_sessions to check if data replication to OneLake is progressing correctly. If issues arise, querying sys.dm_change_feed_errors can help identify reported problems. This ability to use familiar DMVs for introspection is invaluable for maintaining the health and performance of your database.
The familiar T-SQL syntax and connection capabilities also allow you to take advantage of cross-database querying within Fabric. Since your SQL database data is automatically stored in OneLake, you can write queries that join data from other SQL databases, mirrored databases, warehouses, and the SQL analytics endpoint in a single T-SQL query. Using three-part naming conventions, you can easily reference tables across different Fabric items. This powerful capability allows for a unified view of your data estate, enabling rich analytical scenarios without the need for complex data movement or integration pipelines. For example, you can query a table in a SQL database alongside a table in a data warehouse using a syntax that will be instantly recognizable to any SQL developer.
While managed through Fabric's sharing and security settings, the underlying principles of database security, such as controlling access to sensitive information, can be implemented and managed using the familiar database management paradigms offered by tools like SSMS. Although the configuration might occur within the Fabric portal, the understanding of concepts like row-level security (RLS) and object-level security (OLS) will be directly applicable and manageable through familiar administrative interfaces.

Empowering Data Engineers and Scientists

The developer-friendly nature of SQL in Fabric, coupled with its integration with familiar tools, extends its benefits to data engineers and data scientists within the Microsoft Fabric ecosystem.

Data Engineers can leverage their SQL knowledge and the supported tools to interact with data that is also accessible through Spark. They can design pipelines to copy data into SQL database in Fabric, perform data transformations using familiar SQL constructs, and generally integrate SQL databases into their broader data engineering workflows. The ability to use notebooks for data preparation and transformation alongside SQL databases further enhances this synergy.
Data Scientists can also benefit from the familiarity of SQL tools for data exploration, preparation, and cleansing. They can use these tools to query and understand the data residing in SQL databases, which can then be integrated into their machine-learning experiments and model development processes within Microsoft Fabric. The ability to access and manipulate data using familiar SQL interfaces lowers the barrier to entry for data scientists looking to leverage operational data for their analytical models.

Conclusion

SQL in Microsoft Fabric truly offers a developer-friendly experience by building on the strong foundation of Azure SQL Database and embracing widely used and familiar tools like SQL Server Management Studio (SSMS) and the mssql extension for Visual Studio Code. This deliberate design choice brings numerous benefits, most notably a reduced learning curve, more efficient workflows, and the ability to leverage existing skills and expertise. Whether you are managing database schemas, writing complex queries, monitoring performance, or integrating operational data into broader analytics and AI initiatives, the familiarity of the tooling ensures a smooth and productive experience.

As you embark on your journey with Microsoft Fabric, the ability to use your favorite SQL tools will undoubtedly be a significant advantage. We encourage you to explore SQL database in Fabric and experience firsthand the ease of use and the power it brings to operational workloads within a unified analytics platform. Remember that the SQL database in Fabric is currently in preview, and Microsoft continues to enhance its capabilities. We recommend staying updated with the official documentation and tutorials to discover the latest features and best practices. Embrace the familiarity, unlock the potential, and build innovative solutions with SQL in Microsoft Fabric and the tools you already know and trust.