Problem
In the tip Exploring AI Tools for Writing SQL Queries – Are they reliable?, we explored using Artificial Intelligence (AI) tools, ChatGPT and Gemini, for writing SQL queries. As AI demand increases and becomes available in every area, how do we utilize it in Microsoft Azure? Let’s explore the Microsoft Copilot for Azure SQL Database.
Solution
Microsoft offers Copilot, an AI solution that integrates various products like Microsoft 365 Azure Cloud. It is an AI assistant integrated to enhance user productivity automation, streamline the troubleshooting guide, and customize the user help.
Introduction to Microsoft Copilot for Azure
Microsoft Copilot offers integrated AI solutions using large language models (LLMs). It is based on Azure OpenSI service foundation models for user prompts and Copilot responses. It can collaborate with various Azure services and provide intelligent assistance that enhances productivity and improves flexibility in adopting Azure cloud. Users can use the natural language to communicate with the Copilot, ask questions, and get customized information about Azure resources. It is helpful to get answers to your questions, define a step guide for performing tasks, give recommendations, and take actions with user permission.
Microsoft Copilot can improve your productivity with intelligent assistance, providing data insights and customized solutions for specific requirements in the development and administrative lifecycle.
Note: Microsoft Copilot is currently in preview, and it is advisable to review the Terms of Use for Microsoft Azure Previews before using it. It is available for free to all the users of a tenant. However, the global administrator can control Copilot access in the organization.
Microsoft Copilot for Azure is available in two forms: Azure Integration with Microsoft Copilot and Inline Copilot – Natural Language to SQL.
Azure Integration with Microsoft Copilot
This integration helps users with self-guided tutorials, managing Azure resources, and troubleshooting issues. After connecting to the Azure portal, you can see a Copilot icon, as seen in the following image.
Click on the Copilot icon. A welcome page helps to get you started with a few suggestions:
- Design: Users can ask for design solutions such as How to create VNet service endpoints for Azure Database for PostgreSQL using CLI.
- Operate: You can ask questions about your Azure services and subscription. For example, what is the distribution of resource types across my subscriptions?
- Optimize: You can optimize scale resources with Copilot’s help. For example, you can ask it to show how to optimize your Azure SQL Database.
- Troubleshoot: You can get a troubleshooting guide with detailed actions. For example, How do I monitor the health of my VM?
Let’s try out a few responses.
Prompt: What is the cost of scaling up Azure SQL Database?
Copilot:
Prompt: What is the difference between DTU-based and vCore-based purchasing models?
Copilot:
Prompt: How can I switch from DTU-based to vCore-based purchasing model?
Copilot:
Its response can also be based on your deployed Azure resources. For example, I asked the question below. Its response requested I select the database to connect and run queries to get relevant data.
Prompt: How to scale up Azure SQL database?
Copilot:
You need to specify the credentials and wait for the response from the Copilot.
Copilot uses the current user context to execute the DMVs on the Azure SQL Database. If the user does not have the permissions listed below, you might not get the required response.
- VIEW DATABASE STATE
- VIEW SERVER STATE
- VIEW SERVER PERFORMANCE STATE
Microsoft has given a list of skills, description, and example prompt for you to understand how to interact with the Microsoft Copilot (Reference: Learn.microsoft.com)
Inline Copilot – Natural Language to SQL
This natural language to SQL works with the Azure Portal Query Editor. You can ask in natural language, which converts those into SQL for database interactions. It generates queries using existing database tables, view names, column names, and primary or foreign key metadata.
To use this Copilot feature, connect to Azure SQL Database in the portal and go to the Query Editor toolbar. The image below shows the highlighted option – Launch inline copilot.
Click Launch inline copilot. The image below shows the chat prompt. By default, it selects all tables in the database to generate the response. Be advised that filtering the required tables can develop a better response.
Let’s give a prompt: Count all the products and group by each category.
It generates the query with the description in the comment. You can accept or decline the generated T-SQL.
If you accept, you can run and save the query. You can also provide feedback on the generated queries to help Microsoft improve its Copilot skills. You can use thumps up or down beneath the generate query and write feedback to enhance Copilot’s skills.
Let’s try another query: Show me a pivot summary table that displays the number of Product sold in each year. It allows your query to satisfy your requirement for the pivot summary table, as shown below.
Here are a few suggestions for writing effective prompts:
- Make it simple. Always simplify the requirement so inline Copilot can turn into a T-SQL query with desired tables and views. You can mention the required tables or filter the tables to avoid ambiguity.
- You can specify the columns, aggregation, and data filtering requirements to help Copilot understand the schema query requirement and generate effective T-SQL.
- It only supports the English language for the natural language.
- You should design your schema as an expressive table or column name. Copilot won’t work well if object column names are not self-descriptive. For example, suppose you have a table name obj1 or t1, t2. Copilot cannot understand the purpose of this table in query generation due to its generic names.
Note: Microsoft Copilot for Azure is in the preview phase. It may not be accurate in generating query responses. Using an AI solution can be helpful when trying to do things. However, following the steps or guidelines without thinking about its response is not recommended as it might misunderstand your requirement or lack the training data or skills required in a particular query or session. Microsoft has published a warning in their Copilot docs, as shown below.
Next Steps
