标签归档:sql运维

mssql sqlserver 安全检查—数据库实例配置快照


摘要:
下文讲述sqlserver日常安全检查中,数据库实例配置快照也必须作为数据库安全检查的重要环节,下文讲述”数据库实例”配置记载的方法,如下所示:


在大多数环境中,不止一个人可以访问SQL Server,并且不止一个人拥有更改服务器或数据库选项所需的权限。
如果错误的设置发生变化,性能影响可能很大。
可能某一个同事无意间对数据库选项进行改变,导致异常的发生,我们可以通过日常对配置快照,可以快速了解问题出现的原因,并修正异常。
—————————————————–
快照数据库实例配置的方法

    ---创建属性存放表
	IF OBJECT_ID(N'dbo.database_ConfigData', N'U') IS NULL
BEGIN
  CREATE TABLE [dbo].[database_ConfigData] 
  (
    [ConfigurationID] [int] NOT NULL ,
    [Name] [nvarchar](35) NOT NULL ,
    [Value] [sql_variant] NULL ,
    [ValueInUse] [sql_variant] NULL ,
    [CaptureDate] [datetime] NOT NULL DEFAULT SYSDATETIME()
  )  ;
  end 
GO
 
 
 
IF OBJECT_ID(N'dbo.database_DBData', N'U') IS NULL
BEGIN
  CREATE TABLE [dbo].[database_DBData]
  (
    [name] [sysname] NOT NULL,
    [database_id] [int] NOT NULL,
    [source_database_id] [int] NULL,
    [owner_sid] [varbinary](85) NULL,
    [create_date] [datetime] NOT NULL,
    [compatibility_level] [tinyint] NOT NULL,
    [collation_name] [sysname] NULL,
    [user_access] [tinyint] NULL,
    [user_access_desc] [nvarchar](60) NULL,
    [is_read_only] [bit] NULL,
    [is_auto_close_on] [bit] NOT NULL,
    [is_auto_shrink_on] [bit] NULL,
    [state] [tinyint] NULL,
    [state_desc] [nvarchar](60) NULL,
    [is_in_standby] [bit] NULL,
    [is_cleanly_shutdown] [bit] NULL,
    [is_supplemental_logging_enabled] [bit] NULL,
    [snapshot_isolation_state] [tinyint] NULL,
    [snapshot_isolation_state_desc] [nvarchar](60) NULL,
    [is_read_committed_snapshot_on] [bit] NULL,
    [recovery_model] [tinyint] NULL,
    [recovery_model_desc] [nvarchar](60) NULL,
    [page_verify_option] [tinyint] NULL,
    [page_verify_option_desc] [nvarchar](60) NULL,
    [is_auto_create_stats_on] [bit] NULL,
    [is_auto_update_stats_on] [bit] NULL,
    [is_auto_update_stats_async_on] [bit] NULL,
    [is_ansi_null_default_on] [bit] NULL,
    [is_ansi_nulls_on] [bit] NULL,
    [is_ansi_padding_on] [bit] NULL,
    [is_ansi_warnings_on] [bit] NULL,
    [is_arithabort_on] [bit] NULL,
    [is_concat_null_yields_null_on] [bit] NULL,
    [is_numeric_roundabort_on] [bit] NULL,
    [is_quoted_identifier_on] [bit] NULL,
    [is_recursive_triggers_on] [bit] NULL,
    [is_cursor_close_on_commit_on] [bit] NULL,
    [is_local_cursor_default] [bit] NULL,
    [is_fulltext_enabled] [bit] NULL,
    [is_trustworthy_on] [bit] NULL,
    [is_db_chaining_on] [bit] NULL,
    [is_parameterization_forced] [bit] NULL,
    [is_master_key_encrypted_by_server] [bit] NOT NULL,
    [is_published] [bit] NOT NULL,
    [is_subscribed] [bit] NOT NULL,
    [is_merge_published] [bit] NOT NULL,
    [is_distributor] [bit] NOT NULL,
    [is_sync_with_backup] [bit] NOT NULL,
    [service_broker_guid] [uniqueidentifier] NOT NULL,
    [is_broker_enabled] [bit] NOT NULL,
    [log_reuse_wait] [tinyint] NULL,
    [log_reuse_wait_desc] [nvarchar](60) NULL,
    [is_date_correlation_on] [bit] NOT NULL,
    [is_cdc_enabled] [bit] NOT NULL,
    [is_encrypted] [bit] NULL,
    [is_honor_broker_priority_on] [bit] NULL,
    [replica_id] [uniqueidentifier] NULL,
    [group_database_id] [uniqueidentifier] NULL,
    [default_language_lcid] [smallint] NULL,
    [default_language_name] [nvarchar](128) NULL,
    [default_fulltext_language_lcid] [int] NULL,
    [default_fulltext_language_name] [nvarchar](128) NULL,
    [is_nested_triggers_on] [bit] NULL,
    [is_transform_noise_words_on] [bit] NULL,
    [two_digit_year_cutoff] [smallint] NULL,
    [containment] [tinyint] NULL,
    [containment_desc] [nvarchar](60) NULL,
    [target_recovery_time_in_seconds] [int] NULL,
    [CaptureDate] [datetime] NOT NULL DEFAULT SYSDATETIME()
) ;
end 
GO
  
-------------------插入数据库实例相关配置信息------------------


INSERT INTO [dbo].[database_ConfigData]
(
  [ConfigurationID] ,
  [Name] ,
  [Value] ,
  [ValueInUse]
)
SELECT 
  [configuration_id] ,
  [name] ,
  [value] ,
  [value_in_use]
FROM [sys].[configurations];
GO
 
INSERT INTO [dbo].[database_DBData]
(
  [name],
  [database_id],
  [source_database_id],
  [owner_sid],
  [create_date],
  [compatibility_level],
  [collation_name],
  [user_access],
  [user_access_desc],
  [is_read_only],
  [is_auto_close_on],
  [is_auto_shrink_on],
  [state],
  [state_desc],
  [is_in_standby],
  [is_cleanly_shutdown],
  [is_supplemental_logging_enabled],
  [snapshot_isolation_state],
  [snapshot_isolation_state_desc],
  [is_read_committed_snapshot_on],
  [recovery_model],
  [recovery_model_desc],
  [page_verify_option],
  [page_verify_option_desc],
  [is_auto_create_stats_on],
  [is_auto_update_stats_on],
  [is_auto_update_stats_async_on],
  [is_ansi_null_default_on],
  [is_ansi_nulls_on],
  [is_ansi_padding_on],
  [is_ansi_warnings_on],
  [is_arithabort_on],
  [is_concat_null_yields_null_on],
  [is_numeric_roundabort_on],
  [is_quoted_identifier_on],
  [is_recursive_triggers_on],
  [is_cursor_close_on_commit_on],
  [is_local_cursor_default],
  [is_fulltext_enabled],
  [is_trustworthy_on],
  [is_db_chaining_on],
  [is_parameterization_forced],
  [is_master_key_encrypted_by_server],
  [is_published],
  [is_subscribed],
  [is_merge_published],
  [is_distributor],
  [is_sync_with_backup],
  [service_broker_guid],
  [is_broker_enabled],
  [log_reuse_wait],
  [log_reuse_wait_desc],
  [is_date_correlation_on],
  [is_cdc_enabled],
  [is_encrypted],
  [is_honor_broker_priority_on],
  [replica_id],
  [group_database_id],
  [default_language_lcid],
  [default_language_name],
  [default_fulltext_language_lcid],
  [default_fulltext_language_name],
  [is_nested_triggers_on],
  [is_transform_noise_words_on],
  [two_digit_year_cutoff],
  [containment],
  [containment_desc],
  [target_recovery_time_in_seconds]
)
SELECT
  [name],
  [database_id],
  [source_database_id],
  [owner_sid],
  [create_date],
  [compatibility_level],
  [collation_name],
  [user_access],
  [user_access_desc],
  [is_read_only],
  [is_auto_close_on],
  [is_auto_shrink_on],
  [state],
  [state_desc],
  [is_in_standby],
  [is_cleanly_shutdown],
  [is_supplemental_logging_enabled],
  [snapshot_isolation_state],
  [snapshot_isolation_state_desc],
  [is_read_committed_snapshot_on],
  [recovery_model],
  [recovery_model_desc],
  [page_verify_option],
  [page_verify_option_desc],
  [is_auto_create_stats_on],
  [is_auto_update_stats_on],
  [is_auto_update_stats_async_on],
  [is_ansi_null_default_on],
  [is_ansi_nulls_on],
  [is_ansi_padding_on],
  [is_ansi_warnings_on],
  [is_arithabort_on],
  [is_concat_null_yields_null_on],
  [is_numeric_roundabort_on],
  [is_quoted_identifier_on],
  [is_recursive_triggers_on],
  [is_cursor_close_on_commit_on],
  [is_local_cursor_default],
  [is_fulltext_enabled],
  [is_trustworthy_on],
  [is_db_chaining_on],
  [is_parameterization_forced],
  [is_master_key_encrypted_by_server],
  [is_published],
  [is_subscribed],
  [is_merge_published],
  [is_distributor],
  [is_sync_with_backup],
  [service_broker_guid],
  [is_broker_enabled],
  [log_reuse_wait],
  [log_reuse_wait_desc],
  [is_date_correlation_on],
  [is_cdc_enabled],
  [is_encrypted],
  [is_honor_broker_priority_on],
  [replica_id],
  [group_database_id],
  [default_language_lcid],
  [default_language_name],
  [default_fulltext_language_lcid],
  [default_fulltext_language_name],
  [is_nested_triggers_on],
  [is_transform_noise_words_on],
  [two_digit_year_cutoff],
  [containment],
  [containment_desc],
  [target_recovery_time_in_seconds]
FROM [sys].[databases];
GO

----查询相关表,获取配置变化的方法分享------------------
;WITH [tmp] AS
( 
  SELECT
    ROW_NUMBER() OVER (PARTITION BY [ConfigurationID] ORDER BY [CaptureDate] ASC) AS [RowNumber],
    [ConfigurationID] AS [ConfigurationID],
    [Name] AS [Name],
    [Value] AS [Value],
    [ValueInUse] AS [ValueInUse],
    [CaptureDate] AS [CaptureDate]
  FROM  [dbo].[database_ConfigData]
)
SELECT 
  [tmp].[Name] AS [设置项], 
  [tmp].[CaptureDate] AS [写入时间], 
  [tmp].[Value] AS [Previous Value], 
  [tmp].[ValueInUse] AS [Previous Value In Use],
  [tmp2].[CaptureDate] AS [Date Changed], 
  [tmp2].[Value] AS [New Value], 
  [tmp2].[ValueInUse] AS [New Value In Use]
FROM [tmp]
LEFT OUTER JOIN [tmp] AS [tmp2]
ON [tmp].[ConfigurationID] = [tmp2].[ConfigurationID]
AND [tmp].[RowNumber] + 1 = [tmp2].[RowNumber]
WHERE ([tmp].[Value] <> [tmp2].[Value] OR [tmp].[ValueInUse] <> [tmp2].[ValueInUse]);
GO
 

mssql sqlserver 安全检查—日常维护


摘要:
下文讲述mssql sqlserver安全检查中-例行维护任务涉及的事项,如下所示:



一、例行检查-日常维护任务涉及事项:

1.1 数据备份
1.2 完整性检查
1.3 索引维护
1.4 统计更新
常规情况下,我们会将数据库的日常维护任务全部放入数据库作业中自动运行,我们通过监控作业的运行情况,观察日常维护任务是否得到正常的运行


下文将分别举例介绍常用的数据脚本:

  -----此脚本可监控,数据库的所有作业运行历史(包括:数据维护计划 索引维护计划 ...)
  SELECT
  [j].[name] AS [作业名称],
  [h].[step_id] AS [步骤ID],
  [h].[step_name] AS [步骤名称],
  CONVERT(CHAR(10), CAST(STR([h].[run_date],8, 0) AS DATETIME), 121) AS [运行时间],
  STUFF(STUFF(RIGHT('000000' + CAST ( [h].[run_time] AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') 
    AS [RunTime],
  (([run_duration]/10000*3600 + ([run_duration]/100)%100*60 + [run_duration]%100 + 31 ) / 60) 
    AS [运行耗时],
  CASE [h].[run_status] 
    WHEN 0 THEN '失败'
    WHEN 1 THEN '成功' 
    WHEN 2 THEN '重试' 
    WHEN 3 THEN '取消' 
    WHEN 4 THEN '运行中' 
  END AS [作业状态],
  [h].[message] AS [作业信息]  
FROM [msdb].[dbo].[sysjobhistory] [h]
INNER JOIN [msdb].[dbo].[sysjobs] [j] 
ON [h].[job_id] = [j].[job_id]
WHERE   [step_id] = 0
ORDER BY [运行时间];

mssql sqlserver 安全检查—磁盘空间检查


摘要:
经常对sqlserver进行安全检查,可以避免sqlserver遭遇性能问题,可以避免sqlserver出现异常,
下文着重讲述sqlserver安全检查之”磁盘空间检查”,具体方法如下所示:



一、磁盘空间检查的目的

如果数据库所在的服务器磁盘空间不足,则会影响数据库的各项操作, 用户数据无法插入,无法进行查询操作
无法进行日志备份操作,sql脚本时常运行错误,因为无法创建临时表…



二、磁盘空间检查涉及事项

2.1 每个日志文件的大小
2.2 每个数据文件的大小
2.3 每个数据文件中都有可用空间
2.4 每个驱动器上有数据库文件的可用空间
2.5 每个驱动器上的可用空间都有备份文件
2.6 日志文件增长
2.7 预估数据和日志文件的增长率



三、磁盘空间检查方法

3.1 日志空间快照获取方法,
设置一个sql作业定时对服务器下的所有数据库的日志空间进行快照,获取相应的信息,具体脚本如下所示:

	   IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'sqlLog_diskSpace'))
 
BEGIN
	CREATE TABLE [dbo].sqlLog_diskSpace(
		[DatabaseName] [VARCHAR](250) NULL,
		[LogSizeMB] [DECIMAL](38, 0) NULL,
		[LogSpaceUsed] [DECIMAL](38, 0) NULL,
		[LogStatus] [TINYINT] NULL,
		[CaptureDate] [DATETIME2](7) default getdate()
	);

END
 
CREATE TABLE #tmp (
	DatabaseName VARCHAR(100),
	LogSizeMB DECIMAL(10,2),
	LogSpaceUsed DECIMAL(10,2),
	LogStatus VARCHAR(1)
	);
 
INSERT INTO #tmp EXEC('dbcc sqlperf(logspace)');
 
INSERT INTO sqlLog_diskSpace 
	(DatabaseName, LogSizeMB, LogSpaceUsed, LogStatus)
	SELECT DatabaseName, LogSizeMB, LogSpaceUsed, LogStatus
	FROM #tmp;
 
 go
 
DROP TABLE #tmp;
go
	

使用以上的作业快照信息,我们可以监控日志文件剩余空间,何时该备份日志文件,或调整日志文件的大小

mssql_sqlserver_日志空间快照捕捉

mssql_sqlserver_日志空间快照捕捉


——————————————————————————————–
3.2 获取各数据库文件的大小

  SELECT 
    [file_id] AS [文件编号],
    [type] AS [文件类型],
    substring([physical_name],1,1) AS [所属驱动器],
    [name] AS [逻辑文件名],
    [physical_name] AS [物理文件名],
    CAST([size] as DECIMAL(38,0))/128. AS [文件尺寸MB], 
    CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. AS [已用尺寸MB], 
    (CAST([size] AS DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) AS [空闲尺寸MB],
    [max_size] AS [最大尺寸],
    [is_percent_growth] AS [是否自动增长],
    [growth] AS [增长率],
    SYSDATETIME() AS [当前系统时间]
FROM sys.database_files;

对数据库文件大小的检查方法,每个月都必须检查数据文件的大小和使用空间,判断驱动器的空间是否符合系统要求,符合系统的增长情况,
也通过每个月的数据文件检查,判断数据文件增长是否合理,并在运维系统中对磁盘空间的增长设定相应的警报值。

mssql_sqlserver_数据文件快照

mssql_sqlserver_数据文件快照