We often refer to different servers not by their name, but by their associated function or application. Minion Enterprise provides a way to define applications and roles for your servers. For each application you wish to define, insert:
- the application information to dbo.Application
- the server’s role for this application (e.g., Database, SSIS, SSRS, etc.) in dbo.ApplicationRole
- the application’s environment on this server (e.g., production, test, etc.) in dbo.ApplicationEnvironment
- the associated SQL instance
For example: you have an app named Pulse360 on instance number 83, which is the production database server for this application. So we need to insert:
- the application: Pulse360
- the server’s role: Database
- the application’s environment: Prod
- the associated SQL instance ID: 8
/* -- Script to associate a server ID with an application, role, & environment -- Initial setup should include adding your server roles (like "database" or "reporting"), and your environments (like "prod", "dev", and so on). IMPORTANT: Be sure to customize these queries so they are useful for your environment, AND only run these insert statements once. */ ---- Define new server roles: Database, Reporting IF NOT EXISTS (select * from dbo.ApplicationRole) INSERT dbo.ApplicationRole SELECT 'Database' UNION ALL SELECT 'Reporting'; ---- Define environments: Prod, QA, Test, and Dev IF NOT EXISTS (SELECT * FROM dbo.ApplicationEnvironment) INSERT dbo.ApplicationEnvironment SELECT 'Prod' UNION ALL SELECT 'QA' UNION ALL SELECT 'Test' UNION ALL SELECT 'Dev'; ---- Create a new application: Pulse360 -- This is where you can associate contact names with an application (and, -- in the next step, with specific servers VIA that application). If -- Mike Crow is the contact person for Pulse360, we should note it here: DECLARE @AppName varchar(100); SET @AppName = 'Pulse360'; IF NOT EXISTS (select * from dbo.Application WHERE AppName = @AppName) INSERT dbo.Application ( AppName , [Desc] , ContactName , ManagerName , DirectorName ) VALUES ( @AppName , -- AppName - varchar(100) 'Risk Stratification Tool' , -- Desc - varchar(2000) 'Mike Crow' , -- ContactName - varchar(100) 'James Myers' , -- ManagerName - varchar(100) 'Steve Kos' -- DirectorName - varchar(100) ); ---- Associate a server (InstanceID) with an application, role, and environment! -- This is where you tie each of these values (application, role, and environment) to a server. -- Use the name of the server, and the name of each other value, in the following Setup procedure: EXEC Setup.ServerToApplication 'MidnightDBTest',--Servername 'Pulse360', --AppName 'Database', --Role 'Prod'; --Environment
Notes:
- An application can be associated with related contact information.
- A server can potentially perform multiple server roles. For example, a single SQL instance could be a Database, SSIS, SSRS, Web Server, etc.
- Each server can be a part of multiple environments – for example, Prod, QA, Test, Dev, UAT, etc.
Application benefits
After we run our script above, Pulse360 is set up as an application (with proper role and environment assignment). At this point, we can see this information when we use the server stored procedures below.
We'll also have the contact information for each application, and the environments that each database is assigned to.
EXEC Servers.ByName 'MidnightDBTest'; EXEC Servers.ByApp 'Pulse360'; EXEC Servers.ByEnviro 'Prod';
The application will also show up in alerts. Each alert shows you the application name, so you can see at a glance which apps are having issues.
https://minionware.freshdesk.com/support/solutions/articles/43000032884-how-to-define-applications