How To Bundle MSSP Packages?
Introduction
MSSP stands for Management Service Server Packages. Starting with Windows 2012, this feature has been introduced to simplify the management of multiple servers in an MSSQL Instance. The MSSP package is a SQL Agent job created by Microsoft on the Master Database (e.g. masterdb_ms14-068). It executes all jobs that are associated with the MSSP package.
MSSP packages are a native feature of MSSQL, and can be used in a few different ways. It provides a way to consolidate all jobs into one location, and centralize management of multiple servers. Each MSSP package is assigned an owner (for example, SecOPS). This will determine who is notified when the job fails on any of the managed servers associated with the MSSP Package.
Installation of MSSP Package
The main idea is that, MSSP Package will be executed on all servers which are part of the MSSQL Instance. So you need to make sure that, MSSP package has been installed on all your SQL Servers.
There are three ways to install MSSP package , please find these below:
1) From SSMS :
Right click on any server under the ‘Object Explorer’ panel and select “Management Service Configuration” option. Then provide credentials for SQL server where Management Services are installed or using windows user/password box which comes into view when right clicking the same node again. Select “Use Windows Authentication.” Then follow steps as mentioned in screenshot below:
Now right-click on “Management Services” again and select the “Start Management Services” option.
Now you can find “Management Service” under Object Explorer.
Right-click on it and select properties, fill in the details for Master Database, which will be used by MSSP package to run jobs. You can also edit or create new jobs directly from here. Follow steps as mentioned in the screenshot below:
2) From Command Prompt :
Using command prompt , you can install/ uninstall MSSP packages . To install the MSSP package , just type following commands one after another :
net stop mssqlserver
msiexec.exe /i MSSQL$SQLEXPRESS\MSSQL10_50.MSSQLSERVER2014CTP3.1\Setup Bootstrap Completion Status = SUCCESS
To uninstall MSSP package, you need to type:
net stop sql mgm provider and net start sql mgm provider (After removing the MSSP installation folder)
Now , after these steps, the MSSP package will be installed on all your SQL Servers under that particular Domain account. You can add/ remove servers from this account by modifying the configuration in “Configuration Manager”.
3) From PowerShell :
Using PowerShell , you can install/ uninstall MSSP packages . To install the MSSP package , just type following command:
Add-PSSnapin microsoft.sqlserver.management.smlets
After that, to uninstall MSSP package , you need to type:
Remove-PSSnapin microsoft.sqlserver.management.smlets; Remove-Module sqlps
Now , after these steps, the MSSP package will be installed on all your SQL Servers under that particular Domain account . You can add/ remove servers from this account by modifying the configuration in “Configuration Manager”.
How To Bundle MSSP Packages
Bundling MSSP Packages is a pretty simple task, and requires only a few steps:
1. Create a new Job on the Master Database. This will be your MSSP package. Click New Query, paste the following code into the query window, and run it:
IF NOT EXISTS (SELECT name FROM msdb..MS_ManagementServicePackages WHERE name = N’SecOPS’) BEGIN EXECUTE msdb..sp_add_package @local_name = N’SecOPS’, @description=N’MSSP Package for SecOPS’, @category_name = NULL END ELSE BEGIN EXECUTE msdb..sp_update_package@local_name = N’SecOPS’,auto_start = 1 END
Note:
In the code above, SecOPS is a string that helps to identify your package. You can change it to anything you want. The auto_start property does two things for us: starts our MSSP Job on the server when SQL Server starts, and makes this job runnable by anyone who has permissions on it. If you do not want either of these things to happen, comment out both lines of code (as shown in the example).
2. Create a new Login for use with your MSSP package(s) A typical practice for login permissions would be to give SA permission on all servers associated with your MSSQL Instance (and any other logins/roles you like), and add these logins to the login for each MSSP package.
3. Add servers you wish to monitor, and create links between your new MSSP Login, servers, and the MSSP Package you created You can do this by running the following query on each server that will be monitoring via the new MSSP Login:
SELECT @ms_server = SERVERPROPERTY(‘servername’) , @package_name = LINK_IDENTITY()
4. Create a scheduled task on each of your monitored servers using your newly created login (with appropriate permissions)
5. Ensure all jobs are set to run under ONE ‘security context.’ This means one of two things needs to happen: 1) The SQL Agent service account match an existing local administrator 2) The SQL Agent service account is listed as a member of the sysadmin role
6. That’s it! You now have centralized management for your MSSP package(s) that can be modified quickly and easily.
You may need to restart each monitored server after creating the scheduled task for this task to take effect.
Conclusion
MSSP packages are a great tool which allows for multiple servers to communicate with one another. It is used primarily for compliance reasons (i.e. meeting SOX requirements) but also provides an easy way to manage multiple SQL Servers under one login account/password combination. This tutorial will walk you through how to create MSSP packages and add server details on different SQL Instances under the same domain account.
