Let’s check how to install SQL Server Management Studio with Azure Data Studio Installation and Upgrade as Part of the SCCM Secondary Server. The SQL Server Management Studio (aka SSMS) is useful to troubleshoot SCCM secondary server SQL Express Database.
You can download, install, or upgrade SQL management studio and Azure Data Studio separate applications without impacting SQL (or SQL Express) DB. The SCCM installs SQL Server Express as part of a secondary site installation it also automatically creates these SQL express configurations.
You can use the same method to Install or Upgrade SQL Management Studio and Azure Data Studio on SCCM servers or any other terminal servers. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases.
Use SSMS to query, design, and manage your databases and data warehouses from your local server or in the cloud. You can also check out the SCCM Server SQL installation step-by-step guide to understanding the default SSMS installation option.
- Easiest Method to Create SQL Queries for SCCM without Opening SQL Management Studio
- ConfigMgr Create a New Database for SSRS Reporting Service
What is SQL Management Studio (SSMS)?
SQL Management studio is a separate application that is used to configure and manage SQL server application and their Database. From ConfigMgr (aka SCCM) admin perspective, this is mostly to create SQL queries to find different information such as the list of installed apps, office 365 app details, Windows 11 dashboard, etc.
Database collation and Database compatibility level are the other two prominent features that SCCM (aka MEMCM) admins check and configure from SQL management studio. The latest version of SSMS is rebranded as Microsoft SQL Server Management Studio with Azure Data Studio.
You can launch the Microsoft SQL Server Management Studio from Start Menu – Microsoft SQL Server Tools – Microsoft SQL Server Management Studio.
What is Azure Data Studio
You all know about SQL Management Studio. Now, let’s learn about Azure Data Studio. It’s a modern version of the Data Management platform and this can work from Windows, macOS, and Linux. Also, this is built to cater to on-prem, Cloud, and Hybrid infrastructure in mind.
I see this Azure Data Studio is going to enhance the experience of all the Database administrators and SCCM admins. This provides greater features to manage DBs and create SQL queries.
NOTE! – SQL Server Management Studio and Azure Data Studio are installed as part of one SSMS-Setup-ENU.exe. However, these are two separate applications altogether afterward.
Download SSMS (SQL Server Management Studio) with Azure Data Studio
You can download the latest version of SQL server management studio (SSMS) with Azure Data Studio from Microsoft. I normally download (SSMS-Setup-ENU.exe) it to a central location and share the source from there to install or upgrade SQL Management Studio.
Free SQL Management Studio with Azure Data Studio Download Link from Microsoft – https://aka.ms/ssmsfullsetup.
You can also install SQL Management Studio using Intune (MEM) or the SCCM application deployment method. Intune application deployment is supported with Desktop operating systems such as Windows 11, Windows 11, etc.
NOTE! – Are you looking for Azure Data Studio application download options for macOS, Windows, and Linux? The following link will help you. Download and install Azure Data Studio – Azure Data Studio | Microsoft Docs
SSMS with Azure Data Studio Installation Silent Command Line Parameter
Let’s see how to install the Microsoft SQL Server Management Studio with Azure Data Studio. I have tried using the following command line parameters for Microsoft SQL Server Management Studio with Azure Data Studio.
SQL Management Studio Installation Parameter – SSMS-Setup-ENU.exe /install /quiet /norestart /log
- Initializing string variable ‘SetupHelpDescriptionText’ to value ‘/install | /repair | /uninstall – installs, repairs, or uninstalls. Install is the default.
- /passive | /quiet – displays minimal UI with no prompts or displays no UI and no prompts. By default, UI and all prompts are displayed.
- /norestart – suppress any attempts to restart. By default, UI will prompt before restart.
- /log – the prefix for SSMS setup logs. By default, log files are created under %TEMP%\SSMSSetup.
- SSMSInstallRoot = Path to SSMS Location. By default, C:\Program Files (x86)\Microsoft SQL Server Management Studio 18.
New Installation of SQL Server Management Studio and Azure Data Studio
Let’s check how to install SQL Server Management Studio and Azure Data Studio using the manual method. You need administrative permissions on the server or desktop where you are installing the SSMS. Click on SSMS-Setup-ENU.exe to start the installation process.
You can change the Installation folder but the default location of SSMS install is C:\Program Files (x86)\Microsoft SQL Server Management Studio 18. For the SCCM server scenario, I would install it on the same drive where the SCCM is installed.
- Click on the Change button to browse and change the SSMS installation location.
- Click on Install button to start the SSMS installtion once ready.
This is the phase where all the dependency applications and other programs are being extracted from SSMS-Setup-ENU.exe. You can confirm this by launching %temp% from the run menu.
You can check the log file from the %temp%\SsmsSetup folder to get more details about all the MSIs and other programs that got extracted and installed as part of the master SSMS.EXE.
- SSMS-Setup-ENU_20220228120030.log
- SSMS-Setup-ENU_20220228120030_0_HelpViewer2_3.log
- SSMS-Setup-ENU_20220228120030_10_SsmsPostInstall_x64.log
- SSMS-Setup-ENU_20220228120030_1_sql_as_oledb_x64.log
- SSMS-Setup-ENU_20220228120030_2_sql_as_oledb_x86.log
- SSMS-Setup-ENU_20220228120030_3_VS2017IsoShellForSSMS.log
- SSMS-Setup-ENU_20220228120030_4_VSTA2017.log
- SSMS-Setup-ENU_20220228120030_5_sql_ssms_x64.log
- SSMS-Setup-ENU_20220228120030_6_sql_ssms_loc_x64_Loc.log
- SSMS-Setup-ENU_20220228120030_7_ssms_rs_x64.log
- SSMS-Setup-ENU_20220228120030_8_ssms_as_x64.log
- SSMS-Setup-ENU_20220228120030_9_ssms_is.log
The installation of SSMS (SQL Server Management Studio with Azure Data Studio) got completed successfully. This completes the installation of Azure Data Studio as well.
NOTE! – Once installed, both SSMS and Azure Data Studio are two different applications and should be treated differently. For example, update, uninstall, repair etc of Azure Data Studio is different from SSMS.
Connect to SCCM Secondary Server SQL Express DB with SSMS and Azure Data Studio
Now, you can connect to SCCM Secondary Server SQL Express DB with SSMS. This SCCM SQL express DB can be managed with Azure Data Studio as well.
- You can launch SQL Management Studio from Start Menu – Microsoft SQL Server Tools 18 folder – Microsoft SQL server Management Studio 18 (SSMS).
SCCM Secondary server SQL Express Database is connected with SQL Server Management Studio.
You can use Azure Data Studio to connect and manage the SCCM SQL server database (secondary server or primary server).
Fix Upgrade SSMS Error Setup is Blocked
Let’s discuss a scenario when you try to upgrade SQL management studio. There is an automatic update process available for the SSMS and Azure Data Studio applications.
I have received an error “Setup Blocked – something that has blocked the setup from continuing.” I have checked the log file SSMS-Setup-ENU_20220228085737.log and I could see the following error SSMS is currently running. Please close any open instances of SSMS and run this installer again.
This error states some of the users are opened SQL Management Studio. Close the SQL Management Studio before the start the
Initializing string variable ‘SetupFailedDescriptionText’ to value ‘A failure occurred during installation.’
Initializing string variable ‘SetupFailedText’ to value ‘Setup Failed’
MainViewModel.AddFailedCondition: Error: SSMS is currently running. Please close any open instances of SSMS and run this installer again.
MainViewModel.AddFailedCondition: Error: SSMS is currently running. Please close any open instances of SSMS and run this installer again.
I have seen a warning such as – The Generally Available (GA) version of SQL Server Management Studio (SSMS) v18.0 cannot be installed because a pre-release version of SSMS is installed on your computer. Uninstall the pre-release version of SSMS from ‘Add/Remove Programs in the Control Panel and run SSMS setup again.
You can’t upgrade from Microsoft SQL Server Management Studio with Azure Data Studio preview version to the production version. You have to uninstall the preview version of SSMS and Azure Data Studio and then install the newer production version.
SSMS Install using Command Line
SSMS install can be done through the following script or command line as well. You can also install SSMS using a command prompt script. If you want to install SSMS in the background with no GUI prompts, then follow the steps below.
- Launch the command prompt with elevated permissions.
- Type the command below in the command prompt.
start "" /w <path where SSMS-Setup-ENU.exe file is located> /Quiet SSMSInstallRoot=<path where you want to install SSMS>
start "" /w %systemdrive%\HTMDFrom\SSMS-Setup-ENU.exe /Quiet SSMSInstallRoot=%systemdrive%\HTMDToIf all goes well, you can see SSMS installed at %systemdrive%\HTMDTo\Common7\IDE\Ssms.exe” based on the example. If something went wrong, you could inspect the error code returned and take a peek at the %TEMP%\SSMSSetup for the log file.
Auto Update of SSMS is Enabled By Default
SSMS auto-update is enabled by default and this could create confusion for some of the users. If you have published SSMS in Citrix or Terminal server, then it won’t be possible for users to update because of local admin permissions or internet proxy-related issues in the office network.
Whenever a user launches SSMS, they get the message “An Update to SQL Server Management Studio is available.” this can be annoying for the users. You can disable it via the UI and registry method.
3 options to Disable SSMS Automatic Update
Let’s discuss the three methods to disable SSMS auto-update (Automatic Update) because of the issues highlighted below even though I’m personally a big fan of Auto-Update.
- Use SSMS GUI to Disable Auto Update
- Use Registry Entry to Disable the Auto Update of SSMS
- Use Group Policy to Disable the SSMS Auto Update
Launch SSMS console and click on Tools menu and then select Check for Updates.
You can launch the SQL Server Management Studio Updates window from the taskbar and remove the checkmark from the option called “Automatically check for updates for SQL Server Management Studio“
The registry option to disable SSMS Auto Update is given below. Navigate to the following registry entry and change the String value of Enabled = False to disable the automatic update of SSMS.
- Computer\HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\18.0_IsoShell\UpdateChecker
You can use Group Policy Objects (GPO) or the applications such as Ivanty (aka AppSense) to deploy user-based policies to disable the automatic update of SSMS or SQL Server Management Studio.
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\18.0_IsoShell\UpdateChecker]
"Enabled"="False"You can launch the Group Policy Editor using GPMC.MSC and create a user-based group policy via User Configuration – Preferences – Windows Settings – Registry to disable automatic update of SSSMS.
Repair Microsoft SQL Server Management Studio
Let’s see how to repair Microsoft SQL Server Management Studio. You can use the following command-line parameter to uninstall SSMS with Azure Data Studio. This is a good choice when you have a source file with you.
Slient Repair Command Line option for SQL Management Studio – SSMS-Setup-ENU.exe /repair /quiet.
The other option to repair the SSMS with Azure Data Studio is the uninstall option from Add or Remove Programs. The same method we used for many years as part of troubleshooting.
- Open Appwiz.cpl from Run menu.
- Search for “Microsoft SQL Server Management Studio.”
- Right-click on the SSMS and Select Uninstall option.
Click on the Repair button SSMS with Azure Data Studio uninstall or Repair wizard.
SSMS Repair Failed with Unknown Error 0x80072F76
The SSMS Repair Failed with Unknown Error 0x80072F76. Let’s find out the reasons from the log entries below. It seems that the failure is due to something related to the DotNet version. But, before further troubleshooting, I will restart the server and check whether that resolves the issue or not.
The SSMS Log file (SSMS-Setup-ENU_20220228101437) location = %Temp%\SsmsSetup.
Failed to acquire payload: NetFx45Web to working path: C:\Users\anoop\AppData\Local\Temp{9b6f6f32-4e3c-4ee5-a39b-993b301dad73}\NetFx45Web, error: 0x80072f76.
Error 0x80072f76: Failed while caching, aborting execution.
MainViewModel.OnBundleAction: Bundle action failed: Unknown error (0x80072f76) (0x80072F76)
Uninstall SQL Server Management Studio
Even the restart of the server didn’t help to solve the issue. Hence I ended up uninstalling SQL Server Management Studio. You can use the following command-line parameter to silently uninstall SSMS.
Slient Uninstall Command Line option for SQL Management Studio – SSMS-Setup-ENU.exe /uninstall /quiet.
NOTE! – The Azure Data Studio won’t get uninstalled as part of the manual uninstall process from Add or Remove Programs.
This uninstall process will only remove the SQL Server Management Studio. It won’t uninstall Azure Data Studio. As you can see in AppWiz.CPL, there is another application entry for Azure Data Studio. You will need a separate uninstall process for the same.
Author
Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.