Xperience by Kentico's documentation covers the entire spectrum of its technical features from React to SQL, including multiple database API diagrams for features like system objects and content items.

Even with this extensive documentation, a typical Xperience database can easily have 100 or more SQL tables. That's a lot to learn and master. Thankfully, developers today have access to AI which can make for a fantastic learning assistant.

By combining your AI assistant with model content protocol (MCP) and you can quickly explore an Xperience database and learn something about its design along the way.

VS Code + GitHub Copilot + MCP

VS Code's GitHub Copilot integration let's developers bring a variety of LLMs directly into their editor. It even includes a free tier, so any developer can start prompting and learning about their applications with an AI.

To follow along with this post you'll want to add this extension to VS Code if you don't already have it added.

Copilot is powerful on its own, giving you direct access to an LLM with your project's code as context, but we need more than what an LLM can provide if we want to explore an external system like Xperience's MS SQL database.

VS Code recently released support for MCP servers. What's MCP and an MCP server?

MCP is a standardized protocol for AI models and an MCP server is an application that exposed tools to an AI over this standardized protocol. VS Code's documentation gives the following example.

For example, a file system MCP server might provide tools for reading, writing, or searching files and directories. GitHub's MCP server offers tools to list repositories, create pull requests, or manage issues. MCP servers can run locally on your machine or be hosted remotely, and VS Code supports both configurations.

VS Code's MCP support feature let's us add different kinds of MCPs, like command line, HTTP-based, and npm or python packages.

For MS SQL Server insights, we'll use an MCP from npm named mssql-mcp-node.

Not all MCPs work well with VS Code, but this one has documented support for it.

To set up the MCP I'll first create a new Xperience by Kentico sample DancingGoat project using the published dotnet template.

dotnet new install kentico.xperience.templates

I'm using PowerShell 7, which is cross platform so these commands should work on Windows, Linux, and macOS (changing \ to / for folder paths).

dotnet new kentico-xperience-sample-mvc `
  -n DancingGoat `
  -o .\xk-30-03-03-00-sample\src\DancingGoat

Once the project is created and the kentico-xperience-dbmanager tool is restored I can create the database.

cd .\xk-30-03-03-00-sample\src\DancingGoat

dotnet kentico-xperience-dbmanager -- `
  -s "localhost" `
  -u "sa" `
  -p "Pass@12345" `
  -a "Pass@12345" `
  -d "xk-30-03-03-00-sample" `
  --license-file (Resolve-Path ~\xperience-license.txt) `
  --recreate-existing-database

Consult the documentation for the database creation command which might be different for your setup.

We can now open the project in VS Code.

cd ..\..\..\

code .

It's now time to install the MCP server.

We can do this through the VS Code command palette available through the keyboard shortcut ctrl+shift+p (cmd+shift+p on macOS) followed by typing "MCP: add server".

Next, we can select the option "NPM Package" and provide the value mssql-mcp-node.

When prompted, select "Allow" to give the MCP access to your project. After confirming the server ID, select to include the settings in your workspace. This will ensure the database configuration stays with this application.

VS Code will generate the .vscode\mcp.json MCP configuration file with the following default configuration.

{
    "servers": {
        "mssql-mcp-node": {
            "command": "npx",
            "args": [
                "-y",
                "mssql-mcp-node"
            ],
            "env": {
                "MSSQL_SERVER": "your_server_name",
                "MSSQL_PORT": "1433",
                "MSSQL_USER": "your_username",
                "MSSQL_PASSWORD": "your_password",
                "MSSQL_DATABASE": "your_database",
                "MSSQL_ENCRYPT": "false",
                "MSSQL_TRUST_SERVER_CERTIFICATE": "true"
            }
        }
    }
}

We can start to fill in the env settings to match our local environment. Matching the database creation command I ran previously, my mcp.json file looks like this.

{
    "servers": {
        "mssql-mcp-node": {
            "command": "npx",
            "args": [
                "-y",
                "mssql-mcp-node"
            ],
            "env": {
                "MSSQL_SERVER": "localhost",
                "MSSQL_PORT": "1433",
                "MSSQL_USER": "sa",
                "MSSQL_PASSWORD": "Pass@12345",
                "MSSQL_DATABASE": "xk-30-03-03-00-sample",
                "MSSQL_ENCRYPT": "false",
                "MSSQL_TRUST_SERVER_CERTIFICATE": "true"
            }
        }
    }
}

Once we've correctly configured our MCP we can start the MCP server by clicking the "Start" action above the MCP name in the configuration file.

If everything's set up correctly, the "Start" action will change to "✔️Running"

When we open the Copilot chat interface we'll switch the chat mode from "Ask" to "Agent".

We can validate our MCP server is running and available to Copilot by clicking the tool icon in the chat tray, which should open the MCP server in the command palette and show 2 available tools - execute_sql and get_table_schema.

Our LLM agent will use both of these to help us explore the database we configured in the MCP settings.

Now we're ready to start exploring our Xperience by Kentico database!

Exploring the database

Using VS Code's official MS SQL Server extension we can manually explore the database ourselves in the editor, but the whole point of using an MCP is to have an LLM help us learn and explore faster, so let's ask a question!

what kinds of tables are in the database? which tables are likely the most important?

The first time the agent wants to execute a command you will need to give it permission - this protects you from an LLM doing something in your code base or on your computer you haven't authorized.

It's up to you to decide how much control you want to give the agent, but for this example I'll select "Allow in this Workspace".

We can see the MCP context used by this prompt by expanding the "Ran execute_sql" row in the chat window.

{
  "query": "SELECT TABLE_NAME \nFROM INFORMATION_SCHEMA.TABLES \nWHERE TABLE_TYPE = 'BASE TABLE'\nORDER BY TABLE_NAME;"
}

{
  "tables": [
    "CD_Migration",
    "CI_FileMetadata",
    "CI_Migration",
    "CMS_AllowedChildContentType",
    "CMS_AlternativeForm",
    ...
    "Temp_File",
    "Temp_VisualBuilderWidgets"
  ],
  "database": "xk-30-03-03-00-sample"
}

This immediately shows the power of an MCP that has access to Xperience's database. It knows how to access the "metadata" (information schema) of the database and use that to answer our questions.

Investigating an article

Using this full database querying, we can prompt the LLM to tell us about records in the DancingGoat_ArticlePage table.

using the DancingGoat_ArticlePage table, find me an article and the records in other tables related to it

Thanks to Xperience by Kentico's consistent naming conventions and foreign key relationships, the agent can quickly give us a lot of information about an article.

Let me explain what I've found about the "Coffee Beverages Explained" 
article and its relationships in the database:

Main Article Content (DancingGoat_ArticlePage):
Title: "Coffee Beverages Explained"
Published: September 15, 2023
Summary: A comprehensive guide about espresso and filtered coffee preparation methods
Has a teaser image with identifier: "b8117d96-dfb3-474d-ad27-c3b0d3bd26db"

Content Management Data (CMS_ContentItemCommonData):
Content Item ID: 60
Language ID: 1 (presumably English)
Version Status: 2 (published)
Last published: March 7, 2024
Uses the "DancingGoat.Article" template

SEO Information:
Title: "Coffee Beverages Explained - Dancing Goat"
Description: "Espresso and filtered coffee. 
  Two categories of coffee that differ in preparation. 
  Learn about individual types of coffee that belong to these categories."
Search indexing is enabled

The agent noted it found a reference to an image but couldn't find it in the media library tables.

The article includes a teaser image (identifier: "b8117d96-dfb3-474d-ad27-c3b0d3bd26db"), 
though when I checked the Media_File table, I couldn't find the actual image record.
This might indicate that either the image is stored in 
a different way or there might be a broken reference.

I followed this up with a simple prompt to explore other areas of the database.

are there any other tables the teaser image might be stored in?

After a short period of trying different queries, the agent finds the image for me.

I found the content item for the teaser image:

The teaser image is stored as a content item with:

Name: "Articles_Article-1_2x.jpg-n2s18kkg"
ID: 2
GUID: B8117D96-DFB3-474D-AD27-C3B0D3BD26DB
It's a reusable content item (ContentItemIsReusable: true)
Located in content folder ID: 2

The agent even finds the related image file on the file system in my project, helping me learn about a connection between the database identifiers and the local filesystem structure.

Additionally, the agent describes the entire chain of relationships from the original article to the file in the ~/assets folder.

So here's how the image reference system works in this Kentico Xperience application:

The article stores a reference to the image via its GUID in the ArticlePageTeaser field
That GUID maps to a ContentItem in CMS_ContentItem table which provides metadata about the image
The actual image file is stored in the assets folder using a hierarchical structure:
First level: First 2 characters of the GUID
Second level: Full GUID
Third level: A consistent GUID across assets (possibly a version or tenant identifier)
Fourth level: The actual image file with a unique filename

If you've been using Kentico's products for a long time this GUID-related folder structure for file system assets is probably familiar to you, but for everyone else this clarifies something that might have seemed random (the folder structure) at first.

Full context search

Because this agent has access to the database and filesystem it can use both to help answer our questions.

are there any other content items that link to this image?

I'll search for other content items that might reference this teaser image
(GUID: b8117d96-dfb3-474d-ad27-c3b0d3bd26db).
Let me check through content references:

Let me look for other content items that reference this image by
examining different tables and their relationships.

Let me try a different approach to check content item references:

Based on my searches, I don't find any other content items that link
to this teaser image (GUID: b8117d96-dfb3-474d-ad27-c3b0d3bd26db).
The image appears to be used exclusively
as the teaser image for the "Coffee Beverages Explained" article.

The image is physically stored
in the assets folder at: f65d05d0-838f-4e69-ac68-ffc3554a5afd.jpg

While I checked multiple potential relationships including:

Direct content item references (CMS_ContentItemReference table)
Other articles' teaser images
Related content references
Widget configurations that might use the image
None of these showed additional usage of this particular image.
It appears to be uniquely associated with this single article's teaser.

The agent performs this search faster than we could, while also telling us where it searched and why.

We could use any of these data points or notes as a thread to investigate further, but let's first verify that the conclusion is correct.

Using Xperience by Kentico's usage tracking in the Content hub, we can see the agent was correct and the image is only being directly referenced by the one article page.

Understanding architecture

Using an agent to quickly find content in Xperience's database is an obvious win, but we can also use it to gain a big picture perspective on the database architecture.

how does this database manage localized versions of content?

With this prompt the agent uses our SQL Server MCP and explores the database for a few seconds, returning with these insights.

Let me explain how the localization system works in this Kentico Xperience database:

1. Language Configuration:
- The system has English (en-US) set as the default language and Spanish (es-ES) as a secondary language
- Spanish is configured to fall back to English when content isn't available in Spanish
- Each language has its own configuration including display name, culture format, and UI elements like flag icons
2. Content Localization Structure:
- Content items are stored in a language-agnostic way first
- Language-specific versions are managed through the CMS_ContentItemLanguageMetadata table, which tracks:
- Display names in each language
- Version status of content in each language
- Creation and modification dates per language
- Workflow state for each language version
- Publishing schedule specific to each language version
- Links to the corresponding ContentLanguageID
3. Content Fallback:
- The system implements a fallback mechanism (ContentLanguageFallbackContentLanguageID)
- When content isn't available in Spanish, it automatically falls back to English content
- This prevents content gaps while allowing gradual translation of the site

The agent then gives a more description explanation of how the localized content fallback system works.

Let me explain what I found by showing you some real examples
from the database of how localization works in practice:

1. Content Localization Examples:
- An article titled "Origins of Arabica Bourbon" has two language versions:
  - English: "Origins of Arabica Bourbon"
  - Spanish: "Orígenes del Bourbon Arábica"
- The homepage also has both versions:
  - English: "Home"
  - Spanish: "Inicio"
2. Implementation Details:
- Each content item maintains a language-neutral internal name 
(e.g., "OriginsOfArabicaBourbon-epwvew12")
- Different language versions are then managed through metadata entries, 
keeping the core content linked but allowing for language-specific variations
- The system tracks metadata like creation dates, modification dates, 
and publishing status separately for each language version
3. Technical Implementation:
- Language switching is handled through a metadata layer (CMS_ContentItemLanguageMetadata)
- Each piece of content can have multiple language versions 
while maintaining a single source of truth for the base content
- The system supports partial translation - not all content needs to be translated 
to all languages thanks to the fallback system
4. Website Channel Language Settings:
- The website is configured with English as the primary language (ContentLanguageIsDefault = true)
- Spanish content falls back to English when a translation isn't available 
(ContentLanguageFallbackContentLanguageID links Spanish to English)

That's a lot of insight from a simple prompt and similar to what the AI chat returns in Xperience by Kentico's documentation.

Oh, you didn't know we have an AI as a native part of our documentation experience, trained on all of its content and ready to help you find the answers you're looking for?

Now you do!

Wrap up

I've found the MS SQL Server MCP server to be helpful finding references to identifiers and information about datasets. It also extends Copilot's abilities to assist me in an Xperience by Kentico application because there's so much additional information in the database.

Have you been using LLMs, Copilot, AI agents, or MCP servers when working on your Xperience projects? Let me know your setup in the discussion.