Deploy SQL Server Management Studio (SSMS) Updates using SCCM

In this post I will show you how to deploy SQL Server Management Studio (SSMS) updates using SCCM / Configuration Manager.

When you install SQL Server 2014, it has an option to allow installation of management tools. However with SQL 2016 and above, you must install the SQL Server Management studio separately.

Microsoft decided to split the installation of management studio from the main SQL server installation. Probably the idea behind this is to manage your SQL servers remotely.

When you install SCCM in your setup, you connect to your SQL server using SQL Server Management studio. You can deploy SSMS using Configuration Manager. This post covers the steps to patch or deploy SSMS updates using SCCM.

Introduction

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.

To ensure the security compliance, you have to ensure the client machines use the latest version of SQL Server Management studio on their computers.

Using Configuration Manager, you can download and deploy SSMS updates to multiple computers in your setup. To download the latest version of SSMS, click here.

Prerequisites

In order to deploy SSMS updates or any other updates using SCCM, you must first install and configure Software Update Point Role.

All this info is covered in the post – https://www.prajwaldesai.com/deploy-software-updates-using-sccm-2012-r2/. So ensure you have SUP configured properly before you proceed to next step.

Step 1 – Enable SQL Server Management Studio (SSMS) updates

First of all, Configuration Manager doesn’t synchronize by default the SQL Server Management Studio (SSMS) software updates. This has to be done manually.

So before you we deploy SSMS updates, our first task is to enable SQL Server Management Studio (SSMS) product updates in SCCM Software Update Point.

  • Launch the Configuration Manager console.
  • Go to Administration > Site Configuration > Sites.
  • Right click your Primary Site, Configure Site Components, click Software Update Point.
Software Update Point
Software Update Point

In the Software Update Point component properties window, click Products tab.

Software Update Point Component Properties
Software Update Point Component Properties

Under SQL Server product, select the both versions of Microsoft SQL Server Management Studio. We select both versions here because a client machine may have any of these installed.

Enable Microsoft SQL Server Management Studio
Enable Microsoft SQL Server Management Studio

Go to Classifications tab and select Updates. If the Updates classification is already selected, don’t make any changes. Click OK.

Software Update Point Classifications
Software Update Point Classifications

You can check the WCM.log file to ensure that the new changes are correctly applied.

WCM.log
WCM.log

After applying the SUP configuration, go to Software Library > Software Updates > All Software Updates. In the top ribbon, click Synchronize Software Updates.

SCCM Software Updates
SCCM Software Updates

On the Run Synchronization box, click Yes.

Run Software Updates Synchronization
Run Software Updates Synchronization

To monitor the Software Updates synchronization progress, open the wsyncmgr.log file.

Monitor SSMS update sync - Wsyncmgr.log
Monitor SSMS update sync – Wsyncmgr.log

Step 2 – Create Device Collection for SQL Server Management Studio clients

In this step, we will create a new query-based device collection that contains all clients running SQL Server Management Studio tool.

In Assets and Compliance workspace, click Device Collections. On the top ribbon click Create Device Collection.

Create Device Collection for SSMS Clients
Create Device Collection for SSMS Clients

On the General page, specify the collection name. Click Browse and select Limiting collection. Click Next.

Create Device Collection for SSMS Clients
Create Device Collection for SSMS Clients

On the Membership Rules page, click Add Rule. Select Query Rule.

Add a query rule
Add a query rule

Enter name of the query and click Edit Query Statement

Specify Query name
Specify Query name

Click Show Query Language.

Show Query Language
Show Query Language

Copy and paste the below query in the Query statement box. Click OK.

select *  from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%SQL Server Management Studio%"
SSMS Client Query
SSMS Client Query

On the Query Rule Properties window, click OK.

So we have now added query to find the clients running SQL Server Management Studio. Click Next.

deploy SSMS updates using SCCM

On the Summary page, click Next.

deploy SSMS updates using SCCM

The new device collection has been created successfully. Click Close.

deploy SSMS updates using SCCM

Wait for the query to run and once the collection is updated, you should find the computers running SSMS.

In our scenario, we have only one server on which SSMS tool is installed. We will use the same to test the updates deployment.

Step 3 – Create a SQL Server Management Studio software updates group

In this step we will create software update group to include SQL Server Management Studio updates.

In the Configuration Manager console, go to Software Library > Software Updates > All Software Updates. Click Add Criteria and add the following.

If you have only SSMS version 18 installed on clients, maybe you can skip adding version 17 to criteria.

When you add or specify the criteria, you can find the updates applicable for the SSMS product.

SSMS Software Updates Criteria
SSMS Software Updates Criteria

Right click the SSMS software update and click Create Software Update Group.

Create Software Update group
Create Software Update group

Enter Software Update group name and click Create.

SQL Server Management Studio Updates Group
SQL Server Management Studio Updates Group

Now, switch to Software Update Groups, right click the software update group and click Download.

Download SSMS Updates
Download SSMS Updates

Since this is a new deployment for us, let’s create a new deployment package.

Select Create a new deployment package. Enter a name of the Software Update package and specify the package source path. Click Next.

Create deployment Package
Create deployment Package

Click Add button and select the distribution points. Click Next.

Specify the distribution points
Specify the distribution points

You don’t need to change anything here, so click Next.

deploy SSMS updates using SCCM

Select Download software updates from the internet and click Next.

Download the updates
Download the updates

If you want to specify the update languages for products, you may do it here. Otherwise click Next.

Specify update languages for products
Specify update languages for products

Review the settings on Summary page and click Next.

deploy SSMS updates using SCCM

On the Completion page, click Close.

deploy SSMS updates using SCCM

In the next step we will deploy the SQL Server Management studio updates using SCCM.

Step 4 – Deploy the SQL Server Management Studio (SSMS) software updates using SCCM

Finally we are ready to deploy SSMS updates using SCCM. We will deploy the updates to the device collection that we created in Step 2.

In the SCCM console, go to Software Update Groups. Right the SSMS software update group and click Deploy.

deploy SSMS updates using SCCM
deploy SSMS updates using SCCM

Specify deployment name and choose a device collection. Click Next.

Select Deployment Name and Device Collection
Select Deployment Name and Device Collection

On the Deployment Settings page, select the type of deployment. It is either Available or Required. For now I will select Available. That’s because I want to see the update appear in software center.

Click Next.

Specify Deployment settings
Specify Deployment settings

Configure the schedule details for the deployment. Click Next.

Configure Deployment Schedule
Configure Deployment Schedule

On the User Experience page, configure user notifications. Set it to display in Software Center and show all the notifications. Click Next.

Specify User Experience for Deployment
Specify User Experience for Deployment

Click Next.

On the Download Settings page, click Next.

On the Completion page, click Close.

deploy SSMS updates using SCCM

Login to the target computer, launch the Software Center. Click Updates tab and you will see the SQL Server Management Studio 17.9.1 software update ready to install. Click Install.

Install SQL Server Management Studio Updates
Install SQL Server Management Studio Updates

Wait while the updates install.

Install SQL Server Management Studio Updates
Install SQL Server Management Studio Updates

Now check the SQL Server Management Studio version and that should be updated to the latest version.

Related Posts
guest
0 Comments
Inline Feedbacks
View all comments

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More