Посоветуйте лучший вариант.
Есть большая таблица которую можно партицировать по месяцам (буфер сообщений) . Т.е. изменений в прошлых месяцах не бывает.
Хочу реализовать партицирование с возможностью убирать данные из прошлых месяцев безвозвратно в архив для освобождения места т.е. если они понадобятся восстановить одну партицию.
Мне пока пришел в голову только один вариант
а) Сделать партицированную таблицу перелить туда нужные данные.
б) По прошествии нескольких месяцев - прошлый период заархивировать , а после выполнить команду TRUNCATE TABLE PartitionTable1 WITH (PARTITIONS (2,
в) Сделать shrink файловой группы заархивированной партиции тем самым освободив место
Из минусов - truncate по партиции поддерживается только в SQL Server 2016 (13.x), а у меня 2008r2 но это решаемо.
Как понимаю штатных средств сделать отсоединение партиции нет ни в какой версии MS SQL Server.
Возможно есть какие то еще варианты?

Видел в доке но не понимаю как оно мне может помочь в вопрос освобождения места. Основная цель, легко отстегивать партиции в архив освобождая место на дисках.

Задача простая -
взять секцию данных (за месяц) положить на ленту, а если ктото попросит архивные данные вернуть их в секцию той же таблицы. Поскольку это последовательный буфер сообщений, причины возврата секции могут быть разные напр нужно переимпортировать данные в незакрытом годе по новому алгоритму, либо аудиторы попросят исходные данные на основе которых были транзакции. Эти причины сравнительно редкие, а объем за год 400 миллионов записей с XML полем, и гдето 5 терабайт. Bcp даже месяц таких данных тяжело переваривает, а зеркалировать это для отказоустойчивости накладно.

Без приключений в моем случае никак, Transaction log нерезиновый когда за год 400 миллионов записей , а если пакетами записей делать, то нужно городить хороший скрипт на TSQL . Delete самая накладная операция , да еще индексы все затормозят а их отключать нельзя, поскольку это буфер сообщений который должен быть почти всегда Online

спасибо за развернутый ответ
но как я понимаю bcp индексы не сохраняет потом их ребилдить отдельно - неудобно, но решаемо

В принципе красиво будет, как понимаю switch на другую базу пройдет сразу с индексами

А как боролись с объемом файлов данных которые порождает BCP - ? клали в папки типа Compressed folders?

Подскажите пожалуйста способы наиболее эффективного бэкапа для партицированных таблиц, которые используют файловые группы в циклическом режиме
Т.е. заниматься частым архивированием только активной партиции
+ иметь возможность truncate данные одной партиции и восстановить ее без манипуляций со switch
Из
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

https://blog.coeo.com/five-cool-things-you-can-do-using-partitioning

Я понял что можно переводить в режим read only только файловые группы и архивировать именно файловые группы, но я не вижу команд для архивирования просто партиций . Просто при циклическом переключении файловых групп неудобно их переводить в Read only и обратно (нужно следить скриптом)
SQL Server 2019

Да именно бэкап. База является буфером который наполняется последовательно , и поэтому хотелось сократить время резервного копирования + иметь возможность оперативно убирать \восстанавливать старые партиции.
Но видимо придется делать обычную схему - полный бэкап раз в неделю + инкрементальный + дневной бэкап логов.
А с архивными партициями управляться через BCP , конечно неудобно что в MS SQL нет удобного механизма подключения и отключения архивных партиций а только через BCP + Switch .

Надо сказать switch это еще то приключение даже если делаешь таблицы как копия метаданных
switch out проходит норм , а для switch in приходится городить констрейнт

ALTER TABLE [dbo].[msg_buffer_temp] ADD CONSTRAINT Range_msg_buffer_temp CHECK (MsgCounter>2560000000 AND MsgCounter<=2590000000);
иначе MS SQL выдает ошибку "allow values that are not allowed by range defined by partition"

Т.е. получается что партицирование как инструмент удобного перевода исторических данных в архив и обратно (по партициям) не такое уж и удобное.
1) Alter table switch работает только в рамках одной файловой группы в другую базу его не сделаешь
2) Бэкап, truncate , восстановление нужной партиции можно сделать только через BCP - долго
3) Бэкап\восстановление отдельных партиций через файловые группы можно делать только если она в read -only, т.е последующий транкейт не сделаешь.

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


Получается если чтото историческое хочешь отправить в архив проще
1) Сделать архив базы buffer
2) Затранкейтить ненужные партиции
Если понадобится восстановить отдельную партицию
3) При необходимости восстановить его в том же Instance SQL под именем buffer_backup
4) Далее через insert перенести данные в нижний диапазон. Хорошо что insert почти не раздувает transaction log

Я читал про секционированное представление, но для себя решил пока его не использовать по 3м причинам
1) Чтобы соединить два набора данных (архивная + актуальная таблицы) и это будет View с union причем таблиц из разных баз одного инстанса . Т.е. для оптимизатора это будут по сути разные таблицы с разными индесами (даже если там все логически одинаково) и он наверняка будет всегда делать поиск по обоим всегда.
2) Мне может понадобится восстановить более чем один диапазон из архива из разных бэкапов.
3) У меня много запросов с разными комбинациями условий и join + чтобы избежать обращения ко всем партициям (известная особенность) приходится везде ставить условие на основное поле партицированного индекса MsgCounter.

Учитывая это решил, что заставлять оптимизатор Microsoft работать правильно сложнее чем загнать архивные данные через insert если нужно.

Еще один подводный камень с партициями.
Нужно сделать массовую загрузку данных в несколько свободных партиций, а для этого нужно отключить все индексы кроме кластреного (для скорости)
И вот что получается - ребилд по партициям можно сделать , а Disable уже нет только для всего индекса
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15

У microsoft формально есть решение с 2005 года, типа грузите в стейдж таблицу, там ребилд индексов а далее переключите ее в партицию
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966380(v=technet.10)
т.е. если еще нужно констрейнты по диапазонам ставить то уже эта акробатика напрягает. Вроде бы партиции специально придумали для изолированной работы с партициями , но по факту без stage таблиц ничего не живет. Сразу захотелось поизучать как дела у Oracle с этим, по опыту использования там всегда продуманней механизмы были