Back in the earlier part of the year (February maybe?), I put this tutorial together for our early adopters in the SQL Server 2025 program. It’s a guide for setting up SQL Server 2025 in a Hyper-V VM (I used Windows Server) with Ollama and ngnix as an https proxy. It’s a variation of my workshops for Ignite 2024 and a SQL in Fabric one from February 2025.

This tutorial helps you get started using the new AI features of SQL Server 2025 on Windows/Windows Server.

Prerequisites

• SQL Server 2025 Public Preview
• A database created with the adventureworks2025.bak backup file
• SSMS or VS Code with the MSSQL Extension
• Windows environment or Hyper-V Windows VM (Windows or Windows Server)

Set up your environment

The following section guides you through setting up the environment and installing the necessary software and utilities.

Install Ollama

There are two ways to install Ollama.

Via Winget and PowerShell

1. Open up a PowerShell terminal

2. Enter the following command:

winget install Ollama.Ollama

Via Direct Download

1. Download the executable file from the GitHub repository using the following link: Ollama Setup

2. Then double-click the `OllamaSetup.exe` file to install Ollama.

3. Once Ollama is installed, quit or stop it from either the task manager or in the system tray, right select Ollama and select **Quit Ollama**.

Install nginx

1. To install nginx, use the following link to download it: Download Nginx

2. Under the heading **Stable version**, select `nginx/Windows-1.28.0` (the version on as of May 19, 2025) to start the download.

3. Copy the `nginx-1.28.0.zip` file to the `C:\` drive

4. Unzip the `nginx-1.28.0.zip` file here. In Windows, use the **Extract All…** option when right selecting on the file.

5. In the Extract Compressed File dialog, set the extraction directory to be `C:\`. If you leave the default, it will extract the files into a nested folder resulting in `C:\nginx-1.28.0\nginx-1.28.0`.

Set up SSL for Ollama and nginx

The next step will create self-signed certificates that will be used for SSL in nginx.

1. To start, open a PowerShell terminal and create a certs directory with the following command:

mkdir C:\certs

2. Next, using Notepad, copy the following text and save the file as createCert.ps1 in the C:\certs directory.

createCert.ps1

param
(
[parameter(Mandatory=$true)]
[string]
$DnsName,

[parameter(Mandatory=$true)]
[string]
$Password,

[parameter(Mandatory=$true)]
[string]
$FilePath
)

# Create a new self-signed certificate
$cert = New-SelfSignedCertificate -Subject $DnsName -DnsName $DnsName -FriendlyName "SQL Development"

# Export the certificate to a file
Export-PfxCertificate -Cert $cert -FilePath $FilePath -Password (ConvertTo-SecureString -String $Password -Force -AsPlainText)

# Import the certificate as trusted
Import-PfxCertificate -Certstorelocation Cert:\LocalMachine\Root -FilePath $FilePath -Password (ConvertTo-SecureString -String $Password -Force -AsPlainText)

2. Close Notepad after saving the file and go back to the PowerShell terminal.

3. Change the directory to the C:\certs directory

cd C:\certs

4. Next, run the createCert.ps1 script with the following command:

./createCert.ps1

5. Use the following values for the variables when running the createCert.ps1 script:

• For DnsName, use `localhost` For password, use a strong password that you have written down. For FilePath, use `C:\certs\cert.pfx`

The certificate is now created.

Install OpenSSL

OpenSSL needs to be installed next.

1. While in the PowerShell terminal, run the following command:

winget install ShiningLight.OpenSSL.Light

Add OpenSSL to the PATH

Once installed, openssl needs to be added to the PATH environment variable.

Via PowerShell

Run the following command in the PowerShell terminal:

$oldPath = [Environment]::GetEnvironmentVariable("Path", "User")
$newPath = $oldPath + ";C:\Program Files\OpenSSL-Win64\bin"
[Environment]::SetEnvironmentVariable("Path", $newPath, "User")

Via the Environment Variables modal window

1. Start by running the following command in the PowerShell terminal:

rundll32 sysdm.cpl,EditEnvironmentVariables

2. In the Environment Variables modal window, look at the lower section named System variables.

3. Select the Path variable and then select the Edit button.

4. In the Edit environment variable modal window, start by selecting the New button.

5. Under the last line, you can enter text for the next variable. Use the following text:

C:\Program Files\OpenSSL-Win64\bin

6. Then press the OK button to set the variable and close the modal window. Then, select OK to close the Environment Variables modal window.

Create the signed .crt and .key files for nginx

1. To use the new Path, run the following command in the PowerShell terminal:

$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User")

2. Ensure the directory is still set to C:\certs. Run the following command if unsure:

cd C:\certs

3. Next, run the following command:

openssl pkcs12 -in cert.pfx -nocerts -out cert.key -nodes

4. When asked to “Enter Import Password”, enter the strong password you wrote down previously. Then press the enter/return key.

5. Now, run the following command:

openssl pkcs12 -in cert.pfx -clcerts -nokeys -out cert.crt

6. When asked to “Enter Import Password”, enter the strong password you wrote down previously. Then press the enter/return key.

Edit the nginx.conf file

1. Again in the PowerShell terminal, run the following command:

NOTE: The version number installed might be different than the one in this tutorial.

cd C:\nginx-1.28.0\conf

2. Now, edit this file by opening it in Notepad using the following command in PowerShell:

notepad nginx.conf

3. Replace ALL the text in the nginx.conf with the following:

worker_processes auto;

events {
worker_connections 1024;
}

http {

upstream ollama {
server localhost:11434;
}

server {
listen 11435 ssl;
server_name localhost;

ssl_certificate C:\certs\cert.crt;
ssl_certificate_key C:\certs\cert.key;
ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
ssl_ciphers HIGH:!aNULL:!MD5;

location / {
proxy_pass http://localhost:11434;
proxy_http_version 1.1;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header Origin '';
proxy_set_header Referer '';
}
}
}

4. Save the file

5. After you have **Saved the nginx.conf file**, change the directory in PowerShell using the following command:

cd C:\Windows\System32\drivers\etc

6. Edit the hosts file with Notepad using the following command:

notepad hosts

7. After the last line in the file, add the following text:

127.0.0.1 localhost

8. Save the file

9. After you have **Saved the hosts file**, it’s time to start up the services.

Start up the services

Ollama

1. Start by going to the top level of the C: Drive with the following command in PowerShell:

Set-Location -Path "C:\"

2. Back in PowerShell, Ollama needs an embedding model locally. Use the following command to download that model:

ollama pull nomic-embed-text

3. Pulling the model also start Ollama up. We want to stop it so we can start it in PowerShell to monitor the requests. Again stop Ollama from either the task manager or in the system tray, right-click Ollama and select **Quit Ollama**.

4. Once Ollama as stopped, start Ollama with the following command so that we can monitor it in PowerShell:

ollama serve

Nginx

1. Open a new PowerShell terminal by clicking the Plus Sign on the PowerShell terminal tab area.

2. Change the directory to the nginx home directory using the following command in PowerShell:

NOTE: The version number installed might be different than the one in this tutorial.

cd C:\nginx-1.28.0

3. To start nginx, run the following command in PowerShell:

start nginx

Test the Ollama embeddings endpoint

To test the endpoint, run the following command in PowerShell:

Invoke-WebRequest -Uri "https://localhost:11435/api/embeddings" -ContentType "application/json" -Method POST -Body '{ "model":"nomic-embed-text", "prompt":"test text"}'

And the result should be similar to the following:

StatusCode : 200 StatusDescription : OK Content : {“model”:”nomic-embed-text”,”embeddings”:[0.021354584,-0.026753489,-0.16089542,-0.026369257,0.0828 7482,-0.03691292,0.022429287,-0.008543771,0.012165211,-0.02446957,-0.01350472,0.072527215,0.0365559 64… RawContent : HTTP/1.1 200 OK Transfer-Encoding: chunked Connection: keep-alive Content-Type: application/json; charset=utf-8 Date: Fri, 07 Mar 2025 17:12:36 GMT Server: nginx/1.28.0

{“model”:”nomic-embed-t… Forms : {} Headers : {[Transfer-Encoding, chunked], [Connection, keep-alive], [Content-Type, application/json; charset=utf-8], [Date, Fri, 07 Mar 2025 17:12:36 GMT]…} Images : {} InputFields : {} Links : {} ParsedHtml : System.__ComObject RawContentLength : 9634

If you look at the Ollama PowerShell tab, you see a line similar to the following:

[GIN] 2025/03/07 – 09:12:36 | 200 | 27.8195ms | 127.0.0.1 | POST “/api/embeddings”

Set up the database

The following section guides you through using the embeddings model to create vector arrays on relation data and use the new vector similarity search functionality in SQL Server 2025.

Create the EXTERNAL MODEL in the database

1. Using SSMS, login to the database using Windows credentials

2. Open a new query sheet using the AdventureWorksLT2025 database

3. Next, run the following SQL to enable REST communication from within the database:

-- Turn External REST Endpoint Invocation ON in the database
EXECUTE sp_configure 'external rest endpoint enabled', 1;
GO

RECONFIGURE WITH OVERRIDE;
GO

Now, run the following SQL to create an EXTERNAL MODEL that points to the Ollama embedding model that was downloaded:

CREATE EXTERNAL MODEL ollama
WITH (
LOCATION = 'https://localhost:11435/api/embed',
API_FORMAT = 'Ollama',
MODEL_TYPE = EMBEDDINGS,
MODEL = 'nomic-embed-text'
);

Test the EXTERNAL MODEL

To test the embeddings endpoint, run the following SQL:

select AI_GENERATE_EMBEDDINGS(N'test text' USE MODEL ollama);

You should see a JSON vector array returned similar to the following:

[0.1529204398393631,0.4368368685245514,-3.6136839389801025,-0.7697131633758545…

Watch Ollama in the PowerShell terminal where you started it to see any errors or successes.

Embed Product Data

This next section of the tutorial will alter the Adventure Works product table to add a new vector data type column.

1. Run the following SQL to add the columns to the Product table:

ALTER TABLE [SalesLT].[Product]
ADD embeddings VECTOR (768),
chunk NVARCHAR (2000);

2. Next, we are going to use the EXTERNAL MODEL and AI_GENERATE_EMBEDDINGS to create embeddings for text we supply as an input.

Run the following code to create the embeddings:

-- create the embeddings
SET NOCOUNT ON;

DROP TABLE IF EXISTS #MYTEMP;

DECLARE @ProductID int
DECLARE @text NVARCHAR (MAX);

SELECT * INTO #MYTEMP FROM [SalesLT].Product WHERE embeddings IS NULL;

SELECT @ProductID = ProductID FROM #MYTEMP;

SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP;

WHILE @@ROWCOUNT <> 0
BEGIN
SET @text = (
SELECT p.Name + ' ' + ISNULL(p.Color, 'No Color') + ' ' + c.Name + ' ' + m.Name + ' ' + ISNULL(d.Description, '')
FROM [SalesLT].[ProductCategory] c,
[SalesLT].[ProductModel] m,
[SalesLT].[Product] p
LEFT OUTER JOIN [SalesLT].[vProductAndDescription] d
ON p.ProductID = d.ProductID
AND d.Culture = 'en'
WHERE p.ProductCategoryID = c.ProductCategoryID
AND p.ProductModelID = m.ProductModelID
AND p.ProductID = @ProductID
);
UPDATE [SalesLT].[Product] SET [embeddings] = AI_GENERATE_EMBEDDINGS(@text USE MODEL ollama), [chunk] = @text WHERE ProductID = @ProductID;

DELETE FROM #MYTEMP WHERE ProductID = @ProductID;

SELECT TOP(1) @ProductID = ProductID FROM #MYTEMP;
END

2. Use the following query to see if any embeddings were missed:

SELECT *
FROM SalesLT.Product
WHERE embeddings IS NULL;

3. And use this query to see a sample of the new columns and the data within:

SELECT TOP 10 chunk,
embeddings
FROM SalesLT.Product;

Use VECTOR_DISTANCE and VECTOR_SEARCH

Vector similarity searching is a technique used to find and retrieve data points that are similar to a given query, based on their vector representations. The similarity between two vectors is measured using a distance metric, such as cosine similarity or Euclidean distance. These metrics quantify the similarity between two vectors by calculating the angle between them or the distance between their coordinates in the vector space.

Vector similarity searching has numerous applications, such as recommendation systems, search engines, image and video retrieval, and natural language processing tasks. It allows for efficient and accurate retrieval of similar items, enabling users to find relevant information or discover related items quickly and effectively.

This section of the tutorial will be using the new functions VECTOR_DISTANCE and VECTOR_SEARCH. It will also be creating a new DiskANN Vector Index for the VECTOR_SEARCH ANN similarity searches.

VECTOR_DISTANCE

Uses K-Nearest Neighbors or KNN

Use the following SQL to run similarity searches using VECTOR_DISTANCE.

declare @search_text nvarchar(max) = 'I am looking for a red bike and I dont want to spend a lot'
declare @search_vector vector(768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL ollama);
SELECT TOP(4)
p.ProductID, p.Name , p.chunk,
vector_distance('cosine', @search_vector, p.embeddings) AS distance
FROM [SalesLT].[Product] p
ORDER BY distance;

declare @search_text nvarchar(max) = 'I am looking for a safe helmet that does not weigh much'
declare @search_vector vector(768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL ollama);
SELECT TOP(4)
p.ProductID, p.Name , p.chunk,
vector_distance('cosine', @search_vector, p.embeddings) AS distance
FROM [SalesLT].[Product] p
ORDER BY distance;

declare @search_text nvarchar(max) = 'Do you sell any padded seats that are good on trails?'
declare @search_vector vector(768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL ollama);
SELECT TOP(4)
p.ProductID, p.Name , p.chunk,
vector_distance('cosine', @search_vector, p.embeddings) AS distance
FROM [SalesLT].[Product] p
ORDER BY distance;

VECTOR_SEARCH

Uses Approximate Nearest Neighbors or ANN

Use the following SQL to run similarity searches using VECTOR_SEARCH and the DiskANN Vector Index.

1. First, run the following SQL to prepare the database to use the new features:

-- Enable trace flags for vector features
DBCC TRACEON (466, 474, 13981, -1);
GO

-- Check trace flags status
DBCC TRACESTATUS;
GO

2. Now, create the DiskANN indexes on the embeddings column in the Product table.

CREATE VECTOR INDEX vec_idx ON [SalesLT].[Product]([embeddings])
WITH (METRIC = 'cosine', TYPE = 'diskann', MAXDOP = 8);
GO

SELECT * FROM sys.indexes WHERE type = 8;
GO

3. Use the following SQL to run the similarity search using both VECTOR_SEARCH and the DiskANN Index:

-- ANN Search
DECLARE @search_text NVARCHAR (MAX) = 'Do you sell any padded seats that are good on trails?';
DECLARE @search_vector VECTOR (768) = AI_GENERATE_EMBEDDINGS(@search_text USE MODEL ollama);
SELECT t.chunk,
s.distance
FROM vector_search(
table = [SalesLT].[Product] as t,
column = [embeddings],
similar_to = @search_vector,
metric = 'cosine',
top_n = 10
) as s
ORDER BY s.distance;
GO

Chunk with embeddings

This section uses the `AI_GENERATE_CHUNKS` function with `AI_GENERATE_EMBEDDINGS` to simulate breaking a large section of text into smaller set sized chunks to be embedded.

1. First, create a table to hold the text:

CREATE TABLE textchunk
(
text_id INT IDENTITY (1, 1) PRIMARY KEY,
text_to_chunk NVARCHAR (MAX)
);
GO

2. Next, insert the text into the table:

INSERT INTO textchunk (text_to_chunk)
VALUES ('All day long we seemed to dawdle through a country which was full of beauty of every kind. Sometimes we saw little towns or castles on the top of steep hills such as we see in old missals; sometimes we ran by rivers and streams which seemed from the wide stony margin on each side of them to be subject to great floods.'),
('My Friend, Welcome to the Carpathians. I am anxiously expecting you. Sleep well to-night. At three to-morrow the diligence will start for Bukovina; a place on it is kept for you. At the Borgo Pass my carriage will await you and will bring you to me. I trust that your journey from London has been a happy one, and that you will enjoy your stay in my beautiful land. Your friend, DRACULA');
GO

3. Finally, create chunks of text to be embedded using both functions:

SELECT c.*, AI_GENERATE_EMBEDDINGS(c.chunk USE MODEL ollama)
FROM textchunk t
CROSS APPLY
AI_GENERATE_CHUNKS(source = text_to_chunk, chunk_type = N'FIXED', chunk_size = 50, overlap = 10) c

XEvents for embeddings and REST

The following SQL creates an XEvent session for debugging REST calls from the database

CREATE EVENT SESSION [rest] ON SERVER
ADD EVENT sqlserver.external_rest_endpoint_summary,
ADD EVENT sqlserver.ai_generate_embeddings_summary
WITH
(
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);
GO