Build autonomous SQL databases in Microsoft Fabric

Spin up SQL databases in seconds with Microsoft Fabric, seamlessly integrating your operational and analytical data across multiple clouds. Use AI to scale, tune, and secure databases, so you can focus on building powerful, data-rich applications without the manual management overhead.

Create and host API endpoints directly from your SQL database in one click, enabling seamless interaction with your data using popular frameworks like React. Integrate your database management into your CI/CD pipelines with ease, ensuring high availability and streamlined development processes.

Anna Hoffman, Principal GPM, SQL database in Microsoft Fabric, joins Jeremy Chapman, Director of Microsoft 365, to show you how to get started.

Spin up a SQL database in seconds.

Quickly integrate operational and analytical data without needing deep database management skills. Get started with Microsoft Fabric.

Focus on building your app.

Microsoft Fabric’s AI-powered SQL databases handle security, performance, and availability autonomously. See it here.

Streamline the development process & enhance data integration.

Microsoft Fabric’s SQL databases enable direct API creation in one click. Check it out.

Watch our video here.

QUICK LINKS:

00:00 — SQL database in Microsoft Fabric
00:38 — A new class of autonomous database
01:44 — Spin up a database in seconds
03:09 — Use APIs to read and write data
05:00 — Integrate into CI/CD pipeline & source control
05:41 — Bring in data from across your data estate
06:50 — Vector-based semantic search with keyword search
09:03 — See a finished app
11:12 — Query performance
12:31 — Security and control
13:04 — Wrap up

Link References

Sign up for a free trial at https://aka.ms/SQLinFabric

Unfamiliar with Microsoft Mechanics?

As Microsoft’s official video series for IT, you can watch and share valuable content and demos of current and upcoming tech from the people who build it at Microsoft.

• Subscribe to our YouTube: https://www.youtube.com/c/MicrosoftMechanicsSeries

• Talk with other IT Pros, join us on the Microsoft Tech Community: https://techcommunity.microsoft.com/t5/microsoft-mechanics-blog/bg-p/MicrosoftMechanicsBlog

• Watch or listen from anywhere, subscribe to our podcast: https://microsoftmechanics.libsyn.com/podcast

Keep getting this insider knowledge, join us on social:

• Follow us on Twitter: https://twitter.com/MSFTMechanics

• Share knowledge on LinkedIn: https://www.linkedin.com/company/microsoft-mechanics/

• Enjoy us on Instagram: https://www.instagram.com/msftmechanics/

• Loosen up with us on TikTok: https://www.tiktok.com/@msftmechanics

Video Transcript:

- What if I told you you can now spin up a SQL database in seconds that is seamlessly connected to operational and analytical data sitting across multiple clouds, has built-in vector support for search, autonomously scales and tunes itself to meet demand, and is integrated with AI for easy querying and troubleshooting? Well, today, we’ll take a closer look at a new class of databases that’s part of Microsoft’s data and analytics platform, Microsoft Fabric, that does just that as you build modern AI apps. And joining me to unpack all this is Anna Hoffman from the SQL Engineering Team, welcome.

- Thanks for having me on the show,

- And thanks so much for joining us today. So we have a new class of autonomous database with Microsoft Fabric, so what’s behind all this?

- It’s really a significant leap forward for databases. Of course, every app needs data and Microsoft Fabric by design makes data more accessible, whether that’s operational or analytical data from across your data estate. Now, anyone using Fabric without being a database or infrastructure expert can add a database that’s automatically integrated with a Fabric platform so you can build more powerful and data-rich apps for analytics.

- And this is different from current PaaS databases, right? While those can automatically scale, there’s often some bit of manual expertise needed to configure and also tune and optimize the performance of those, right?

- Right, this is totally different. This new class of database in Fabric is autonomous, and beyond automatic scale, they handle everything from provisioning along with index tuning, applying best practice security controls, automatic updates, and using multiple availability zones by default for high availability, which means there’s less manual burden and you can focus on just building your app.

- And the first autonomous database here in this case is a SQL relational database. So how easy is it to spin up one of these if you’re a developer?

- Oh, it’s super easy. One of the standout capabilities of autonomous SQL databases in Fabric is their simplicity. You can spin one up in seconds. This lets you focus on building apps rather than managing a database. Let me show you. I’m in the Fabric home experience where you can see the different workloads in Fabric, which now includes databases, and you’ll see the option for SQL databases. And as we mentioned, SQL is the first database to land in Fabric with more on the way. To create one, I’ll just give it a name, I’ll call this Dev and hit Create, and in seconds, I have a SQL database ready to go. We land in the editor and on the left you can see the familiar Object Explorer. You can see it’s currently empty. To get data, I can create data flows or a pipeline to bring external data in right from here. I can also create shortcuts. This doesn’t move the data, but it gives the ability to query other data sources, like OneLake, and even external source options like Amazon S3 buckets, Azure Data Lake storage and more. And from the quick actions in the center, I can see options to get started quickly. I’m going to start by importing some sample data. Once my sample data is ready, I can expand my tables and you can see the data structure. I can open up my product table to get a data preview and that’s it.

- Okay, so now you’ve got your database set up. How would you work with that as part of your app?

- Yeah, of course, you can do this using T-SQL, that’s fully supported. But what can be more powerful and flexible is using APIs to read and write this data. Databases in Fabric are the first to let you directly create and host an API endpoint to your data in one click. Let me show you. Here in the top ribbon, I have some new options to create a new query, access templates, or create a new GraphQL API. I’ll do that, name it api1, and then confirm. Here in Choose data, I can select the tables that I want to be exposed in my API. I can see a preview for the tables I select, then I just select Load and it just takes a moment. It takes me to the GraphQL playground where I can start writing queries. I’m going to paste in a query I wrote before, then go ahead and run it. Now choose to Generate code. This part’s really powerful because this Python code I can use to interact directly from my app. I’m going to go ahead and copy this sample, and from there, I can move over to my dev environment, in this case VS Code, and paste it in, can run it right from here. And in the terminal, you’ll see that it just works and it’s printed the results of my query. So if you work in React or other front end frameworks and are using APIs, as a developer, you don’t need to build intermediary services or install drivers to work with this data.

- Okay, so it’s a really streamlined dev experience, but from a SQL perspective, is the SQL database that’s in Fabric the same one that we’re used to from a developer’s perspective?

- Yeah, great question. So this is the exact same SQL server in Azure SQL database engine, it’s familiar, you don’t need to learn anything new. The experience is just more simple and it’s integrated into the whole data and analytics stack.

- Right, and like you’ve shown, you don’t have to be a SQL database expert to create one right inside of Microsoft Fabric, and you can even generate an API in order to use that with your code.

- And everything you do can be integrated into your CI/CD pipeline and source control as part of your change management process. Let me show you. From Microsoft Fabric, you can commit changes directly into source control. You can see this task flow shows the solution for our data tier and it’s fully integrated with Git. And I’ll go ahead and select all of my changes and commit them. That will run for a moment, and once it’s complete, these changes are also visible here in my Azure DevOps repo. And this can also work with GitHub too. I’ll head back to Fabric and here I can take advantage of deployment pipelines to move through the release cycle. Here you can see that I’ve added stages to move from Dev to Test to Production. So it’s fully integrated with the DevOps process as you build and maintain your apps.

- And of course, as you mentioned, one of the great things here and the big advantage is part of Microsoft Fabric, so you can easily bring in your data from across your entire data estate to use it with your app. So how does all of that work?

- Yeah, so I’ve shown you how you can create databases for your apps. Let me explain how the data then becomes available to others and how you can also consume data more easily across your data estate. First with SQL database and Fabric, all the data is automatically replicated to Delta Parquet format and lands in near real time into OneLake, giving you a source database endpoint and a SQL analytics endpoint. This means that you can use both your source data and replicated data as a way to load balance operational and analytical processes without them impacting each other. And if you’re new to Fabric, OneLake is the central hub where all data across your estate is represented. It can use shortcuts as references to data wherever it lives, so you can use it in place without moving or duplicating data. This makes it easier to analyze and generate insights and reports. Additionally, real-time intelligence brings streaming and operational data in with fresh insights as they’re happening, which can also be used to trigger actions and automated workflows.

- And with so many developers right now probably watching that are building generative AI apps with retrieval augmented generation, how would something like Microsoft Fabric and the SQL database inside of it support those types of apps?

- So these types of apps work best with vector-based semantic search together with keyword search. This is so you can retrieve the right information to augment your prompts, and we have built-in vector support, so let me show you an example. Back in Fabric, I can navigate to the files in my Lakehouse, and you can see that I have a lot of product documentation for hybrid and electric vehicles and their parts. These are pretty text-heavy PDFs with a lot of written information. We’re going to use Azure AI services and SQL database in Fabric to build a chat experience for asking questions on this data. First we need to extract and chunk the text and we can do that with Azure Document Intelligence. We can reference the PDFs in the Lakehouse and we use the function begin analyze document to extract the text from the PDF and break it up into chunks. We can clean it up and create a data frame or a table with columns like the file name, chunk ID and text. You can see that the code has gone through all the PDFs, extracted the text and generated chunks with unique IDs. Next, we need to generate embeddings, which are numerical representations of the text segments. We’ll use OpenAI to help us do this. Now you can see for every file and chunk, we have a vector representation. Now that we have embeddings, we can use the new native vector type support in SQL database to store the vectors directly in the database. Here we have a simple program that will essentially generate the embedding for a user search query, and then use the built-in vector distance function to find the most similar chunks of text. Here’s an example where we search for how to replace the oil filter and the most similar file segments are returned. We can further enhance this by leveraging LLMs to make it more conversational with completions. Here a user asked how to replace the oil filter on the CA hybrid utility truck and they ask about the minimum tire load ratings and tire pressures for the CA3 model. The AI is able to segment out the request and gives two sections of results, one with details for oil filter replacement and one with load ratings and tire pressures based on the wheel diameters for the CA3.

- Okay, so now we’ve seen all the foundational components kind of in code and the Fabric portal, do we have an example maybe of a completed kind of customer-facing production app?

- Yeah, I do, actually, to go along with our automotive example, I have a web app built out using Microsoft Fabric as the data backend for a vehicle manufacturer and its sales network. So this is our Contoso Automotive website, and you can see right at the top that booking a test drive is a major call to action, so we’re going to walk through that flow. The site also encourages people to sign in to both personalize the experience and make sure that when they return, they can easily pick up where they left off. You can use a Microsoft account or others so that basic data like the username can be queried from the connected account. To find the right vehicle, the questionnaire matches the options to your specific needs. This analysis is also running on the backend in Fabric to find the right model. And using the data, it recommends a personalized tour of the best vehicle match. This car looks great, but you might still have a few questions to ask before committing to a test drive. And you can do this right here using an AI agent for natural language interaction over the app’s knowledge base. You can prompt it with something very specific like, do you offer a vegan leather interior for the CA3? And a question like this could be challenging for a normal keyword search, but because we’re using semantic search with our built-in vector support, it knows that vegan leather is a synthetic material and then responds appropriately. And once you’re ready to schedule a test drive, you can prompt the agent to see if that’s something it can help with. Looks like it can. Let’s continue and ask if it’s okay to do the test drive with a car seat. Looks like that’s okay too. And from here, still using natural language, you can start to arrange a time, like you can see here for something on the weekend. The generated response also includes details about the car, a long range CA3, like we saw before, in the pearl white color and suggests the closest location with a number of available times. Once you confirm the time, the agent confirms, giving you the details for who to meet, and it will send a confirmation email with directions based on your signed in account. And it also creates a customer record with a detailed recap of the interactions along with sentiment and predictions for buying intent and more.

- Okay, so now we’ve got our app running. Then as it gets more popular, how do I ensure that it’s always performant?

- Yeah, great question. So SQL database in Fabric scales automatically to meet demand. The other side of this coin is about query performance, and we have you covered there too. For example, here in the performance dashboard, I can see above normal spikes in CPU consumption across my running queries, and I can drill into any of these spikes for more details and lower time granularity. This query on top is consuming the most CPU in this time period, so let’s see what’s behind that. I can see details for its runs over time and the T-SQL query on the right. I’m going to change the time interval to 24 hours so I can take a look at what’s been going on in the past day. Fabric makes it easy for me to then copy this query and open the Query Editor. Here, I’m going to see if Copilot can help me optimize this query. I’m just going to add a comment to the bottom with a simple prompt that says, “Optimize above query.” It tells me an easy to follow language that it recommends a CTE or common table expression to pre-aggregate the data before joining, then select from the CTE to be more efficient. It gives me the new optimized query that I can verify and use to rewrite my original query. So I can get actionable recommendations from Copilot to make these types of improvements pretty easily.

- I’ve got to say that coding, scripting and querying are some of the best applications at the moment for generative AI. And this is really a simplified provisioning and developer experience then for databases, but sometimes these things can come at the cost of things like security or control.

- Well, Microsoft Fabric is built with security in mind. Access management and compliance really is at its core. Identity and access management is controlled via Microsoft Intra. You also have full role-based options for any people or entity working directly with Fabric. Additionally, Fabric is deeply integrated with data security and compliance tools in Microsoft Purview where it supports sensitivity labels, as well as the protection policies you set across your data estate.

- So now you’ve got everything that you need to build robust data services for your apps, with all the controls that you need to manage it, and as a developer, you can just focus on your code. So what do you recommend for the people who are watching right now to learn more?

- So this one is easy, go try it out. SQL database in Fabric is in public preview today. And to learn more, you can just go to aka.ms/SQLinFabric and sign up for a free trial.

- Thanks so much for joining us today, Anna. And of course, to stay up to date with the latest tech, be sure to subscribe to Microsoft Mechanics, and as always, thank you for watching.