Автор: Alexey Knyazev
Секционирование (partitions) — один из основных инструментов для обеспечения оптимальной работы с большим объёмом данных за счёт горизонтального масштабирования. Повышается управляемость и производительность, как модификации данных, так и запросы на выборку.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы.
Но не все обращают внимание на ещё одну уникальную возможность, которую предоставляет секционирование, а именно: обеспечение высокой доступности данных. Сокращает время восстановления после сбоев.
Для демонстрации создадим тестовую БД:
|
1 2 3 4 5 6 7 8 9 |
use master; go if db_id( 'TestDB' ) is not null drop database TestDB; go create database TestDB on ( name = 'TestDB', filename = 'c:\temp\TestDB.mdf' ) log on ( name = 'TestDB_log', filename = 'c:\temp\TestDB.ldf' ); go |
Теперь к нашей новой БД добавим несколько файловых групп, каждая из которых будет состоять из одного вторичного файла данных (*.ndf):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
alter database TestDB add filegroup FG1; alter database TestDB add filegroup FG2; alter database TestDB add filegroup FG3; alter database TestDB add filegroup FG4; alter database TestDB add filegroup FG5; alter database TestDB add filegroup FG6; alter database TestDB add filegroup FG7; alter database TestDB add filegroup FG8; alter database TestDB add filegroup FG9; alter database TestDB add filegroup FG10; alter database TestDB add filegroup FG11; alter database TestDB add filegroup FG12; go alter database TestDB add file ( name = 'fg1', filename = 'c:\temp\fg1.ndf' ) to filegroup FG1; alter database TestDB add file ( name = 'fg2', filename = 'c:\temp\fg2.ndf' ) to filegroup FG2; alter database TestDB add file ( name = 'fg3', filename = 'c:\temp\fg3.ndf' ) to filegroup FG3; alter database TestDB add file ( name = 'fg4', filename = 'c:\temp\fg4.ndf' ) to filegroup FG4; alter database TestDB add file ( name = 'fg5', filename = 'c:\temp\fg5.ndf' ) to filegroup FG5; alter database TestDB add file ( name = 'fg6', filename = 'c:\temp\fg6.ndf' ) to filegroup FG6; alter database TestDB add file ( name = 'fg7', filename = 'c:\temp\fg7.ndf' ) to filegroup FG7; alter database TestDB add file ( name = 'fg8', filename = 'c:\temp\fg8.ndf' ) to filegroup FG8; alter database TestDB add file ( name = 'fg9', filename = 'c:\temp\fg9.ndf' ) to filegroup FG9; alter database TestDB add file ( name = 'fg10', filename = 'c:\temp\fg10.ndf' ) to filegroup FG10; alter database TestDB add file ( name = 'fg11', filename = 'c:\temp\fg11.ndf' ) to filegroup FG11; alter database TestDB add file ( name = 'fg12', filename = 'c:\temp\fg12.ndf' ) to filegroup FG12; |
Теперь наша БД состоит из 13 файловых группы: первичной и 12 вторичных
|
1 2 3 4 |
use TestDB; go select name, data_space_id from sys.filegroups; go |
Файлы:
|
1 2 3 4 5 6 7 8 9 10 |
use TestDB; go select file_id , data_space_id , name , physical_name , state_desc from sys.database_files order by data_space_id; go |
Все эти подготовки были сделаны не случайно, теперь мы создадим функцию и схему секционирования. При этом мы будем секционировать нашу будущую «важную» таблицу с данными за 2012 год по месяцу. Каждая отдельная секция будет располагаться в своей выделенной файловой группе.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
use TestDB; go create partition function pf_dt (datetime) as range right for values ( '20120101', '20120201', '20120301', '20120401' , '20120501', '20120601', '20120701', '20120801' , '20120901', '20121001', '20121101', '20121201' , '20130101' ); go create partition scheme ps_dt as partition pf_dt to ( [primary], FG1, FG2, FG3, FG4, FG5, FG6, FG7, FG8, FG9, FG10, FG11, FG12, [primary] ); go |
Обратите ваше внимание, что все данные, которые будут с датой < 2012 и > 2012 года расположатся в файловой группе primary.
Ну, а теперь создадим нашу наиважнейшую таблицу, которая будет содержать информацию о продажах за 2012 год. При этом к этим данным выдвигаются очень серьёзные бизнес-требования в плане доступности.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
if object_id ( N'dbo.sales', 'U' ) is not null drop table dbo.sales; go create table dbo.sales ( id int identity , val varchar(255) default ( replicate ( 'A', 255) ) , price money default ( rand() ) , dt datetime ) on ps_dt( dt ); go insert into dbo.sales ( dt ) values ( '20120101' ) , ( '20120201' ) , ( '20120301' ) , ( '20120401' ) , ( '20120501' ) , ( '20120601' ) , ( '20120701' ) , ( '20120801' ) , ( '20120901' ) , ( '20121001' ) , ( '20121101' ) , ( '20121201' ); go 10000 |
В результате у нас таблица с 14 секциями, 12 из которых содержат данные…в нашем случаи по 10000 строк:
|
1 2 3 |
select partition_number, rows from sys.partitions where object_id = object_id ( N'dbo.sales', 'U' ); |
Т.к. у нас каждая секция хранится в отдельной файловой группе, то мы можем создавать резервные копии не только всей БД, но и отдельно для каждой файловой группы.
Следующим скриптом мы создадим 12 резервных копий файловых групп:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
backup database TestDB filegroup = 'FG1' to disk = 'C:\temp\backup\fg1.bak'; backup database TestDB filegroup = 'FG2' to disk = 'C:\temp\backup\fg2.bak'; backup database TestDB filegroup = 'FG3' to disk = 'C:\temp\backup\fg3.bak'; backup database TestDB filegroup = 'FG4' to disk = 'C:\temp\backup\fg4.bak'; backup database TestDB filegroup = 'FG5' to disk = 'C:\temp\backup\fg5.bak'; backup database TestDB filegroup = 'FG6' to disk = 'C:\temp\backup\fg6.bak'; backup database TestDB filegroup = 'FG7' to disk = 'C:\temp\backup\fg7.bak'; backup database TestDB filegroup = 'FG8' to disk = 'C:\temp\backup\fg8.bak'; backup database TestDB filegroup = 'FG9' to disk = 'C:\temp\backup\fg9.bak'; backup database TestDB filegroup = 'FG10' to disk = 'C:\temp\backup\fg10.bak'; backup database TestDB filegroup = 'FG11' to disk = 'C:\temp\backup\fg11.bak'; backup database TestDB filegroup = 'FG12' to disk = 'C:\temp\backup\fg12.bak'; |
Теперь сэмулируем аварию, для этого внесем изменения в одну из файловых групп через любой текстовый редактор. Но прежде необходимо отключить нашу БД (sp_detach_db) или остановить SQL Server, т.к. иначе файлы БД нельзя отредактировать.
|
1 2 3 4 |
use master; go exec sp_detach_db 'TestDB'; go |
Теперь внесём изменения в один из файлов БД, например в файл C:\temp\fg7.ndf. В качестве редактора я использую Far Manager.
Нам достаточно изменить всего один символ, чтобы наша БД не прошла проверку контрольной суммы. За эту проверку отвечает параметр БД PAGE_VERIFY = CHECKSUM (значение по умолчанию и если вы его не меняли, то вам не стоит о нём беспокоиться), более подробно об этом можно прочитать по ссылке —http://msdn.microsoft.com/ru-ru/library/bb522682.aspx.
Теперь подключим нашу базу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
use master; go exec sp_attach_db 'TestDB' , 'C:\temp\TestDB.mdf' , 'C:\temp\TestDB.ldf' , 'C:\temp\fg1.ndf' , 'C:\temp\fg2.ndf' , 'C:\temp\fg3.ndf' , 'C:\temp\fg4.ndf' , 'C:\temp\fg5.ndf' , 'C:\temp\fg6.ndf' , 'C:\temp\fg7.ndf' , 'C:\temp\fg8.ndf' , 'C:\temp\fg9.ndf' , 'C:\temp\fg10.ndf' , 'C:\temp\fg11.ndf' , 'C:\temp\fg12.ndf'; go |
После подключения БД, попытаемся считать данные из файловой группы, которую мы отредактировали.
|
1 2 |
select * from dbo.sales where $partition.pf_dt(dt) = 8; |
В результате мы получим ошибку:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x95830087; actual: 0x959e8087). It occurred during a read of page (9:8) in database ID 5 at offset 0x00000000010000 in file ‘C:\temp\fg7.ndf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
При этом данные из других файловых групп доступны для работы
|
1 2 3 4 |
select count(*) from dbo.sales where $partition.pf_dt(dt) != 8; --110000 |
Т.е., не смотря на битые данные в одной из секций, мы продолжаем работать с остальными данными в обычном режиме. Теперь попытаемся восстановить нашу «битую» файловую группу в режиме ONLINE, т.е. без остановки работы с другими данными.
Начинаем всё с переключения нашей ФГ (файловой группы) в режим OFFLINE
|
1 2 3 |
alter database TestDB modify file ( name = FG7, offline ); go |
Теперь посмотрим статусы файловых групп:
|
1 2 3 4 5 6 7 |
select file_id , data_space_id , name , physical_name , state_desc from sys.database_files order by data_space_id; |
Теперь, при обращении к нашей ФГ, мы получим уже другую ошибку
|
1 2 |
select count(*) from dbo.sales where $partition.pf_dt(dt) = 8; |
One of the partitions of index » for table ‘dbo.sales'(partition ID 72057594039500800) resides on a filegroup («FG7») that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.
Далее выполним само восстановление FG7
|
1 2 3 4 5 6 7 |
use master; go restore database TestDB filegroup = 'FG7' from disk = 'C:\temp\backup\fg7.bak' with norecovery; go |
Статус нашей ФГ изменился с OFFLINE на RESTORING.
Последним шагом необходимо сделать копию журнала транзакций и восстановить его с параметром RECOVERY.
|
1 2 3 4 5 6 7 8 |
backup log TestDB to disk = 'C:\temp\backup\TestDB_log.bak'; go restore log TestDB from disk = 'C:\temp\backup\TestDB_log.bak' with recovery; go |
После этого статус ФГ сменится на ONLINE и все данные нашей таблицы вновь доступны. Мало того, что на протяжении всего этого времени вся наша БД была в полном доступе для пользователей (за исключением данных в FG7), так мы ещё и восстановление произвели в разы быстрее, чем если бы производили поднятие базы из полной резервной копии. Поэтапное восстановление более подробно описано по ссылке —http://msdn.microsoft.com/ru-ru/library/ms177425.aspx
Но теперь мы проведём более серьёзный тест и удалим одну из файловых групп, но прежде сделаем новую резервную копию нашей файловой группы, которую будем удалять:
|
1 2 3 |
backup database TestDB filegroup = 'FG7' to disk = 'C:\temp\backup\fg7.bak' with init; |
Теперь остановим службу SQL Server, удалим файл C:\temp\fg7.ndf и вновь запустим службу SQL Server.
|
1 2 |
select name, state_desc from sys.databases where name = 'TestDB'; |
Обратимся за подробностями к логу
|
1 |
exec xp_readerrorlog; |
И так: наша база данных повреждена т.к. нет доступа к одному из файлов. Первым делом нужно вернуть БД в оперативный доступ. Для этого необходимо перевести в OFFLINE одну ФГ, файл которой «пропал», а всю базу перевести в ONLINE-режим.
|
1 2 3 4 5 6 |
alter database TestDB modify file ( name = FG7, offline ); go alter database TestDB set online; go |
Далее восстановление очень похоже на то, что мы делали ранее
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
use master; go restore database TestDB filegroup = 'FG7' from disk = 'C:\temp\backup\fg7.bak' with norecovery; go backup log TestDB to disk = 'C:\temp\backup\TestDB_log_new.bak'; go restore log TestDB from disk = 'C:\temp\backup\TestDB_log_new.bak' with recovery; go |
