SQL Server Management Studio 21 (Preview) was announced last week at PASS Data Community Summit 2024. It has some cool new features that are available in the preview.
You can check those out here.
If you're familiar with Ansible, you know how easy it is to deploy software. I'll share a playbook that can be used to install this version quickly across a set number of development servers (after all, it is the preview edition, so I wouldn't recommend deploying to production just yet).
Let's jump in.
Create the Offline Installation:
We'll create an offline installation and copy the folder to our Ansible control node. This could be hosted on a file share just as easily.
- Download the bootstrapper.
- Create a local layout. Open a command prompt as an administrator, and run "vs_ssms.exe --layout c:\localSSMSlayout --add Microsoft.Component.HelpViewer". Change the --layout parameter to meet your needs.
Deploying SSMS 21 Using Ansible:
- Next, zip the localSSMSLayout folder and copy it to your Ansible control node. I've created a folder named SSMS21 and then copied the zip file.
- Copy the playbook from below.
Playbook Breakdown:
Here's what the playbook does.
1. Setup:
- Creates a temporary directory on the target Windows servers. In my example, I have 3 Windows Server 2022 machines I'd like to test this preview on.
- Copies the SSMS 21 installation files from the control node to the managed nodes.
- Extracts the installation package on the managed nodes.
2. Installation:
- Runs the SSMS 21 installer silently, adding the Help Viewer component.
- The installation command is registered to capture the exit code.
3. Reboot if needed:
- Checks if the installation requires a reboot (exit code 3010).
- Reboots the server if needed, ensuring the installation completes properly.
4. Cleanup:
- After the installation (and any necessary reboot), removes the temporary directory and files from the target servers.
Additional Notes:
- Error Handling:
- ignore_errors: true: Used during the installation to prevent the playbook from failing if the installer returns a non-zero exit code (e.g., 3010).
- Reboot Logic:
- The playbook intelligently handles reboots by checking the installer exist code and only rebooting when required.
- Tags:
- tags: installonly: Allows you to run or skip this task when using tags in your Ansible command.
- Module Collections:
- community.windows.win_unzip; The win_unzip module is part of the community.windows collection, so ensure this collection is installed
Conclusion:
This playbook could be enhanced by checking to ensure SSMS 21 isn't already installed before running and adding additional variables. It currently streamlines the process of handling file transfers (in case your managed Windows nodes don't have internet access), streamlines the installation process, reduces manual effort, and handles the cleanup of temporary installation files afterward.
If managing SQL Server on Windows with Ansible is interesting, check out my Ansible for SQL Server DBAs: Level 1 course (coming soon).