January 2009

You are currently browsing the monthly archive for January 2009.

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


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'Alexander.Gladchenko@kaspersky.com', @pager_address=N'79647864473@sms.beemail.ru', @category_name=N'[Uncategorized]', @netsend_address=N'servermonitoringa' GO EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=N'MS-SQL-Admins', @notificationmethod=1 GO EXEC msdb.dbo.sp_add_alert @name=N'Access denied', @message_id=10011, @severity=0, @enabled=1, @delay_between_responses=36000, @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=36000, @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=36000, @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=36000, @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'Login fails 18456', @message_id=18456, @severity=0, @enabled=1, @delay_between_responses=36000, @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 18456', @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=36000, @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=36000, @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=36000, @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=36000, @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=36000, @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=36000, @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=36000, @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=36000, @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=36000, @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=600, @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@kaspersky.com' EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Administrator', @description = 'Mail account for administrative e-mail.', @replyto_address = 'Alexander.Gladchenko@kaspersky.com', @email_address = @email_address, @display_name = @email_address, @mailserver_name = 'mail.avp.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=3, @notify_level_email=2, @notify_level_netsend=0, @notify_level_page=0, @notify_email_operator_name=N'MS-SQL-Admins', @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 -- Шаблон задания для автоматического создания недостающих индексов 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)' -- Переназначение пути по умолчанию для папки резервных копий 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' -- Тут нужно указать правильный путь к папке, куда решили класть копии GO --http://sqlcat.com/top10lists/archive/2008/11/24/top-10-sql-server-2008-features-for-isv-applications-burzin.aspx EXEC sp_configure 'show advanced options',1 RECONFIGURE GO EXEC sp_configure 'optimize for ad hoc workloads',1 RECONFIGURE GO EXEC sp_configure 'show advanced options',0 RECONFIGURE GO --- END


Под общей редакцией А. Гладченко и В. Щербинина.

Москва. ЭКОМ Паблишерз, 2008Г. 288 страниц. ISBN: 978-5-9790-0086-2. Книга уже в продаже.

Эта книга – сборник статей, которые посвящены ключевым для понимания репликации SQL Server моментам. Кроме переводов наиболее интересных зарубежных авторов, являющимисяхся признанными во всём мире специалистами, в книге вы найдёте ряд статей участников сообщества SQL.RU, которые многие годы помогаю решать разнообразные проблемы на технических форумах этого замечательного интернет – ресурса. Если во время прочтения книги у вас возникнут сомнения или вопросы по поводу изложенных тут материалов, смело обращайтесь к авторам статей на форумах SQL.RU, они с удовольствием вам помогут.
В сборник включены переводы и статьи следующих участников сообщества SQL.RU: Дмитрий Артёмов, Александр Волок, Александр Гладченко, Ильдар Даутов, Григорий Кoрнилов, Алексей Ковалёв, Наталья Кривонос, Ян Либерман, Ирина Наумова и Владислав Щербинин. Причём, на момент написания книги, Александр Гладченко, Ян Либерман и Ирина Наумова являлись SQL Server MVP.
Книга в первую очередь ориентирована на администраторов баз данных, которые собираются углубить свои познания в репликации SQL Server. Назначение этой книги состоит в том, чтобы предоставить читателям набор апробированных в течение нескольких лет рецептов по использованию и настройке репликации в SQL Server.

ЗАКАЗАТЬ


Обсудить книгу можно тут: http://www.sql.ru/forum/actualthread.aspx?tid=643700


Пример статьи из сборника:


Основы репликации SQL Server 2008


Автор Александр Юрьевич Гладченко


Репликация – это одна из разновидностей систем, поддерживающих распределенную обработку данных. В последнее десятилетие направление распределенной обработки данных бурно развивалось, и на сегодняшний день это одно из наиболее динамично растущих направлений. В докладе об изменении профиля корпоративных данных, который был озвучен на саммите APAC, посвященном хранилищам данных и состоявшемся в 2007г. в Хошимине, Рик Вилларс (Rick Villars, Head of Investment Technical Services HSBC) показал, что объем тиражируемых данных увеличивается ежегодно на 43.9% и к 2009 г. сравняется с объемом традиционных данных, размещаемых в хранилищах. Назначение этой книги состоит в том, чтобы предоставить читателям набор апробированных в течение нескольких лет рецептов по использованию и настройке репликации в SQL Server.


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


Назначение репликации состоит в управляемом тиражировании данных между несколькими СУБД. Задача эта не нова, и поэтому в репликации используется давно всем известная метафора издательского дела: издатель, публикация, статья, распространитель и подписчик. Для SQL Server характерно также наличие специализированных программных модулей, с помощью которых организуется взаимодействие задействованных в репликации баз данных, и которые в документации принято называть агентами репликации.


Вот определение репликации, которое было представлено в учебном пособии Майкрософт: “Репликация – это процесс автоматического распределения копий данных и объектов БД между экземплярами SQL Server с одновременной синхронизацией всей распространяемой информации”. Известный теоретик реляционной теории баз данных Крис Дейт дал репликации такое определение: “Система поддерживает репликацию данных, если данная таблица или ее фрагмент может быть представлена несколькими отдельными копиями или репликами, которые хранятся на нескольких отдельных узлах”. Однако, ни одно из приведенных тут определений мне не кажется полным. В профессиональных интернет-форумах очень часто можно встретить вопрос, как организовать репликацию без прямого подключения серверов друг к другу, например, посредством электронной почты? Мне видится неверной даже сама постановка вопроса таким образом. Дело в том, что нужно четко отделять репликацию от простого тиражирования данных. По моему мнению, распределенная система может называться системой с репликацией, если она не только автоматически синхронизирует данные по заданным правилам, но и умеет гарантировать их синхронность. Например, если для доставки изменений в данных используется протокол без какой-либо гарантии доставки, такую систему уже нельзя назвать репликацией, хотя это еще не означает, что данные в такой системе будут не синхронны. С другой стороны, репликация также является очень эффективным способом тиражирования данных, но она не является единственной стратегией тиражирования данных. Давайте кратко рассмотрим несколько стратегий тиражирования данных, которые также доступны на платформе Майкрософт.


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


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


Службы интеграции данных в составе SQL Server также предоставляют богатый набор средств тиражирования информации. С помощью пакетов Data Transformation Services (DTS) можно не только импортироватьэкспортировать схему и данные между узлами с SQL Server или любых самая разнообразная трансформация передаваемых между узлами данных. Пакеты DTS могут передаваться также как и резервные копии и даже использоваться для передачи изменений в стандартной репликации. Бурное развитие мобильных устройств привело к появлению, например, такой новой технологии синхронизации данных как Microsoft Synchronization Services для ADO.NET. Эта новая служба позволяет синхронизировать данные из разных источников, используя двухуровневую или многоуровневую архитектуру взаимодействия специализированных служб. Вместо копирования данных и схемы базы, прикладной программный интерфейс службы синхронизации предоставляет в распоряжение разработчика набор компонентов, с помощью которых можно синхронизировать данные между службами предоставления данных и локальным хранилищем данных пользователя. Такая архитектура ориентирована в первую очередь на тех мобильных клиентов, которые не имеют гарантированного и надежного подключения по сети к центральному серверу, и которые могут работать какое-то время с локальной копией данных автономно. В данном случае причиной тиражирования данных является необходимость временного кэширования информации на устройстве клиента-подписчика.


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


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


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



1. Локальная независимость.
2. Отсутствие опоры на центральный узел.
3. Непрерывное функционирование.
4. Независимость от расположения.
5. Независимость от секционирования.
6. Независимость от репликации.
7. Обработка распределенных запросов.
8. Управление распределенными транзакциями.
9. Аппаратная независимость.
10. Независимость от операционной системы.
11. Независимость от сети.
12. Независимость от типа СУБД.




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


Локальная независимость означает, что все узлы распределенной системы должны быть независимы друг от друга и легко переводиться в автономный режим работы. Кроме того, все операции в рамках узла должны быть подконтрольны этому узлу и не зависеть от состояния или иным образом от других узлов распределенной системы. При этом локальные данные должны храниться и обслуживаться на своем узле в локальной базе данных, подчиняться действующим на этом узле правилам и ограничениям, быть доступными в рамках политики безопасности, действующей на локальном узле и в локальной базе данных, иметь локальное управление, аудит и администрирование. При обращении к данным из других узлов распределенной системы, данные должны сохранять свою локальную принадлежность. На практике, во многих современных СУБД, каждая используемая в распределенных системах база данных, управляется отдельно и независимо от других баз данных, как будто бы каждая такая база никак не связана через сеть с другими базами данных. Хотя каждая база данных может работать с другими, они представляют собой отличные, отдельные системы, каждая из которых требует индивидуального подхода.


Поскольку данные реплицируются в место локальной обработки, то сбои в работе сети и удаленных систем оказывают незначительное влияние на пользователя. Данные могут обрабатываться локально, пока связь с источником данных не будет восстановлена.


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


Дейт считает, что отсутствие опоры на центральный узел, в общем случае означает, что локальный узел не должен зависеть от служб, работающих на другом, возможно едином для всех узлов распределенной системы, “центральном” или “главном” узле. Здесь не имеются в виду службы, отвечающие за тиражирование данных, не должно быть служб централизованной обработки запросов или управления транзакциями, как и любых других служб, из-за которых система в целом может стать зависимой от центрального узла. В идеальном случае, каждый узел может быть самодостаточен, а распределенная система реализует схему одноранговой репликации с совмещением на каждом узле ролей издателя и подписчика. Однако, это не исключает возможности построения схем с централизованным тиражирование или накоплением данных. Часто требуется как раз централизованная схема управления распределенной системой, а опора на центральный узел задается как одно из бизнес-требований. К таким системам можно отнести хранилища баз данных, которые пополняются информацией из множества источников распределенной сети узлов, или наоборот, когда одна и та же информация тиражируется по узлам филиальной сети. Часто, единую точку входа создают в целях балансирования нагрузки на систему распределенных узлов, что тоже не укладывается в обозначенную Дейтом цель – отсутствие опоры на центральный узел. Отсутствие опоры на центральный узел важно в тех случаях, когда все узлы должны быть равноправны и потеря любого не должна приводить к потере работоспособности остальных. Обычно, репликация выполняется отдельными от ядра СУБД модулями, специально предназначенным для репликации данных.


В своей книге Дейт под непрерывным функционированием понимает такие показатели СУБД как надежность и доступность. Часто, репликация используется не только для приближения данных к потребителю (повышение доступности), но и для повышения надежности хранения информации (за счет ее тиражирования). Под надежностью, в частности, понимается высокая степень вероятности того, что система будет работать в любой момент времени. Для распределенных систем с репликацией характерно сохранение работоспособности даже в случаях отказов части их компонентов, таких как отдельный узел. Под доступностью понимается вероятность непрерывного функционирования системы в течение заданного времени. Поскольку репликация позволяет дублировать данные, можно незаметно переключать пользователей между узлами, повышая тем самым доступность распределенных систем за счет дублирования.


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


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


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


Система, по словам Дейта, независима от секционирования, если секционирование (т.е. физическое разделение таблиц на части или фрагменты) выполняется прозрачно для конечного пользователя, т.е. логика работы должна быть такой же, как и с несекционированными данными. Секционирование используется для повышения производительности извлечения данных за счет сокращения объема оперируемых данных. Также, с помощью распределенного секционирования данные могут физически храниться в том месте, где они чаще всего используются. Это позволяет локализовать большие запросы и сократить объем внешнего сетевого трафика. Применение распределенного секционирования позволяет добиться совместного использования удаленными узлами только тех данных распределенной системы, в которых они нуждаются, и не более. Независимость от секционирования – это гарантия того, что секции данных в любой момент могут быть заново собраны в одну таблицу, причем это не повлияет на работоспособность и правильность отображения данных в приложениях пользователя.


Независимость от репликации означает, что репликация должна быть “прозрачной для пользователя”, т.е. логика работы с данными должна быть такой же, как и без репликации. Иными словами, таблицы или секции таблиц базы данных, с которыми работает пользователь, могут включаться в репликацию без переделки приложений и, зачастую, незаметно для пользователя. Эта цель не так проста, как кажется на первый взгляд, поскольку “прозрачность” репликации зависит не только от реализации репликации производителем СУБД, а также от того, как была реализована топология и схема репликации объектов базы данных разработчиком приложения или администратором баз данных. Кроме того, отвечающие за репликацию компоненты могут влиять на работу узла и косвенно влиять на операции пользователей, что тоже усложняет решение поставленной цели. Непросто обеспечить независимость от репликации в условиях гетерогенных узлов.


Распределенные системы должны быть реляционными, т.к. такие системы позволяют оптимизировать обработку запросов. По производительности распределенная реляционная система может существенно превосходить не реляционную систему.


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


Дейт подчеркивает два основных аспекта управления транзакциями: управление восстановлением и управление параллельностью работы. В репликации оба этих аспекта получают дополнительный смысл и значение. Когда мы имеем дело с распределенной системой, в рамках одной транзакции могут быть выполнены DML или DDL-операции с объектами на нескольких узлах, причем, распределенная система должна уметь управлять ходом исполнения всех таких операций и не допускать блокирование одних и тех же ресурсов при исполнении операций в рамках распределенной транзакции. Точно также, репликация должна гарантировать атомарность распределенных транзакций и предоставлять возможность отката таких транзакций, как это предусмотрено протоколом двухфазной фиксации.


Управление параллельностью у распределенных систем на основе SQL Server основано на механизмах блокировок. В случае гетерогенных узлов, система обеспечивающих распараллеливание запросов блокировок должна быть транспарентной.


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


Под аппаратной независимостью подразумевается возможность запуска одной и той же СУБД на разных аппаратных платформах. Желательно, чтобы работающие на разных платформах узлы в рамках распределенной системы с репликацией были равноправны.


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


Под независимостью от типа СУБД подразумевается, что системы управления базами данных, которые могут работать на разных платформах и под управлением разных операционных систем, при включении в распределенную систему смогут использовать для организации репликации данных один и тот же интерфейс.


Выбор типа репликации


Одними из основных факторов при выборе типа репликации для тиражирования данных является степень автономности подписчиков и величина допустимой задержки передачи изменений от породившего их узла на все участвующие в репликации узлы. В SQL Server существует три разновидности репликации: репликация моментальных снимков, репликация транзакций и репликация слиянием. У всех этих типов есть подтипы, со своими характеристиками автономности и задержки. Под автономностью принято понимать возможность использования базы данных без обеспечивающих репликацию механизмов, и без подключения к другим системам баз данных. Например, база данных на портативном компьютере может использоваться и тогда, когда компьютер не подключен к сети. Задержка же определяется периодом времени, в течение которого расположенные на разных узлах копии данных могут быть не идентичны из-за незавершенности процесса тиражирования изменений. Это могут быть секунды, минуты, часы, дни и даже месяцы, максимальное время возможной задержки определяется требованиями бизнес-правил.


Кроме репликации, у SQL Server есть еще несколько технологий, пригодных для тиражирования данных, для которых тоже применимы характеристики автономности и задержки. К таким технологиям можно отнести: распределенные запросы, технику резервирования с последующим восстановлением на другом узле, доставку журналов, зеркальное отражение, копирование DTS-пакетов средствами службы Integration Services, экспорт/импорт через BCP, а также новую технологию в Visual Studio 2008 – службы синхронизации для ADO.NET. Многие из этих технологий применяются на разных стадиях репликации.


С точки зрения автономности, многие технологии близки по показателям и легко объединяются в группы. Так, например, все способы передачи данных посредством файлов импорта-экспорта показывают хорошую автономность, поскольку в перерывах между передачами и загрузкой-выгрузкой данные доступны для локального использования. Однако, при передаче больших объемов данных посредством транспортных файлов может сильно возрасти время, затрачиваемое на их транспортировку и загрузку-выгрузку. При этом задержка тиражирования может быть весьма значительной. В репликации аналогичные показатели автономности наблюдаются у двух типов репликации. Во-первых, это репликация слиянием, которая имеет в своей основе триггерный механизм, не использует распределенных транзакций и нуждается в метаданных других узлов только во время сеансов обмена изменениями с издателем. Все это делает участвующие в репликации узлы автономными и степень автономности высока. Этот тип репликации отличается небольшими значениями задержки тиражирования. Кроме того, типы репликации, допускающие передачу изменений с подписчика на издателя (а репликация слиянием является по своей природе двунаправленной с точки зрения передачи таких изменений) имеют специальные параметры запуска своих агентов репликации, позволяющие регулировать число изменений в рамках одного сеанса. Таким образом, можно дифференцированно влиять на задержку тиражирования изменений, передаваемых с издателя каждому подписчику.


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


Второй из наиболее автономных типов репликации – это репликация моментальных снимков. В чистом виде, репликации моментальных снимков использует для тиражирования данных их моментальные снимки, после чего распространитель тиражирует эти снимки (набор файлов или архивный файл) подписчикам, которые потом их самостоятельно применяют в своих базах данных. Создание и тиражирование моментальных снимков может осуществляться на периодической основе или по требованию. Поскольку в самом простом случае каждый подписчик получает одни и те же данные и не может вносить в них изменения, конфликты исключены. Получается, что каждый подписчик имеет свою копию публикации издателя, чем обеспечивается очень высокая автономность, а возможность тиражирования изменений данных на стороне подписчика не предусмотрена вовсе. Время передачи снимка от издателя подписчикам определяется размерами транспортных файлов, характеристиками канала передачи данных и возможностями серверного комплекса и может быть довольно большим, обуславливая высокую задержку тиражирования. Т.к. в репликации моментальных снимков используется передача всех реплицируемых объектов и данных, этот тип репликации, наряду с восстановлением из резервной копии, используется во всех других типах репликации для первоначальной синхронизации данных. Существуют разновидности репликации моментальных снимков, которые предусматривают возможность обновления реплицируемых данных на подписчике. Например, в SQL Server 2000 такое обновление осуществлялось посредством распределенной транзакции непосредственно в базе данных издателя или через механизм обслуживания очередей. Нужно понимать, что использование распределенных транзакций или очередей влияет на автономность базы данных подписчика и, в случае интенсивного и постоянного использования этих механизмов, может привести к потере автономности подписчика.


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


Агенты репликации


Одну из ведущих ролей в репликации SQL Server играют агенты, которые являются обычными программами, реализованными в виде исполняемых модулей. Наиболее важную роль в репликации играют четыре агента репликации, это программы: distrib.exe – Replication Distribution Agent (агент распространителя); snapshot.exe – Replication Snapshot Agent (агент моментальных снимков); replmerg.exe – Replication Merge Agent (агент слияния); и logread.exe – Replication Log Reader Agent (агент чтения журнала транзакций в репликации транзакций).


Для SQL Server 2000 все эти четыре программы можно найти в каталоге по умолчанию: “C:\Program Files\Microsoft SQL Server\80\COM”. Для SQL Server 2005/2008 путь к программам отличается только папкой версии, вместо “80” будет “90” или “100”. Для того чтобы посмотреть параметры вызова этих программ, необходимо запустить соответствующие исполняемые файлы с ключом “/?”. На экране будет представлен синтаксис их запуска и перечень возможных ключей. Эта информация подробно изложена в документации, поставляемой с сервером баз данных.


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


В табл. 1 представлены параметры вызова агентов репликации SQL Server 2000, с помощью которых можно регулировать порождаемую репликацией нагрузку на серверы, а также ограничивать трафик репликации между серверами.


Табл. 1. Управление нагрузкой и трафиком


BcpBatchSize MaxBcpThreads ReadBatchThreshold
Buffers MaxCmdsInTran SrcThreads
CommitBatchSize MaxDeliveredTransactions StartQueueTimeout
CommitBatchThreshold MaxDownloadChanges UploadReadChangesPerBatch
DestThreads MaxUploadChanges UploadReadChangesPerBatch
DownloadGenerationsPerBatch PacketSize UploadWriteChangesPerBatch
DownloadReadChangesPerBatch PollingInterval UseInprocLoader
DownloadWriteChangesPerBatch ReadBatchSize  


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


Параметры запуска агента распространителя можно изменить, добавив к стандартному набору параметров дополнительный, например, с именем UseInprocLoader и присвоив ему значение “1”. Для справки замечу, что это повышает эффективность использования первоначального снимка, предписывая агенту распространителя использовать команду BULK INSERT при применении файлов снимка на подписчике.


Профиль агентов репликации может быть задан, переопределен или создан с помощью специализированного мастера, в SQL Server 2000 он носил название “Agent Profiles for”. Мастер использует в своей работе системные таблицы в базе данных msdb. В таблице MSagent_profiles храниться информация о существующих профилях агентов репликации. Посмотреть эту информацию можно с помощью следующего запроса:

    SELECT [profile_id] ,[profile_name] ,CASE [agent_type] WHEN 1 THEN ‘агент моментальных снимков′ WHEN 2 THEN ‘агент чтения журнала’ WHEN 3 THEN ‘агент распространителя’ WHEN 4 THEN ‘агент слияния’ WHEN 9 THEN ‘агент чтения очереди’ ELSE END ,[type] ,ISNULL ([description], ) AS description ,[def_profile] FROM [msdb].[dbo].[MSagent_profiles] ORDER BY [profile_id]

    
    


Для каждого из типов репликации существует заранее предопределенный набор стандартных системных профилей. Воспользовавшись выбором соответствующего режима, можно применить указанный профиль ко всем агентам репликации, зарегистрированным на сервере. Каждый профиль имеет заданный набор параметров, которые в электронной документации к SQL Server принято называть аргументами. По умолчанию, набор параметров такой, как это определено в таблице MSagent_parameters. Однако, параметров может быть больше, чем определено и полный список возможных аргументов для каждого из типов агентов можно увидеть в таблице MSagentparameterlist.


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


Работой агентов репликации управляет служба SQL Server Agent. Это не только позволяет обеспечить простоту запуска агентов репликации по расписанию, практически, в стандартной реализации все агенты репликации работают как подсистемы службы SQL Server Agent. Однако, вы можете начать сеанс репликации, запустив соответствующего агента репликации из командной строки, из прикладной программы, из командлета PowerShell или, например, из командного файла, вызываемого на исполнение планировщиком операционной системы. В SQL Server 2000 были реализованы компоненты для контроля работы агентов репликации из пользовательских приложений посредством Microsoft ActiveX controls. Соответствие библиотек программам агентов репликации показано в табл. 2.


Табл. 2. Библиотеки Microsoft ActiveX controls



Имя агента Исполняемый файл Элемент управления ActiveX
Агент моментальных снимков snapshot.exe sqlinitx.dll
Агент чтения журнала logread.exe Нет
Агент чтения очередей qrdrsvc.exe Нет
Агент распространителя distrib.exe sqldistx.dll
Агент слияния replmerg.exe Sqlmergx.dll


В SQL Server 2005 был сделан следующий шаг по улучшению возможностей управления работой агентов репликации из приложений и встраивания репликации в приложения третьих фирм. Одним из новшеств репликации в этой версии стал набор объектов управления репликацией, который называется: “Replication Management Objects” (RMO), и представляет собой управляемый код, предоставляющий доступ к функциональности агентов репликации. Все задачи репликации, которые доступны в программе SQL Server Management Studio, могут выполняться программно с помощью RMO.


У репликации и ее агентов есть свои собственные счетчики производительности, которые можно найти в оснастке системного монитора. Например, число запущенных агентов репликации показывает счетчик: “Microsoft SQL Server: Replication Agents”.


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


Replication Snapshot Agent


Программа агента моментальных снимков (Replication Snapshot Agent) на основе заданной параметрами запуска или профилем конфигурации создает файлы моментального снимка. Моментальный снимок содержит схему данных и сами данные изданных таблиц и объектов базы данных. Файлы сохраняются в папке моментальных снимков, а запуск агента выполняется по расписанию соответствующим заданием на сервере, где расположена база данных распространителя. Кроме того, агент моментальных снимков фиксирует в базе данных распространителя информацию о состоянии синхронизации. Агент моментальных снимков не предназначен для распространения созданных им снимков. При репликации снимками этот агент запускается настолько часто, насколько необходимо обеспечить приемлемую периодичность обеспечения синхронности подписанных баз данных. Для репликации транзакций или репликации слиянием моментальные снимки необходимы реже, вплоть до того, что они могут создаваться только тогда, когда возникнет необходимость подключения нового подписчика.


После того, как запущенный на распространителе агент моментальных снимков устанавливает подключение к издателю (по умолчанию, это может быть тот же самый сервер), он устанавливает совмещаемую (shared lock) блокировку на всех таблицах, включенных в публикацию. Это ему нужно для копирования схемы данных и гарантии непротиворечивости снимка. Поскольку такой уровень блокировки мешает другим пользователям вносить изменения в таблицы, создание снимка лучше планировать на время минимальной активности. Сценарии схемы таблиц, представлений и хранимых процедур сохраняются в файлах с расширением “sch”. Сценарии создания индексов сохраняются в файлах с расширением “idx”. Сценарии создания триггеров будут в файлах с расширением “trg”. Ограничения для поддержки декларативной ссылочной целостности будут находиться в файлах с расширением “dri”. Данные будут находиться в файлах с расширением “bcp”. Агент моментальных снимков получает всю необходимую информацию в базах данных издателя, выгружая данные из публикуемых таблице в файлы моментального снимка. Эти файлы представляют собой сценарии для создания всех необходимых объектов и синхронизации данных, сохраняя моментальный снимок данных на момент времени создания снимка. Снимок создается для тех статей, которые входят в публикацию, и все это регистрируется в системных таблицах базы данных распространителя: MSrepl_commands и MSrepl_transactions. В таблице MSrepl_commands хранятся указатели на места размещения снимков статей, а также ссылки на предваряющие синхронизацию сценарии, если таковые имеются. В таблице MSrepl_transactions хранятся ссылки на соответствующие задачи синхронизации подписчика. После того как записи в этих двух таблицах будут сделаны, совмещаемая блокировка с издаваемых таблиц снимается.


В SQL Server 2000 мастер создания публикации “Create Publication Wizard” по умолчанию не включает опцию параллельной работы со снимком, которая позволяет смягчить воздействие, вызванное наложением совмещаемой блокировки на издаваемые таблицы. В свойствах публикации, на вкладке Snapshot можно включить опцию “Do not lock tables during snapshot generation”, которая позволяет пользователям публикуемой базы данных продолжать работу и во время создания моментального снимка. Не рекомендуется включать эту опцию, если публикуемая таблица имеет уникальный индекс, который не является первичным ключом или кластерным индексом. Кроме того, включение этой опции может существенно увеличить время создания снимка. Для создания снимков данных используется программа массового копирования BCP. В профиле агента создания снимков и в числе аргументов вызова программы агента моментальных снимков есть параметры, которые позволяют оптимизировать работу BCP. В частности, речь идет о значение аргумента BcpBatchSize, который позволяет разбивать поток экспорта/импорта данных на блоки. Этот аргумент задает число строк, используемых в операциях массового копирования. При выполнении операций “BCP IN”, указываемый размер блока – это число строк, которые посылаются серверу как одна транзакция, а также число строк, которые должны быть посланы, чтобы агент распространителя зарегистрировал в своем журнале очередной шаг в последовательности операций BCP. При выполнении операций “BCP OUT” используется установленный по умолчанию размер пакета, равный 1000. Значение 0 соответствует отсутствию регистрации шагов BCP. Если вы сомневаетесь, какой размер блока следует выбрать для создания моментального снимка вашей публикации, воспользуйтесь значением, предлагаемом по умолчанию в системном профиле агента моментальных снимков. Для этого агента в SQL Server 2000 предлагался только один профиль, на основании которого можно строить свои, пользовательские профили для всех публикаций или для каждой публикации в отдельности.


Табл. 3. Предопределенный в SQL Server 2000 профиль для агента моментальных снимков



Параметр Значение по умолчанию
BcpBatchSize 100000
HistoryVerboseLevel 2
LoginTimeOut 15
MaxBcpThreads 1
QueryTimeOut 300


Заметное влияние на время создания снимка оказывает аргумент MaxBcpThreads. Он определяет число потоков операций массового копирования, которые могут быть выполнены параллельно. Максимальное число потоков и подключений, которые существуют одновременно, будет не больше, чем значение MaxBcpThreads или число запросов на массовое копирование, которые окажутся при синхронизации транзакций в базе данных распространителя (для агента слиянием они будут в системной таблице sysmergeschemachange базы данных издателя). Аргумент MaxBcpThreads должен быть больше нуля и не имеет верхнего предела. Значение по умолчанию равно -1. При применении снимка, который был создан издателем, использующим опцию параллельного создания снимка, будет использоваться один поток, независимо от заданного для аргумента MaxBcpThreads значения. Практика показывает, что даже для однопроцессорных систем есть смысл устанавливать значение аргумента MaxBcpThreads больше единицы. На одном процессоре я предпочитаю задавать этому аргументу значение, равное 3.


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


При каждом запуске агента моментальных снимков выполняется проверка на появление новых подписок. Если за время, прошедшее с последнего создания снимка, не появилось новых подписок, файлы сценариев или снимков данных не создаются. Однако, если была задана команда немедленного создания моментального снимка, агент моментальных снимков создаст новые файлы схемы и снимков данных.


Для облегчения работы с уже готовыми моментальными снимками рекомендуется использовать опцию сжатия снимка в CAB-файл. Для гарантии доставки файла снимка лучше снимки выкладывать в каталог сервера FTP. Однако, сжатие файлов снимков требует дополнительного расхода ресурсов системы при создании и применении файла моментального снимка и увеличению времени генерации и применения снимка.


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


В случае длительного прерывания сеансов репликации, может наступить момент, когда выгодней не дожидаться синхронизации всех накопленных изменений, а применить свежий моментальный снимок. При большом размере публикации это может оказаться весьма продолжительной и дорогостоящей операцией. Чтобы избежать такой ситуации, желательно настроить рассылку оповещений обо всех критичных событиях в системе репликации. Желательно также создать “оператора последней надежды” (fail-safe operator), чтобы гарантировать доставку таких уведомлений.


Применением моментального снимка на подписчике занимается агент распространителя. Для этого он вначале устанавливает подключение к серверу, где располагается база данных распространителя, чтобы получить в таблицах MSrepl_commands и MSrepl_transactions информацию о том, какие снимки необходимо применить данному подписчику. Там же он получает сведения о расположении папки моментального снимка. Следующим шагом он применяет в базе данных подписчика сценарии создания/изменения схемы, которые создают все необходимые объекты. При необходимости осуществляется конвертация типов данных, которая бывает нужна для подписчиков других версий или СУБД. Когда схема готова, осуществляется операция массовой вставки данных. После того как все статьи станут синхронны с публикацией, и для основных таблиц будет обеспечена транзакционная и ссылочная целостность, в подписанной базе данных создаются системные объекты репликации, а также триггеры, процедуры или представления. Все эти шаги регистрируются в базе данных распространителя.


Для мониторинга производительности работы агентов моментальных снимков можно использовать счетчики производительности объекта “SQL Server: Replication Snapshot.”, которые показывают сколько команд или транзакций было передано распространителю. Счетчик: “Snapshot: Delivered Cmds/sec” показывает передаваемое распространителю число команд в секунду, а счетчик “Snapshot: Delivered Trans/sec” передаваемое в секунду число транзакций.


Replication Distribution Agent


Агент распространителя (Replication Distribution Agent) используется для доставки моментальных снимков подписчикам, а также для тиражирования изменений в репликации транзакций. Свою конфигурацию он получает из строки запуска или запрашивает ее из заданного ему профиля. После этого он перемещает моментальный снимок (для репликации снимков и репликации транзакций), определенный в таблицах базы данных распространителя (для репликации транзакций), в указанные места назначения на подписчиках. Агент распространителя запускается для каждой публикации и исполняется на подписчике, при подписке в режиме “pull”, а при подписке в режиме “push”, он работает на распространителе. Агент распространителя умеет передавать данные в виде команд или транзакций не только напрямую подписчикам, но и на вход DTS-пакета. Для репликации моментальных снимков периодичность запуска определяется требованиями к обновлению информации на подписчиках.


Для репликации транзакций передача моментального снимка выполняется при первоначальной синхронизации (если в настройках подписки не указано иное) или для принудительной, повторной инициализации подписчика. В остальное время агент распространителя в репликации транзакций занимается доставкой подписчикам пакетов команд изменения данных, которые он берет из своей базы данных, обращаясь к таблице MSrepl_commands, содержащей записи о реплицированных командах, и к таблице MSrepl_transactions, которая содержит по одной строке для каждой реплицируемой транзакции. Кроме того, распространитель передает подписчикам хронологию сеансов репликации и сведения о возникших ошибках.


SQL Server 2005 появились два новых типа первоначальной синхронизации (один задавался параметром “replication support only”, а второй использовал инициализацию резервной копией), упрощающие инициализацию подписчиков, которые позволяют выполнить синхронизацию вручную (в англоязычной документации такая синхронизация называется: “no-sync subscriptions”). Необходимость ручной синхронизации была вызвана задачами автоматизации подготовки базы данных подписчика для репликации, а также, с помощью ручной синхронизации можно существенно сократить время простоя, если использовать уже синхронную резервную копию.


В SQL Server 2008 появился еще один новый тип синхронизации – инициализация по LSN. Этот тип синхронизации используется для внутренних нужд в одноранговой репликации транзакций. Он походит на инициализацию резервной копией, отличаясь тем, что инициализация будет продолжена начиная с указанного LSN, т.е. от Распространителя в базу данных подписчика будут догружены те транзакции, которые были зарегистрированы после указанного номера виртуального журнала. Такой тип синхронизации очень удобен для сокращения времени восстановления подписчика после отказа, поскольку догрузка транзакций занимает, как правило, меньше времени, чем полная инициализация, а Агент Распространителя продолжит работу с того места, где он остановился из-за отказа Подписчика, или отказа Зеркального отображения.


База данных распространителя пополняется сведениями о вновь созданных моментальных снимках, которые поставляет агент моментальных снимков. Агент чтения журнала запускает на издателе системную процедуру sp_replcmds, возвращающую команды, из которых состоят помеченные для репликации транзакции. Эти команды хранятся в базе данных распространителя, который доставляет их всем подписчикам и отслеживает, были ли команды доставлены успешно за отведенное для этого время. Кроме этого, агент чтения журнала, с помощью системной хранимой процедуры sp_repldone, обновляет запись в журнале транзакций издателя, которая идентифицирует последнюю распределенную транзакцию сервера. Т.е. те транзакции, которые были успешно реплицированы всем зарегистрированным подписчикам, могут быть убраны из журнала в резервную копию, и занимаемое ими место в журнале регистрации транзакций публикуемой базы данных на издателе может быть высвобождено для других виртуальных журналов. Информацию для этого он также берет из базы данных распространителя, где находит данные об успешно реплицированных командах.


С помощью аргумента MaxDeliveredTransactions программы агента распространителя можно задать максимальное число “push” или “pull”-транзакций, примененных на подписчике в рамках одного сеанса синхронизации. Значение 0 указывает, что будет применено максимально возможное число транзакций. Другие значения могут использоваться на подписчике для того, чтобы сократить продолжительность синхронизации с издателем. Это позволяет управлять трафиком транзакций по сети, а также нагрузкой, порождаемой агентом распространителя на сервер запуска и на сервер, который обслуживает базу данных подписчика.


Нагрузкой на подписчике позволяют управлять еще два аргумента агента распространителя. Аргумент CommitBatchSize задает число транзакций, которые будут исполнены подписчиком прежде, чем будет исполнена инструкция COMMIT обрамляющей транзакции. Значение по умолчанию равно: 100. Аргумент CommitBatchThreshold задает число команд репликации, которые будут исполнены подписчиком прежде, чем будет исполнена инструкция COMMIT. Значение по умолчанию равно: 1000.


В базе данных распространителя присутствуют и другие таблицы, используемые для некоторых режимов репликации. Таблица MSrepl_backup_lsns нужна для синхронизации репликации транзакций с резервной копией. Таблица MSrepl_errors содержит информацию об ошибках агентов распространителя и слияния. Таблица MSrepl_identity_range предназначена для управления диапазонами идентификаторов. Таблица MSrepl_originators нужна обновляемым подписчикам. Таблица MSreplication_queuedtraninfo нужна для организации очередей команд. В таблице MSrepl_version хранятся данные о текущих версиях репликации. Таблица MSreplication_monitordata используется монитором репликации, и т.д.


Если вы запускаете службу SQL Server Agent под учетной записью локальной системы (значение по умолчанию), а не под учетной записью пользователя домена, служба сможет обращаться только к локальному компьютеру. Если агент распространителя, работа которого управляется службой SQL Server Agent, будет при этом использовать для доступа к экземпляру SQL Server режим аутентификации Windows, агент распространителя не сможет нормально работать. В SQL Server 2000, параметр по умолчанию для режима аутентификации – собственная аутентификация SQL Server.


У агента распространителя существует несколько предопределенных профилей. Для SQL Server 2000 их было четыре. Один профиль использовался по умолчанию и включал значения аргументов запуска агента распространителя для наиболее распространенных вариантов репликации. Этот профиль можно взять в качестве отправной точки для начала настройки агентов распространителя.


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


Другим профилем является профиль для расширенной диагностики и хронологии сеансов репликации агента распространителя. Он определяет детализацию хронологии, регистрируемой во время исполнения операций агента дистрибутора, агента слияния или в течение исполнения операций с моментальным снимком. Основное отличие этого профиля от других – это значение аргумента HistoryVerboseLevel, который определяет объем информации, протоколируемой в журнале работы агента. Увеличение уровня детализации помогает быстрее локализовать проблему и принять соответствующие меры за счет получения более подробной хронологии сеанса репликации. Нужно заметить, что этот аргумент используется во всех агентах репликации. Возможны три значения для этого аргумента. В профилях по умолчанию для агента распространителя, агента моментальных снимков и агента чтения журнала для этого аргумента задано значение 1. При таком уровне детализации всегда обновляются предыдущие хронологические записи с таким же как у текущей задачи состоянием (startup, progress, success и так далее). Если не существует ни одной предыдущей записи с тем же самым состоянием, в отчет вставляется новая запись. Для агента слияния в профиле по умолчанию аргументу задано значение “2”. При таком уровне детализации вставляются хронологические записи, если эти записи не являются сообщениями об отсутствии активности или сообщениями о выполняющихся долго заданиях, для которых происходит обновление предыдущих записей. Если аргументу задать значение “3”, это приведет к тому, что новые записи в хронологический журнал будут вставляться во всех случаях, кроме тех, когда они являются сообщениями об отсутствии активности. Кроме этого аргумента для получения максимально возможного уровня детализации хронологии работы агентов репликации, можно использовать два аргумента, которые позволяют выводить хронологию во внешний файл. Включается такой режим журналирования добавлением аргумента Output, значение которого задает путь к файлу отчета работы агента и его имя. Если путь не указан, вывод осуществляется на консоль. Если указанное имя файла существует, записи добавляются в конец файла. Еще один аргумент, OutputVerboseLevel, определяет уровень подробности отчета, сохраняемого в файл. Если уровень подробности – 0, записываются только сообщения об ошибках. Если уровень подробности – 1, будут записаны все сообщения о результатах работы. Если уровень подробности – 2 (значение по умолчанию), будут записаны все сообщения об ошибках и о результатах работы, которые очень полезны для отладки.


Устанавливать уровень подробности хронологии работы агентов репликации больше нулевого стоит в тех случаях, когда проводится тестирование репликации, на время периодического контроля работы агентов или в целях отладки и разрешения проблем. Отказ от ведения хронологии сеансов репликации может дать прирост производительности сеанса репликации до 15%.


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


Во время применения агентом распространителя моментального снимка на подписчике можно задействовать средства повышения производительности массовых операций, которые применяются в программе BCP. Использование при запуске агента распространителя аргумента UseInprocLoader повышает эффективность использования моментального снимка, предписывая агенту распространителя при применении файлов снимка на подписчике использовать команду BULK INSERT. В результате, производительность применения снимка может получить прирост до 30 % (если нет проблем с установками порядка сортировки (collation)). Также до 30% повышение в производительности может быть достигнуто при применении аргумента MaxBcpThreads. Этот аргумент определяет число потоков операций массового копирования, которые могут быть выполнены параллельно. Его смысл точно такой, как и у одноименного аргумента агента моментальных снимков. Совместное применение аргументов UseInprocLoader и MaxBcpThreads может дать до 50% выигрыша в производительности.


Еще одним полезным аргументом для повышения производительности агента распространителя является аргумет Buffers. Он задает число буферов, доступных для асинхронных транзакций. Значение по умолчанию равно 2. Увеличение этого числа может способствовать повышению эффективности за счет сокращения листания (memory paging) памяти.


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


Если вы обновляете какой-либо столбец таблицы базы данных, обслуживаемой Microsoft SQL Server 2000 Standard и Enterprise Edition, который является частью уникального или составного индекса, SQL Server осуществляет обновление как отложенное изменение. Отложенное изменение означает, что команда UPDATE будет передана подписчику как пара операций: DELETE и INSERT. Отложенное изменение более подробно описано в статье базы знаний Microsoft: “Q238254 INF: UPDATE Statements May be Replicated as DELETE/INSERT Pairs”. Возможны случаи, когда передача подписчикам изменений в виде пары команд DELETE и INSERT не отвечает требованиям бизнес-правил, которые, например, могут предписывать передачу подписчику действий триггеров при обновлениях. Именно для того, чтобы помочь правильно разрешить эту ситуацию, был введен флаг трассировки №8207, который появился в SQL Server 2000 Service Pack 1, и при использовании которого в репликации транзакций допускаются изменения одной командой. Обновление уникального столбца, которое затрагивает только одну строку (изменение, которое принято называть “singleton”), осуществляется как команда UPDATE, а не как пара операций DELETE и INSERT. Если изменения затрагивают несколько строк, изменение будет выполняться как пара команд DELETE и INSERT. Вы можете задать ключ трассировки 8207 на сервере, исполняющем роль издателя, выполняя команду DBCC TRACEON (8207, -1) при каждом запуске SQL Server, или добавив к параметрам запуска сервера баз данных ключ -T8207. Ключ трассировки 8207 используется только в варианте репликации транзакций без обновляемых подписчиков.


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


Использование подписок типа “pull” или анонимных подписок позволяет разгрузить издателя и/или распространителя. Аналогично действует использование опции удаленного запуска агента (Remote Agent Activation). Анонимные подписки не хранят информацию о подписке в базе данных распространителя.


И наконец, если сервер-издатель перегружен, но не может быть масштабирован до необходимого уровня производительности, можно переместить базу данных распространителя на другой, менее загруженный сервер.


Для мониторинга производительности работы агентов распространителя можно использовать соответствующие счетчики производительности объекта “SQL Server: Replication Dist.”, которые показывают сколько команд и транзакций было передано подписчикам из базы данных распространителя указанным агентом распространителя. Таких счетчиков три. Первый счетчик: “Dist: Delivered Cmds/sec”, он показывает число предаваемых подписчику команд в секунду. Второй счетчик: “Dist: Delivered Trans/sec”, показывает число передаваемых в секунду транзакций подписчику. И третий счетчик: “Dist: Delivery Latency”, показывает задержку исполнения транзакции на подписчике после ее отправки из базы данных распространителя в миллисекундах.


Replication Log Reader Agent


Агент чтения журнала транзакций (Replication Log Reader Agent) является наиболее важным и уязвимым элементом топологии репликации транзакций. У издаваемой базы данных не может быть больше одного агента чтения журнала. Чтобы понять его работу, стоит кратко описать схему взаимодействия компонент SQL Server в репликации транзакций. В этом типе репликации отдельные транзакции, обрамляющие команды INSERT, UPDATE и/или DELETE, передаются от издателя подписчикам. После запуска программы агент чтения журналов считывает свою конфигурацию из системных таблиц базы данных распространителя, в частности, там он узнает, где располагается и как называется издаваемая база данных. Первым шагом при настройке подписки репликации транзакций выполняют первоначальную синхронизацию схемы и данных подписчика с издателем, для чего на подписчике применяется моментальный снимок публикации с издателя.


После синхронизации, единственный для публикации агент чтения журнала по установленному расписанию или непрерывно просматривает журнал транзакций издаваемой базы данных, выбирая в нем записи о транзакциях, отмеченных для репликации. Обо всех обнаруженных транзакциях, подлежащих репликации, агент чтения журнала делает записи в соответствующих системных таблицах базы данных распространителя, и заодно сохраняет в этой базе данных записи о хронологии своей работы. В таблице базы данных распространителя MSlogreader_history агент чтения журнала делает записи о последних прочитанных в сеансах репликации номерах виртуальных журналов (LSN). Это необходимо для того, чтобы в следующем сеансе репликации начать чтение с этого номера LSN. Само чтение осуществляется с помощью системной хранимой процедуры sp_replcmds, которая будет просматривать все записи в журнале с указанного ей LSN и до конца журнала, если не задан ограничивающий размер чтения аргумент ReadBatchSize. Агент чтения журнала отбирает только те транзакции, которые отмечены для репликации и если они удовлетворяют условиям фильтрации статьи (когда публикация использует фильтры). Найденные записи буферизуются и копируются пакетами в системную таблицу MSrepl_commands в базу данных распространителя. На этом этапе агент чтения журнала оценивает эффективность составляющих транзакцию команд и может внести изменения в их синтаксис, если это приведет к повышению эффективности. Например, команды могут быть преобразованы в реплицируемые вызовы процедур с заданными агентом параметрами, или в синтаксис может быть добавлено использование первичного ключа (присутствующего, но не заданного явно), а также в известных случаях команда UPDATE может быть преобразована в пару команд DELETE и INSERT. Но, несмотря на все возможные изменения, есть взаимно однозначное соответствие между транзакциями в журнале и записями команд в базе данных распространителя.


Агент распространителя передает подписчикам на исполнение все реплицируемые команды, которые были обнаружены агентом чтения журнала. Если команды в базе данных распространителя являются частью подписки, подлежащей преобразованию, агент распространителя передает команды в загрузчик данных DTS-пакета. Если это обычная подписка, агент распространителя применяет команды на подписчике уже как транзакции. Когда команда успешно исполнена на всех зарегистрированных подписчиках, она соответствующим образом помечается в базе данных распространителя. Агент чтения журнала, на основании получения информации об успешности репликации команд, снимает с записи в журнале отметку о том, что соответствующая транзакция подлежит репликации. Для этого используется системная хранимая процедура sp_repldone. Это важный момент, поскольку при полной модели восстановления, те виртуальные журналы транзакций, которые отмечены для репликации, не будут высвобождать место в журнале транзакций при выполнении команды BACKUP LOG, что может привести к существенному увеличению размера файла журнала транзакций издаваемой базы данных. Усечены могут быть только те записи журнала транзакций, которые не помечены для репликации. Хранение в базе данных распространителя команд, подлежащих репликации всем подписчикам (replicated transactions stay), осуществляется до истечения установленного периода хранения (retention period). Если существуют анонимные подписчики, команды будут храниться до истечения этого периода. Из-за этого установка большого периода хранения может потребовать увеличения размера базы данных распространителя и отводимого под папки моментальных снимков места на диске.


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


Например, используя уже упоминавшийся выше аргумент ReadBatchSize можно задать максимальное число транзакций, читаемое из журнала базы данных издателя. Если же использовать аргумент ReadBatchThreshold, то можно задать не число транзакций, а число команд репликации, которые читаются из журнала транзакций, для репликации подписчикам. Если этот аргумент не определен, агент чтения журнала будет осуществлять чтение до конца журнала транзакций или с учетом значения аргумента ReadBatchSize (число транзакций).


Еще один аргумент MaxCmdsInTran появился после выхода SQL Server 2000 Service Pack 1. Он определяет максимальное число инструкций, сгруппированных в транзакцию, которые агент чтения журнала запишет в базу данных распространителя. Как раз использование этого параметра позволяет агенту чтения журнала совместно с агентом распространителя делить большие транзакции (состоящие из большего числа команд) издателя на несколько транзакций меньшего размера. Применение этого параметра может уменьшить конкуренцию на распространителе и время ожидания репликации между издателем и подписчиком. Поскольку первоначальная транзакция применяется меньшими порциями, подписчик может обращаться к строкам большой логической транзакции издателя до того, как транзакция будет завершена, что в принципе нарушает атомарность транзакции. Значение этого атрибута по умолчанию не позволяет делить транзакции издателя.


Как правило, сервер базы данных издателя и сервер базы данных распространителя располагаются в одной подсети или даже являются одним и тем же сервером. Для повышения производительности доставки данных агентом чтения журнала из журнала транзакций издаваемой базы данных в базу данных распространителя, который находится в этой же подсети и соединен с сервером издателя высокоскоростным соединением, можно использовать аргумент программы агента чтения журнала PacketSize, с помощью которого задается размер сетевого пакета в байтах. Значение по умолчанию – 4096 (байт). Увеличение размера пакета, если это допустимо используемым сетевым оборудованием, может снизить нагрузку на сеть и сократить число возможных коллизий.


По умолчанию агент чтения журнала работает в непрерывном режиме. Если его запускать по расписанию, то этим тоже можно снизить нагрузку на журнал транзакций, особенно, если за этот ресурс большая конкуренция системных процессов. Судя по всему, большинство реализаций используют непрерывную работу агента чтения журнала. Главным образом этот выбор сделан потому, что такой вариант предлагается по умолчанию. Изменение этой опции позволяет осуществлять репликацию по расписанию. В SQL Server 2000 набор аргументов запуска агента чтения журнала в шаге задания агента сервера выглядел так:


-Publisher [ИМЯ ИЗДАТЕЛЯ] -PublisherDB [ИМЯ ИЗДАВАЕМОЙ БД] -Distributor [ИМЯРАСПРОСТРАНИТЕЛЯ] -DistributorSecurityMode 1 -Continuous


Аргумент “Continuous” как раз и обеспечивает непрерывность работы агента чтения журнала. Убрав этот аргумент из числа аргументов вызова агента, вы можете запускать его по расписанию. Однако, если нагрузка на журнал невелика и, если бизнес-правила требуют частого запуска, лучше оставить непрерывный режим работы.


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


В SQL Server 2005 была добавлена новая схема репликации транзакций, которая на языке оригинала называется Peer-to-Peer (P2P), а в русской версии электронной документации SQL Server 2005 ее принято называть одноранговой репликацией транзакций. Основная ее идея – публикуемые статьи могут быть подписаны. Все узлы в одноранговой топологии равноправны, каждый является и издателем и подписчиком на одну и ту же публикацию, схемы на всех узлах идентичны. Есть механизм блокирования циклических изменений. Конфликты в версии SQL Server 2005 не отслеживаются, поэтому необходимо принимать меры, предотвращающие одновременную модификацию одних и тех же данных на разных узлах. Реализован механизм автоматической повторной синхронизации после восстановления одного из узлов из резервной копии. Изменения в восстановленной из резервной копии базы данных на время синхронизации с другими узлами запрещены. Резервная копия должна быть сделана после включения базы в одноранговую репликацию. К ограничениям новой схемы можно отнести невозможность использования фильтров строк и столбцов. Кроме того, не рекомендуется использовать свойство колонок “identity”, т.к. придется вручную разделять диапазоны. Нельзя использовать издателей-подписчиков, не являющихся SQL Server. Нельзя использовать параметры публикации: “Queued” или “immediate updating”. Нельзя использовать DTS-преобразования подписки. Нельзя использовать системную хранимую процедуру sp_addarticle и столбцы со свойством “timestamp”. Нельзя использовать параметр агента чтения журнала: MaxCmdsInTran и параметр агента распространителя SubscriptionStreams.


Как показывает время, возможности агентов репликации увеличиваются с появлением новых версий и при добавлении или развитии функциональных возможностей. Агент чтения журнала в SQL Server 2005 получил еще одну, очень интересную функциональную возможность. Теперь он может быть задействован в публикации серверов Oracle через распространителя на основе SQL Server. Репликация транзакций с издателем на сервере Oracle основывается на стандартной схеме репликации транзакций, но отслеживание изменений на Oracle и доставка их распространителю реализуется дополнительным промежуточным слоем. На издаваемые таблицы репликация создает триггеры уровня строки, срабатывающие на вставку, изменение и удаление (DML). Триггеры отслеживают операции DML и фиксируют их в специальных таблицах – журналах для каждой статьи, в порядке их исполнения. Вставка и удаление добавляют одну строку в журнал, а изменение – две строки (состояние до и после изменения). При изменении первичного ключа, триггеры срабатывают так, что фиксируются все связанные с ключом изменения. Агент чтения журнала считывает изменения в журналах и переносит их в базу данных распространителя, в таблицы MSrepl_commands и MSrepl_transactions. Подписчикам изменения тиражирует агент распространителя.


Для мониторинга производительности работы агента чтения журнала транзакций можно использовать счетчики производительности объекта “SQL Server Replication: Logreader”, которые показывают сколько команд или транзакций было передано распространителю, а также сопутствующую этим процессам задержку. Счетчик: “Logreader: Delivered Cmds/sec” показывает число команд в секунду, а счетчик “Logreader: Delivered Trans/sec” число транзакций в секунду, которые передаются распространителю. Еще один счетчик: “Logreader: Delivery Latency” показывает текущее время, в миллисекундах, которое прошло с тех пор, как транзакция после применения на издателе была передана в базу данных распространителя.


Для разрешения проблем репликации транзакций и исследования ее функционирования по всей цепочке топологии репликации используются следующие системные хранимые процедуры и команды: sp_browsereplcmds, sp_replcounters, sp_replshowcmds, sp_repltrans, sp_replflush, sp_repldone, sp_replqueuemonitor, sp_replcmds и DBCC OPENTRAN. В упрощенном виде алгоритм можно описать следующим образом. После обнаружения проблемы в мониторе репликации, включите максимальный уровень детализации истории агентов чтения журнала и распространителя. Измените значение аргумента агента распространителя BatchCommitSize на 1, это позволит обрабатывать единовременно по одной транзакции или инструкции, которые могут состоять из нескольких команд. Потом, отключите работу заданий по очистке метаданных распространителя, чтобы избежать удаления информации, необходимой для поиска и устранения проблемы. После этого, перезапустите агентов чтения журнала и распространителя (если это необходимо). На подписчике убедитесь, что в таблице MSreplication_subscriptions присутствуют и верны записи исследуемой подписки. Запомните значение в колонке transaction_timestamp для агента распространителя этой подписки, которое будет нам указывать на последнюю транзакцию, поставленную распространителем подписчику. В таблицах базы данных распространителя присутствует колонка xact_seqno, которая соотносится с колонкой transaction_timestamp на подписчике (за исключением завершающих нулей). Далее, полученную информацию можно использовать для вызова системной хранимой процедуры sp_browsereplcmds @xact_seqno_start =”<transaction_timestamp> ” которая вернет реплицируемые команды. Большим подспорьем в разрешении проблем репликации транзакций может стать системная функция fn_dblog, которая позволяет заглянуть внутрь журнала регистрации транзакций. Ее можно использовать, опираясь на тот факт, что в журнале даже после прохождения контрольной точки остаются записи реплицируемых транзакций, которые еще не были помечены агентом чтения журнала, как успешно реплицированные. Т.о. можно определить, например, являются ли транзакции репликации причиной роста файла журнала транзакций. Посмотреть это не составляет труда, достаточно простого вызова:


    SELECT * FROM fn_dblog(null,null)

    
    


Далее, остается только проанализировать возвращаемую представленной выше командой информацию. Помните, что команды одной транзакции имеют одинаковый идентификатор. Есть особые метки, которые могут помочь Вам ориентироваться в содержимом журнала. Например, следующие две метки отмечают начало и конец прохождения контрольной точки: LOP_BEGIN_CKPT и LOP_END_CKPT. Такие метки, как LOP_BEGIN_XACT, LOP_INSERT_ROWS и LOP_COMMIT_XACT отмечают транзакцию вставки данных в таблицу, а по колонке AllocationUnitName можно определить какой объект был задействован в транзакции.


Replication Merge Agent


Программа агента слияния (Replication Merge Agent) получает конфигурацию и умеет не только проводить сеансы репликации слиянием между базой данных издателя и базой данных подписчика, но и применяет моментальный снимок для первоначальной или повторной инициализации подписчика, если это не запрещено в настройках подписки. Этот агент универсален и является самым сложным из всех агентов репликации.


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


Агенту слияния не нужен агент распространителя, и он почти не использует базу данных распространителя. Кроме того, у этого агента больше всего разнообразных профилей работы.


Поскольку репликация слиянием использует триггерный механизм отслеживания изменений, каждой строке сопоставляется глобальный уникальный идентификатор и номер поколения ее изменения. Фактически, транзакции базы данных издателя или подписчика преобразуются в поколения изменений данных, записи о которых сохраняются в системные таблицы метаданных слияния: MSmerge_contents, MSmerge_tombstone и MSmerge_genhistory. Такие таблицы создаются в участвующих в репликации базах данных во время оформления публикации или подписки. Кроме того, триггерный механизм является той причиной, почему одна таблица не может участвовать в двух публикациях репликации слиянием, поскольку логика работы триггеров была бы очень сложной, а конфликты изменений для нескольких публикаций было бы очень трудно отслеживать. Во время создания публикации репликации слиянием к таблицам может быть добавлен столбец с типом uniqueidentifier и с именем rowguid. Это происходит потому, что репликации слиянием необходим столбец, который уникально идентифицирует строку в распределенной топологии репликации.


Такое возможное изменение схемы данных нужно учитывать, поскольку его можно использовать в собственных приложениях или внести необходимые правки в уже существующий код, чтобы добавление столбца не привело к ошибкам обращения к таблице. Кроме столбца, для таблицы создается уникальный некластеризованный индекс по столбцу rowguid. И это еще не все изменения схемы. Для каждой таблицы, включаемой в публикацию, создается набор триггеров, имена которых начинаются со следующих префиксов: ins_, del_ и upd_, и заканчиваются глобальными уникальными идентификаторами. Именно эти триггеры выполняют синхронизацию, сохраняя в системных таблицах информацию о тиражируемых изменениях. Триггеры для команд INSERT и UPDATE сохраняют значения rowguid измененных и вставленных строк в таблице MSmerge_contents. Триггер для команд DELETE сохраняет метаданные об удаляемых строках в таблице MSmerge_tombstone. При этом, в отличие от репликации транзакций, каждое изменение строки перезаписывает предыдущие изменения в таблице MSmerge_contents, т.е. в этой таблице хранятся только самые последние изменение и именно последние изменения попадают в очередной сеанс репликации.


Агент слияния сравнивает содержимое таблиц MSmerge_contents подписчика и издателя. Ориентируясь по значениям столбца rowguid и по номеру поколения каждой из представленных там строк, он копирует новые строки и изменяет уже имеющиеся. Если одна и та же строка в сеансе числится измененной на издателе и на подписчике, регистрируется конфликт. Механизм поколений позволяет принимать решение о том, какие строки должны быть изменены без полного просмотра таблиц.


Двунаправленный характер передачи изменений и другие особенности репликации слиянием объясняют большее чем у других агентов число аргументов запуска программы агента слияния. Многие из аргументов несут такой же смысл и названия, как у других агентов, что объясняется универсальностью агента слияния.


Так же как и у других агентов, целый ряд аргументов может использоваться для балансировки нагрузки сеанса репликации, а также для выстраивания передачи очередности поколений, когда велика вероятность частого изменения одной и той же строки. Например, аргумент SrcThreads задает количество потоков на источнике, которые агент слияния использует для того, чтобы посчитать изменения на источнике. В течение сеанса репликации источником будет подписчик, когда выполняются операции с типом “upload”, а издатель будет источником, когда выполняются операции с типом “download”.


Аргумент DestThreads задает число потоков, которые агент использует для передачи изменений с одного сервера на другой сервер, участвующий в репликации слиянием. В зависимости от направления передачи изменений, в качестве принимающего сервера может выступать как издатель, так и подписчик.


Аргумент UploadGenerationsPerBatch задает число поколений, которые будут обработаны в одном пакете при загрузке изменений от подписчика к издателю. Поколение представляет собой логическую группу изменений в статье. Значение по умолчанию для профиля, рассчитанного на надежные коммуникации между издателем и подписчиком, принимается равным 100. Значение по умолчанию для ненадежной связи – 1.


Аргумент UploadReadChangesPerBatch задает число изменений, которые будут считаны в одном пакете при загрузке от подписчика к издателю.


Аргумент UploadWriteChangesPerBatch задает число изменений, которые будут применены в рамках одного пакета при загрузке изменений от подписчика к издателю.


Аргумент DownloadGenerationsPerBatch задает число поколений изменений, которые будут обработаны в одном пакете при их загрузке от издателя к подписчику.


Аргумент DownloadReadChangesPerBatch задает число изменений, которые будут прочитаны в одном пакете при их загрузке от издателя к подписчику.


Аргумент DownloadWriteChangesPerBatch задает число изменений, которые будут применены в одном пакете при их загрузке от издателя к подписчику.


Еще одним, очень интересным аргументом вызова программы агента слияния, является ForceConvergenceLevel, который задает агенту степень слияния. В электронной документации SQL Server 2000 Books Online он не был документирован. Синтаксис использования этого аргумента следующий:


    -ForceConvergenceLevel 0 | 1 | ( 2 (Publisher | Subscriber | Both) )



Значение по умолчанию – 0, при этом используется стандартное слияние без принудительного сведения. Значение 1 задает принудительное сведение для всех поколений. Значение 2 задает принудительное сведение для всех поколений и корректирует неправильные поколения. Для этого режима важно, где неправильные поколения должны быть исправлены. Аргумент ForceConvergenceLevel полезен в тех ситуациях, когда изменения не сходятся в течение сеанса слияния (проблема известна как несходимость репликации). Использование аргумента ForceConvergenceLevel помогает разрешению проблем в таких ситуациях, когда в исходной, точной копии, были пропущены некоторые поколения (generation). Также его можно использовать, когда значения столбца “lineage” в системной таблице MSmerge_contents стали логически неправильными. Когда аргументу присваивается значение 1, агент слияния (в течение закрытия актуальных, открытых поколений, и открытия новых поколений) делает изменения значений поколений, сохраняемых в системных таблицах MSmerge_contents и MSmerge_tombstone так, чтобы любые пропущенные поколения были учтены. Это гарантирует, что любые изменения, представленные этими поколениями, имеют право на согласование в течение следующего сеанса слияния. Однако, существует одно ограничение, которое определяет, что поколение уже не может существовать в точной копии адресата. То есть в системной таблице MSmerge_genhistory уже не может быть строки для этого поколения в адресате.


Когда аргументу ForceConvergenceLevel устанавливается значение 2, агент слияния исполняет по описанному предварительно сценарию все поколения и также выполняет хранимую процедуру на точной копии, указанной для исправления (“fix up”) любых неправильных значений столбца “lineage” в системной таблице MSmerge_contents. Столбец “lineage” в системной таблице MSmerge_contents, это столбец с типом varbinary, который хранит информацию об уровне строки (versioning). Столбец “lineage” используется при отслеживании изменений в репликации слиянием и для обнаружения конфликтов, связанных со столбцом “colv1″ в системной таблице MSmerge_contents. Неправильные значения в столбце “lineage” или “colv1″ могут привести к неправильным изменениям. Однако, важно понимать, что этот аргумент гарантированно не исправит проблемы несходимости. Возможны случаи, когда изменения были пропущены, но из-за других проблем. Обратите внимание, что речь идет о тех проблемах, которые известны на сегодняшний день. В дополнение, можно отметить, что использование в промышленной среде аргумента ForceConvergenceLevel, может повлиять на производительность. Для получения дополнительной информации об известных проблемах, которые невозможно обойти с помощью аргумента ForceConvergenceLevel, изучите следующие статьи базы знаний Microsoft: “Q304703 FIX: Pull Subscribers Experience Non-Convergence After Running sp_mergecleanupmetadata Against a Published Database”, “Q304551 FIX: Merge Publishing with Vertical Filters Results in Nonconvergence” и “Q304222 FIX: Merge Replication Non-Convergence Occurs with Local Subscribers when Published Table has 32 or More Columns”.


В репликации слиянием очень важно поддерживать разумный баланс объема хранимых на подписчиках и на издателе метаданных, сосредоточенных в таблицах MSmerge_contents, MSmerge_tombstone и MSmerge_genhistory. В SQL Server 2000 Service Pack 1 появился новый аргумент агента слияния, который отвечает за то, будет ли производиться автоматическая очистка устаревших метаданных с учетом периода задержки издателя. Этот аргумент называется MetadataRetentionCleanup и ему может быть присвоено два значения 0 и 1. Важность отслеживания и своевременной очистки метаданных очень важна. На рис. 1 показана диаграмма трафика сети, на которой видно, как изменился трафик в 15 часов после того, как была выполнена очистка метаданных.



Рис. 1.


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


В SQL Server 2005 появилась возможность организовать репликацию слиянием через веб-сервер, т.е. через протоколы http:// и https://. Это расширило возможности агента слияния и добавило ему новые, дополнительные аргументы запуска. Суть нового типа подключения репликации состоит в том, что изменения на подписчике упаковываются в формат XML и посылаются на веб-сервер (IIS) по протоколу HTTP. IIS посылает данные в бинарном формате издателю уже по принятому в интернет протоколу TCP/IP. На IIS сервере регистрируется библиотека replisapi.dll (SQL Server Replication Listener), которая обеспечивает обмен сообщениями с издателем и подписчиками. XML-данные обслуживаются библиотекой replrec.dll (Merge Reconciler), которая поставляется с SQL Server 2005. После сеанса слияния, Merge Reconciler вызывает библиотеку msgprox.dll (Message Replication Provider), которая в свою очередь вызывает xmlsub.dll (SQL Server Replication Provider) – обрабатывающую XML-файлы. Возможна подписка только по схеме “pull”, поэтому агент слияния запускается на подписчике и может быть стандартным, “Merge Agent ActiveX control” или приложением с поддержкой Replication Management Objects (RMO). Добавлены новые параметры запуска агента слияния: InternetURL, InternetLogin и InternetPassword. Моментальный снимок посылается по HTTP как вложенный файл. Синхронизация происходит непрерывно, по расписанию или по требованию.


Возможно использование аутентификация IIS через “Secure Sockets Layers” (SSL), в дополнение к схеме “Basic Authentication”. Также, в программе SQL Server Management Studio стало возможным подписать базы данных SQL Server Compact, создавая подписку на рабочем столе или непосредственно на мобильном устройстве.


Для мониторинга производительности работы агента слияния можно использовать счетчики производительности объекта “SQL Server: Replication Merge”, которые показывают сколько строк было передано между издателем и подписчиком, и сколько при этом возникло конфликтов. Счетчик: “Conflicts/sec” показывает число зафиксированных в секунду конфликтов, на протяжении сеанса репликации слиянием. Счетчик: “Downloaded Changes/sec” показывает какое число строк в секунду копируется от издателя подписчику, и наоборот, число строк, копируемых в секунду от подписчика издателю показывает счетчик производительности: “Uploaded Changes/sec”.


С помощью трассировки запросов репликации, собранной программой SQL Profiler, можно определить, кто является виновником повышенной нагрузки или задержек в тиражировании данных. Программа SQL Profiler отображает запросы, посылаемые клиентами на компьютер SQL Server, и обеспечивает информацию о каждом переданном серверу запросе. Главным событием для анализа является RPC: Completed из класса событий хранимых процедур, которое позволяет увидеть какие запросы и операции агенты исполняют на SQL Server. Полезную информацию будут содержать столбцы TextData и Duration. Если потребуется, дополнительную полезную информацию можно будет найти в столбцах DatabaseId и Spid (Server Process ID), которые позволяют различать подключения издателя и распространителя, если они находятся на одном сервере. Информация в столбцах LoginName и HostName будет полезна для идентификации подписчиков. Также, из-за очень большого количества анализируемых событий, полезно накладывать фильтры на собираемую в трассировке информацию, используя для этого системные имена агентов или таких программ, как SQL Server Enterprise Manager или SQL Profiler.


Если перечисленных событий и колонок недостаточно, можно рассмотреть необходимость добавления в трассировку следующих событий: SQL:StmtStarting, SQL:StmtCompleted, Scan:Started, Scan:Stopped, Show Plan Statistics, Show Plan All и Show Plan Text.


Задания обслуживания репликации


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


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


Для очистки устаревшей хронологии работы агентов используется специальное задание: “Agent History Cleanup Agent”. По умолчанию, в SQL Server 2000 это задание запускается каждые 10 минут, и удаляет записи старше 48 часов. Это помогает сократить размер системных метаданных.


Задание “Очистка распространения” (Distribution Cleanup Agent) запускается каждые 10 минут и удаляет успешно реплицированные всем подписчикам команды, метаданные которых хранятся в системных таблицах базы данных распространителя (значение по умолчанию). Кроме этого, удаляются все моментальные снимки, которые уже были применены подписчиками, и с момента применения последнего снимка прошло больше 72 часов (значение по умолчанию). Удаление снимков позволяет избежать исчерпания дискового пространства под моментальные снимки. Если к публикации разрешены анонимные подписки с опцией немедленного создания первоначального снимка, в папке снимков сохраняется хотя бы одна копия моментального снимка (не подлежащая очистке). Это гарантирует то, что для анонимных подписчиков всегда будет доступен самый “свежий″ моментальный снимок. Подписки, которые не были синхронизированы в течение максимального срока хранения распространения, также деактивируются в процессе работы этого задания.


Очистка истекших подписок осуществляется заданием “Expired Subscription Cleanup Agent”. По умолчанию, задание запускается каждый день в час ночи, и удаляет все просроченные подписки к издаваемой базе данных.


Задание “Повторная инициализация подписок, имеющих сбои при выполнении проверки данных” (Reinitialize Subscriptions Having Data Validation Failures Agent) призвано автоматизировать реакцию на неудачи при сверке данных издателя с подписчиком. По умолчанию, это задание остановлено и не имеет расписания. Сделано это потому, что подписчики, у которых выявлены расхождения данных с издателем, будут принудительно инициализированы и применят последний моментальный снимок при следующем запуске агента слияния или агента распространителя.


Для поиска агентов репликации, которые не подают “признаков жизни”, используется специальное задание: “Replication Agents Checkup Agent”. Это задание запускается каждые 10 минут, и делает записи в системных журналах, если обнаруженные агенты репликации, которые долгое время не делали хронологических записей о сеансах своей работы. Есть еще один агент репликации, который не имеет своего собственного задания, работает постоянно и настраивается на одной из вкладок оснастки монитора репликации. Название этого агента: “Replication monitoring refresher for distribution”, нужен он для настройки обновления наблюдаемой в мониторе репликации картины активности агентов. В его задачу входит обновление буферов запросов, используемых монитором репликации.


Подготовка к репликации


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


Адаптации к репликации, в первую очередь, подлежит схема данных. Например, если для организации ссылочной целостности в базе данных используются поля IDENTITY, и приложение подразумевает ввод новых записей не только на издателе, но и на подписчике, нужно предусмотреть меры по разделению диапазонов IDENTITY между подписчиками и издателем или заменить столбцы IDENTITY на столбцы глобальных уникальных идентификаторов – GUID.


Если в публикацию в виде статей включаются не все таблицы базы данных, необходимо принять меры на уровне схемы, чтобы обеспечить возможность выполнения каскадных операций и поддержку ссылочной целостности между таблицами, входящими и не входящими в публикацию. Аналогичные меры нужно продумать для триггеров, поскольку срабатывание триггера на привнесенные репликацией операции может быть нежелательно, и для такого триггера нужно использовать параметр NOT FOR REPLICATION. Старайтесь создавать дополнительные триггеры, индексы и представления на издателе, а не на подписчиках.


Пользовательские приложения должны уметь работать с фильтрованными репликацией таблицами, это могут быть как горизонтальные фильтры, так и вертикальные фильтры.


При проектировании таблиц для репликации транзакций, в целях повышения эффективности работы репликации, стоит придерживаться нескольких простых рекомендаций. Во-первых, все таблицы, включенные в репликацию транзакций, должны иметь первичные ключи. Во-вторых, для входящих в публикацию таблиц стоит рассмотреть возможность отключения срабатывания триггеров и проверок ограничений внешнего ключа для реплицируемых операций. Ограничения внешнего ключа и триггеры используются для обеспечения целостности данных. Поддержка ссылочной целостности в репликации может обходиться слишком дорого, поскольку любые изменения будут приводить к срабатыванию этих механизмов на всех подписчиках. Кроме того, такие срабатывания зачастую паразитные, поскольку дублируют уже выполненные на издателе срабатывания. Для того, чтобы отключить срабатывание триггеров и ограничений внешних ключей, Вы можете пересоздать их с опцией NOT FOR REPLICATION. Объекты, созданные с опцией NOT FOR REPLICATION будут срабатывать только в ответ на действия пользователей, но не на действия репликации. Опция таблицы NOT FOR REPLICATION становится активна только тогда, когда к таблице обращается агент репликации.


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


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


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


Поскольку мы имеем дело со сложной системой, возрастает количество критически важных компонент, сбой которых приведет к потере производительности или краху репликации.


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


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


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


Наиболее распространенной сетевой средой локальных вычислительных сетей сегодня является Ethernet. Практически повсеместно используются сети пропускной способностью 1000 или 100 Мб/сек. Необходимо помнить и об ограничениях, которые накладывает такая сеть. Сети Ethernet являются коллизионными, т.е. возникающие одновременно в сети два пакета отклоняются оборудованием сети и повторяются через случайный промежуток времени, разный для каждого из пакетов. Наличие большого числа коллизий может привести к снижению производительности операций, которые сервер баз данных осуществляет через сеть. Сети Ethernet имеют ограничение на протяженность сегмента. Превышение длинны сегмента может привести к тому, что ответ о получении пакета не будет вовремя получен узлом, его ожидающим. Такая ситуация приводит к потерям пакетов, что тоже негативно отражается на производительности. Сети Ethernet имеют ограничение на число соединений между парой устройств активного сетевого оборудования. Превышение установленного стандартом числа пассивных соединений может увеличить время отклика и породить неоднородности или аномалии в сети, снижающие ее производительность. Протяженность сети, наличие между серверами большого количества активного и пассивного сетевого оборудования, имеющего собственную задержку обработки запроса, также может снижать производительность, хотя будут соблюдены требования стандарта, и каждый участок в отдельности будет работать с высокой производительностью. Сегодня многие локальные сети связаны между собой протяженными коммуникационными линиями, имеющими меньшую пропускную способность, чем ЛВС. Для этого применяются модемные соединения через обычные телефонные линии или выделенные линии, каналы Т1 и Е1, фрейм-релейные сети, радио Ethernet, разного рода беспроводные технологии передачи данных, широкополосный интернет и т.д. По существу, все такие соединения накладывают ограничения на пропускную способность коммуникаций между серверами и имеют высокие значения задержки передачи запросов. Используемое на коммуникационном канале оборудование, контролирующее заявленную полосу пропускания, при превышении трафика может “резать” превышающие трафик пакеты, что также может приводить к снижению пропускной способности канала в целом или даже к ошибкам в сеансе синхронизации данных.


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


Плохо спланированная локальная сеть или недостаточная пропускная способность интернет-канала также могут создать проблемы при организации репликации. Для своевременной реакции на возникновение проблем репликации стоит наладить постоянный контроль утилизации сетевых ресурсов и обеспечить возможность диагностики всей топологии вашего внешнего и внутреннего коммуникационного канала для репликации. Размещайте сервера в центральных сегментах и старайтесь обеспечить высокую производительность обслуживающих репликацию сегментов. Обеспечьте максимально возможную пропускную способность для внешнего коммуникационного канала, используемого для репликации. Лучше иметь для этого отдельный канал, не используемый для других целей. Будьте осторожны в применении диагностических и журналирующих сетевой трафик систем. Они способны создавать собственный трафик в сети, конкурирующий с трафиком репликации. При этом, увеличение числа сетевых ошибок будет способствовать росту служебного трафика и трафика репликации, что может привести к затоплению всего канала.


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


Появление коммуникационных проблем, особенно при репликации через интернет, чревато лавинообразным ростом трафика репликации. Например, значительный посторонний трафик может привести к существенному увеличению продолжительности сеансов репликации. Задания на запуск агентов будут отрабатывать долго, и практически сразу запускаться снова. Возрастет количество сбоев в передаче данных и повторов попыток агента провести сеанс синхронизации данных. Увеличится доля служебного трафика, и так далее, подобно эффекту снежного кома… В таких случаях, самым простым и действенным средством на время локализации коммуникационных проблем является изменение расписания запуска сеансов репликации. Разнесение по времени сеансов разных подписчиков также позволит сократить взаимное влияния их трафика друг на друга. Для предотвращения приводящего к затоплению сети поведения агентов репликации можно отказаться от повторных попыток синхронизации при неудаче. После этого можно автоматизировать изменение периодичности запуска агента репликации и обеспечить принудительное прекращение сеанса репликации, если его продолжительность превысила допустимое время.


Еще одной опасностью для репликации через сети передачи данных является запуск процедур сверки данных между издателем и подписчиками. Это длительная по своей природе операция, которая сопровождается значительным сетевым трафиком, причина возникновения которого связана с необходимостью выполнения распределенных запросов для сверки данных по каждой статье публикации и, возможно, для каждой пары издатель-подписчик. Необходимость в сверке данных издателя с подписчиком может возникнуть тогда, когда вы подозреваете, что не все записи в процессе репликации были переданы или, например, записи на подписчике могли быть удалены по неосторожности.


Сверка может выполняться со всеми, либо только с одним подписчиком, это указывается для SQL Server 2000 в специальном диалоговом окне программы Enterprise Manager, которое называется: “Validation Options”. Существует три вида сверки, которые отличаются степенью нагрузки на серверы и каналы связи.


Можно выбрать быстрый подсчет строк (самый щадящий метод), точный подсчет строк (для сверки с другими версиями) и самый “тяжелый″ способ – вычисление контрольных сумм. Сверка может выполняться не только вручную, но и автоматически, по расписанию. Кроме того, если сверка выявит расхождения, можно настроить автоматическую повторную инициализацию для не прошедшего сверку подписчика. Для этого в SQL Server 2000 существовала опция: “Reinitialize Subscriptions Having Data Validation Failures Agent”.


В состав системных хранимых процедур входят три специализированные процедуры, которые предназначены для реализации сверки данных. Хранимая процедура sp_article_validation предназначена для сверки одной статьи и применима в репликации транзакций (для некоторых версий она применима и для репликации снимков). Процедура sp_publication_validation сверяет всю публикацию репликации транзакций (для некоторых версий она применима и для репликации снимков). И еще одна процедура применима для всех типов репликации и позволяет осуществлять сверку таблиц или индексированных представлений. Называется эта системная хранимая процедура: sp_table_validation. Ее нельзя применять для таблиц, публикуемых издателем Oracle.


Развитие репликации


Репликация не стоит на месте, она постоянно развивается, и новшества появляются не только в новых версиях, но и буквально в каждом сервисном пакете. Наиболее значимый шаг был сделан еще в SQL Server 7.0. В следующей версии SQL Server 2000 к четвертому сервисному пакету репликация заработала очень стабильно и надежно.


В SQL Server 2005 появилась целая серия новинок, одной из ожидаемых была репликация DDL (команд языка определения данных: ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION и ALTER TRIGGER). Для предыдущих версий SQL Server поддерживал только добавление или удаление столбца статьи. Это нововведение позволяет существенно упростить изменения схемы публикуемой базы данных. Теперь изменения схемы можно выполнять с помощью программы SQL Server Management Studio, запуская в окне запроса инструкцию DDL из состава языковых конструкций Transact-SQL, или можно использовать объекты SQL Management Objects (SMO) в собственных приложениях. DDL команды, по умолчанию, будут растиражированы всем подписчикам в сеансах агента распространителя или агента слияния. Изменения схемы можно делать только на издателе, и оно подчиняется всем ограничениям для команды ALTER, предписываемым правилами языка Transact-SQL (например, нельзя изменять поля первичного ключа). Есть и целый ряд других ограничений. Репликация слиянием не позволяет изменять схему на подписчике, а в репликации транзакций это возможно, но может привести к сбоям. Также, в репликации слиянием нельзя удалять поля со свойством uniqueidentifier или ROWGUIDCOL. Изменения схемы, переданные переиздающему подписчику, по умолчанию будут тиражироваться и на его подписчиков. Если изменения схемы затрагивают объекты, имеющие ссылки или ограничения, связанные с объектами, которые есть на издателе, но их нет на подписчике, изменение пройдет успешно на издателе, но окончится неудачей на подписчиках. Все используемые в инструкции DDL объекты подписчика должны иметь одинаковые с издателем имена и одних и тех же владельцев. Все ограничения должны иметь имена, иначе они могут быть именованы поразному (автоматическое именование) на издателе и подписчиках. В инструкциях DDL необходимо использовать трехсоставное именование, только если база данных подписчика имеет такое же имя как на издателе. Изменения схемы, тиражируемые подписчикам других СУБД, требуют повторной инициализации. Не поддерживаются изменения схемы для издателей других типов СУБД. Также, повторная инициализация потребуется в репликации транзакций после добавления колонки со свойством timestamp.


В SQL Server 2000 вы не могли для изменения набора столбцов статьи использовать команду ALTER TABLE, для этого нужно было использовать системные процедуры sp_repladdcolumn и sp_repldropcolumn. Кроме этого, существовала возможность внести изменения в окне свойств публикации или использовать соответствующие объекты SQL Distributed Management Objects (SQL/DMO).


Еще один способ исполнения сценария DDL на всех подписчиках предоставляет системная хранимая процедура sp_addscriptexec. Ее использование немного отличается в разных версиях, поэтому перед ее использованием ознакомьтесь с описанием ее работы в электронной документации по SQL Server.


Репликация в SQL Server 2005 может работать более производительно, чем в прежних версиях, с помощью использования возможности динамической фильтрации. Это возможно за счет введения используемых по умолчанию предварительно вычисляемых секций (Precomputed Partitions). Эта новая опция стала теперь доступна в свойствах публикации. Предварительно вычисленные по возможным значениям динамических фильтров секции позволяют без задержек на фильтрацию начинать сеанс синхронизации подписчика. Для этого необходимо выполнение нескольких условий. Любые функции, используемые в динамических фильтрах, такие как HOST_NAME() и SUSER_SNAME(), должны использоваться только в условии фильтрации и не могут находиться внутри представления, join-фильтра или пользовательской динамической функции. Представления, используемые в join-фильтрах, тоже не должны содержать динамические функции. Значение, возвращаемое для каждого подписчика, не должно измениться после того, как раздел был создан. Например, для HOST_NAME() нельзя будет изменить имя сервера подписчика. Условия динамической фильтрации и join-фильтры не могут использовать поля text, ntext или image. И последнее, в публикации с join-фильтром не должно быть замкнутых связей.


Кроме больших нововведений, в SQL Server 2005 появились и не столь значимые, но зато добавляющие удобства в работе функции. Так, например, теперь стала возможной инициализация подписчика репликации транзакций из резервной копии. Повышена производительность синхронизации данных во время сеанса репликации. Более удобным стал монитор репликации. Добавлены предупреждения о проблемах производительности. Можно измерить задержки репликации транзакций по завершению подписчиком начатой на издателе транзакции. Реализовано добавление в сеанс репликации транзакций записей трассировки производительности. Статистика сеанса репликации слиянием углублена до уровня статьи. Можно видеть процент исполнения сеанса агентом репликации слияния. Добавлена процедура, позволяющая корректно менять метаданные репликации после смены имени сервера. Для этого нужно выполнить следующую инструкцию:


    EXEC sp_rename_replication_server ‘SubscriberA’, ‘SubscriberB’

    
    



Новые, максимальные типы данных для больших значений не имеют присущих текстовым значениям недостатков и ограничений репликации. Такие типы, как varchar(max), nvarchar(max) и varbinary(max) можно применять вместо привычных типов: text, ntext и image. В SQL Server 2008 получила дальнейшее развитие одноранговая репликация транзакций. Появилась возможность автоматического обнаружения конфликтов сеансов репликации, которые отслеживает агент распространителя и это поведение включено по умолчанию. Также, в этой версии добавление узлов в топологию репликации не требует остановки репликации по всей топологии до того момента, пока изменения не будут доставлены всем узлам. Для этого был добавлен еще один тип синхронизации “init from lsn”, который передается в качестве параметра @sync_type системной хранимой процедуры sp_addsubscription. Разумеется, все это возможно сделать и с помощью соответствующего мастера определения топологии одноранговой репликации транзакций. Кстати, этот входящий в состав SQL Server 2008 мастер позволяет управлять топологией одноранговой репликации с помощью визуальных средств.


Монитор репликации в SQL Server 2008 получил усовершенствования пользовательского интерфейса. Выводимые на экран в виде таблиц метаданные сеансов репликации теперь можно сортировать по нескольким столбцам, накладывать вертикальные и горизонтальные фильтры. Кроме того, внесены некоторые косметические изменения в окна мастеров и свойств объектов репликации.


Заключение


В этой статье был дан краткий обзор репликации и нюансов работы ее агентов в SQL Server. Вам был представлен не типовой обзор репликации, а взгляд со стороны агентов репликации и представлены основные особенности их работы. Такой подход ближе администраторам баз данных. Далее, в этой книге, вы найдете множество разных подходов и рецептов, пригодных не только для администраторов, но и для разработчиков приложений баз данных. Книга представляет собой сборник совершенно самостоятельных статей, которые собраны в разделы, посвященные разным типам репликации, и сгруппированы по двум основным версиям: SQL Server 2005 и SQL Server 2008. Такая структура книги поможет вам использовать ее не только для изучения особенностей использования и настройки репликации, но и для решения конкретных прикладных задач.


Надеюсь, собранный в этой книге опыт окажется для вас полезным, а сама книга будет востребована и станет настольной и для администраторов баз данных и для разработчиков приложений



Уже стало для меня некой традицией в начале года верстать очередной, актуальный BlogROLL. За основу этого списка новостных лент берётся мой актуализированный OPML, т.е. счастливым обладателям IE7 и выше достаточно просто импортировать его каналы. Как это сделать уже было подробно и наглядно описано в моём блоге. Тех же, кто предпочитает список блогов, я пригашаю перейти по этой ссылке на страницу, где я набрался смелости сгруппировать блоги по нескольким тематическим направлениям: SQL Server BLOGROLL 2009

Настоящий документ является неотъемлемой частью политики безопасности ____________________ (далее, Политики), он  содержит общие правила, определяющие инфраструктуру безопасности приложений баз данных SQL Server организации (далее, Приложений). В настоящем документе излагаются основные положения и руководящие принципы, которые далее детализируются в процедурах и стандартах безопасности проектируемых и используемых Приложений. Настоящий Регламент определяет, что необходимо защищать, а процедуры и стандарты, создаваемые на основе настоящего Регламента,  должны описывают, как реализовать эту защиту.


Основные цели защиты приложений баз данных SQL Server

Основной целью Регламента является повышение приоритета задач обеспечения информационной безопасности приложений баз данных, эксплуатируемых в _____________________________________ (далее, Заказчик). Регламент и Политика должны стать основой нормативной и технической документации используемых и унаследованных Приложений, а также технических заданий на разработку новых и доработку существующих Приложений.

Основной целью регламентируемых задач является устранение угроз информационной безопасности Приложений. Полный список возможных рисков и угроз, а также способов их предотвращения можно найти в документе Майкрософт: Таблица угроз и уязвимостей (компонент Database Engine).

Задача 1. Бизнес-требования к безопасности баз данных

Перед развертыванием SQL Server необходимо спланировать политику безопасности баз данных. Базовым принципом такого планирования должно быть обеспечение полной безопасности данных на уровне сервера, а затем смягчение этих тре­бований к безопасности в отдельных областях в соответствии с потребностями бизнеса. Кроме того, перед развертыванием следует также выбрать способы снижения рисков, связанных с сетевыми атаками на сервер.

Реализация Политики должна обеспечивать исполнение следующих требований:

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

 

Реализация Политики в Приложении должна обеспечивать безопасность по умолчанию, т.е. необхо­димо реализовать в Приложении максимально строгие требования Политики, прежде чем начать раз­работку специальных требований и отступлений для нужд Приложения. Только после реализации этой строгой политики следует приступать к разработке адаптированной (менее жесткой) политики, соответствующей потребностям Приложения.

Перед настройкой Политики в Приложении необходимо собрать требова­ния к безопасности баз данных заинтересованных в работе Приложения подразделений Заказчика. Затем нужно оценить риски, связанные с безопасностью. Далее следует выработать общие требования Политики, указав все допустимые исключения из нее.

Сбор требований

До формирования Политики Приложения необходимо собрать требования к безопасности, руководствуясь следующими рекомендациями:

  • Проведите интервью с руководством заинтересованных подразделений Заказчика.

Обсуждение с руководством и другими заинтересованными лицами потребности пользо­вателей в доступе к данным и требований организации по защите конфиденциальных данных. Требования фиксируются в формальном документе о политике бе­зопасности.

  • Учёт требования законодательства.

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

  • Фиксация всех необходимых отклонения от базовой Политики.

При сборе требований к безопасности необходимо выявить все отклонения от Поли­тики (когда привилегии некоторых пользователей или групп отличаются от приви­легий большинства пользователей).

  • Упрощайте бизнес-требования к безопасности.

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

 

Оценка требований

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

Базовая Политика и исключения

После сбора и оценки требований к безопасности следует определить базовую Политику безопасности и зафиксировать её в документе по безопасности. Эта Политика может включать список серверов баз данных, для которых необходимо шифрование, или список прав доступа к базе данных, предоставляемых через встроенную роль базы данных public.

В документе по безопасности необходимо также указать все исключения из базовой Полити­ки. При этом следует придерживаться следующих рекомендаций:

  • Количество исключений должно быть минимальным.

Исключения усложняют политику безопасности и, как следствие, затрудняют ее вне­дрение и управление. Поэтому количество исключений следует минимизировать.

  • Необходимо учитывать последствия исключений из политики безопасности.

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

  • Документация по всем исключениям из политики безопасности.

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

 

Задача 2. Защита SQL Server от сетевых атак

 

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

Атаки вирусов и червей

Необходимо разработать и предусмотреть комплекс организационных, технических и программных мер по снижению риска заражения Приложения компьютерными «вирусами» (далее, вирусами), а систем Приложения компьютерными червями (далее, червями). Под компьютерным червём понимается такой тип вируса, который может заразить компьютер без участия пользователя и без проникновения в какой-либо файл, а под вирусом понимается вредоносная программа, которая может изменить или стереть данные, хранящиеся в базе данных, повредить исполняемые файлы SQL Server, вывести из строя сервер, нарушить сетевые соединения и т. п.

Для снижения риска заражения вирусами и червями, следует придерживаться следующих рекомендаций:

  • В технических заданиях на разработку новых Приложений должна закладываться необходимость реализации адекватных мер по препятствию заражения файлов приложения вирусами.
  • В технических заданиях на разработку новых Приложений должна закладываться необходимость реализации адекватных мер предотвращению заражения систем Приложения червями, через интерфейсы Приложения.
  • Новое Приложение должно допускать установку на серверах баз данных антивирусного ПО.
  • Новое Приложение должно адекватно реагировать на установку в системах свежих сервисных пакетов и обновлений SQL Server и Microsoft Windows.
  • Приложение не должно использовать и предоставлять клиентский доступ к компонентам DatabaseMail и SQL Mail.
  • Приложение не должно работать напрямую с Интернетом. Должны использоваться межсетевые экраны, в настройках которых нужно разрешать входящий трафик только на необходимые порты.
  • У всех учетных записей пользователей и групп, создаваемых для доступа к данным Приложения, должны быть надежные пароли.

Атаки типа «отказ в обслуживании»

Необходимо принять меры, предотвращающие использование разрабатываемого Приложений для организации или в качестве вспомогательного средства атаки типа «отказ в обслуживании» (Denial of Service, DoS).

Атаки с внедрением SQL-кода

Приложение должно препятствовать организацию атак с внедрением SQL-кода (SQL Injection). Входные данные должны проходить проверку правильности входных запросов Приложения. Запросы, не прошедшие проверку, должны отвергаться.

Наиболее эффективный способ гарантировать, что к базе данных SQL Server выполня­ются только корректные запросы, – использовать в Приложениях для досту­па к данным только хранимые процедуры.

Не желательно использование динамических SQL-запросов. Динамические запросы очень часто являются причиной атак с внедрением SQL-кода. Если необходим динамический код, предпочтительно использовать параметрические за­просы или хранимые процедуры, поскольку они гораздо безопаснее, чем нерегла­ментированные динамические запросы.

Задача 3. Обеспечение безопасности SQL Server

Модель безопасности Приложения должна включать участников безопасности и защищае­мые объекты на различных уровнях. Участники безопасности должны проходить аутентификацию в Active Directory, либо встроенную аутентификацию SQL Server. В качестве основного документа, помогающего наметить конкретные направления работ по обеспечению безопасности обслуживаемых SQL Server данных, следует использовать следующий публичный документ Майкрософт: Рекомендации по безопасности SQL Server 2005 – задачи эксплуатации и администрирования. В качестве средства проверки и контроля безопасности SQL Server необходимо на регулярной основе использовать специализированное средство Майкрософт, программу: Microsoft Baseline Security Analyzer 2.1 (for IT Professionals)

Участники системы безопасности

Под участниками безопасности Приложения – это такие сущности, как пользователи, учет­ные записи, группы и роли, которые могут обращаться к базе данных, серверу или к схеме. Группы и роли известны также как коллекции, поскольку могут включать в себя других участников безопасности.

В модели безопасности Приложения существование участников безопасности допускается на трех уровнях: уровне Windows (Active Directory), уровне SQL Server и уровне базы данных.

Уровень Windows

Участники безопасности на уровне Windows – это доменные имена входа Windows и ло­кальные группы Windows. Для простоты управления и лучшей безопасности желательно везде, где только воз­можно, использовать в Приложении участников безопасности Windows.

Уровень SQL Server

На уровне SQL Server участниками безопасности являются имена входа SQL Server и серверные роли. Имена входа SQL Server использовать в Приложениях не рекомендуется, они допустимы только после предоставления Заказчику подробного обоснования этой необходимости, которое в обязательном порядке согласуется со службой безопасности Заказчика.

Имена входа SQL Server обычно используются для внешних пользователей компа­нии, например для тех, кто подключается к базе данных через веб-сайт. Кроме того, у каждого экземпляра SQL Server есть встроенное имя входа «sa» и могут также быть имена входа NETWORK SERVICE и SYSTEM (в зависимости от конфигурации экземпляра), которые не должны использоваться Приложением.

Уровень базы данных

На уровне базы данных участниками безопасности являются пользователи базы дан­ных, роли приложения и роли базы данных.

Пользователи базы данных – это сущности, связанные с именами входа Windows или SQL Server, которым назначен определенный набор разрешений и привилегий к отдельным объектам (например, к таблицам) базы данных. Стан­дартные (встроенные) пользователи баз данных SQL Server: guest, dbo, INFORMATION_SCHEMA и sys не должны использоваться Приложением. Guest - это специальный пользователь, который добавляется к базе данных, чтобы дать возможность любому обладателю имени входа SQL Server получить доступ к базе дан­ных, и, как правило, он заблокирован. Владелец базы данных (или dbo) – это специальный тип пользователя базы дан­ных (обычно создатель базы данных), которому предоставлены все разрешения и при­вилегии доступа к базе данных, включая право назначать разрешения другим пользова­телям. Владельца БД могут олицетворять другие пользователи, если это предусматривается логикой работы Приложения. Желательно не предоставлять набор прав dbo подключающимся через Приложение пользователям, оставив подобные задачи администратору баз данных. Пользователи INFORMATION_SCHEMA и sys предназначены только для внутрисистемного использования (для обращения к представлениям метаданных).

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

Посредством роли Приложения допускается создавать защищенные паролем роли для конкрет­ных приложений. Например, пользователь может подключиться к базе данных через веб­-приложение, которое может активировать соответствующую роль, обеспечив пользова­теля разрешениями и привилегиями этой роли.

Роли базы данных можно использовать для назначения разрешений на уровне базы данных. Например, вы можете создать роль Users, которая позволяет пользователям применять операторы EXECUTE, SELECT, INSERT и UPDATE к определенным таблицам базы данных. Затем можно назначить эту роль некоторым пользователям, вместо того чтобы назначать разрешения каждому из них по отдельности.

Ниже приведен список предопределенных ролей SQL Server, которые есть в каждой базе данных:

      public

Роль по умолчанию, назначается всем пользователям базы данных. Если необходимо предоставить всем пользователям базы данных определенный набор разрешений, назначьте эти разрешения роли public. Не допускается предоставлять этой роли весь набор прав, ограничивая доступ пользователей на уровне Приложения. Права на объекты базы данных пользователя не должны зависеть от места подключения.

      db_accessadmin

Предназначена для пользователей, которым необходимо добавлять или удалять име­на входа в базе данных.

      db_backupoperator

Предназначена для пользователей, которым необходимо выполнять резервное копи­рование базы данных.

      db_datareader

Предназначена для пользователей, которым необходимо просматривать данные в базе данных. Члены этой роли могут выбирать все данные из любой пользовательс­кой таблицы базы данных. Для пользователей Приложения предоставление прав через эту роль нежелательно.

      db_datawriter

Предназначена для пользователей, которым необходимо добавлять или изменять любые данные в любой пользовательской таблице базы данных. Члены этой роли могут выполнять следующие задачи: DELETE, INSERT и UPDATE. Для пользователей Приложения предоставление прав через эту роль нежелательно.

      db_ddladmin

Предназначена для пользователей, которым необходимо выполнять задачи, связан­ные с языком определения данных (DDL) SQL Server. Члены этой роли могут вы­полнять любые операторы DDL, за исключением GRANT, REVOKE и DENY

     db_dеnуdаtаrеаdеr

Предназначена для ограничения доступа к данным в базе данных. Члены этой роли не могут читать никакие данные пользовательских таблиц базы данных.

     db_denydatawriter

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

     db_owner

Предназначена для пользователей, которым необходим полный контроль над всей базой данных. Для пользователей Приложения предоставление прав через эту роль нежелательно.

     db_sесuritуаdmin

Предназначена для пользователей, которым необходимо управлять разрешениями, принадлежностью объектов и ролями.

Для упрощения администрирования SQL Server в производственной среде рекомендуется по­местить учетные записи Windows всех доменных пользователей, которым нужны одина­ковые привилегии SQL Server, в одну группу учетных записей Windows. Затем нужно назначить этой группе подходящую серверную роль. В общем случае следует избегать назначения ролей напрямую индивидуальным учетным записям Windows.

Защищаемые объекты SQL Server

Защищаемые объекты – это сущности SQL Server, к которым назначаются раз­решения. Есть три защищаемых объекта верхнего уровня: сервер, база данных и схема. Каждый из этих объектов содержит другие защищаемые объекты, которые, в свою оче­редь, также могут содержать другие защищаемые объекты. Эти вложенные иерархии называются областями действия. Область действия сервера включает такие защищае­мые объекты, как экземпляры сервера, базы данных, конечные точки, имена входа и серверные роли. Примерами защищаемых объектов области действия базы данных яв­ляются роли приложения, роли базы данных, схемы и пользователи. Защищаемые объек­ты области действия схемы – функции, процедуры, таблицы и представления.

Проверка разрешений и привилегий участников безопасности

Для проверки разрешений и привилегий защищаемых объектов в SQL Server 2005 в Приложениях допускается использование функций Transact-SQL: IS_SRVROLEMEMBER и HAS_PERMS_BY_NAME.

Функция IS_SRVROLEMEMBER возвращает значение 1, если текущее имя входа является членом указанной фиксированной серверной роли.

Функция HAS_PERMS_BY_NAME определяет действующие разрешения текущего пользователя на доступ к защищаемому объекту.

Режимы аутентификации SQL Server 2005

SQL Server 2005 можно настроить на использование одного из двух режимов аутентифи­кации. В режиме аутентификации Windows вы назначаете пользователям разрешения и привилегии доступа к ресурсам SQL Server только через учетные записи Windows (или группы Windows). В режиме аутентификации SQL Server и Windows (известен и как сме­шанный режим защиты) пользователи могут также подключаться к SQL Server с помо­щью отдельного имени входа SQL Server и получать через него разрешения и привиле­гии.

Режим аутентификации Windows

В режиме аутентификации Windows для подключения к SQL Server используются суще­ствующие учетные записи пользователей и групп Windows. Этот режим позволяет ло­кальным и доменным пользователям Windows получить доступ к базам данных, не ис­пользуя имя входа и пароль SQL Server. Режим аутентификации Windows удобен для пользователей, поскольку не требует от них запоминать разные пароли для аутентифи­кации в Windows и в SQL Server. Кроме того, режим аутентификации Windows безопас­нее. К тому же, пользователи в этом случае подчиняются правилам модели безопаснос­ти Windows, поэтому возможна блокировка учетных записей, ведение аудита входа в систе­му и принуждение пользователей к периодической смене своих паролей.

SQL Server в режиме аутентификации Windows аутентифицирует пользователей на основе их учетных записей Windows и членства в группах Windows. Администратор баз данных может назна­чать разрешения баз данных любой учетной записи пользователя или группы Windows. Доменные учетные записи являются лучшим способом управления пользователями, которые об­ращаются к базе данных из внутренней сети организации. Объединение пользователей в доменные группы с последующей настройкой прав доступа для этих групп в SQL Server существенно сокращает накладные расходы на администрирование.

Если для SQL Server выбрана аутентификация Windows в домене Active Directory, то в сети обязательно должен быть контроллер домена. Если контроллер домена недоступен, то любые попытки регистрации пользователей потер­пят неудачу. Поэтому при использовании в SQL Server режима аутентификации Windows в домене Active Directory должны быть приняты дополнительные меры по обеспечению высокой готовности контроллера домена.

Смешанный режим аутентификации SQL Server и Windows

При смешанном режиме аутентификации (режим аутентификации SQL Server и Windows) пользователь соединяется с SQL Server, используя имя входа SQL Server или учетную запись Windows. Одно из преимуществ аутентификации SQL Server (в отличие от аутентификации Windows) в том, что аутентификация SQL Server не зависит от работоспособности ка­ких-либо внешних служб. Использование смешанного режима аутентификации нежелательно и допускается в Приложении только с предоставлением подробного обоснования невозможности аутентификации Windows. Решение о приемлемости использования смешанного режима аутентификации принимает служба безопасности Заказчика.

Задача 4. Защита SQL Server от внутренних угроз

Защита приложений баз данных от внутренних угроз является наиболее трудоёмкой, затратной и сложной задачей информационной безопасности. Информация, по отношению к которой существуют внутренние угрозы, может носить конфиденциальный характер, быть предназначенной ограниченному числу сотрудников и/или составлять секрет технического, коммерческого либо иного рода. Классификация такой информации осуществляется на основе действующих бизнес – правил и является предметом регулирования подразделений безопасности Заказчика. Наиболее действенным способом защиты от внутренних угроз является обеспечение лояльности технического персонала, разработчиков и пользователей Приложений.

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

Размещение серверов баз данных

К сервера баз данных, которые обслуживают защищаемые от внутренних угроз Приложения, должны предъявляться особые требования к их физическому размещению. Это могут быть отдельные серверные комнаты или зоны в общих серверных комнатах, доступ к которым осуществляется по собственным правилам и открыт для отдельного списка сотрудников. Например, в общей серверной комнате это может быть отдельная, запираемая стойка, ключи от которой выдаются только тем сотрудникам, которые включены в список доступа, составленный и актуализируемый сотрудниками безопасности Заказчика.

Сервера, содержащие защищаемую от внутренних угроз информацию не должны располагаться в зонах DMZ. Подключения к портам служб таких серверов должны осуществляться через межсетевые экраны, и кроме мер аутентификации и авторизации домена, предусматривать листы доступа. Операционная система сервера баз данных должна обеспечиваться встроенным брандмауэром, который должен обеспечивать разграничение доступа к портам служб для подключений из ближайшего сетевого окружения сервера. Политики разграничения такого доступа регламентируются сотрудниками безопасности и реализуются системными администраторами Заказчика. Разработчики Приложения обязаны предоставить Заказчику полное и подробное описание «Контактной Зоны» Приложения и сервера баз данных.

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

Требования к аудиту приложения баз данных

Требования к аудиту приложения баз данных, оперирующего защищаемой от внутренних угроз информацией, должны соответствовать рекомендациям Майкрософт, изложенным в электронной документации BOL: Соответствие стандартам безопасности (Common Criteria и/или FIPS 140-2).

Включение подробного аудита действий пользователей Приложения может существенно повысить нагрузку на сервер, сети и персональные компьютеры. При разработке технических заданий на создание программно – аппаратных комплексов Приложения необходимо учитывать технические требования для обеспечения необходимого уровня производительности с включённым аудитом. Включение аудита не должно существенно влиять на работу пользователей Приложения и других, задействованных в программно – аппаратном комплексе систем.

Детализация каждого из возможных уровней аудита Приложения должна отражать бизнес – требования и текущий уровень внешних и внутренних угроз. Конкретный состав уровней аудита предлагается разработчиком Приложения и утверждается в подразделении безопасности Заказчика.

Шифрация защищаемой от внешних угроз информации

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

Несмотря на то, что шифрование является полезным средством обеспечения безопасности, его не обязательно применять ко всем данным или соединениям. Для вновь разрабатываемых Приложений необходимо согласовать со специалистами по информационной безопасности Заказчика конкретные наборы данных, подлежащие шифрованию. Также, в зависимости от уровня защиты, определяются способы и алгоритмы шифрования и планируется иерархия средств шифрования. Использование шифрования подчиняется принятым у Заказчика политикам управления паролями, ключами и сертификатами. Допускается разработка индивидуальных политик для отдельных Приложений.

Основным руководством при планировании шифрования защищаемых от внутренних угроз данных является документ Майкрософт: Шифрование SQL Server.

 


В повседневной работе администратора баз данных важной задачей является мониторинг работы его подопечных серверов и систем. Ниже представлен пример регламента мониторинга баз данных, который объединяет в виде таблицы наиболее типичные требования к отслеживанию работы и предупреждению проблем приложений баз данных.

№ п/п Частота Задача Средства реализации мониторинга Ресурсы реагирования на проблемы
1 На этапе установки Конфигурация системуы DBA, регламент установки. DBA.
2 На этапе установки Установка и настройка Database Mail или SQL Mail. DBA, шаблоны сценариев. DBA.
3 На этапе установки Автоматизация обслуживания и журналирования. DBA, планы обслуживания и аварийные планы. DBA.
4 На этапе установки Документировние системы. MS VSTS 2008 for Database Professional DBA.
5 Ежедневно. Проверка резервного копирования. SCOM2007 DBA.
6 Ежедневно. Анализ системных журналов. SCOM2007 DBA, системные администраторы.
7 Ежедневно. Анализ журналов ошибок SQL Server. SCOM2007 DBA, автоматическая реакция на ошибки.
8 Ежедневно. Анализ дискового пространства. SCOM2007 DBA, системные администраторы.
9 Ежедневно. Анализ производительности. DBA, SCOM2007, SQL Server 2005 Best Practices Analyzer DBA, системные администраторы, разработчики приложений баз данных.
10 Ежедневно. Анализ истории работы заданий по расписанию. Отслеживание заданий с чрезмерной продолжительностью. Idera SQL Job Manager, SCOM2007 DBA, системные администраторы, разработчики приложений баз данных.
11 Ежедневно. Анализ журналов ошибок SQL Server Agent, Database Mail и планов обслуживания. DBA. DBA.
12 Ежедневно. Проверка доступности серверов. SCOM2007 DBA.
13 Ежедневно. Анализ журналов аппаратных средств. SCOM2007 DBA.
14 Ежедневно. Анализ безопасности и точек доступа. DBA, регламенты безопасности, Microsoft Baseline Security Analuzer DBA, системные администраторы, разработчики приложений баз данных.
15 Ежемесячно. Анализ эффективности индексов. DBA, регламент, MS Performance Dashboard. DBA.
16 Ежемесячно. Анализ файловой фрагментации. DBA. DBA.
17 Ежемесячно. Анализ тенденций изменения параметров мониторинга. SCOM2007 DBA.
18 Ежеквартально. Тестирование восстановления резервных копий. DBA, новые тестируются сразу, остальные раз в месяц или после изменений схемы. DBA.
19 Аварийный план. Резервное копирование баз данных. DBA, аварийный план. DBA.
20 Регламент. Внешнее архивирование резервных копий. DBA, регламент. DBA, системные администраторы.
21 Регламент. Дефрагментация индексов и актуализация статистики. DBA, регламент. DBA.
22 Регламент. Архивирование данных. DBA, регламент. DBA, разработчики приложений баз данных.
23 По требованию. Анализ блокировок и ожиданий. DBA, SQLBlocks, Profiler DBA.
24 По требованию. Профилирование запросов. Profiler, DTA DBA.
25 По требованию. Установка обновленией. DBA. DBA, системные администраторы.
26 По требованию. Пересмотр аварийных планов. DBA. DBA, системные администраторы.
27 По требованию. Пересмотр регламентов обслуживания. DBA, регламент. DBA.


Analysis Services

CLR

Clustering

Data Mining

Data Modeling

Database Management

Express&Compact

Full-Text Search

Integration Services

MSDN

MSSQLServer

Native Client & Protocols

Relational Engine

Replication

Reporting Services

Security

Service Broker

SMO & PowerShell

Storage Engine

TechNet

Training

TSQL

XML

Русские

GotDotNet

Russian SQL Server Club

Издательства

Майкрософт

Рассылки


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

                                                           Анкета

Бизнес - требования к администрированию базы данных: ____________________________________
Сервер базирования: ______________________ Краткое назначение базы данных: ______________
_________________________________________________________________________________________
_________________________________________________________________________________________
После аварии, нужно ли восстанавливать данные максимально полно (на предшествующий аварии
момент времени), либо данные быстрее восполнить другими способами? Да / Нет
Необходимо ли хранить хронологию резервных копий? Нет / __ дней / __ недель / __ месяцев
Восполнима ли потеря вводимых и изменяемых данных за рабочий день (без восстановления из
копии) ? Да / Нет
Нужен ли резервный сервер? Да / Нет
Период времени, когда база данных должна быть доступна: с _____ до _____ ; с ____ до ____
Время для технологических окон: с _____ до _____
Период активной работы с данными в течение суток: с ______ до ______ ; с ______ до ______
Время, отводимое на восстановление работоспособности после аварии? ______________________
Число пользователей базы данных: _______ чел.
Ожидаемый прирост данных в месяц: _____ Гб
Список основных приложений, работающих с базой данных: __________________________________
_________________________________________________________________________________________
_________________________________________________________________________________________
Число программ автоматизации, работающих с БД (роботы, репликация, др.):_________________
Критично ли время исполнения запросов к базе? Да / Нет
Ожидаемое время исполнения запроса (простая выборка): _____ сек.
Время простой выборки, которое является порогом допустимости: _____ сек.
Ожидаемое время исполнения аналитического запроса: ______ мин.
Время аналитического запроса, которое является порогом допустимости: ______ мин.
Требования к информационной безопасности: Низкие / Средние / Высокие
Ответственные разработчики приложений: __________________________________________________
_________________________________________________________________________________________
Ответственные от бизнеса: _______________________________________________________________
Подразделение, с которого списываются затраты на обслуживание:___________________________


    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    -- Если всё совсем уж плохо - UNCOMMITTED

    SET NOCOUNT -- меньше трафик

       ,QUOTED_IDENTIFIER -- стандартизация кавычек

       ,ANSI_NULLS -- стандартизация сравнения с NULL

       ,ANSI_WARNINGS -- вывод ошибок агрегации NULL и деления на 0

       ,ANSI_PADDING -- стандартизация оконечных пробелов и нулей

       ,ARITHABORT -- стандартизация отката транзакций

       ,XACT_ABORT -- стандартизация отката транзакций

       ,CONCAT_NULL_YIELDS_NULL -- сцепление с NULL

    ON;

    SET NUMERIC_ROUNDABORT OFF; -- стандартизация потери точности

    GO


NOCOUNT


Если инструкция принимает значение ON, то количество строк (которые обработаны инструкцией Transact-SQL) не возвращается. Запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Если запросы выполняются из программы, то в результирующем наборе таких инструкций Transact-SQL как: SELECT, INSERT, UPDATE и DELETE значение: “nn rows affected” (строк обработано: nn) отображаться не будет. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество строк данных, это может значительно повысить производительность за счет существенного снижения объема сетевого трафика. Инструкция SET NOCOUNT устанавливается во время исполнения, а не на этапе синтаксического анализа.


QUOTED_IDENTIFIER


ON по умолчанию. Идентификаторы можно заключать в двойные кавычки, а литералы должны быть разделены одинарными кавычками. Все строки, разделенные двойными кавычками, рассматриваются как идентификаторы объектов. Если в именах объектов базы данных используются зарезервированные ключевые слова, то параметру SET QUOTED_IDENTIFIER должно быть присвоено значение ON. При создании или изменении индексов в вычисляемых столбцах или индексированных представлениях параметру SET QUOTED_IDENTIFIER должно быть присвоено значение OFF. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически присваивают параметру QUOTED_IDENTIFIER значение ON. По умолчанию параметр SET QUOTED_IDENTIFIER имеет значение OFF для соединений из приложений DB-Library. Когда создается хранимая процедура, параметры SET QUOTED_IDENTIFIER и SET ANSI_NULLS фиксируются и используются для последующих вызовов этой хранимой процедуры. При выполнении операций внутри хранимой процедуры значение SET QUOTED_IDENTIFIER не меняется. Если параметр SET ANSI_DEFAULTS имеет значение ON, параметр SET QUOTED_IDENTIFIER включается. Параметр SET QUOTED_IDENTIFIER устанавливается во время синтаксического анализа. Настройка на время синтаксического анализа означает, что если инструкция SET присутствует в пакете или хранимой процедуре, она выполняется вне зависимости от того, достигает ли выполнение кода фактически этой точки. Кроме того, инструкция SET выполняется до выполнения любых инструкций.


ANSI_NULLS


Стандарт SQL-92 требует, чтобы операторы “=” и “<>” при использовании со значениями NULL всегда возвращали FALSE. SQL Server интерпретирует пустую строку как один пробел или действительно пустую строку в зависимости от настройки уровня совместимости. Директива SET ANSI_NULLS ON влияет только на сравнения, где в качестве одного из операндов используется NULL в виде переменной или литеральной константы. Если оба операнда представляют собой столбцы или составные выражения, эта настройка не влияет на результат сравнения. Для хранимых процедур SQL Server использует значение настройки SET ANSI_NULLS, которое действовало в момент создания процедуры. Значение SET ANSI_NULLS должно быть равно ON при выполнении распределенных запросов. SET ANSI_NULLS также должно быть ON при создании или изменении индексов вычисляемых столбцов или индексированных представлений (это один из семи обязательных для этого параметров директивы SET: ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER и CONCAT_NULL_YIELDS_NULL должны иметь значение ON, а параметр NUMERIC_ROUNDABORT – значение OFF). Драйвер ODBC и поставщик OLE DB собственного клиента SQL Server при соединении автоматически устанавливают параметру ANSI_NULLS значение ON. Для соединений из приложений DB-Library значением по умолчанию для параметра SET ANSI_NULLS является OFF. Установка значения SET ANSI_NULLS происходит во время запуска или выполнения, но не во время синтаксического анализа.


ANSI_WARNINGS


Формирует предупреждающее сообщение, если значения NULL появляются в статистических функциях: SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP или COUNT. Инструкции INSERT или UPDATE, выполнение которой привело к ошибке деления на ноль или арифметического переполнения, в соответствии со стандартом SQL-92 будут откачены и сформировано сообщение об ошибке. Конечные пробелы игнорируются для символьных столбцов, а конечные значения NULL игнорируются для бинарных столбцов. Значение ANSI_WARNINGS игнорируется при передаче аргументов хранимой процедуре или пользовательской функции, а также при объявлении и настройке переменных в инструкции пакетных заданий. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок. Параметр SET ANSI_WARNINGS должен иметь значение ON при создании или изменении индексов, основанных на вычисляемых столбцах или индексированных представлениях. Параметр ANSI_WARNINGS должен быть установлен в ON для выполнения распределенных запросов. Драйвер ODBC собственного клиента и поставщик OLE DB для собственного клиента SQL для SQL Server при соединении автоматически устанавливает параметр ANSI_WARNINGS в значение ON. Параметр SET ANSI_WARNINGS устанавливается во время выполнения, а не во время синтаксического анализа. Если значение параметра SET ARITHABORT или SET ARITHIGNORE установлено в OFF, а значение параметра SET ANSI_WARNINGS установлено в ON, то SQL Server возвращает сообщение об ошибке при обнаружении ошибок деления на ноль и переполнения.


ANSI_PADDING


Контролирует способ хранения значений, которые короче, чем заданный размер поля, а также способ хранения в полях типов: char, varchar, binary и varbinary таких значений, которые имеют оконечные пробелы. Производитель рекомендует ON. Значение параметра инструкции SET ANSI_PADDING не оказывает влияния на значения типа nchar, nvarchar, ntext, text, image, а также на большие значения, для которых SET ANSI_PADDING всегда ON. Это означает, что оконечные пробелы и нули не отбрасываются. SET ANSI_PADDING ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически устанавливают параметр ANSI_WARNINGS в значение ON. Значение параметра SET ANSI_PADDING устанавливается во время выполнения или запуска, а не во время синтаксического анализа.


ARITHABORT


Ошибка в транзакции приведёт к её откату, а не к предупреждению. Если параметры SET ARITHABORT и SET ANSI WARNINGS установлены в ON, ошибка приведёт к завершению запроса. Если SET ARITHABORT ON а SET ANSI WARNINGS OFF, ошибка прервёт пакет. Установка параметра SET ARITHABORT происходит при запуске или во время исполнения, но не во время синтаксического анализа. SET ARITHABORT ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.


XACT_ABORT


Если произошла ошибка при исполнении инструкции Transact-SQL, транзакция будет откачена целиком. Инструкция SET XACT_ABORT не влияет на компиляцию ошибок (например, синтаксических). Параметр XACT_ABORT должен иметь значение ON для инструкций изменения данных в явных или неявных транзакциях, применяющихся к большинству поставщиков OLE DB, включая SQL Server. Единственным случаем, когда этот параметр не требуется, является поддержка поставщиком вложенных транзакций. Дополнительные сведения см. в разделе Распределенные запросы и распределенные транзакции. Значение параметра XACT_ABORT устанавливается во время выполнения, а не во время синтаксического анализа. Включение этого параметра позволяет не заботиться об обработке ошибок при вставках и изменениях.


CONCAT_NULL_YIELDS_NULL


При этой установке, сцепление значения NULL со строкой дает в результате NULL. Настройка SET CONCAT_NULL_YIELDS_NULL устанавливается во время выполнения или запуска, но не во время синтаксического анализа. SET CONCAT_NULL_YIELDS_NULL ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.


NUMERIC_ROUNDABORT


Потеря точности не приводят к формированию сообщений об ошибках, а результат округляется с точностью столбца или переменной, в которых будет сохранен. Потеря точности происходит, когда выполняется попытка сохранения значения с фиксированной точностью в столбце или переменной с меньшей точностью. Если параметру SET NUMERIC_ROUNDABORT присвоено значение ON, параметр SET ARITHABORT определяет серьезность формируемой ошибки. В следующей таблице показано влияние этих двух параметров на сообщения об ошибках при потере точности. Значение параметра SET NUMERIC_ROUNDABORT задается на этапе выполнения или запуска, но не на этапе синтаксического анализа. При создании или изменении индексов вычисляемых столбцов или индексированных представлений параметр SET NUMERIC_ROUNDABORT должен принимать значение OFF.


Общие рекомендации


    
    

     


    1. Избегите использования звёздочки (*) в SELECT, всегда перечисляйте только необходимые столбцы.
    2. В инструкции INSERT всегда указывайте имена столбцов.
    3. Всегда присваивайте таблицам (а при необходимости и столбцам) псевдонимы – это позволяет избежать путаницы. При использовании псевдонима столбца обязательно добавляйте ключевое слово AS.
    4. При ссылке на объект всегда указывайте схему (владельца).
    5. Избегите использования non-SARGable предикатов (“IS NULL”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, “LIKE ‘%500′”, CONVERT и CAST, Строковые функции: LEFT(Column,2) = ‘GR’ , Функции даты/времени: DATEPART (mm, Datecolumn) = 5, Математические операции со столбцом: qty+1> 100 ).
    6. Для сокращения числа итераций старайтесь по возможности использовать строчный оператор CASE. Например:

    select sum(case when e.age < 20 then 1 else 0 end) as under_20

            , sum(case when e.age >= 20 and age <= 40 then 1 else 0 end) as between_20_40

           , sum(case when e.age > 40 then 1 else 0 end) as over_40

    from dbo.employee e

    7. Используйте индексы. Что бы понять, работает ли индекс, всегда проверяйте планы исполнения разрабатываемых запросов.
    8. Используйте формат даты по стандарту ISO – yyyymmdd или ODBC – yyyy-mm-dd hh:mi:ss
    9. Используйте ANSI стиль соединений. Для левых соединений опускайте ключевое слово OUTER.
    10. Для форматирования кода используйте стандартный размер табуляции – четыре символа, и отделяйте логически независимые модули кода пустой строкой.
    11. Старайтесь не использовать недокументированные средства.
    12. Если важна безопасность, не используйте динамический SQL.
    13. Порядок сортировки задавайте только предложением ORDER BY.
    14. Старайтесь хранить скрипты объектов схемы и серверного кода в системе управления версиями (например: VSS или CVS), и включать теги редакций в блок описания назначения скрипта.
    15. Всегда располагайте все DLL команды в начале кода, дабы избежать лишних компиляций.
    16. Избегите использования триггеров и курсоров, оставьте эти инструменты на крайний случай, когда по-другому задачу решить невозможно. Если пришлось писать курсор, предпочтение отдавайте локальным, в режиме: FAST_FORWARD, они самые диетические из всех остальных.
    17. Для повышения производительности соединений, когда ничего другого уже не помогает, используйте индексированные представления соединяемых точно таким же образом таблиц (в не Enterprise редакциях нужно добавлять подсказку NOEXPAND).
    18. Следует помнить, что представления могут маскировать необходимые для оптимизации метаданные, например, когда они скрывают соединения/объединения таблиц из разных баз данных, или когда не задействованы используемые для внутреннего соединения столбцы. В подобных случаях, всегда проверяйте план исполнения запроса, что бы вовремя принять меры по исправлению ситуаций с не оптимальным планом запроса.
    19. Старайтесь делать определяемые пользователем функции детерминированными, они дают более эффективные планы исполнения.
    20. Никогда не используйте в именах процедур префикс “sp_”, он зарезервирован для системных процедур, которые вначале ищутся в базе master.


Неявное преобразование типов


Если оператор связывает два выражения различных типов данных, то по правилам приоритета типов данных определяется, какой тип данных имеет меньший приоритет и будет преобразован в тип данных с большим приоритетом. Если неявное преобразование не поддерживается, возвращается ошибка. Если оба операнда выражения имеют одинаковый тип данных, результат операции будет иметь тот же тип данных. В SQL Server 2005 используется следующий приоритет типов данных:


    1. определяемые пользователем типы данных (высший приоритет);
    2. sql_variant;
    3. xml;
    4. datetime;
    5. smalldatetime;
    6. float;
    7. real;
    8. decimal;
    9. money;
    10. smallmoney;
    11. bigint;
    12. int;
    13. smallint;
    14. tinyint;
    15. bit;
    16. ntext;
    17. text;
    18. image;
    19. timestamp;
    20. uniqueidentifier;
    21. nvarchar;
    22. nchar;
    23. varchar;
    24. char;
    25. varbinary;
    26. binary (низший приоритет).


В тему: