DBA AutoMon configure Database Centralized management server CMS

Setup Database Centralized management server CMS DBA AutoMon

This is a series of post which will have lot of posts and scripts, which will help DBAs to do proactive work – consolidation and quickly understand the environments.

Some background and thanks to my senior DBA Roshan Joe Joseph, we have started together this automation and scripts in year 2008, when we don’t have much tools and where the client could not invest money to buy a tool. We started the DBA AutoMon and planned to build a front end GUI, we could not finish that due to family, personal life and different company career.

In most of the service based corporation, we need to work different accounts/clients some period of time. This scripts will help us to understand the environments quickly to stabilize the environment.

Before we start build this, better to get dedicated server/instance and service accounts for SQL. It is better to get since we will have complications like different domain and DMZ SQL firewall access, linked server be made SQL credential etc. It is easy to get a VM and now the license is esxi host based.

What we need:

Instance, Service account and Database mail to configured, SQL agent service

How to enable database mail

https://sqlserverblogforum.com/dba/how-to-setup-the-database-mail-in-sql-server-2005/

In this post, I will show how to prepare and setup DBA AutoMon CMS scripts. You can setup all in one shot or manually, when you have 500+ SQL servers, it will take time to add everything manually.

We need to get the list of SQL servers or inventory and add them into register server, I prefer to create version wise and add them all into it by manually. You can even automate this as well.

https://www.mssqltips.com/sqlservertip/3252/automate-registering-and-maintaining-servers-in-sql-server-management-studio-ssms/

 

–Collect the Server Name lists from Customer. Create two tables one is for project inventory and other is for AutoMON.

–Check the server access and create a registered server and run the following query make results as in excel.

select @@SERVERNAME as server,isnull(serverproperty ('InstanceName'),'Default')as Instance,
serverproperty ('Edition')as edition,serverproperty('ProductVersion') as SQL_Version,serverproperty('ProductVersion') as SQL_full_version,
serverproperty ('ProductLevel')as SP,
case when serverproperty ('IsIntegratedSecurityOnly')=0 then 'SQL' else 'Windows'end as login_mode,
'update HA' as [HA],case when serverproperty ('IsClustered')=0 then 'Stand alone' else 'Clustered'end as IsClustered ,
serverproperty ('Collation') as Collation,
serverproperty ('BuildClrVersion') as  BuildClrVersion,
'update Domain' as [Domain],'update OS' as [OS],

--serverproperty ('LicenseType') as LicenseType ,

--CONNECTIONPROPERTY('net_transport') AS net_transport,
--CONNECTIONPROPERTY('protocol_type') AS protocol_type,
--CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS IP,
--CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
--CONNECTIONPROPERTY('client_net_address') AS client_net_address,
--sysinfo.virtual_machine_type_desc,
server_type = case 
when sysinfo.virtual_machine_type =1 then 'Virtual' else 'Physical' end,
cpu_count as [No_of_logical_cpu],
hyperthread_ratio,
cpu_count/hyperthread_ratio as [No_of_physical_cpu],
physical_memory_kb,
'Prod' as Category,
'update location' as Location,
'update Applications' as Applications,
'update Business_owner' as Business_owner,
'update Critical_service_level' as Critical_service_level,
'update Severity' as Severity,
'Running' as server_status,
'Update Windows patch' as is_win_A_path,
'Update SQL patch' as Is_SQL_Auto_Path,
 'Update Backup details' as Is_backup,
 'Update Monitoring Details' as Is_monitoring,
 'Update License Detalis ' as License_Detalis,
'update comments_1' as comments_1,
'update comments_2' as comments_2,
getdate() as Added_date,
getdate() as Maintenance_date

from sys.dm_os_sys_info sysinfo



EXEC master.dbo.xp_regread 
'HKEY_LOCAL_MACHINE',
'Software\Microsoft\Windows NT\CurrentVersion',
'productname'

 

–Take the server lists and align it in excel. The sheet should have (Servername,Description,Version,Category,location,Login_mode,Edition,SVR_status) these details. If the server is using mixed mode then the login_mode is SQL, for windows mode – windows.

 

–Copy the excel sheet to notepad use Ctrl+H and replace the empty space with ‘<>’

–Create a table which matches the excel column name and upload the notepad to the table.

–Modify the fields whichever needed like environment prod or dev.

 

Create database called DBAData & DBAData_Archive. DBAdata is for OLTP to process each run and DBAData_Archive will store the historical data to take report and setup SSRS.

Both server name and description are server name, by using registered server, the server name is auto populated.

drop table tbl_SQL_AutoMON
go
use DBAData
go
create table tbl_SQL_AutoMON
(
  Servername varchar(50) NOT NULL,Description varchar(50) NOT NULL,Instance varchar(50),Edition varchar(50) NOT NULL,
  Version varchar(20) NOT NULL,Version_number varchar(20),SP varchar(20),Login_mode varchar(20) NOT NULL,   HA varchar(20),
  IS_clustered varchar(20),Collation varchar(50),BuildClrVersion varchar(20),Domain varchar(20),
  OS_version varchar(50),IP varchar(20),IS_VM varchar(20),CPU_logical int,hyperthread_ratio int,
  CPU_physical int,RAM bigint,Category varchar(20),location varchar(20),Applications varchar(200),
  Business_owner varchar(200),Critical_service_level varchar(50),Severity varchar(50),SVR_status varchar(20),
  is_win_A_path varchar (30), Is_SQL_Auto_Path varchar (30), Is_backup varchar(30),Is_monitoring varchar(30),
  License_Detalis varchar(50),comments_1 varchar(20),comments_2 varchar(20),Added_date datetime,Maintenance_date datetime
)

–Upload the notepad to the table.

 

BULK INSERT tbl_SQL_AutoMON  FROM 'C:\Users\Muthu\Desktop\Blog_post_2019_oct_from\Automon_post_series\servers.txt'WITH (FIELDTERMINATOR = '<>',ROWTERMINATOR = '\n')

 

–Check the server name to remove the duplicate entries.

select server_name,COUNT() from dbo. tbl_SQL_AutoMON group by server_name having COUNT() >1

–Update the table values that matches the add server scripts.

/The query will update the right version/

select version from tbl_SQL_AutoMON group by version
update tbl_SQL_AutoMON set version   ='SQL2000' where version like '8%'
update tbl_SQL_AutoMON set version   ='SQL2005' where version like '9%'
update tbl_SQL_AutoMON set version   ='SQL2008' where version like '10.0%'
update tbl_SQL_AutoMON set version   ='SQL2008R2' where version like '10.5%'
update tbl_SQL_AutoMON set version   ='SQL2012' where version like '11%'
update tbl_SQL_AutoMON set version   ='SQL2014' where version like '12%'
update tbl_SQL_AutoMON set version   ='SQL2016' where version like '13%'
update tbl_SQL_AutoMON set version   ='SQL2017' where version like '14%'
update tbl_SQL_AutoMON set version   ='SQL2019' where version like '15%'


-- update category

UPDATE tbl_SQL_AutoMON set category ='Non-Prod' --where servername not like '%ip%'

select * from tbl_SQL_AutoMON

Create a DBA Automon base table, all the scripts going to use this table as a server repository.

use DBAData
go
drop table DBA_All_servers

CREATE TABLE dbo.DBA_All_servers(
  id int NOT NULL identity,
  [Servername] [varchar](100) NOT NULL,
  [ComputerName] [varchar](100) NULL,
  [Description] [varchar](100) NOT NULL,
  [Instance] [varchar](50) NULL,
  [Login_mode] [varchar](20) NULL,
  [Edition] [varchar](500) NOT NULL,
  [ProductBuild] [varchar](50) NULL,
  [ProductBuildType] [varchar](50) NULL,
  [ProductLevel] [varchar](50) NULL,
  [ProductMajorVersion] [varchar](50) NULL,
  [ProductMinorVersion] [varchar](50) NULL,
  [ProductUpdateLevel] [varchar](50) NULL,
  [ProductUpdateReference] [varchar](50) NULL,
  [Version] [varchar](50) NOT NULL,
  [ResourceLastUpdateDateTime] [varchar](50) NULL,
  [ResourceVersion] [varchar](50) NULL,
  [MachineName] [varchar](50) NULL,
  [IsClustered] [varchar](11) NULL,
  [IsFullTextInstalled] [varchar](50) NULL,
  [IsHadrEnabled] [varchar](50) NULL,
  [IsLocalDB] [varchar](50) NULL,
  [IsPolyBaseInstalled] [varchar](50) NULL,
  [IsSingleUser] [varchar](50) NULL,
  [IsXTPSupported] [varchar](50) NULL,
  [LCID] [varchar](50) NULL,
  [LicenseType] [varchar](50) NULL,
  [NumLicenses] [varchar](50) NULL,
  [ProcessID] [varchar](50) NULL,
  [SqlCharSet] [varchar](50) NULL,
  [SqlCharSetName] [varchar](50) NULL,
  [SqlSortOrder] [varchar](50) NULL,
  [SqlSortOrderName] [varchar](50) NULL,
  [FilestreamShareName] [varchar](50) NULL,
  [FilestreamConfiguredLevel] [varchar](50) NULL,
  [FilestreamEffectiveLevel] [varchar](50) NULL,
  [CollationID] [varchar](50) NULL,
  [ComparisonStyle] [varchar](50) NULL,
  [EditionID] [varchar](50) NULL,
  [EngineEdition] [varchar](50) NULL,
  [HadrManagerStatus] [varchar](50) NULL,
  [InstanceDefaultDataPath] [varchar](max) NULL,
  [InstanceDefaultLogPath] [varchar](max) NULL,
  [IsAdvancedAnalyticsInstalled] [varchar](50) NULL,
  [net_transport] [varchar](50) NULL,
  [protocol_type] [varchar](50) NULL,
  [auth_scheme] [varchar](50) NULL,
  [IP] [varchar](50) NULL,
  [local_tcp_port] [varchar](50) NULL,
  [client_net_address] [varchar](50) NULL,
  [HA] [varchar](20) NULL,
  [Domain] [varchar](50) NULL,
  [OS] [varchar](50) NULL,
  [server_type] [varchar](20) NULL,
  [No_of_logical_cpu] [int] NULL,
  [hyperthread_ratio] [int] NULL,
  [No_of_physical_cpu] [int] NULL,
  [physical_memory_kb] [bigint] NULL,
  [Category] [varchar](50) NOT NULL,
  [Location] [varchar](20) NOT NULL,
  [Applications] [varchar](max) NULL,
  [Business_owner] [varchar](max) NULL,
  [Critical_service_level] [varchar](50) NULL,
  [Severity] [varchar](50) NULL,
  [SVR_status] [varchar](20) NOT NULL,
  [MS_Patch] [varchar](50),
  [EOL] [Datetime],
  [E_EOL] [Datetime],
  [OS_EOL] [Datetime],
  [Esxi_mapping] [varchar](200),
  [Esxi_hostName] [varchar](200),
  [Patch_compliance] [varchar](15),
  [is_win_A_path] [varchar](20) NULL,
  [Is_SQL_Auto_Path] [varchar](20) NULL,
  [Is_backup] [varchar](20) NULL,
  [Is_monitoring] [varchar](25) NULL,
  [License_Detalis] [varchar](20) NULL,
  [comments_1] [varchar](100) NULL,
  [comments_2] [varchar](100) NULL,
  [Added_date] [datetime] NULL default (getdate()),
  [Maintenance_date] [datetime] NULL default (getdate())

PRIMARY KEY CLUSTERED 
(
  Servername ASC
)
)

 

Create Add server SP — USP_DBA_ADDSERVER_FOR_MONITOR

–Write a single custom script for adding all the servers.

Adding table SP Example:

EXEC USP_DBA_ADDSERVER_FOR_MONITOR    @P_SERVER='LAPTOP-ISGUKEUC\MUTHU',  @P_DESC='LAPTOP-ISGUKEUC\MUTHU',    @P_VERSION='SQL2014',   @P_USERNAME='SA',   @P_PWD='SApassword',    @P_category='Non-Prod', @P_location='India',    @P_edition='Enterprise Edition: Core-based Licensing (64-bit)', @P_svr_status='Running',    @P_login_mode='SQL'

 

Custom Script for add all server: Use either of one method. Better go with SQL linked server.

--Custom Script to add all the servers: SQL linked server
select 'EXEC USP_DBA_ADDSERVER_FOR_MONITOR','@P_SERVER='''+ServerName+''',','@P_DESC='''+ServerName+''',',
'@P_VERSION='''+Version+''',','@P_USERNAME=''SA'',','@P_PWD=''SApassword'',',
'@P_category='''+Category+''',','@P_location=''India'',','@P_edition='''+Edition+''',','@P_svr_status=''Running'',','@P_login_mode='''+Login_Mode+''''
from dbo.tbl_SQL_AutoMON --where svr_Status <>'Server Not running'


--Custom Script to add all the servers: Other source
select 'EXEC USP_DBA_ADDSERVER_FOR_MONITOR','@P_LINK_SERVER=''DBA_'+ServerName+''',@P_SERVER='''+ServerName+''',','@P_DESC='''+ServerName+''',',
'@P_VERSION='''+Version+''',','@P_USERNAME=''SA'',','@P_PWD=''SApassword'',',
'@P_category='''+Category+''',','@P_location=''India'',','@P_edition='''+Edition+''',','@P_svr_status=''Running'',','@P_login_mode='''+Login_Mode+''''
from dbo.tbl_SQL_AutoMON --where svr_Status <>'Server Not running'

 

Custom Script to Drop all the servers:

Based on what method you have used to create a auto linked server, use the same source to drop it all.

Create Drop server SP — USP_DBA_DROPSERVER_FOR_MONITOR

--Custom Script to Drop all the servers:
--Custom Script to drop all the servers: SQL linked server
select 'EXEC USP_DBA_DROPSERVER_FOR_MONITOR',''''+ServerName+''',',
''''+Version+''',',''''+ServerName+''''
from dbo.tbl_SQL_AutoMON --where servername like '%ii%'

--Custom Script to drop all the servers: Other source

--Custom Script to Drop all the servers:
--Custom Script to drop all the servers: SQL linked server
select 'EXEC USP_DBA_DROPSERVER_FOR_MONITOR','''DBA_'+ServerName+''',',''''+ServerName+''',',
''''+Version+''',',''''+ServerName+''''
from dbo.tbl_SQL_AutoMON --where servername like '%ii%'

 

Also, useful to keep the following.

/*
-- Install Details
create table tbl_server_installed
(Server_name varchar (50), Version varchar(20), Install_Date datetime, Tickert_no varchar (50),
Business_Owner varchar(50),Application varchar(50), Catagory varchar (20), Location varchar(20),
Domain varchar(20), Is_added_backup varchar (20), Is_Added_monitoring varchar(20), Is_added_Patch varchar(20),
Comments varchar(50), Added_date datetime default getdate()
)

-- Decommission Details
create table tbl_server_decommission
(Server_name varchar (50), Version varchar(20), Server_or_DB varchar(30), Decom_Date datetime, Tickert_no varchar (50),
Business_Owner varchar(50),Application varchar(50), Catagory varchar (20), Location varchar(20),
Domain varchar(20), Is_removed_backup varchar (20), Is_removed_monitoring varchar(20), Is_Removed_Patch varchar(20),
Comments varchar(50), Added_date datetime default getdate()
)



-- Migration Details
create table tbl_server_Migration
(Source_Server_name varchar (50),Migrated_server_name varchar (50), Old_Version varchar(20), New_Version varchar(20),
Server_or_DB varchar(30), Migration_Date datetime, Tickert_no varchar (50),
Business_Owner varchar(50),Application varchar(50), Catagory varchar (20), Location varchar(20),
Domain varchar(20), Is_added_backup varchar (20), Is_Added_monitoring varchar(20), Is_added_Patch varchar(20),
Comments varchar(50), Added_date datetime default getdate()
)


-- Upgrade Details
create table tbl_server_Upgrade
(Server_name varchar (50), Old_Version varchar(20), New_Version varchar(20),
Upgrade_Date datetime, Tickert_no varchar (50),
Business_Owner varchar(50),Application varchar(50), Catagory varchar (20), Location varchar(20),
Domain varchar(20), Is_added_backup varchar (20), Is_Added_monitoring varchar(20), Is_added_Patch varchar(20),
Comments varchar(50), Added_date datetime default getdate()
)

*/

 

Add and drop server stored procedure in next post.