Copilot in Azure SQL
It has been quite clear in the last months that Microsoft is investing, heavily, in Copilot. Every Microsoft product either has a reference to Copilot or it's already available to you. This all depending on your licenses.
When I open my Teams for instance, there's a Copilot logo looking me right in the face, ready for all my questions.
The same goes for Copilot in Azure SQL. Copilot is available in the Azure portal, waiting on your prompts. And as long as your prompt makes sense, the answer will make sense as well. If you've been following me on my personal blog, you might have seen some blogposts on this subject already, I won't repeat that content here. But I would like to show you something that didn't work at the time of writing of those blogs.
SQL Server maintenance questions
I'm going to ask Copilot a simple, short and maybe even blunt question in the Azure portal Query editor.
Click generate query to see the results.
This is the result of the prompt.
That doesn't look too bad, right?
When I run the query, it compiles without errors and returns a valid result for this specific database.
This serves as a very nice example on how Copilot can and will help you. Experienced DBA's probably will point out that rebuilding indexes on tables that have two pages (one page equals 8 Kb of data) doesn't make much sense. And they'd be right. That's where experience comes into play. Copilot is here to help you at the best of its ability but you should always check the results and finetune the query before running it on production.
But why this short demo of how Copilot works in Azure Sql?
Well, there was this announcement:
SQL Server 2025
As announced at Ignite, Microsoft will release SQL Server 2025 next year. And looking at all the Copilot integrations we've seen, it won't come as a huge surprise that this new version will also support a lot of AI functionality.
Vector search
If you've been working with AI models, you'll probably know that RAG models use vectors to determine if they're on the right path in answering your questions. Now, SQL Server 2025 will be able to store vector data to feed your models. Which can be pretty cool if you're developing your own, on-premises AI model.
Your own AI model
SQL Server 2025 will also get the option to connect to your own AI model. You create an external model with similar syntax to creating an external data store or external table in Azure Synapse Analytics. When it's created, you can use it in your queries to leverage your model, for instance one hosted on Azure Open AI.
In the end, you won't store the model itself in the SQL engine, but just all the vectors. This is called an embedding. And yes, we need to learn some new terms with this new technology inside SQL Server.
What about performance?
Well, this all sounds really cool but what about performance? Well, as you might have heard, there will be some sort of indexing that should make it all run fast. To be honest with you, I'm really curious as to how this is going to work in real life. Things like index maintenance, execution plans and optimization will be high on my list to check out and try to understand. And I'm sure people like Hugo Kornelis will have a lot of fun digging very deep into these things.
Connection to a remote model
If you want to connect to a remote model, there will be a stored procedure called sp_invoke_externel_rest_endpoint. This name implies that you can use it for other REST endpoints as well and this is the case. So getting data from a REST API directly into SQL Server should be possible. another fun thing to dig into as it removes the need for an SSIS process to do that. How it will work will depend on syntax and security but nonetheless a cool feature to dig into.
Concluding
I think this is a logical step for SQL Server 2025 to make, connect to an AI model and increase the value of our data in the database. Now, I started this blog with Copilot and that one didn't really return in the SQL 2025 blog. Because it doesn't have to. Copilot for SQL Server on-premises is on the roadmap of SQL Server Management Studio 21. This means that not only can we use our own models to enrich our data and get more value from it, there's also a Copilot standing by to help with writing the queries and lending a hand creating the maintenance scripts. As stated before, always check the outcome of your prompt and when in doubt, ask the expert and run it in a safe, non-production environment.
I think that when you combine SQL Server 2025 with Management Studio 21, you're ready for the future!