SQLDBA- SQL Server Management Studio 22 Download, Install and Configure

 

SQL Server Management Studio 22 Download, Install and Configure

 

This article describes how to install SQL Server Management Studio (SSMS). Below are the key notes.

 

Key information	Action required
SSMS 22 is installed with the Visual Studio Installer	The SSMS 22 installer link downloads a stub installer (vs_SSMS.exe) that opens Visual Studio Installer to install SSMS. There's no standalone MSI.
How to install SSMS	Download the SSMS 22 installer, run vs_SSMS.exe, pick any optional workloads/components, then select Install.
Use the Visual Studio Installer to update SSMS 22	To update SSMS 22, follow the instructions in Update SQL Server Management Studio.
Offline or enterprise deployment	To create an offline layout for disconnected or managed installs, see Create an offline installation of SQL Server Management Studio.
Database Engine compatibility	SSMS 22 works with SQL Server 2014 (12.x) and later versions. If you need to manage older versions, see the Release history. SSMS 22 also works with Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and SQL database in Microsoft Fabric.

 

Step1. If we want to install SSMS 22, before that we need to download a stub installer (vs_SSMS.exe). below link use to download the vs_SSMS.exe

Release Notes for SQL Server Management Studio (SSMS) | Microsoft Learn

Step2:  After completion of step1, go the download files location and right click on vs_SSMS.exe file  and select   Run as administrator

 

 

 

 

 

 

Step3: Click on Continue

The installer will install it as part of Visual Studio.

 

Step4: if we have previous versions of SSMS, you can copy the workloads, components, and settings to the new version. Also, you can select your vsconf file. This file is in JSON format and shows the components to install.

Below window will appear if we have  SSMS21 version.

 

If you have a previous version installed, you may receive a message that some packages are not available. Click on Ok

 

 

Step5: After this, press the Install button as shown below.

 

 

Step6: Finally, the installer may ask you to restart the machine.

 

 

 

Upgrade SQL Server Management Studio 22

1. Launch Visual Studio Installer

1. Press Start.
2. Type Visual Studio Installer.
3. Click to open it.

 

2. Locate SSMS in Installed Components

1. In Visual Studio Installer, go to the Installed tab.
2. Scroll to find SQL Server Management Studio listed as an installed component.

3. Check for SSMS Updates

• If an update is available, you will see an Update button next to SSMS.
• If the button is not visible, your SSMS is already up to date.

---

4. Apply the Update

1. Click Update.
2. The Visual Studio Installer downloads only the necessary component updates.
3. Wait until the progress bar completes.

---

5. Complete the Installation

• When the update finishes, the Installer will show Completed.
• No reboot is usually required unless prompted.

 

---

6. Verify the Upgrade

1. Open SQL Server Management Studio.
2. Click Help → About.
3. Confirm the SSMS version is now updated.

 

---

7. Notes and Best Practices

• Ignore SSMS upgrade popup notifications inside SSMS itself — updates must go through Visual Studio Installer.
• The SSMS .exe from Microsoft’s website is only a bootstrapper and will automatically redirect to Visual Studio Installer.

 

SQLDBA- What breaks with SQL server 2025

 

SQL Server 2025 known issues:

 With SQL Server 2025, Microsoft has introduced a major change in the OLE DB provider

·       MSOLEDBSQL 19 is now the default provider

The new provider enforces:

• Encrypt = TRUE (default)
• TrustServerCertificate = FALSE (default)
• Strict certificate chain validation

 

Impact: Connections will fail by default

 If our environment uses:

• Self-signed certificates
• Internal CA not trusted
• Or no encryption

 

Affected Components

This change impacts:

• Linked Servers
• Replication
• Log Shipping
• Applications using legacy connection strings

Earlier:

• SQLNCLI (old provider) ignored certificate validation issues

Now:

• MSOLEDBSQL 19 enforces strict security

 

✅ 1. Linked Servers Fail After Upgrade

Issue

• Linked servers fail due to mandatory encryption changes in SQL Server  2025
• If We configured linked servers using SQLNCLI (which was the default for years), they'll fail after upgrade with errors like:
• Errors: 7303, SSL trust, certificate issues [mssqltips.com], [learn.microsoft.com]

Msg 7303, Level 16, State 1

Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "<linked server name>".

TCP Provider: The certificate chain was issued by an authority which is not trusted.

Msg 10054, Level 20, State 0 

A transport-level error occurred when receiving results from the server.

Msg 17832, Level 20, State 18

The login packet used to open the connection is structurally invalid; the connection has been closed.

[SQL Server]The target principal name is incorrect

Root Cause

SQL Server 2025 enforces :

• Encrypt = TRUE by default
• Strict TLS certificate validation

The fix is to either install proper certificates or reconfigure the linked server with TrustServerCertificate=yes — which defeats the security improvement but at least gets you running.

Fix Option 1 (Quick workaround) This bypasses certificate validation.  Not recommended for long-term use

EXEC sp_addlinkedserver

@server='MyServer',

@provider='MSOLEDBSQL',

@datasrc='ServerName',

@provstr='Encrypt=Yes;TrustServerCertificate=True';

 

Fix Option 2 (Best Practice – Recommended)

1. Install valid SSL certificate on SQL Server

 

 

 

---

✅ 2. Replication Failure After Upgrade

Issue

Replication breaks due to encryption + certificate mismatch [learn.microsoft.com]

If publisher is SQL Server 2025 and your distributor is remote without a trusted certificate, replication will fail. You'll see:

 

OLE DB provider "MSOLEDBSQL19" for linked server "repl_distributor" returned message 

"Client unable to establish connection".

Msg -2146893019, Level 16, State 1

SSL Provider: The certificate chain was issued by an authority that is not trusted.

 

This hits transactional, snapshot, peer-to-peer, and merge replication. Replication Monitor in SSMS will also fail if it can't validate the certificate chain.

The workaround (if you can't deploy trusted certificates yet):

EXEC sp_changedistributor_property 

    @property = N'trust_distributor_certificate', 

    @value = N'yes';

 

Symptoms

• Replication monitor fails
• Publication changes fail

Fix

1. Configure trusted certificate on all nodes
2. Verify linked server between publisher & distributor
3. Restart SQL Agent
4. Reinitialize replication if required

 

 

✅ 3.  Full-Text Search

Issue

SQL Server 2025 introduces a new full-text index version. Existing catalogs stay on version 1 (unchanged since 2005) unless you manually upgrade them. After the engine upgrades, your full-text queries will fail:

Msg 30010, Level 16, State 2

An error has occurred during the full-text query. Common causes include: 

word-breaking errors or timeout, FDHOST permissions/ACL issues, 

service account missing privileges, malfunctioning IFilters...

 

Fix:

The fix is to rebuild your full-text indexes — or if you need to keep using the old version temporarily:

ALTER DATABASE SCOPED CONFIGURATION SET FULLTEXT_INDEX_VERSION = 1;

But version 1 is deprecated. This is a temporary workaround, not a long-term solution.

Full text search won’t index all of big plaintext documents whose size is larger than 25MB. The workaround is to edit the registry to remove the 25MB limit.

---

✅ 4.  Database Mail  another bug 

Issue: After upgrading to SQL Server 2025, Database Mail may stop working mainly due to enforced TLS/SSL requirements, disabled configuration, or SMTP compatibility issues.

 

What happens to Database Mail after upgrade to SQL Server 2025

• ❌ Emails not sending
• ❌ SQL Agent alerts not working
• ❌ Error like:
• The SMTP server requires a secure connection
• ❌ Messages stuck in queue

 

 Step-by-step approach

Check if DB Mail is enabled- If the below query result output value 0 then enable it.

SELECT value_in_use FROM sys.configurations WHERE name = 'Database Mail XPs';

 

If 0 → enable it:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

 

EXEC sp_configure 'Database Mail XPs', 1;

RECONFIGURE;

 

Check service status

EXEC msdb.dbo.sysmail_help_status_sp;

If stopped:

EXEC msdb.dbo.sysmail_start_sp;

 

Fix SMTP security

EXEC msdb.dbo.sysmail_update_account_sp

    @account_name = 'YourAccount',

    @enable_ssl = 1,

    @port = 587;

 Use TLS-enabled SMTP (587 or 465)

 Not old port 25 (unless secured)

 

Check failures & queue

SELECT *  FROM msdb.dbo.sysmail_allitems ORDER BY send_request_date DESC;

SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;

 

 

Restart queue (if stuck)

EXEC msdb.dbo.sysmail_stop_sp;

EXEC msdb.dbo.sysmail_start_sp;

Validate network & permissions

Ensure:

• SQL Server service account has access to SMTP
• Firewall allows:
• Port 587 (recommended)

TEST email

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'YourProfile',

    @recipients = 'your@email.com',

    @subject = 'Test Mail',

    @body = 'Test after upgrade';

 

 

✅ 5. SSIS / Visual Studio Issues

Issue

Old SSIS packages fail to open or upgrade

Fix

1. Install SSDT (SSIS extension) in VS 2026
2. Open project → upgrade packages
3. Replace deprecated providers (SQLNCLI → MSOLEDBSQL)
4. Reconfigure connection managers

 

 

✅ 6. Installation Errors (General)

Common Issues

• .NET missing
• WMI service failure
• OS patch missing

Fix Steps

1. Install:
• .NET 4.7.2+
2. Run:
3. sfc /scannow
4. Ensure WMI service is running
5. Apply Windows updates
6. Run installer as Administrator
   
   

✅ 7. Installation Failure (TLS 1.2 Disabled)

Issue

 SQL Server 2025 (17.x) installation fails if TLS 1.2 is disabled on the machine, including failover cluster instances.

Workaround: Enable TLS 1.2 on the machine before attempting to install SQL Server 2025 (17.x).  [learn.microsoft.com]

Fix (Step-by-step)

1. Open Registry Editor
2. Navigate to:
3. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
4. Enabled TLS 1.2 (Client & Server = Enabled = 1)
5. Restart the server
6. Re-run SQL Server installation

After successfully installation of SQL Server 2025, we need to disable the TLS 1.2

7.       Turn it back off again.

 

 

SQLDBA- Get the jobs list running with 32 Bit

 

Hi, 

We have an issue with CPU spikes   and we observed that the SQL server integration services execute package utility 32 is  consuming high CPU. So, we  have to find out  which job is running with 32 bit. 

Below query  will help us figure out what  jobs causing the  issue. 

USE msdb;

GO

SELECT * FROM (

SELECT

    j.name        AS job_name,

    js.step_id,

    js.step_name,

    js.subsystem,

    js.command,

    CASE 

        WHEN js.command LIKE '%/X86%' THEN 'Yes'

        ELSE 'No'

    END AS runs_32_bit,

    js.last_run_outcome

,js.flags 

FROM dbo.sysjobsteps js

JOIN dbo.sysjobs j

    ON js.job_id = j.job_id

WHERE js.subsystem = 'SSIS'

)a WHERE runs_32_bit='yes'

--ORDER BY j.name, js.step_id;