Thursday, February 20, 2025

Is Vector Search in SQL Server 2025 the Key to Unlocking New Data Insights?

 The advent of AI has ushered in ground breaking changes in most areas of technology. AI is synonymous with a humongous amount of data, data of all types including text, image, audio and video. The databases of the future needs to cater to this changing vista of data and yet compatible with existing technologies.

Microsoft SQL Server has always catered to the technological changes and along its developmental path has introduced many data types to handle diverse data varieties and in recent years has introduced data types such as, "Json', "Stock" and "Geographical" data types. These address  the stock market and the geographical data that accompanies all global operations. 

In addition to data types, it has also changed itself to accommodate not only relational data but also non-relational NoSQL type of data using integration with polyphase technology.

Vector Data Type

SQL Server 2025 has introduced a new **vector data type** designed to store vector data optimized for operations like similarity search and machine learning applications. This is quite a leap for a relational database management system (RDBMS), as it traditionally handles structured data but can handle NoSQL Data.

How is Vector datatype stored in the SQL Server 2025?

A vector in SQL Server is typically stored as a JSON array of floating-point values. Here's an example of how you might define and use a vector in SQL Server:

-- Create a table with a vector column

CREATE TABLE dbo.vectors (

    id INT PRIMARY KEY,

    v VECTOR(3) NOT NULL

);

-- Insert data into the table

INSERT INTO dbo.vectors (id, v) VALUES 

(1, '[0.11, 2, -35]'), 

(2, '[-100.2, 0.0123, 9.876]'

-- Select data from the table

SELECT * FROM dbo.vectors;

This table with two columns has a column of vector data type not null. The vectors are [0.11, 2, -35] and [-100.2, 0.0123, 9.876]

The columns store three dimensional vectors with each dimension stored as a single-precision floating point (float type).

Vector Data Type is not available in 2022

The vector data type is not available in SQL Server 2022. You may have to store vector data type in a JSON column or something customized.

Image Processing and AI

In recent times image processing has been intensely studied. One may ask how to store the dimensional representation of an image. Well, images can have a very high dimensional representation.

Grayscale Image: A 100x100 pixel grayscale image can be represented as a 10,000-dimensional vector (100x100 = 10,000).

Color Image: A 100x100 pixel color image with RGB channels can be represented as a 30,000-dimensional vector (100x100x3 = 30,000).

In SQL Server 2025, the maximum number of dimensions that can be stored using the vector data type is 1998. Each element of the vector is stored as a single-precision (4-byte) floating-point value.

Can image dimensions be stored in SQL Server 2025?

The short answer for storing image dimensions in SQL Server is that it is not possible to store image dimensions without reducing the number of dimensions. There are techniques to reduce the dimensions with various pay offs in quality. We shall discuss them in a later post using a programming language such as python with the use of certain libraries.

Going forward in populating vector data type into the server the SQL Server Integration services can be used to preprocess the image data and then populate the database. This will also be for a future post.


If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here.  http://hodentekMSSS.blogspot.com

You may also have a look at my database related books:



Saturday, February 1, 2025

Encryption and connecting to SQL Server using SSMS

 This post is describes connecting to SQL Server 2022 using the SQL Server Management Studio and the encryption of connection.

For installing SQL Server 2022 and Microsoft SQL Server Management Studio go here[https://hodentekmsss.blogspot.com/2024/09/installing-sql-server-2022-on-laptop.html],here [https://hodentekmsss.blogspot.com/2024/09/customized-installing-sql-server-2022.html] and here[https://hodentekmsss.blogspot.com/2025/01/install-latest-version-of-sql-server.html].

This post assumes you completed the above two tasks and explores the SQL Server 2022.

Type SQL Server Management Studio App in the search box and click the app in the Search's pop-up.

You should be seeing this image with the Studio interface in the back of the Connect to Server window.

The Server Type:    Database Engine

The Server  Name: Computer name/SQL Server Instance name

Authentication:      Windows Authentication

User name: greyed Computer Name/Computer admin name

These are all the choices you made during installation. Refer to the earlier links in this post.

Connection Security: Early versions did not have this Encryption in this screen and it has three options:

1. Mandatory

2. Optional

3. Strict (SQL Server 2022 and Azure SQL)

This is followed by a checkbox to trust the Server Certificate. Place check mark here and click Connect.

This opens up the Object Explorer, the navigation hub of the SQL Server Management Studio App. 


In earlier versions you would have come across Always Encrypted feature. This feature ensures that sensitive data is always encrypted, even when it is being processed by the SQL Server. It uses client-side encryption to protect data before it is sent to the server, ensuring that the data remains encrypted throughout its lifecycle. 

Mandatory Encryption: This option ensures that the connection to the SQL Server is always encrypted. It uses Transport Layer Security (TLS) to encrypt the data transmitted between the client and the server. This is the default setting in SSMS 20 and later versions.

Optional Encryption: With this option, the connection can be encrypted if the server supports it, but it is not required. If the server does not support encryption, the connection will proceed without encryption. Note: This means that all features of the server can still be accessed, but the data transmitted between the client and the server will not be encrypted, potentially exposing it to security risks.

Strict Encryption: This option is available in SQL Server 2022 and Azure SQL. It enforces the highest level of encryption and does not allow the connection to proceed if the server does not support encryption. This ensures that the connection is always secure.

These encryption options are related to SQL Server encryption in the following ways:

Transport Layer Security (TLS): All three options use TLS to encrypt the data transmitted between the client and the server. TLS is a widely used protocol for securing data in transit and is supported by SQL Server.

Encryption Hierarchy: SQL Server uses a hierarchical encryption and key management infrastructure to protect sensitive data. This includes the use of encryption keys, certificates, and algorithms to secure data at rest and in transit.

The encryption options in SQL Server Management Studio (SSMS) have evolved over time. The "Always Encrypted" feature, which provides client-side encryption to protect sensitive data, was introduced in SQL Server 2016 and became available in SSMS starting with the October 2015 preview version (13.0.700.242) . This feature allows users to encrypt data in their databases without the database system having access to the encryption keys, ensuring that sensitive data remains protected even if the database is compromised.




wyatt from atlas
wyatt from atlas

Mandatory Encryption: This option ensures that the connection to the SQL Server is always encrypted. It uses Transport Layer Security (TLS) to encrypt the data transmitted between the client and the server. This is the default setting in SSMS 20 and later versions.


Optional Encryption: With this option, the connection can be encrypted if the server supports it, but it is not required. If the server does not support encryption, the connection will proceed without encryption. Note: This means that all features of the server can still be accessed, but the data transmitted between the client and the server will not be encrypted, potentially exposing it to security risks.


Strict Encryption: This option is available in SQL Server 2022 and Azure SQL. It enforces the highest level of encryption and does not allow the connection to proceed if the server does not support encryption. This ensures that the connection is always secure.


These encryption options are related to SQL Server encryption in the following ways:


Transport Layer Security (TLS): All three options use TLS to encrypt the data transmitted between the client and the server. TLS is a widely used protocol for securing data in transit and is supported by SQL Server.


Encryption Hierarchy: SQL Server uses a hierarchical encryption and key management infrastructure to protect sensitive data. This includes the use of encryption keys, certificates, and algorithms to secure data at rest and in transit.


The encryption options in SQL Server Management Studio (SSMS) have evolved over time. The "Always Encrypted" feature, which provides client-side encryption to protect sensitive data, was introduced in SQL Server 2016 and became available in SSMS starting with the October 2015 preview version (13.0.700.242) . This feature allows users to encrypt data in their databases without the database system having access to the encryption keys, ensuring that sensitive data remains protected even if the database is compromised.


Tuesday, January 14, 2025

Install the latest version of SQL Server Management Studio 20.2

This is the latest version of SQL Server Management Studio [build no:20.2.30.0, released July 9,2024]. Note: it is not an upgrade to SQL Server 19.x

  • SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. 
  • SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. 
  • Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications and build queries and scripts.

You may download the executable from this link:

https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver16

You will find the following (SSMS-ENU) in your download:


You can doble-click the application to start installing. It takes a very short time to install as shown in these screenshots:


Click Install and installation begins.


These are some new features of the SQL Server Management Studio (from the Microsoft documentation).


Connecting to an existing SQL Server 2022:

Kindly follow the links for installing the SQL Server 2022:



If your SSMS 20.2 installation is successful you should be seeing in the Windows Control Panel  several additional components to ensure a smooth and comprehensive experience in using SQL Server Management Studio:

  • Microsoft Visual Studio Tools for Applications 2019: This is necessary for certain features within SSMS that rely on Visual Studio's development environment.
  • SSMS 20.2: The main application for managing SQL Server instances.
  • Microsoft Help Viewer: Provides access to documentation and help files within SSMS.
  • Microsoft OneDrive: This might have been included to facilitate cloud storage and synchronization of your files.
The SSMS20.2 installation also adds the launch program for the SSMS that you can find in the Search as shown here:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server Tools.

This folder has the following files: 

Shortcut to the SSMS 20.2 launch
Shortcut to Analysis Services Deployment Wizard 20
Performance tools folder

You can doble click to launch from this location or on the Search results:


The SSMS 20.2 above when clicked launches the application and the SSMS user interface is displayed as shown:


Although the program connects to the installed version of SQL Server it is looking for a trusted connection by way of a certificate. There is a check box below Encryption (that has three options) shown above.

If you do not check this box and try to connect you get the following exception:



If you do place a checm mark saying you trust and then connect you get the following display:


This connection is now OK and the SSMS is OPEN FOR BUSINESS!!!

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here.  http://hodentekMSSS.blogspot.com

You may also have a look at my database related books:






 




 








Saturday, January 11, 2025

What is SQLAlchemy?

 SQLAlchemy a toolkit for Python programming language. You can build full blown database applications with it and use the full range of the SQL Language. It is also a Object Relational Mapper(ORM).

SQLAlchemy is what you need (libraries and other tools) for building data-centric apps using Python and using other libraries the possibilities are limitless including AI and Machine Learning.


SQLAlchemy and connecting to data:

The connectors are elements that connect your application to data but in the case of SQLAlchemy they are called 'database dialects'. There are database dialects for each kind of database. These are the most common kinds:


SQLite: A lightweight, disk-based database that doesn’t require a separate server process.

https://hodentekmsss.blogspot.com/2016/09/sqlite-using-visual-studio-community.html


PostgreSQL: A powerful, open-source object-relational database system.

https://hodentekmsss.blogspot.com/search?q=postgresql


MySQL: A widely used open-source relational database management system.

https://hodentekmsss.blogspot.com/2024/11/install-latest-mysql-and-improve.html


Oracle: A proprietary database management system by Oracle Corporation.

https://hodentekmsss.blogspot.com/2013/11/a-collection-of-sql-server-related.html


Microsoft SQL Server: A relational database management system by Microsoft.

https://hodentekmsss.blogspot.com/2024/11/sql-server-2025-ready-to-go.html


MariaDB: A community-developed fork of MySQL.

https://hodentekhelp.blogspot.com/2015/11/how-do-you-access-mariadb-in-xampp.html

These connectors make it easy to connect SQLAlchemy to various database systems, ensuring compatibility and flexibility in developing data-centric applications.

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here.

You may also have a look at my database related books:





You may


Saturday, November 23, 2024

SQL Server 2025 ready to go

 I have not yet done looking at SQL Server 2022, SQL Server 2025 is ready to go. Microsoft is indeed relentless! 

Microsoft announced SQL Server 2025 at the Microsoft Ignite event in Chicago. Now , AI spans all over the Microsoft Eco system and that includes SQL Server. This time around Microsoft introduced SQL Server 2025 and SQL database on Fabric.


SQL Server is integrated with AI with:

  • Vector search
  • Vector Indexing using DiskANN
  • T-SqL functions to support generation embedding and text chunking

Another useful feature added is the support for calling external REST APIs into SQL Server, a feature that existed in AZURE SQL Database. This AI feature makes it easy to store your AI models on premises and use them right away.

SQL Server 2025 will have optimized locking and will support native JSON datatype and improvements to Always on Availability groups. Also,GIT support for Microsoft Management Studio was announced. With this SSMS has now what is called a dark mode! 

Furthermore, a new product was announced, the SQL Database on Fabric. Microsoft Fabric is its unified analytics platform that integrates various datatools under one title. It is supposed to simplify data analysis and insights for both professionals and business users. 

Hey, Google does not have one such thing under a single title, but it has various functionalities dispersed in its cloud.

Let's move on!

More here:

https://www.microsoft.com/en-us/sql-server/blog/2024/11/19/announcing-microsoft-sql-server-2025-apply-for-the-preview-for-the-enterprise-ai-ready-database/







Sunday, November 17, 2024

Do you know what you installed while custom installing SQL Server 2022?

 When you install any software in Windows OS, the key information gets into the Registry and some of which you can look up in the control panel.

For example, I Custom installed SQL Server 2022 on 9/14/2024. I see the following entries in the Control Panel/Programs/Programs and Features.

Browser for SQL Server 2022----------9/15/2024

Microsoft OLEDB Driver for SQL Server-9/15/2024

Microsoft SQL Server Setup(English)--9/15/2024

Microsoft SQL Server 2022(x64Bit)--9/14/2024

However, the installation does more than this.

Browser for SQL Server 2022: This service helps clients find SQL Server instances on the network. It’s essential for managing multiple SQL Server instances on a single machine1.

Microsoft OLEDB Driver for SQL Server: This driver allows applications to connect to SQL Server databases using OLEDB, a standard API for accessing database management systems2. There is an associated OLEDB Manager GUI.

Microsoft SQL Server Setup (English): This entry represents the setup files and installation resources for SQL Server 2022. It includes the necessary tools and configurations for installing and managing SQL Server.

Microsoft SQL Server 2022 (x64Bit): This is the main SQL Server engine, which includes the core database services and features. It’s the primary component that handles database operations.

SQL Server 2022 like its predecessors a Windows service. It is a good practice to take a look at what it is.  Therefore, all SQL Server Services can be found in the Windows Services listing as shown here (just pop-up the Services from Control Panel, or just type Services in the Windows Search on the desktop). It is too important a tool and the used should get a handle on this.


There are couple of services that came up with our custom installation we discussed earlier.(link here).The SQL Server related services are all shown inside the red rectangle. If you are unfamiliar with this screen, it is important to understand how things work on Windows.

Let us just look at the SQL Server instant we installed earlier. 

This service is described on the left with your cursor on the service in the left. This service as seen in its properties provides storage, processing and controlled access of data for rapid transaction processing. If you encounter an error that the service is not running etc. you should immediately get to this screen and start the service from this screen.

Note that from this screen, you can not only start but stop, restart (if stopped),or temporarily pause the service. In the Services window shown above place your cursor and make a left click and from the drop-down list, pick Properties.


The Log On tab shows the present long on credentials of the Regency Server. You can make changes in this screen, if you want to.

 

This tab Recovery shows the steps you can take in case the service fails.


The Dependencies shows the interconnectedness of services, system groups etc. The four components shown at the bottom of the next screen all depend on SQL Server.


In earlier versions of SQL Server that I worked with the SQL Server Configuration Manager was installed when the SQL Server was installed and there used to be a shortcut in even earlier versions inthe start menu. However, this shortcut was missing in SQL Server 2016. The link explains why it was moved out of the start menu.

https://hodentekmsss.blogspot.com/2016/10/sql-server-configuration-manager.html

In 2022, there is no such shortcut or link. 

SQL Server Configuration Manager is a very important tool that you may have to access to configure network protocols, manage network connectivity of clients connecting to the server. In SQL Server 2022(16.x) later you can also manage Azure extension for SQL Server.

This link https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16, shows where it is located for various versions of SQL Server.

Just type in SQLServerManager13.msc in your Run command (that can be invoked from Search box in Windows 11) as shown,

On clicking OK, the Sql Server Configuration Manager pops-up as shown.

In future posts we look at logging on to the server and other activties.


 


Is Vector Search in SQL Server 2025 the Key to Unlocking New Data Insights?

 The advent of AI has ushered in ground breaking changes in most areas of technology. AI is synonymous with a humongous amount of data, data...