Deploy SSMS Updates using SCCM | Patch SSMS Installation

I’ll demonstrate how to deploy SSMS updates using SCCM (ConfigMgr) if you’re looking to update to the most recent version of SQL Server Management Studio.

When you patch SSMS installations on multiple computers, Configuration Manager is the best option. Using SCCM, you can completely automate SQL Server Management Studio updates across your organization.

We know that Configuration Manager requires SQL Server to create the database which is used to store the critical data. To connect and access the SCCM database, you will require tool called SQL Server Management studio installed on your computer. You must always use the latest version of SSMS to connect to SQL Server.

The option to permit the installation of management tools is available when installing SQL Server 2014. But for SQL Server 2016 and later, you have to install the SQL Server Management Studio on its own. Microsoft decided to separate the main SQL Server installation from the management studio installation. Most likely, the purpose of this is to remotely manage your SQL servers.

When you install SCCM in your setup, you connect to your SQL server using SQL Server Management Studio tool. You can also automate SSMS deployment using Configuration Manager and make it available in Software Center for installation. This post covers the steps to patch or deploy SSMS updates using SCCM.

Introduction to SSMS Updates

SQL Server Management Studio (SSMS) is a unified 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. You can always download the latest version of SSMS from Microsoft site.

There are two ways to update SQL Server Management Studio (SSMS) on your computer:

I’ll talk about the first technique in this article, which is using Configuration Manager to automate the deployment of SSMS updates.

Prerequisites

In order to deploy SSMS updates using SCCM, you must first install and configure the Software Update Point Role. Ensure the SUP is configured correctly to synchronize the SSMS product updates. Patching SSMS can be done through ADR (Automatic Deployment Rule) or you can manually select the latest SSMS updates and deploy them to the device collection.

To test the SSMS software updates deployment and installation, you can create a device collection containing the computers installed with SSMS.

Step 1: Enable SQL Server Management Studio Product updates

To begin with, Configuration Manager does not synchronize SQL Server Management Studio (SSMS) software updates by default. For synchronizations, you must enable the SSMS product in the software update point.

Use the following steps to enable the SQL Server Management Studio product under Software Update Point:

  • Launch the Configuration Manager console.
  • Go to Administration > Site Configuration > Sites.
  • Right-click server installed with SUP role and select Configure Site Components > Software Update Point.
Enable SQL Server Management Studio Product updates
Enable SQL Server Management Studio Product updates

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

Enable SQL Server Management Studio Product updates
Enable SQL Server Management Studio Product updates

From the list of products under the SQL Server category, select both versions of Microsoft SQL Server Management Studio (versions 17 and 18). Since either of these versions could be installed on a client machine, we chose both here. Click Apply.

Enable SQL Server Management Studio Product updates
Enable SQL Server Management Studio Product updates

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

Software Update Point Classifications
Software Update Point Classifications

After you select the SSMS product, you must sync the software updates manually from the SCCM console. Review the WCM.log and Wsyncmgr.log on the server to ensure that the new changes are correctly applied. From the screenshot below, we see the Microsoft SQL Server Management Studio updates are now synchronized in Configuration Manager.

Synchronize SSMS Updates in SCCM
Synchronize SSMS Updates in SCCM

To confirm if the SSMS Updates are now synced in SCCM, go to Software Library > Software Updates > All Software Updates. In the top ribbon, click Synchronize Software Updates.

Synchronize SSMS Updates in SCCM
Synchronize SSMS Updates in SCCM

On the Run Synchronization box, click Yes.

Run Software Updates Synchronization
Run Software Updates Synchronization

To monitor the Software Updates synchronization of SSMS product, review the wsyncmgr.log file on the server. From the screenshot below, we see the SSMS updates are now being synced.

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

Step 2: Create Device Collection for SQL Server Management Studio

In this step, we will use a WQL query to create a device collection that contains all computers running SQL Server Management Studio software. This allows you to target the SSMS patches to a device collection instead of all computers.

Perform the following steps to create SSMS device collection in SCCM:

  • Launch the Configuration Manager console.
  • Navigate to Assets and Compliance workspace and select Device Collections.
  • On the top ribbon click Create Device Collection.
Create Device Collection for SQL Server Management Studio
Create Device Collection for SQL Server Management Studio

On the General page, specify the collection name as “SQL Server Management Studio“. Click Browse and select Limiting collection. Click Next.

Create Device Collection for SSMS Clients
Create SSMS device collection in SCCM

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

Create SSMS device collection in SCCM
Create SSMS device collection in SCCM

Enter name of the query and click Edit Query Statement

Specify Query name
Specify Query name

Switch to the General tab and select Show Query Language.

Show Query Language
Show Query Language

Copy and paste the below SSMS WQL 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 WQL Query
SSMS WQL Query

On the Query Rule Properties window, click OK.

SSMS WQL Query
SSMS WQL Query

Once you have added the SSMS WQL query, click Next.

Create SSMS Device Collection
Create SSMS Device Collection

On the Summary page, click Next.

Create SSMS Device Collection
Create SSMS Device Collection

The new SSMS device collection has been created successfully. Close the device collection wizard. You’ll find this new SSMS device collection under list of Device Collections in Configuration Manager console.

Create Device Collection for SQL Server Management Studio
Create Device Collection for SQL Server Management Studio

After the SSMS device collection is created, the query runs in the background and collects all the computers that are installed with SSMS.

SSMS Device Collection in ConfigMgr
SSMS Device Collection in ConfigMgr

Step 3: Create SSMS Software Updates Group

In this step, we will create software update group in SCCM 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.

  • Product: Microsoft SQL Server Management Studio v17
  • Product: Microsoft SQL Server Management Studio v17

If you have only SSMS version 18 installed on computers, you may skip adding version 17 product to the search criteria. When you add or specify the search criteria, you can find the updates applicable for the SSMS product.

Create SSMS Software Updates Group
Create SSMS Software Updates Group

To create a SSMS software update group, select the SSMS updates that you wish to deploy and select Create Software Update Group.

Create SSMS Software Updates Group
Create SSMS Software Updates 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 SSMS 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.

Deploy SQL Server Management Studio Updates using SCCM Snap36

On the Download Settings page, click Next.

Deploy SQL Server Management Studio Updates using SCCM Snap37

On the Completion page, click Close.

deploy SSMS updates using SCCM

Step 5: Patching SSMS with SCCM

In the above step, we deployed the SSMS updates to a device collection running computers installed with SQL Server Manager Studio. Let’s go ahead install SSMS updates with SCCM. Login to the client computer and launch the Software Center. Select the Updates tab, and you will see the SQL Server Management Studio software updates ready to install. Click Install.

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

The SSMS updates are downloaded from the distribution point server and installed on the client computer.

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

In the background, the SSMS updates are installed. Launch the SQL Server Management Studio, and we see that it is updated to the latest version.

Patch SSMS with SCCM
Patch SSMS with SCCM

Leave a Reply

Your email address will not be published. Required fields are marked *