Стань частью ИТ-сообщества

Вашему вниманию предлагается сценарий, который позволяет быстро создать набор полезных для обслуживания баз данных нового сервера уведомлений, заданий, конечных точек и некоторых настроек. Сценарий не претендует на полноту, и может использоваться в качестве дополнительного шаблона к подобным сценариям администраторов баз данных. В сценарии использованы фиктивные адреса электронной почты и имена серверов, которые нужно исправить на действующие в конкретной организации.

USE master
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE 
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'MS-SQL-Admins', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235900, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235900, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235900, 
		@pager_days=127, 
		@email_address=N'MS-SQL-Admins@domain.ru', 
		@pager_address=N'MS-SQL-Admins@domain.ru', 
		@category_name=N'[Uncategorized]', 
		@netsend_address=N'servermonitoringa'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Access denied', 
		@message_id=10011, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Access denied', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Access is denied due to a password failure', 
		@message_id=3279, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Access is denied due to a password failure', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Login fails', 
		@message_id=4060, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Login fails', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Permission Denied', 
		@message_id=229, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Permission Denied', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Server shut down', 
		@message_id=6006, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1,
		@database_name=N'', 
		@notification_message=N'Остановлена служба', 
		@event_description_keyword=N'Server shut down', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Server shut down', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Переполнен журнал транзакций', 
		@message_id=9002, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=5, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Переполнен журнал транзакций', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 19 Errors', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 19 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 20 Errors', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 20 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 21 Errors', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 21 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 22 Errors', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 22 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 23 Errors', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 23 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 24 Errors', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 24 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 25 Errors', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N' ', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev. 25 Errors', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'tempdb: full used space', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=5, 
		@category_name=N'[Uncategorized]', 
		@performance_condition=N'SQLServer:Databases|Log File(s) Used Size (KB)|tempdb|>|9000000', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'tempdb: full used space', 
		@operator_name=N'MS-SQL-Admins', 
		@notification_method = 1;
GO

USE master
GO

-- Компонент Database Mail
EXECUTE sp_configure 'show advanced options',1
RECONFIGURE
EXECUTE sp_configure 'Database Mail XPs',1
RECONFIGURE
EXEC sp_configure 'default trace enabled', 0
RECONFIGURE
EXECUTE sp_configure 'show advanced options',0
RECONFIGURE
GO
DECLARE @email_address nvarchar(50)
SELECT	@email_address = @@servername + '-MSSQL@domain.ru'
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Administrator',
    @description = 'Mail account for administrative e-mail.',
	@replyto_address = 'MS-SQL-Admins@domain.ru',
    @email_address = @email_address,
    @display_name = @email_address,
    @mailserver_name = 'SMTP.domain.ru',
	@mailserver_type = 'SMTP',
	@port = 25,
	@use_default_credentials = 0;
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'AlertProfile',
       @description = 'Profile used for administrative mail.' ;
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AlertProfile',
    @account_name = 'Administrator',
    @sequence_number = 1 ;
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'AlertProfile',
    @is_default = 1 ;
GO


USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'UseDatabaseMail'
, N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, N'DatabaseMailProfile'
, N'REG_SZ'
, N'AlertProfile'
GO

EXECUTE msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
--EXECUTE sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = '.......';
EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';
EXECUTE msdb.dbo.sysmail_help_account_sp;
EXECUTE msdb.dbo.sysmail_help_profile_sp;
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'AlertProfile'
EXECUTE msdb.dbo.sysmail_help_principalprofile_sp;
SELECT * FROM msdb.dbo.sysmail_event_log
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_faileditems
GO

USE master
GO

CREATE ENDPOINT SBEndpoint
  STATE = STARTED
  AS TCP 
    (
       LISTENER_IP = ALL, 
       LISTENER_PORT = 9669
    )
  FOR SERVICE_BROKER 
    (
       AUTHENTICATION = WINDOWS,
       MESSAGE_FORWARDING = DISABLED
    )
GO
CREATE ENDPOINT MirroringEndpoint
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022, LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING (
       AUTHENTICATION = WINDOWS KERBEROS,
       ENCRYPTION = SUPPORTED,
       ROLE=ALL);
GO


-- Открытие нового файла журнала ошибок SQL Server

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
	N'Software\Microsoft\MSSQLServer\MSSQLServer', 
	N'NumErrorLogs', 
	REG_DWORD, 21
GO
USE [msdb]
GO
DECLARE @jobId BINARY(16), @ReturnCode int
EXEC	@ReturnCode =  msdb.dbo.sp_add_job 
		@job_name=N'Открытие нового файла журнала ошибок SQL Server', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'--USE msdb ;
--GO
--EXEC dbo.sp_cycle_agent_errorlog ;
--GO
--EXEC sp_cycle_errorlog ;
--GO', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC	@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, 
		@step_name=N'sp_cycle_errorlog', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC sp_cycle_errorlog', 
		@database_name=N'master', 
		@flags=0
EXEC	@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, 
		@start_step_id = 1
EXEC	@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, 
		@name=N'sp_cycle_errorlog', 
		@enabled=1, 
		@freq_type=32, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=1, 
		@freq_recurrence_factor=1, 
		@active_start_date=20080422, 
		@active_end_date=99991231, 
		@active_start_time=235900, 
		@active_end_time=235959
EXEC	@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, 
		@server_name = N'(local)'
GO

--

EXEC master..xp_regwrite 
     @rootkey='HKEY_LOCAL_MACHINE', 
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer', 
     @value_name='BackupDirectory', 
     @type='REG_SZ', 
     @value='D:\MSSQL\BACKUP' -- Тут нужно указать правильный путь к папке, куда решили класть копии


-- Шаблон задания для автоматического создания недостающих индексов

USE [msdb]
GO

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories 
WHERE name=N'[Обслуживание индексов]' AND category_class=1)
BEGIN
EXEC	msdb.dbo.sp_add_category @class=N'JOB', 
		@type=N'LOCAL', 
		@name=N'[Обслуживание индексов]'
END

DECLARE @jobId BINARY(16)
EXEC	msdb.dbo.sp_add_job @job_name=N'Создание недостающих индексов', 
		@enabled=0, 
		@notify_level_eventlog=3, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Обслуживание индексов]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'MS-SQL-Admins', 
		@job_id = @jobId OUTPUT
EXEC	msdb.dbo.sp_add_jobstep @job_id=@jobId, 
		@step_name=N'1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @sql nvarchar(1024)  
SELECT TOP 1 @sql = ''CREATE INDEX [IX_'' + OBJECT_NAME(mid.object_id) + ''_'' + 
CAST(mid.index_handle AS nvarchar) + ''] ON '' + 
mid.statement + '' ('' +
   CASE
      WHEN mid.equality_columns IS NOT NULL and mid.inequality_columns IS NOT NULL
      THEN mid.equality_columns +'',''+ mid.inequality_columns
      WHEN mid.equality_columns IS NULL and mid.inequality_columns IS NOT NULL  
      THEN mid.inequality_columns
      WHEN mid.equality_columns IS NOT NULL and mid.inequality_columns IS NULL
      THEN mid.equality_columns
   END
+ '') ''+
   CASE
      WHEN mid.included_columns IS NOT NULL 
      THEN ''INCLUDE ('' + mid.included_columns+'')''
      ELSE ''''
   END
FROM	sys.dm_db_missing_index_groups mig
JOIN	sys.dm_db_missing_index_group_stats migs 
ON	migs.group_handle = mig.index_group_handle
JOIN	sys.dm_db_missing_index_details mid 
ON	mig.index_handle = mid.index_handle
AND	mid.database_id = DB_ID()
ORDER BY	migs.user_seeks DESC
IF @sql IS NOT NULL
EXECUTE sp_executesql @sql
PRINT @sql', 
		@database_name=N'tempdb', 
		@flags=4
EXEC	msdb.dbo.sp_update_job @job_id = @jobId, 
		@start_step_id = 1
EXEC	msdb.dbo.sp_add_jobschedule @job_id=@jobId, 
		@name=N'1', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20081107, 
		@active_end_date=99991231, 
		@active_start_time=80000, 
		@active_end_time=235959
EXEC	msdb.dbo.sp_add_jobserver @job_id = @jobId, 
		@server_name = N'(local)'
--- END