Problem
In technology, Artificial Intelligence (AI) integration is in nearly every tool, and now SQL Server. With the recent release of SQL Server Management Studio Version 21 (SSMS), a new feature allows for the integration of SSMS and Copilot. How can we incorporate SQL Server Management Studio and Copilot into our existing workflows?
Solution
SSMS has long had auto-complete and IntelliSense – this functionality has long been the go-to help to more quickly formulate and complete both DDL and DML SQL commands. IntelliSense did not always provide the “correct” answer, and, likewise, as you begin to explore the use of Copilot in SSMS, be sure to check the results for validity and soundness.
The current implementation of Copilot in SSMS is established as a RAG-based sematic model. RAG stands for retrieval augmented generation and means that the execution of the request uses additional supplemental information (i.e., data) along with the training data that was used to train the large language model (LLM).
Enabling Copilot in SSMS
Enabling Copilot in SSMS 21 is fairly straightforward once you have completed all the needed details.
After clicking the Copilot button (shown above), the Copilot setup window opens. As seen below, you will need three main detailed items to complete the setup:
- Azure OpenAI Endpoint URL.
- Azure OpenAI Deployment.
- Azure OpenAI API Key.
Although the Copilot feature is currently free, the process of the AI requests requires the use of specific Azure AI services, which are not necessarily free (although you get a free credit for signing up for Azure Services for the first time).
In my case, using my Azure pay-as-you-go account subscription, I must create the following Azure Open AI Service:
Step 1: Azure Open AI resource services.
Step 2: This setup created an endpoint URL along with a set of two keys; only one key is needed for the setup.
Step 3: Finally, a model must be created and deployed. At the time of this writing, the only model available for use is the gpt-4o.
Note: Microsoft is not sending your data back to gpt.4o for further addition to the LLM. This is where the RAG infrastructure works to supplement what Copilot is processing in the background.
Using SQL Server Management Studio with Copilot
Once the Copilot connection is configured, the Copilot pane is now available for use in SSMS. As shown below, the top half of the pane is the chat log, while the bottom half is where the chat with Copilot is completed. Notice the warning at the bottom of the chat box – it notes that the content from Copilot can include mistakes, and that review of the results must be completed.
From this point, let’s work through several scenario chats to show the power of working with Copilot in SSMS.
Create a Database with SSMS and Copilot
The first prompt is to create an airport database with:
- Airports
- Flights
- Airlines
- Terminals
The screenshot below shows the prompt was specifically: “create an airport database with airports, flights, airlines and airport terminals and other needed tables.”Copilot starts by creating the database, and then specifically switches to using the newly created database.
Impressively, a very large amount of code gets generated in less than a minute.
-- Created by Copilot in SSMS - review carefully before executing
-- Step 1: Create the database
CREATE DATABASE AirportDB;
GO
-- Step 2: Use the database
USE AirportDB;
GO
-- Step 3: Create tables
-- Airports table
CREATE TABLE Airports (
AirportID INT IDENTITY(1,1) PRIMARY KEY,
AirportName NVARCHAR(100) NOT NULL,
City NVARCHAR(100) NOT NULL,
Country NVARCHAR(100) NOT NULL,
IATA_Code CHAR(3) NOT NULL UNIQUE, -- International Air Transport Association code
ICAO_Code CHAR(4) NOT NULL UNIQUE -- International Civil Aviation Organization code
);
-- Airlines table
CREATE TABLE Airlines (
AirlineID INT IDENTITY(1,1) PRIMARY KEY,
AirlineName NVARCHAR(100) NOT NULL,
IATA_Code CHAR(2) NOT NULL UNIQUE, -- Airline IATA code
ICAO_Code CHAR(3) NOT NULL UNIQUE -- Airline ICAO code
);
-- Airport Terminals table
CREATE TABLE AirportTerminals (
TerminalID INT IDENTITY(1,1) PRIMARY KEY,
AirportID INT NOT NULL,
TerminalName NVARCHAR(50) NOT NULL,
FOREIGN KEY (AirportID) REFERENCES Airports(AirportID) ON DELETE CASCADE
);
-- Flights table
CREATE TABLE Flights (
FlightID INT IDENTITY(1,1) PRIMARY KEY,
FlightNumber NVARCHAR(10) NOT NULL,
AirlineID INT NOT NULL,
DepartureAirportID INT NOT NULL,
ArrivalAirportID INT NOT NULL,
DepartureTime DATETIME NOT NULL,
ArrivalTime DATETIME NOT NULL,
TerminalID INT NULL,
FOREIGN KEY (AirlineID) REFERENCES Airlines(AirlineID) ON DELETE CASCADE,
FOREIGN KEY (DepartureAirportID) REFERENCES Airports(AirportID) ON DELETE CASCADE,
FOREIGN KEY (ArrivalAirportID) REFERENCES Airports(AirportID) ON DELETE CASCADE,
FOREIGN KEY (TerminalID) REFERENCES AirportTerminals(TerminalID) ON DELETE SET NULL
);
-- Passengers table
CREATE TABLE Passengers (
PassengerID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
PassportNumber NVARCHAR(20) NOT NULL UNIQUE,
Nationality NVARCHAR(50) NOT NULL
);
-- Flight Bookings table
CREATE TABLE FlightBookings (
BookingID INT IDENTITY(1,1) PRIMARY KEY,
FlightID INT NOT NULL,
PassengerID INT NOT NULL,
SeatNumber NVARCHAR(5) NOT NULL,
BookingDate DATETIME NOT NULL DEFAULT GETDATE(),
FOREIGN KEY (FlightID) REFERENCES Flights(FlightID) ON DELETE CASCADE,
FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID) ON DELETE CASCADE
);
-- Step 4: Add indexes for performance optimization
CREATE INDEX IX_Flights_DepartureTime ON Flights (DepartureTime);
CREATE INDEX IX_Flights_ArrivalTime ON Flights (ArrivalTime);
GOHighlights
Only Script Plus Insert and Copy
The code was not executed; only the script was created. However, the insert button in the chat windows can be used to move the code into the query window.
The code can also be copied to the clipboard as needed. Once the code is in the query window, you will want to review the suggested queries to execute. Copilot adds the following disclaimer at the top of the script: “Created by Copilot in SSMS – review carefully before executing.”
Table Explanations
Next, as shown below, Copilot adds an explanation of the tables it recommends creating. Notice that the four tables included in the prompt were created; however, the prompt also asked for other needed tables. Thus, Copilot also added the Passenger and the FlightBookings tables to the script.
Additional Notes
Finally, Copilot generated a few notes about what was created.
Again, the entire script was created in less than 1 minute. The response window also includes a Like or Dislike button, which prompts a feedback form (shown below).
Results are Not Guaranteed
However, we can see that the creation process for the database and table is not foolproof. One of the foreign keys for the flights table is not quite correct due to multiple pathways for the cascade.
After fixing the issue, the rest of the script ran, and now the database is set up, including tables, primary keys, foreign keys, and indexes.
Again, this was all completed in a short period of time and with minimal changes.
Insert Sample Data with SSMS and Copilot
The next step is to request sample data be created for each table.
Note: In the original script, a change was made to the name of the database, labeling it Airport, not AirportDB. As shown in the result below, Copilot did not take this change into account when creating the insert statements for the data.
Chatting with CoPilot, I requested that the database name be updated to Airport. Copilot makes the change, but as shown below, it regenerates both the database and table DDL scripts and the Insert scripts.
Notice that the data being inserted contains actual real data, such as real airport and airline names. Some of the other data, however, is more or less “made up.” Running the insert statements is completed without error.
Backup the Database with SSMS and Copilot
So far, Copilot has completed some basic DDL and DML scripts. Let’s take this to the next step by asking it to back up the database. Copilot responds with a script to create the backup location and then backs up the database. However, it did not quite set up the daily part fully.
However, it provides the steps to complete the scheduling of backups:
Next, let’s press Copilot to create an SQL Agent job to back up the database daily.
Now, as shown in the SQL Agent job list, it creates a backup job to back up the Airport database daily. By prompting Copilot with more details, it understands the request processes for the backup.
More Advanced Queries with SQL Server Management Studio and Copilot
Switching gears somewhat, let us now see Copilot attempting to generate more advanced queries. In the below example, the ReportServer (SSRS) database is used. Specifically, the prompt asks about seeing reports in the execution log that display the report name and the start time.
Since the prompt specifically asked for a stored procedure, Copilot provides details on how to execute a stored procedure and includes some sample output from the procedure (which is running a SELECT statement).
The initial requested stored procedure included just two fields; as shown below, Copilot remembers the context of the previous request, and a second prompt is sent that says: “add to stored procedure end data, folder path, owner, and type.”
Next, Copilot assumes the UserName for Created By is the owner. One impressive item: there is no lookup table for Report Type, and thus Copilot creates a case statement lookup statement that lists all the valid types.
Summary of Recommendations with SSMS and Copilot
If there is a need to check on the configuration of a database, Copilot can also assist with recommended changes to the database’s configuration. As shown below, Copilot generates a substantial number of best practice recommendations, including:
- Indexes
- Security
- Maintenance
- Monitoring
- Backup
We could also ask which databases have never been backed up. One item you will notice is that Copilot gives you the script to execute, but does not give you the “answer.” Note: This is the “RAG process” being executed here.
As you can see, Copilot’s perception and performance can be a game changer, sidekick helper, and another method to solve SSMS questions in a quick and conversational manner.
Saving Chat History
If you would like to save your chat history, the Copilot pane includes an Export to MD file feature; an MD file can be reviewed in a Text Editor as needed.
The MD file lists the prompt and the related responses (including any scripts) for the current conversation.
Uninstalling Copilot from SSMS
One question that comes up when adding Copilot to SSMS is how to uninstall Copilot if it isn’t used.
First, if you do not connect it to the Azure Portal for AI, the Copilot pane will not become active.
However, if you would like to uninstall it completely:
Step 1. Execute the Visual Studio Installer. Under SQL Server Management Studio 21 tool, click Modify.
Step 2. On the modification screen, uncheck the AI Assistance option.
Conclusion
In this tip, we cover the integration of Copilot into SSMS. Once the Copilot connection is established with the Azure AI portal, Copilot can be used as a chat agent to provide substantial script creation assistance. It can assist with everything from database creation to configuration suggestions to writing queries and stored procedures.
Next Steps
- Install SSMS 21 – SSMS 21 – Download, Install and Configure.
- Changes in SSMS 21 – SQL Server Management Studio (SSMS) 21 Changes.
