AutoMon,  DBA

DBA AutoMon configure Database Centralized management server CMS

Advertisements

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://www.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 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. And the login mode should be SQL for mixed mode.

 

–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.

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),
  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(50) NOT NULL,ComputerName varchar(50) NOT NULL,Description varchar(50) NOT NULL,
  Instance varchar(50),Edition varchar(50) NOT NULL,
  Version varchar(20) 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),
  comments_1 varchar(20),comments_2 varchar(20),Added_date datetime,Maintenance_date datetime

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 ‘DBA_LAPTOP-ISGUKEUC\MUTHU’,’LAPTOP-ISGUKEUC\MUTHU’,’SQL2014′,’LAPTOP-ISGUKEUC\MUTHU’,’DBA’,’G0d$peed’,’Non PROD’,’UK’,’Standard Edition (64-bit)’,’Running’,’Windows’

Custom Script for add all server:

select ‘EXEC USP_DBA_ADDSERVER_FOR_MONITOR’,”’DBA_’+ServerName+”’,’,””+ServerName+”’,’,

””+Version+”’,’,””+ServerName+”’,’,”’SA”,’,”’SApassword”,’,

””+Category+”’,’,”’India”,’,””+Edition+”’,’,”’Running”,’,””+Login_Mode+””

from dbo.tbl_SQL_AutoMON where svr_Status <>’Server Not running’

 

Custom Script to Drop all the servers:

Create Drop server SP — USP_DBA_DROPSERVER_FOR_MONITOR

select ‘EXEC USP_DBA_DROPSERVER_FOR_MONITOR’,”’DBA_’+ServerName+”’,’,””+ServerName+”’,’,

””+Version+”’,’,””+ServerName+””

from dbo.tbl_SQL_AutoMON where servername like ‘%ii%’

Add and drop server stored procedure in next post.

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: