SQL Server 2025 Platform Architecture

SQL Server 2025 (17.x) is a major leap forward, primarily because it shifts the database from a "storage engine" to an AI-ready data platform. It integrates features that previously required external services—like vector searches, machine learning, and advanced text processing—directly into the T-SQL engine.

Here is a breakdown of the most significant advancements in this release:

1. Built-in AI and Vector Search

The standout feature of 2025 is native AI integration. You no longer need to export data to specialized vector databases to build "intelligent" apps like recommendation engines or chatbots.

• Native VECTOR Data Type: Allows you to store embeddings (numerical representations of meaning) directly.

• DiskANN Indexing: A state-of-the-art vector indexing technology that enables lightning-fast "nearest neighbor" searches even on massive datasets.

• T-SQL Model Management: You can now register and call external AI models (like Azure OpenAI or Ollama) directly from a SQL query using sp_invoke_external_rest_endpoint.

2. Modern Developer Productivity

Microsoft has addressed several long-standing "wish list" items for developers, making T-SQL much more flexible.

• Native JSON Type: Unlike previous versions that stored JSON as strings, 2025 uses a native binary format. This makes parsing faster, storage smaller, and allows for direct indexing on JSON fields.

• Regular Expressions (RegEx): After decades of requests, functions like REGEXP_LIKE and REGEXP_REPLACE are finally built into T-SQL, eliminating the need for complex workarounds or CLR assemblies for string validation.

• Change Event Streaming (CES): You can now stream data changes directly to Azure Event Hubs in real-time, simplifying the creation of event-driven architectures.

3. "Zero-ETL" and Cloud Connectivity

The "choice of environment" you mentioned is realized through deeper integration with Microsoft Fabric and Azure Arc.

• Fabric Mirroring: This allows your on-premises SQL Server data to be mirrored into Microsoft Fabric's "OneLake" in near real-time. This provides a "Zero-ETL" experience, meaning you can run heavy analytics in the cloud without setting up complex data pipelines.

• Managed Identities (Entra ID): You can now use Azure Managed Identities for on-premises servers. This means your SQL Server can authenticate to Azure services (like Blob Storage for backups) without you ever having to manage or rotate passwords.

4. Performance and Scalability Boosts

SQL Server 2025 brings several "cloud-born" features from Azure SQL Database to the on-premises engine.

• Optimized Locking: Uses a new "Transaction ID" (TID) locking mechanism that significantly reduces memory consumption and blocking for concurrent transactions.

• Standard Edition Upgrades: Microsoft has increased the limits for the Standard Edition to 32 cores and 256 GB of RAM, acknowledging that modern hardware has outpaced the old 2022 limits.

• Zstandard (ZSTD) Compression: A new backup compression algorithm that offers better ratios and faster performance than the older default compression

Cached Query Plan

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE objtype = 'Adhoc'; 

Change Data Capture for Large table in SQL Server

If you are planning to transfer the data from one db server to another db server regular basis, You should plan to design Change Data Capture (CDC) which allow you to move changed data from source db to Destination server. I have implemented and tested completely with below sql scripts. You can also free to use this one.

-- STEP 1

-- Enable Database for CDC

USE [AdventureDB]

GO

EXEC sys.sp_cdc_enable_db

GO

use [AdventureDB]

GO

ALTER TABLE dbo.[Employee]

ENABLE CHANGE_TRACKING  WITH (TRACK_COLUMNS_UPDATED = ON)  

GO 

 

 --STEP2

USE [ChangeDB]

GO

CREATE TABLE [dbo].[CHG_Employee](

[EMP_ID] [varchar](12) NOT NULL,

[SYS_CHANGE_OPERATION] [char](1) NOT NULL,

[SYS_CHANGE_VERSION] [bigint] NOT NULL,

[Created_Date] [datetime] NOT NULL,

[Upload_Status] [char](1) NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CHG_Employee] ADD  DEFAULT (getdate()) FOR [Created_Date]

GO

ALTER TABLE [dbo].[CHG_Employee] ADD  DEFAULT ('N') FOR [Upload_Status]

GO

-- STEP3

USE [ChangeDB]

GO 

CREATE PROCEDURE [dbo].[usp_CT_CHG_Employee]

AS

/*Description : Change Tracking on [AdventureWorksDB] database to Employee table  */

BEGIN

Declare @last_synchronization_version bigint;

Declare @ChangNum bigint ;

SELECT @ChangNum = max(SYS_CHANGE_VERSION) FROM [ChangeDB].dbo.[CHG_Employee]

-- print @ChangNum

IF Exists (select 1  FROM  [ChangeDB].[dbo].[CHG_Employee] )  

   Begin

    -- PRINT '1 ONE'

INSERT INTO [ChangeDB].dbo.CHG_Employee  (EMP_ID, SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)

SELECT

CT.EMP_ID,

CT.SYS_CHANGE_OPERATION,

CT.SYS_CHANGE_VERSION

FROM

CHANGETABLE(CHANGES AdventureDB.dbo.Employee, @last_synchronization_version) AS CT 

WHERE CT.SYS_CHANGE_VERSION > @ChangNum

   End

ELSE

   Begin

    --  PRINT '0 ZERO'

INSERT INTO [ChangeDB].dbo.CHG_Employee (EMP_ID ,SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION)

SELECT

CT.EMP_ID,

CT.SYS_CHANGE_OPERATION,

CT.SYS_CHANGE_VERSION 

FROM

CHANGETABLE(CHANGES [AdventureDB].dbo.Employee, @last_synchronization_version) AS CT 

End

END

GO

-- exec [dbo].[usp_CT_CHG_Employee]

Find Database Role with Objects and its Permissions

Finding a Database role with permission for Objects like (Table/View) to ensure the users got appropriates access permission within the database.

I have written this query to find database role (Users) with Object permissions.

USE [UserDatabase]

go

SELECT  

sp.[state_desc] ,

sp.[permission_name],

'ON'as Col1, 

ss.[name] AS [Schema_name], 

so.[name] as [Table_View_name], 

--so.[Type],

'TO' as Col2,

 dr.[Name]

FROM    sys.objects as so

INNER JOIN sys.schemas as ss ON so.schema_id = ss.schema_id

INNER JOIN sys.database_permissions AS sp ON sp.major_id    = so.object_id

INNER JOIN sys.database_principals  AS dr ON dr.principal_id = sp.grantee_principal_id

where  so.[Type] ='V' 

Order by  dr.[Name]

Announcing SQL Server 2025 (preview): The AI-ready enterprise database from ground to cloud

https://www.microsoft.com/en-us/sql-server/blog/2025/05/19/announcing-sql-server-2025-preview-the-ai-ready-enterprise-database-from-ground-to-cloud/

How to apply patches on SQL Database Mirror Servers

The following list of steps can be performed against Database Mirror servers to apply patches

1. Take backup of all dbs in Principal Database Server
2. Pause Mirroring on Principal Database Server for all mirrored dbs
3. The Principal server databases becomes (Principal,Suspended)
3. Stop all SQL services on Mirror Database Server (Including WMI service)
4. Apply CU patch on mirrored servers
5. Unpause Mirrors (wait for them to catch up)
6. Failover the databases (the mirror server becomes Principal)
7. Pause Mirroring
8. Hand-over to Application users to test and wait for their confirmation to proceed.
9. Stop all SQL services on Mirror Server (Including WMI service)
10. Apply CU patch on Principal Database servers
11. Unpause mirrors
12. Failback databases.

Find Backup History from SQL Server Database Server

The below scripts which allow you to find SQL Server Database Last night backup history. If you want see more files in this list please change date.

SELECT 

 [Server_name],

 [Database_name],

 [Type] AS Backup_type,

 case

    when [type]= 'D' THEN 'Full Backup'

when [type]= 'I' THEN 'Incremental Backup'

when [type]= 'L' THEN 'Log Backup'

 end  as Backup_types ,

 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  

AS  [Compressed_Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))/1024  AS  [Compressed_Backup_Size (GB)],

 100- ((compressed_backup_size/backup_size)*100)   AS 'Compressed%',   

 [backup_start_date],

 [backup_finish_date]

FROM msdb.dbo.backupset

WHERE [Type]='D'

  and backup_finish_date> getdate()-1 -- '2025-04-01 00:00:00.000'

ORDER BY backup_set_id desc

Could not find the Database Engine startup handle. Error code: 0x851A0019

Installing SQL Server 2022 on Azure Virtual machine I have encountered an issue

Action required:
Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

Feature failure reason:
An error occurred during the setup process of the feature.

Error details:
§ Error installing SQL Server Database Engine Services Instance Features

Could not find the Database Engine startup handle.
Error code: 0x851A0019

Solution:

Run cmd  prompt on this below command check Drive sectorinfo

C:\Fsutil fsinfo sectorinfo E:

Make sure to have 512 bytes to 4096 bytes allocated for the Drive which master.mdf file is installed

Shrining Database Transaction Log file is not working Transaction Log file size from 42 GB to 100MB

It is interesting to fix one of major database issue which causing a problem to reduce Transaction Log file size from 42 GB to 100MB.

Database file (MDF) 760MB

Database Log file (LDF) 42GB

 -- STEP 1

DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

-- not working above statement as we expected

I took database backup followed by Transaction Log backup then again tried not working neither T-SQL nor SSMS.

What a day

 DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

 -- STEP 2

  DBCC OPENTRAN()

Transaction information for database 'Adventuredb'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (73283:414:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- STEP 3

select log_reuse_wait_desc,* from sys.databases  where name = 'Adventuredb'

log_reuse_wait_desc 

REPLICATION

EXEC sp_removedbreplication  'Adventuredb' 

--Commands completed successfully.

select log_reuse_wait_desc from sys.databases where name = 'Adventuredb'

log_reuse_wait_desc

NOTHING

Finally i have managed shrink the log file to 100MB

Microsoft SQL Server 2025

 Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud

Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud - Microsoft SQL Server Blog

Scripting All Agent Jobs Using SQL Server Management Studio

You have to Press the F7 key so the Object Explorer Details window appears like below. Select jobs you want to scripts and Right click to Generate scripts.

Database is Recovery Pending in sql server

If the Database LOG file is  missing in SQL Server then Database is going to be RECOVERY PENDING mode.

First run the sql statement below check to find the name of the database with status

ALTER DATABASE [AdventureWorks] SET MULTI_USER

Msg 5120, Level 16, State 101, Line 24

Unable to open the physical file "D:\MSSQL\DATA\AdventureWorks_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5181, Level 16, State 5, Line 24

Could not restart database "AdventureWorks". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 24

ALTER DATABASE statement failed.

-- Database LOG file is  missing

AdventureWorks_log D:\MSSQL\DATA\AdventureWorks_log.ldf

Use master

go

ALTER DATABASE [AdventureWorks] REBUILD LOG ON(

NAME ='AdventureWorks_log' ,

FILENAME='D:\MSSQL\DATA\AdventureWorks_log.LDF' )

Warning: The log for database 'AdventureWorks' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

-- Now Database is in Restricted_user mode change to Multiuser

ALTER DATABASE [AdventureWorks] SET MULTI_USER

sp_helpdb [AdventureWorks]

Generate User Name and Role Name from all databases

SET NOCOUNT ON

DECLARE @DBName VARCHAR(255)

DECLARE @sqlStm NVARCHAR(500)

CREATE TABLE #dbTable

(

ServerName  varchar(200) ,

DBName varchar(255) ,

UserName varchar(100) ,

RoleName varchar(100) 

)

DECLARE dbCursor CURSOR FOR

SELECT name AS DbName

FROM sys.databases

WHERE name not in( 'master', 'msdb' , 'tempdb', 'model'  )

        AND state=0 

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

--SET @sqlStm = 'USE ' + @DBName  

--Print @DBName

--EXEC (@sqlStm)

--INSERT INTO  #dbTable (ServerName , DBName, [UserName], RoleName )

EXECUTE('USE ' + @DBName + '; INSERT INTO  #dbTable (ServerName , DBName, [UserName], RoleName ) SELECT @@ServerName , db_name(), u.[name] , r.[name]

     FROM sys.database_principals u 

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id 

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id') 

FETCH NEXT FROM dbCursor INTO @DBName

END

CLOSE dbCursor

DEALLOCATE dbCursor

SELECT * FROM #dbTable

 DROP TABLE #dbTable

GO

Partitioning FACT Table with 664 Million Records

I had recently completed Creating Partitioning  and adding new index for table with 664 million records to improve query performance for Group finanance Report generation. 

 --PART1

-- Creating a Partition Function

CREATE PARTITION FUNCTION IntegerPartitionFunction (INT)

AS RANGE LEFT FOR VALUES (2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029 ); 

--The result for this RANGE LEFT assignment is

CREATE PARTITION SCHEME IntegerPartitionScheme

AS PARTITION  IntegerPartitionFunction

ALL TO ([PRIMARY]) -- Because all data currently in One File group

GO

/*

CREATE PARTITION SCHEME IntegerPartitionScheme

AS PARTITION IntegerPartitionFunction

TO (

[PARTITION_FG1], [PARTITION_FG2], [PARTITION_FG3], [PARTITION_FG4],

[PARTITION_FG5], [PARTITION_FG6], [PARTITION_FG7], [PARTITION_FG9],

[PARTITION_FG9], [PARTITION_FG10], [PARTITION_FG11] ,

[PRIMARY])

 */

-- Check Partition schemas

SELECT ps.name, pf.name, boundary_id,value

FROM sys.partition_schemes ps

INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id

INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

GO

 

--PART2

--Index #1

CREATE NONCLUSTERED INDEX IX_FACT_1

ON dbo.FACT_SALES_DATA 

(

[YearId]

)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON IntegerPartitionScheme(YearId)

GO

 

--Index #2

CREATE NONCLUSTERED INDEX IX_FACT_2

ON dbo.FACT_SALES_DATA 

(

 [SId] ASC , 

 [YearId] ASC , 

 [MonthId] ASC 

)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON IntegerPartitionScheme(YearId)

GO

 

Database Security is most important to every organisation with any elevated access should be disabled to product the database. 

Below script is quick check to understand the User and its database roles in specific database. Make sure to grant or revoke  appropriate database level access to end users/Group. Keep checking/auditing the role regularly.

USE [Your Database];

SELECT u.[name] AS [UserName], r.[name] AS RoleName 

FROM sys.database_principals u 

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id 

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id

where  u.[name] = 'dbo'  OR  u.[name] = 'db_owner'

Image from Microsoft

Data Read Access to All the users in the specific database after refresh

 After you refresh database from Production to UAT environments make sure to run below scripts to drop production users first then grant data read access to UAT users.

use [Database_Name]
go

select  'DROP USER' , '['+ name+']'  

from dbo.sysusers

where name LIKE '%PRD-%' 

GO

use [Database_Name]
go
select 'EXEC sys.sp_addrolemember '   ,   ''' db_datareader '''   ,   '['+ name+']'
from master.dbo.syslogins

GO

Generate Foreign Key constraints for all table in Database

SELECT 'ALTER TABLE [dbo].[' +''+  object_name(fk.parent_object_id)+']' AS  ParentTableName, 

   'WITH NOCHECK ADD  CONSTRAINT'+' ['+  fk.[name] +']' ,    

   'FOREIGN KEY([' +''+  COL_NAME(fc.parent_object_id,fc.parent_column_id) +'])' AS  ParentColName ,

   'REFERENCES [dbo].[' +''+  object_name(fk.referenced_object_id) +']' AS  RefTableName,

   '([' +''+    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) +'])' AS  RerfColName ,

   ' NOT FOR REPLICATION'

FROM sys.foreign_keys fk

 INNER JOIN   sys.foreign_key_columns AS fc        

        ON fk.OBJECT_ID = fc.constraint_object_id

 INNER JOIN   sys.tables t 

      ON t.OBJECT_ID = fc.referenced_object_id

--WHERE fc.parent_object_id = object_id('Table_name')

Check DATABASEPROPERTYEX ()

If you are managing more than 'n' of SQL Server database estate in your organisation then you may required to check some times  a specified database current setting in SQL Server to understand.

This below function returns the current setting of the specified database option or property.

DATABASEPROPERTYEX ( database , property )

-- You should defined database name for each function to retrieve the property details

SELECT 
DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation, 
DATABASEPROPERTYEX( 'AdventureWorks2022' , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX('AdventureWorks2022', 'Recovery') AS Recovery_State

--Alternatively you can use db_name function for the current database to  retrieve the property details

USE [db_name]
GO
SELECT   
DATABASEPROPERTYEX( db_name() , 'Collation') AS Collation,
DATABASEPROPERTYEX( db_name() , 'IsAutoShrink') AS IsAutoShrink  ,
DATABASEPROPERTYEX( db_name() , 'Recovery') AS Recovery_State

-- Check with Sys.databases to see all the database related property values

SELECT * FROM sys.databases;

What is new in SQL Server 2022

The new SQL Server 2022 version is a game changer for Data Analytics and Security which also improved  with Availability Group and Performance area. Please check it out  below link to Microsoft Learn Page

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Feature highlights in SQL Server 2022

The following sections identify features that are improved our introduced in SQL Server 2022 (16.x).

• Analytics
• Availability
• Security
• Performance
• Query Store and intelligent query processing
• Management
• Platform
• Language

https://learn.microsoft.com/en-us/training/modules/introduction-to-sql-server-2022/2-deploy-and-feature-difference

Features removed or deprecated in SQL Server 2022

The following features have been removed from SQL Server 2022 that were available in previous releases:

• R, Python, and Java runtimes - R, Python, and Java runtimes are no longer included as part of the setup for SQL Server 2022. The Machine Learning Services feature is still supported, but you'll need to add your own packages that include runtimes you need.

• Polybase Hadoop Connectivity with Java - The Polybase feature with Hadoop connectivity is removed from SQL Server 2022. You can still use Polybase services with ODBC drivers or new REST API based connectors for Azure Blob storage, Azure Data Lake Storage, or S3 compatible object storage.

• Polybase scale out groups - The Polybase scale out group feature has been removed from SQL Server 2022. Queries using external tables or OPENROWSET for data virtualization can take advantage of scale-up processing built into SQL Server.

• Machine Learning Server - Machine Learning Server was retired in July of 2022. Therefore, the Machine Learning Server feature has been removed from the SQL Server setup.

• Distributed Replay - Distributed Reply is no longer available to configure with the setup for SQL Server 2022 on Windows.

• Stretch Database - Stretch Database is deprecated in SQL Server 2022. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Microsoft Fabric

Microsoft Fabric is a new end-to-end Data and Analytics Platform. This new platform connect with Microsoft’s OneLake data lake,  This is going to game changer for Data Analytics Platform (DAP).

Image Credit : Microsoft

The Data Analytics workloads like below are inter connected to perform all in one environment

• Data integration  
• Data Engineering  
• Data warehousing  
• Data science 
• Real-time analytics 
• Business intelligence  

Check it out https://www.microsoft.com/en-us/microsoft-fabric