Напильник и щепотка фантазии… или как слепить Enterprise из SQL Server Express Edition

[jobgemws]

Большое спасибо за интересный материал!
Ещё бы добавить сценарий как разделяют большую базу данных на маленькие в Express, т к есть ограничения на размеры (много БД и одна с представлениями, в которой объединяются все нужные таблицы из всех малых БД), а также как происходит поддержка всего этого (резервное копирование и восстановление синхронно по малым БД).
Также рекомендую скрипты и длинные выводы по ним вложить в спойлеры, а то немного глаза болят, когда код с текстом перемешаны и коды скриптов по умолчанию не свёрнуты.

[AlanDenton]

Ещё бы добавить сценарий как разделяют большую базу данных на маленькие в Express, т к есть ограничения на размеры (много БД и одна с представлениями, в которой объединяются все нужные таблицы из всех малых БД)

Это в статье есть. Привел пример вертикального шардирования в рамках одного инстанса.

также как происходит поддержка всего этого

Увы тут быстрого ответа не выйдет — все зависит от ситуации. Например, всякие там AlwaysOn на Express Edition не сделаешь. С другой стороны, что мешает перевести базу в Full Recovery делать бекапы лога и их через PowerShell разворачивать на другом сервере для копии этой базы в режиме Read-Only. Не скажу что идеально, но дешевый аналог отказоустойчивости под репортную нагрузку себя оправдывает.

Также рекомендую скрипты и длинные выводы по ним вложить в спойлеры

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

[jobgemws]

Решения по поддержке уже есть и используются.
Будет время-напишу.

[abatishchev]

Это тот редкий случай, когда обновление от мелкомягких стоит того, чтобы его установить.

Вы не правы :) Всё значительно несколько сложнее.

[AlanDenton]

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

[abatishchev]

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

[AlanDenton]

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

[unfilled]

Спасибо за статью. Имхо, стоит добавить, что и для columnstore индексов, и для in-memory oltp в express edition даже более смешные ограничения, чем на всё остальное.
upd: Увидел, что про ограничения для columnstore написано, сорри.

[AlanDenton]

Касательно ограничений колумнсторов и In-Memory стало интересно и потестировал:

Скрипт создания базы

USE [master]
GO

SET NOCOUNT ON
SET STATISTICS IO, TIME OFF

IF DB_ID('express') IS NOT NULL BEGIN
    ALTER DATABASE [express] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [express]
END
GO

CREATE DATABASE [express]
ON PRIMARY (NAME = N'express', FILENAME = N'D:\express.mdf', SIZE = 200 MB, FILEGROWTH = 100 MB)
    LOG ON (NAME = N'express_log', FILENAME = N'D:\express_log.ldf', SIZE = 200 MB, FILEGROWTH = 100 MB)

ALTER DATABASE [express] SET AUTO_CLOSE OFF
ALTER DATABASE [express] SET RECOVERY SIMPLE
ALTER DATABASE [express] SET MULTI_USER
ALTER DATABASE [express] SET DELAYED_DURABILITY = ALLOWED
ALTER DATABASE [express] ADD FILEGROUP [MEM] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [express] ADD FILE (NAME = 'MEM', FILENAME = 'D:\MEM') TO FILEGROUP [MEM]
ALTER DATABASE [express] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO 

USE [express]
GO

DROP TABLE IF EXISTS [CCL]
CREATE TABLE [CCL] (
      [INT]      INT NOT NULL
    , [VARCHAR]  VARCHAR(100) NOT NULL
    , [DATETIME] DATETIME NOT NULL
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX ON [CCL] WITH (DATA_COMPRESSION = COLUMNSTORE)
GO

DECLARE @i INT = 0

lbl:
    ;WITH E1(N) AS (SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
        , E2(N) AS (SELECT '1' FROM E1 a, E1 b)
        , E4(N) AS (SELECT '1' FROM E2 a, E2 b)
        , E8(N) AS (SELECT '1' FROM E4 a, E4 b)
    INSERT INTO [CCL] WITH(TABLOCK) ([INT], [VARCHAR], [DATETIME])
    SELECT TOP(5000000)
          ROW_NUMBER() OVER (ORDER BY 1/0)
        , CAST(ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(100))
        , DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY 1/0) % 100, '20180101')
    FROM E8

    SET @i += 1

IF @i < 20 GOTO lbl

SELECT 'DATA', CAST(SUM(size_in_bytes) / (1024. * 1024) AS DECIMAL(18,2))
FROM sys.indexes i
CROSS APPLY sys.fn_column_store_row_groups(i.[object_id]) s
WHERE i.[type] IN (5, 6)

UNION ALL

SELECT 'DICT', CAST(SUM(on_disk_size) / (1024. * 1024) AS DECIMAL(18,2))
FROM sys.column_store_dictionaries
GO

DROP TABLE IF EXISTS [MEM]
CREATE TABLE [MEM] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX2 NONCLUSTERED, C VARCHAR(4000))
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

DECLARE @i INT
      , @s DATETIME
      , @runs INT = 10

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs BEGIN

    ;WITH E1(N) AS (SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
        , E2(N) AS (SELECT '1' FROM E1 a, E1 b)
        , E4(N) AS (SELECT '1' FROM E2 a, E2 b)
        , E8(N) AS (SELECT '1' FROM E4 a, E4 b)
    INSERT INTO [MEM] ([A], [B], [C])
    SELECT TOP(500000)
          ROW_NUMBER() OVER (ORDER BY 1/0) + (@i * 1000000)
        , GETDATE()
        , NEWID()
    FROM E8

    SET @i += 1
END

SELECT *
FROM sys.dm_db_xtp_table_memory_stats

Если говорить о колумнсторах, то там размер данных и словарей может превышать 350Мб:

---- ----------
DATA 509.02
DICT 1072.04

При этом для In-Memory таблиц при превышении размера в 350Мб получаем такую ошибку:

Msg 41823, Level 16, State 109, Line 85
Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation. See 'http://go.microsoft.com/fwlink/?LinkID=623028' for more information.

[unfilled]

Спасибо!
Насколько я понимаю ситуацию с columnestor'ами — размер сегмента не ограничивается (хотя не знаю не пытается ли он сегмент целиком прочитать и что будет, если не сможет), но в памяти больше чем 352 МБ «держаться» не будет. Причём ограничение там на экземпляр, т.е. несколько DWH-запросов в разные БД могут сильно конкурировать за память.

[vagon333]

А еще, можно запускать SQL Server Express под Linux.
У меня multi-tenant SaaS, когда каждый клиент, в силу regulatory requirements, требует отдельную базу.
Так вот, если грамотно наладить IaC, то для каждого клиента на проде можно создавать бесплатный сервер в ubuntu контейнере с хранением баз на внешнем volume, который прекрасно выдерживает нагрузки одного клиента и бэкапит базки (не через агент, которого нет в Express, а через sp_procoption).

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

Если на одном сервере разрешены несколько ELT процессов одновременно, может легко забрать память.

[AlanDenton]

К слову было бы интересно потестировать In-Memory таблицы с SCHEMA_ONLY в условиях нехватки памяти. Скажем, что будет если превышается предел в 350Мб

[3263927]

ого! вот это да! настоящий взлом! спасибо очень интересно! даже не подозревал что можно так делать, для небольших проектов действительно хорошее решение

[jobgemws]

Больше скажу: такие подходы применяются и для очень больших распределенных решений