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.
Table of Contents
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.
In the Software Update Point component properties window, click Products tab.
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.
Go to Classifications tab and select Updates. If the Updates classification is already selected, don’t make any changes. Click OK.
You can check the WCM.log file to ensure that the new changes are correctly applied.
After applying the SUP configuration, go to Software Library > Software Updates > All Software Updates. In the top ribbon, click Synchronize Software Updates.
On the Run Synchronization box, click Yes.
To monitor the Software Updates synchronization progress, open the wsyncmgr.log file.
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.
On the General page, specify the collection name. Click Browse and select Limiting collection. Click Next.
On the Membership Rules page, click Add Rule. Select Query Rule.
Enter name of the query and click Edit Query Statement…
Click 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%"
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.
On the Summary page, click Next.
The new device collection has been created successfully. Click Close.
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.
Right click the SSMS software update and click Create Software Update Group.
Enter Software Update group name and click Create.
Now, switch to Software Update Groups, right click the software update group and click Download.
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.
Click Add button and select the distribution points. Click Next.
You don’t need to change anything here, so click Next.
Select Download software updates from the internet and click Next.
If you want to specify the update languages for products, you may do it here. Otherwise click Next.
Review the settings on Summary page and click Next.
On the Completion page, click Close.
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.
Specify deployment name and choose a device collection. Click Next.
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.
Configure the schedule details for the deployment. Click Next.
On the User Experience page, configure user notifications. Set it to display in Software Center and show all the notifications. Click Next.
Click Next.
On the Download Settings page, click Next.
On the Completion page, click Close.
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.
Wait while the updates install.
Now check the SQL Server Management Studio version and that should be updated to the latest version.
