Как хранить более 10 ГБ в SQL Express (часть 2)

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

Достаточно часто встречаются базы данных в которых храняться бинарные данные большого объема. И если вы выбрали SQL Express то вы достаточно быстро подойдете к лимиту. Если вы конечно храните бинарные данные непосредствено в в файлах данных.

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

Итак начнем. Создаем базу для тестов, создаем в ней таблицу BlobData и заполняем бинарными данными.

use master;
go

create database FSDb;
go

use FSDb;
go

create table dbo.BlobData (
   id int identity (1, 1)
 , ModTime datetime2(4)
 , data varbinary(max)
 , constraint PK_BlobData primary key clustered ( id ) 
)
go

with cte ( id )
as ( 
    select   1
    union all
    select id + 1
    from cte
    where id < 10000
)
insert into dbo.BlobData ( ModTime, data )
select dateadd(day, -id, '20120901')
     , convert(varbinary(max), '0x6a418c763f4d87b8e74663f4')
from cte
option ( maxrecursion 0 )
go

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

exec sp_configure filestream_access_level, 1
reconfigure

Теперь давайте добавим файловую группу для наших бинарных данныхю

use master;
go

alter database FSDb
add filegroup filestreamgroup 
contains filestream;
go

alter database FSDb
add file
(
 NAME= 'FSDb_filestream',
 FILENAME = 'c:\FSDb_filestream'
)
to filegroup filestreamgroup
go

Далее чтобы "магия" заработала нужно выполнить следующие действия:

use FSDb;
go

-- 1. добавляем уникальный идентификатор строки в 
--    таблицу BlobData
alter table dbo.BlobData
add FileId uniqueidentifier not null 
       rowguidcol 
       default (newid()) 
       unique
go

-- 2. указываем в каой файловой группе должны храниться 
--    бинарные данные
alter table dbo.BlobData
set ( filestream_on = filestreamgroup )
go

-- 3. Создаем varbinary(max) поле и указываем что 
--    это filestream 
alter table dbo.BlobData
add DataFs varbinary(max) filestream null
go

-- 4. Перегоняем данные в новое поле
update dbo.BlobData
set DataFs = Data
go

-- 5. Удаляем старое поле Data (оно нам больше не 
--    понадобиться)
alter table dbo.BlobData 
drop column Data;
go

-- 6. Переименовываем поле DataFs в Data, так чтобы нам 
--    не пришлось менять наше приложение или процедуры 
--    которые обращаются к данным из этого столбца.
exec sp_rename 
    'dbo.BlobData.DataFs'
  , 'Data'
  , 'COLUMN';

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

-- создаем большой объект, примерно 344 MB
declare @Blob varchar(max) 
           = '16a418c763f4d87b0398e746a418c763f409812734'
      , @i int = 0
while (@i < 23)
begin
 set @Blob += @Blob
 set @i += 1
end
--select len(@Blob)

-- а теперь записываем его в БД 50 раз. Это в районе 17 ГБ.
-- 344MB * 50 = 17200MB = 17.2GB
;with    cte ( id )
          as ( select   1
               union all
               select   id + 1
               from     cte
               where    id < 50
             )
insert into dbo.BlobData ( ModTime, data )
    select dateadd(day, -id, '20120901')
         , convert(varbinary(max), '0x' + @Blob)
    from    cte
go

Данные записались, никаких ошибок о привышении лимита. И в этом нет ничего удивительного, Express версия позволяет использовать FileStream который не имеет никаких органичений по объему хранимых данных.
Вот скрин стандартного отчета по используемуму месту в БД, размер файла данных всего 10 МБ:

А вот как выглядят наши данные в файловой системе

Как хранить более 10 ГБ в SQL Express

Сегодня я хотел бы поговорить о SQL Express, а именно о лимите в 10ГБ на файлы данных. Многие разработчики выбирают эту СУБД для своих проектов. Она имеет достаточно шрокий функционал. Но рано или поздно, они сталкиваются с ограничениями которые не позволяют дальше пользоваться бесплатной версией. Одно из ограничений это лимит на файлы данных. Сегодня я постараюсь ответить на вопрос можно ли как-то обойти это ограничение.

Давайте сначала вспомним что бесплатная Express версия имеет следующие ограничения:

• Может использовать только 1 Физический процессор (но все ядра)
• Использует только 1 ГБ оперативной памяти (на компьютере может быть установлен любой объем) 
• Имеет ограничение 10ГБ на файлы данных (начиная с версии SQL Server 2008 R2)

С процессором и оперативной памятью все понятно. Давайте подробнее остановимся на файлах данных.

• Ограничение касается только фалов данных, но не файлов логов;
• Ограничение распространаяется только на одну базу данных, т.е. вы можете иметь несколько баз на одном инстансе SQL Express;
• Ограничение не распространяется на FileStream.

О FileStream я напишу в следующем посте. А сейчас я расскажу и продемострирую как мы можем распределить данные по нескольким базам существено не меняя наше приложение, тем самым уйдя от 10ГБ лимита. Речь пойдет о базе данных в которых ода или несколько таблиц существено больше остальных, и они занимают львиную долю всего пространства в базе данных.

К примеру у нас в базе имеется таблица dbo.Data следующей структуры, и она занимает в БД более 80% места.

use master;
go

create database BigDb;
go

use BigDb;
go

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (Id)
)

-- заполним базу данных данными, чтобы было с чем работать
;with cte ( id )
as (
    select 1
    union all
    select id + 1
    from cte
    where id < 500000
)
insert into BigDb.dbo.Data 
(
    Id
  , CreatedDate
  , PlaceHolder
)
select
    id
  , dateadd(minute, id, '20120826')
  , 'cte data'
from cte
option ( maxrecursion 0 )

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

Теперь наша задача найти подходящий столбец, по которому возможно было бы разделить данные. Если у вас есть подходящий столбец можно использовать его. Хорошим вариантом был бы какой нибудь тип записи в таблице RecordTypeId, в котором храняться значения от 1-8, тогда можно распределить данные по 8 базам.

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

use master;
go

create database BigDb1;
create database BigDb2;
create database BigDb3;
create database BigDb4;
go


use BigDb1;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 1 )
)
go

use BigDb2;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 2 )
)
go

use BigDb3;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null     
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 3 )
)
go

use BigDb4;

create table dbo.Data
(
    Id int 
  , CreatedDate datetime2(0) not null
  , DB tinyint not null
  , PlaceHolder char(100) not null  
  , constraint PK_Data primary key clustered (id, DB)
  , constraint CH_CreatedDate check ( DB = 4 )
)
go

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

1. Мы добавили столбец DB tinyint not null - это будет наш сурогатный тип записи (разделитель)
2. Мы добавили в структуру таблицы ограничение, чтобы в каждой из таблиц мог храниться только определенный тип записи CH_CreatedDate check ( DB = 4 ) например.
3. Мы включили столбец DB в главный ключ таблицы PK_Data primary key clustered (id, DB)

Далее нам нужно объединить записи из этих таблиц. Это делается c помощью представления dbo.vData. Создаем его в нашей основной базе данных BigDb.

use BigDb;

create view dbo.vData
as  
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb1.dbo.Data d

    union all
 
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb2.dbo.Data d

    union all
 
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb3.dbo.Data d
 
    union all
 
    select Id, DB, CreatedDate, PlaceHolder
    from BigDb4.dbo.Data d 
go

А теперь можно перелить данные через представление.

insert into dbo.vData ( Id, DB, CreatedDate, PlaceHolder )
select
    d.Id
  , d.Id % 4 + 1
  , d.CreatedDate
  , d.PlaceHolder
from dbo.Data d

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

Теперь, можно проверить как данные распределились по базам

select *
from BigDb1.dbo.Data

select *
from BigDb2.dbo.Data

select *
from BigDb3.dbo.Data

select *
from BigDb4.dbo.Data

Теперь старую таблицу нужно удалить, и работаеть с данными через представление dbo.vData, для простоты можете дать представлению тоже название что имела таблица т.е. dbo.Data.

Хорошо давате теперь поэкспериментируем с типичными запросами к данным, и посмотрим на их планы выполнения.

use BigDb;

select * 
from dbo.vData

Как видите были просканированы 4 таблицы которые находятся в разных базах, после чего результаты были объеденены

use BigDb;

select *
from dbo.vData
where id between 100 and 1000

Тожесамое произошло и здесь, только в место сканирования мы имеем поиск в кластерном индексе

Также можно ограничивать и используемые базы

use BigDb;

select *
from dbo.vData
where id between 100 and 1000
  and DB in ( 1, 3 )

Тут произведен поиск в кластерных индексах только двух таблиц

А как насчет не кластерных индексов, они работают? Давайте проверим. Создаем индексы на поле CreatedDate для каждой таблицы.

use BigDb1;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb2;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb3;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb4;
create index IDX_Data_CreatedDate on dbo.Data(CreatedDate)
go

use BigDb;

select
    Id
  , DB
  , CreatedDate
  , PlaceHolder
from dbo.vData
where CreatedDate between '20120827' and '20120828'

Некластерные индексы используютя в плане

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