В SQL Server 2005 SP1 появилась технология Database Mirroring, которая позволяет обеспечить высокую доступность баз(ы) данных и удаленное расположение резервное системы. Это не единственная технология обеспечения высокой доступности. Общий обзор я делал здесь и здесь. Хочу поделиться некоторыми наблюдениями о реальных примерах использованния данной технологии и о типичных ошибках в конфигурировании, которые приводят к проблемам.
1. Некорректный расчет полосы пропускания
Одним из плюсов Database Mirroring заключается в том, что основную и резервную систему связывает только сеть, а значит сервера можно расположить на большом расстоянии друг от друга и тем самым обеспечить защиту от выхода из строя целой площадки. Но если сервера расположены далеко друг от друга, то пропускная способность канала и его задержки могут повлиять на производительность. Особенно, что проявляется с синхронном режиме (асинхронный доступен только в Enterprise Edition), т.к. пока зеркало не подтвердило получение и сохранение транзакции, она не завершается и клиент ждет.
Следовательно, при планировании зеркалирования нужно оценить сколько данных и как часто будут передаваться между системами. Самый простой способ это сделать – это запустить Performance Counter, который будет собирать данные о количестве отсылаемых данных SQLServer: Database Mirroring – Bytes Sent/sec, но для этого уже нужно иметь работающее зеркалирование (например, на стоящий рядом сервер). Если это сделать нельзя, то приблизительные данные можно получить со счетчика SQLServer: Databases – Log Bytes Flushed/sec (который показывает скорость сброса данных в Transaction Log).
Еще одно замечание. В SQL Server 2008 передаваемым на зеркало данные сжимаются, что также позволяет выиграть на полосе пропускания.
2. Частая и неоправданная реиндексация
Реиндексация снижает уровень фрагментации индексов и в результате повышает скорость выполнения операций с ними. Но когда вы делаете реиндексацию выполняется генерация большого объема Transaction Log’а, которые передается на зеркало. И если запустить полную реиндексацию ВСЕ базы данных в 10-ки или 100-ни Гб, а канал между серверами не самый быстрый, то операция может растянуться на часы или даже дни.
По этому нужно реиндексировать только то, что действительно нужно:
- Не используйте DBCC DBREINDEX, во-первых, этот механизм depricated начиная с SQL Server 2008, а во-вторых реиндексация в таком случае сводиться к простому SELECT …. INSERT… для всей таблицы, что приведет к тому, что будет прочитанна и заново записанна ВСЯ таблица и ВСЕ индексы, и это будет передано на зеркало.
Для реиндексации нужно использовать:
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD;
а для реогранизации:
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE ;
Это позволит делать эффективное и выборочное перестроение
- Перед операцией определить, что действительно нужно реиндексировать. Т.к. реиндексация и/или реорганизация индекса нужна только если он слишком фрагментирован, то нужно сначала определить уровень фрагментации. Для этого можно воспользоваться системной табличной функцией sys.dm_db_index_physical_stats и значением возвращаемым в столбце avg_fragmentation_in_percent:
SELECT t.name, st.avg_fragmentation_in_percent, st.page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) st
JOIN sys.objects t on st.object_id = t.object_id
order by st.avg_fragmentation_in_percent desc
Если процент фрагметации:
- меньше 5%, то делать ничего не нужно;
- от 5 до 30%, то нужно сделать реогранизацию индекса (перестроить только листовый страницы индекса);
- если больше 30%, то стоит делать реиндексацию индекса
Я написал небольшой скрипт который делает это автоматически:
declare @minRows int
set @minRows = 100
declare @reindexQuery nvarchar(max)
set @reindexQuery =
REPLACE(REPLACE(
cast(
(
select
'ALTER INDEX '+idx.name+' ON '+ sc.name+'.'+ t.name+
CASE
WHEN st.avg_fragmentation_in_percent > 30 THEN ' REBUILD WITH (ONLINE=ON)'
ELSE ' REORGANIZE'
END as query
from sys.dm_db_index_physical_stats( DB_ID(),NULL,NULL,NULL,NULL) st
join sys.tables t on (st.object_id=t.object_id)
join sys.schemas sc on (sc.schema_id=t.schema_id)
join sys.indexes idx on (t.object_id=idx.object_id and st.index_id=idx.index_id)
join sys.partitions p on (p.index_id=idx.index_id and p.object_id=idx.object_id)
where p.rows > @minRows and st.avg_fragmentation_in_percent > 5
order by st.avg_fragmentation_in_percent desc
FOR XML PATH(''), TYPE
) as nvarchar(max))
,'</query>',';
'),'<query>','')
print @reindexQuery
exec (@reindexQuery)
Скрипт будет реиндексировать только таблицы в которых более 100 строк (см. параметр @minRows)
3. Организация сети, которая приводит к ложным срабатываниям механизма переноса
Обычно схему Database Mirroring рисуют следующим образом:
Задача Witness’а следить за ситуацией и если Primary вышел из строя, то сказать Mirror’у, что теперь он активен. Зачем нужен Witness и почему Mirror, не может сам следить за Primary? На самом деле Mirror тоже следит за Primary, а Witness нужен, чтобы при сбое соединения между Primary и Mirror не происходило ложное срабатывание процедуры переключения, после которого у на будут два активных сервера, потерявших синхронизацию.
Как конфигурирую на самом деле:
И когда рветься связь между Primary и Switch, то и Witness, и Mirror видят, что Primary не доступен и начивают процедуру активации. Ложную.
Что делать? Не пожалеть средств на установку дополнительных сетевых карт и реализовать топологию с верхней картинки, либо отказаться от Witness’а и делать ручную активацию в случае сбоя.