internal

package
v0.0.0-...-2a50bf3 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 29, 2025 License: Apache-2.0 Imports: 10 Imported by: 0

Documentation

Overview

Package internal provides data structures and functions for collecting SQL Server information.

Index

Constants

View Source 
const (
	// PowerProfileSettingRule used for power profile of machine.
	PowerProfileSettingRule = "power_profile_setting"
	// LocalSSDRule used to connect physical drive to disk type.
	LocalSSDRule = "local_ssd"
	// LogicalDiskToPartition info used for windows os collection.
	LogicalDiskToPartition = "logical_disk_to_partition"
	// PhysicalDiskToType info used for windows os collection.
	PhysicalDiskToType = "physical_disk_to_type"
	// DataDiskAllocationUnitsRule used to see blocksize of a physical drive.
	DataDiskAllocationUnitsRule = "data_disk_allocation_units"
	// GCBDRAgentRunning used for checking if GCBDRAgentRunning is running on the target.
	GCBDRAgentRunning = "gcbdr_agent_running"
)
View Source 
const (
	// ExperimentalMode .
	ExperimentalMode = true

	// AgentVersion is the version of the agent.
	AgentVersion = `1.3`

	// ServiceName .
	ServiceName = "google-cloud-sql-server-agent"

	// AgentUsageLogPrefix .
	AgentUsageLogPrefix = "wlm-sqlserver-eng"
)

Variables

View Source 
var MasterRules = []MasterRuleStruct{
	{
		Name: "DB_LOG_DISK_SEPARATION",
		Query: `SELECT type, d.name, physical_name, m.state, size, growth, is_percent_growth
						FROM sys.master_files m
						JOIN sys.databases d ON m.database_id = d.database_id`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"db_name":           HandleNilString(f[1]),
					"filetype":          HandleNilInt(f[0]),
					"physical_name":     HandleNilString(f[2]),
					"physical_drive":    "unknown",
					"state":             HandleNilInt(f[3]),
					"size":              HandleNilInt(f[4]),
					"growth":            HandleNilInt(f[5]),
					"is_percent_growth": HandleNilBool(f[6]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_MAX_PARALLELISM",
		Query: `SELECT value_in_use as maxDegreeOfParallelism
						FROM sys.configurations
						WHERE name = 'max degree of parallelism'`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"maxDegreeOfParallelism": HandleNilInt(f[0]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_TRANSACTION_LOG_HANDLING",
		Query: `WITH cte AS (
						SELECT d.name, MAX(b.backup_finish_date) AS backup_finish_date, MAX(m.growth) AS growth
						FROM master.sys.sysdatabases d
								LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND b.type = 'L'
								LEFT JOIN sys.master_files m ON d.dbid = m.database_id AND m.type = 1
						WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
						GROUP BY d.name
						)
					SELECT cte.name,
					CASE
						WHEN b.backup_finish_date IS NULL THEN 100000
						ELSE DATEDIFF(HOUR, b.backup_finish_date, GETDATE())
					END AS [backup_age],
					b.backup_size, b.compressed_backup_size,
					CASE
						WHEN growth > 0 THEN 1
						ELSE 0
					END AS auto_growth
					FROM cte
					LEFT JOIN msdb.dbo.backupset b
					ON b.database_name = cte.name
					AND b.backup_finish_date = cte.backup_finish_date`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"db_name":                HandleNilString(f[0]),
					"backup_age_in_hours":    HandleNilInt(f[1]),
					"backup_size":            HandleNilInt(f[2]),
					"compressed_backup_size": HandleNilInt(f[3]),
					"auto_growth":            HandleNilInt(f[4]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_VIRTUAL_LOG_FILE_COUNT",
		Query: `SELECT [name], COUNT(l.database_id) AS 'VLFCount', SUM(vlf_size_mb) AS 'VLFSizeInMB',
								SUM(CAST(vlf_active AS INT)) AS 'ActiveVLFCount',
								SUM(vlf_active*vlf_size_mb) AS 'ActiveVLFSizeInMB'
						FROM sys.databases s
						CROSS APPLY sys.dm_db_log_info(s.database_id) l
						WHERE [name] NOT IN ('master', 'tempdb', 'model', 'msdb')
						GROUP BY [name]`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"db_name":               HandleNilString(f[0]),
					"vlf_count":             HandleNilInt(f[1]),
					"vlf_size_in_mb":        HandleNilFloat64(f[2]),
					"active_vlf_count":      HandleNilInt(f[3]),
					"active_vlf_size_in_mb": HandleNilFloat64(f[4]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_BUFFER_POOL_EXTENSION",
		Query: `SELECT path, state, current_size_in_kb
						FROM sys.dm_os_buffer_pool_extension_configuration`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"path":       HandleNilString(f[0]),
					"state":      HandleNilInt(f[1]),
					"size_in_kb": HandleNilInt(f[2]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_MAX_SERVER_MEMORY",
		Query: `SELECT [name], [value], [value_in_use]
						FROM sys.configurations
						WHERE [name] = 'max server memory (MB)';`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"name":         HandleNilString(f[0]),
					"value":        HandleNilInt(f[1]),
					"value_in_use": HandleNilInt(f[2]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_INDEX_FRAGMENTATION",
		Query: `SELECT top 1 1 AS found_index_fragmentation
						FROM sys.databases d
							CROSS APPLY sys.dm_db_index_physical_stats (d.database_id, NULL, NULL, NULL, NULL) AS DDIPS
						WHERE ddips.avg_fragmentation_in_percent > 95
							AND d.name NOT IN ('master', 'model', 'msdb', 'tempdb')
							And d.name NOT IN (
								SELECT DISTINCT dbcs.database_name AS [DatabaseName]
								FROM master.sys.availability_groups AS AG
									INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id
									INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
									INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id
								WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1)`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"found_index_fragmentation": HandleNilInt(f[0]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_TABLE_INDEX_COMPRESSION",
		Query: `SELECT COUNT(*) numOfPartitionsWithCompressionEnabled
						FROM sys.partitions p
						WHERE data_compression <> 0 and rows > 0`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"numOfPartitionsWithCompressionEnabled": HandleNilInt(f[0]),
				})
			}
			return res
		},
	},
	{
		Name: "INSTANCE_METRICS",
		Query: `SELECT
							SERVERPROPERTY('productversion') AS productversion,
							SERVERPROPERTY ('productlevel') AS productlevel,
							SERVERPROPERTY ('edition') AS edition,
							cpu_count AS cpuCount,
							hyperthread_ratio AS hyperthreadRatio,
							physical_memory_kb AS physicalMemoryKb,
							virtual_memory_kb AS virtualMemoryKb,
							socket_count AS socketCount,
							cores_per_socket AS coresPerSocket,
							numa_node_count AS numaNodeCount
						FROM sys.dm_os_sys_info`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"os":                 HandleNilString(f[10]),
					"product_version":    HandleNilString(f[0]),
					"product_level":      HandleNilString(f[1]),
					"edition":            HandleNilString(f[2]),
					"cpu_count":          HandleNilInt(f[3]),
					"hyperthread_ratio":  HandleNilInt(f[4]),
					"physical_memory_kb": HandleNilInt(f[5]),
					"virtual_memory_kb":  HandleNilInt(f[6]),
					"socket_count":       HandleNilInt(f[7]),
					"cores_per_socket":   HandleNilInt(f[8]),
					"numa_node_count":    HandleNilInt(f[9]),
				})
			}
			return res
		},
	},
	{
		Name: "DB_BACKUP_POLICY",
		Query: `WITH cte AS (
							SELECT master.sys.sysdatabases.NAME AS database_name,
								CASE
									WHEN MAX(msdb.dbo.backupset.backup_finish_date) IS NULL THEN 100000
									ELSE DATEDIFF(DAY, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE())
								END AS [backup_age]
							FROM
									master.sys.sysdatabases
									LEFT JOIN msdb.dbo.backupset
									ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
							WHERE
									master.sys.sysdatabases.name NOT IN ('master', 'model', 'msdb', 'tempdb' )
							GROUP BY
									master.sys.sysdatabases.name
							HAVING
									MAX(msdb.dbo.backupset.backup_finish_date) IS NULL
									OR (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
					)
					SELECT
							MAX(backup_age) as maxBackupAge
					FROM cte`,
		Fields: func(fields [][]any) []map[string]string {
			res := []map[string]string{}
			for _, f := range fields {
				res = append(res, map[string]string{
					"max_backup_age": HandleNilInt(f[0]),
				})
			}
			return res
		},
	},
}

MasterRules defines the rules the agent will collect from sql server.

Functions

func CommandLineExecutorWrapper 

func CommandLineExecutorWrapper(ctx context.Context, executable string, argsToSplit string, exec commandlineexecutor.Execute) (string, error)

CommandLineExecutorWrapper executes a windows or linux command with arguments given

func GetPhysicalDriveFromPath 

func GetPhysicalDriveFromPath(ctx context.Context, path string, windows bool, exec commandlineexecutor.Execute) string

GetPhysicalDriveFromPath gets the physical drive associated with a file path for linux and windows env

func HandleNilBool 

func HandleNilBool(data any) string

HandleNilBool converts generic bool to desired string output, or returns 'unknown' if desired type if nil.

func HandleNilFloat64 

func HandleNilFloat64(data any) string

HandleNilFloat64 converts generic float64 to desired string output, or returns 'unknown' if desired type if nil.

func HandleNilInt 

func HandleNilInt(data any) string

HandleNilInt converts generic int64 to desired string output, or returns 'unknown' if desired type if nil.

func HandleNilString 

func HandleNilString(data any) string

HandleNilString converts generic string to the desired string output, or returns 'unknown' if desired type if nil.

func PrettyStruct 

func PrettyStruct(data any) (string, error)

PrettyStruct converts the passed in struct into a pretty json format.

func SaveToFile 

func SaveToFile(path string, data []byte) error

SaveToFile saves data to given path.

Types

type Details 

type Details struct {
	Name   string
	Fields []map[string]string
}

Details represents collected details results.

type DiskTypeEnum 

type DiskTypeEnum int

DiskTypeEnum enum used for disktypes to keep linux and windows collection consistent . 

const (
	// LocalSSD - local disk
	LocalSSD DiskTypeEnum = iota
	// PersistentSSD - persistent disk
	PersistentSSD
	// Other - not local or persistent disk but still a valid disk type
	Other
)

func (DiskTypeEnum) String 

func (disk DiskTypeEnum) String() string

type MasterRuleStruct 

type MasterRuleStruct struct {
	// Name defines the rule name.
	Name string
	// Query is the sql query statement for the rule.
	Query string
	// Fields returns the <key, value> of collected columns and values. Different rules query
	// different tables and columns.
	Fields func([][]any) []map[string]string
}

MasterRuleStruct defines the data struct of sql server master rules.

Source Files

View all Source files

Directories

Path Synopsis
Package activation contains functionalities for activating sql server client.
 Package activation contains functionalities for activating sql server client.
Package agentstatus provides functions that log SQL Server Agent status.
 Package agentstatus provides functions that log SQL Server Agent status.
Package configuration contains functionalities for agent configuration operations.
 Package configuration contains functionalities for agent configuration operations.
Package daemon uses kardianos service to make the app run as service / daemon on windows / linux.
 Package daemon uses kardianos service to make the app run as service / daemon on windows / linux.
Package flags defines the flags in the command line.
 Package flags defines the flags in the command line.
Package instanceinfo provides functionality for interfacing with the compute API.
 Package instanceinfo provides functionality for interfacing with the compute API.
Package remote ssh'es into remote machines and runs a command
 Package remote ssh'es into remote machines and runs a command
Package secretmanager is the wrapper of google cloud secretmanager api.
 Package secretmanager is the wrapper of google cloud secretmanager api.
Package sqlcollector contains modules that collects rules from Sql server.
 Package sqlcollector contains modules that collects rules from Sql server.
Package sqlservermetrics run SQL and OS collections and sends metrics to workload manager.
 Package sqlservermetrics run SQL and OS collections and sends metrics to workload manager.
Package wlm contains types and functions to interact with WorkloadManager cloud APIs.
 Package wlm contains types and functions to interact with WorkloadManager cloud APIs.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.