Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

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

Откуда: Moscow
Сообщений: 31906
selis76
Как понимаю штатных средств сделать отсоединение партиции нет ни в какой версии MS SQL Server.
Как это нет, для чего тогда нужны партиции???
См. ALTER TABLE ... SWITCH ...
7 май 20, 21:21    [22129024]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
alexeyvg
selis76
Как понимаю штатных средств сделать отсоединение партиции нет ни в какой версии MS SQL Server.
Как это нет, для чего тогда нужны партиции???
См. ALTER TABLE ... SWITCH ...

Видел в доке но не понимаю как оно мне может помочь в вопрос освобождения места. Основная цель, легко отстегивать партиции в архив освобождая место на дисках.
7 май 20, 21:28    [22129027]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
alexeyvg
Как это нет, для чего тогда нужны партиции???
См. ALTER TABLE ... SWITCH ...

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

Я бы хотел, что бы в сиквеле можно было отсоединить файлгруппу от базы, и подсоединить её к другой базе - вот это было бы "отстёгивать в архив". Или положить файлы на ленту, а потом, через 10 лет, подсоединить к этой или другой базе.

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

Для этого
1) делаете пустую таблицу, такую же, как партицированная, но без секциониролвания.
2) Потом делаете ALTER TABLE ... SWITCH ..., и данные секции оказываются в этой новой таблице, а партиция освобождается.
3) Далее, если данные не нужны, дропаете эту таблицу (или делаете TRUNCATE) - место освободилось.
4) Далее, если вам не нужна пустая секция, то можете её схлопнуть, выполнив ALTER PARTITION FUNCTION ... MERGE RANGE. Но для освобождения места это не нужно.
5) (если партиции в отдельных файлгруппах) После этого можно либо шринкнуть файл до пустого (если не делать (4)), либо его удалить (если делать (4))
7 май 20, 21:42    [22129032]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
selis76,

"Легко отстегивать" легко. Но вот перемещать в архив придется переливкой. Волшебства, по которому кусок дынных из одного файла "легко" переместится в другой, нет.
7 май 20, 21:43    [22129033]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
alexeyvg
1) делаете пустую таблицу, такую же, как партицированная, но без секциониролвания.
Если партиции в отдельных файлгруппах, то делать таблицу её надо в удаляемой файлгруппе.

alexeyvg
3) Далее, если данные не нужны, дропаете эту таблицу (или делаете TRUNCATE) - место освободилось.
Если партиции в отдельных файлгруппах, то только drop, ибо даже пустая таблица сделает файлгруппу непустой
7 май 20, 21:45    [22129035]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
Гавриленко Сергей Алексеевич
selis76,

"Легко отстегивать" легко. Но вот перемещать в архив придется переливкой. Волшебства, по которому кусок дынных из одного файла "легко" переместится в другой, нет.
Вот это да, печально, а как было бы хорошо пинать файлргуппы между базами
7 май 20, 21:45    [22129036]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4844
selis76,

Давайте начнет с другого. А что вы в точности понимаете под "архивировать" партицию или её данные?
8 май 20, 08:02    [22129130]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
aleks222
Member

Откуда:
Сообщений: 1414
selis76
Посоветуйте лучший вариант.


Тупо делать

delete t 
  output deleted.* into archiveDB..archiveTable
  from mainDB..мainTable as t
  where t.Date < @ArchiveDate


И не выдумывать приключений на собственную задницу.

Сообщение было отредактировано: 8 май 20, 10:01
8 май 20, 10:02    [22129168]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
aleks222
selis76
Посоветуйте лучший вариант.

Тупо делать

delete t 
  output deleted.* into archiveDB..archiveTable
  from mainDB..мainTable as t
  where t.Date < @ArchiveDate


И не выдумывать приключений на собственную задницу.
Простое решение всегда лучше сложного :-)

Но если данных действительно много, то приходится оптимизировать (как у selis76 - непонятно, про размеры он не писал).
8 май 20, 10:24    [22129178]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
alexeyvg
selis76
пропущено...

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

Я бы хотел, что бы в сиквеле можно было отсоединить файлгруппу от базы, и подсоединить её к другой базе - вот это было бы "отстёгивать в архив". Или положить файлы на ленту, а потом, через 10 лет, подсоединить к этой или другой базе.

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

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

Сообщение было отредактировано: 8 май 20, 12:27
8 май 20, 12:19    [22129241]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
aleks222
selis76
Посоветуйте лучший вариант.


Тупо делать

delete t 
  output deleted.* into archiveDB..archiveTable
  from mainDB..мainTable as t
  where t.Date < @ArchiveDate


И не выдумывать приключений на собственную задницу.

Без приключений в моем случае никак, Transaction log нерезиновый когда за год 400 миллионов записей , а если пакетами записей делать, то нужно городить хороший скрипт на TSQL . Delete самая накладная операция , да еще индексы все затормозят а их отключать нельзя, поскольку это буфер сообщений который должен быть почти всегда Online
8 май 20, 12:28    [22129249]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8690
selis76,

если только копировать секцию в новую базу и эту базу бэкапить. Саму секцию очищать при помощи switch/truncate. Обратное действие такое же - восстановление бэкапа и копирование в исходную базу, если требуется, через промежуточную таблицу/switch.

Сообщение было отредактировано: 8 май 20, 13:23
8 май 20, 13:24    [22129319]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
alexeyvg
пропущено...
"Отстёгивать в архив" не совсем понятное задание :-)

Я бы хотел, что бы в сиквеле можно было отсоединить файлгруппу от базы, и подсоединить её к другой базе - вот это было бы "отстёгивать в архив". Или положить файлы на ленту, а потом, через 10 лет, подсоединить к этой или другой базе.

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

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

Увы, просто скопировать файлы секции нельзя. так что только либо копировать данные, либо выгружать bcp. Зеркалирование тут неважно, оно же не заменяет бакапа/выгрузки в архив.

По моему опыту, bcp в нейтив формат будет быстрый.
Секции на месяц будут по полтеррабайта, в общем не особо много, но, конечно, дисковые подсистемы должны соответствовать. У меня были ссуточные секции по 150 гигов, не помню точно, сколько их bcp выгружал, но вроде меньше часа. На слабом железе.

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

Владислав Колосов
если только копировать секцию в новую базу и эту базу бэкапить
Не знаю, будет ли быстрее копирование, чем bcp
Но этот вариант тоже можно проверить.
Прямо делать select * into из отсоединённой секции.
8 май 20, 15:02    [22129438]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
aleks222
Member

Откуда:
Сообщений: 1414
selis76
aleks222
пропущено...


Тупо делать

delete t 
  output deleted.* into archiveDB..archiveTable
  from mainDB..мainTable as t
  where t.Date < @ArchiveDate


И не выдумывать приключений на собственную задницу.

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


Не смешите мои тапочки.

Весь скрипт:
delete top(nnn) ...
8 май 20, 15:18    [22129454]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
msLex
Member

Откуда:
Сообщений: 9022
aleks222
delete top(nnn) ...
Удалять все данные из секции через delete?
Вы всерьез считаете это оптимальным методом?
8 май 20, 15:23    [22129462]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
aleks222
Member

Откуда:
Сообщений: 1414
msLex
aleks222
delete top(nnn) ...
Удалять все данные из секции через delete?
Вы всерьез считаете это оптимальным методом?


Секция страдальцу НЕ нужна.
Кластерный индекс по дате и фсе.
8 май 20, 15:24    [22129463]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
aleks222,

И правда, транкейт для трусов. Он же примерно около 0 байт в лог напишет, не будет долго и упорно на вторичной реплике применяться, не будет долго и упорно в лог бэкапиться. Скукотища, в общем.
8 май 20, 15:27    [22129465]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
aleks222
Секция страдальцу НЕ нужна.
Кластерный индекс по дате и фсе.
DELETE вместо удаления секции, для 500гб ежемесячных данных?
По моему, это как раз тот случай, когда нужно использовать секционирование. Не то, что без него не обойтись (удаление по 20 гб в день мелкими порциями вполне реально, при кластерном индексе по дате), но польза будет.
8 май 20, 15:29    [22129467]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
aleks222
Member

Откуда:
Сообщений: 1414
Гавриленко Сергей Алексеевич
aleks222,

И правда, транкейт для трусов. Он же примерно около 0 байт в лог напишет, не будет долго и упорно на вторичной реплике применяться, не будет долго и упорно в лог бэкапиться. Скукотища, в общем.


Ну, если бы надо было только транкейтнуть. Желания страдальца немного шире.

alexeyvg
aleks222
Секция страдальцу НЕ нужна.
Кластерный индекс по дате и фсе.
DELETE вместо удаления секции, для 500гб ежемесячных данных?
По моему, это как раз тот случай, когда нужно использовать секционирование. Не то, что без него не обойтись (удаление по 20 гб в день мелкими порциями вполне реально, при кластерном индексе по дате), но польза будет.


Если цель: быстро и сразу.
Да флаг ему в руки.
Только куда торопиться?
8 май 20, 16:15    [22129506]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8690
alexeyvg,

пустую базу можно создавать сразу нужного размера, если применить bulk вставку при помощи SSIS, то должно быть быстро. Плюс надежность резервных копий. Хотя у Вас практический опыт применения.
8 май 20, 16:45    [22129524]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
Владислав Колосов
пустую базу можно создавать сразу нужного размера
Это конечно, я и файлы для секций сразу создавал нужного размера

Владислав Колосов
Плюс надежность резервных копий.
По удобству это хорошее решение.
И вообще, можно замутить восстановление (при необходимости) таких баз, и модификацию вьюх для доступа к архивным "секциям" (объединяя архивные данные )
Если стоит задача регулярного доступа к архивным данным, то получится быстро.

PS Мы SSIS не применяли, потому что у нас архивный сервер был доступен только по sftp, поэтому была такая извращённая схема.
PPS А только по sftp, потому что сетевые админы клиента где то случайно закрыли порты, и не знали, где
8 май 20, 17:56    [22129580]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
alexeyvg
Владислав Колосов
пустую базу можно создавать сразу нужного размера
Это конечно, я и файлы для секций сразу создавал нужного размера

Владислав Колосов
Плюс надежность резервных копий.
По удобству это хорошее решение.
И вообще, можно замутить восстановление (при необходимости) таких баз, и модификацию вьюх для доступа к архивным "секциям" (объединяя архивные данные )
Если стоит задача регулярного доступа к архивным данным, то получится быстро.

PS Мы SSIS не применяли, потому что у нас архивный сервер был доступен только по sftp, поэтому была такая извращённая схема.
PPS А только по sftp, потому что сетевые админы клиента где то случайно закрыли порты, и не знали, где

спасибо за развернутый ответ
но как я понимаю bcp индексы не сохраняет потом их ребилдить отдельно - неудобно, но решаемо
19 май 20, 14:44    [22135827]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
Владислав Колосов
alexeyvg,

пустую базу можно создавать сразу нужного размера, если применить bulk вставку при помощи SSIS, то должно быть быстро. Плюс надежность резервных копий. Хотя у Вас практический опыт применения.


В принципе красиво будет, как понимаю switch на другую базу пройдет сразу с индексами
19 май 20, 14:45    [22135831]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4844
selis76
Владислав Колосов
alexeyvg,

пустую базу можно создавать сразу нужного размера, если применить bulk вставку при помощи SSIS, то должно быть быстро. Плюс надежность резервных копий. Хотя у Вас практический опыт применения.


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


А есть способ перекинуть файловую группу или файл БД из одной БД в другую БД.
19 май 20, 14:52    [22135839]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35143
Блог
aleks222
Весь скрипт:
delete top(nnn) ...


Делал так на несекционированных логах, когда их по чуть-чуть переносил в архив. Работало вполне нормально на довольно большом объеме. Но у меня операция выполнялось редко и только во время минимальной активности пользователей.
19 май 20, 15:18    [22135871]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
но как я понимаю bcp индексы не сохраняет потом их ребилдить отдельно - неудобно, но решаемо
Я же в секции загружал. Как описал выше.
То есть bcp в таблицу-кучу, потом строю кластерный индекс, потом переключаю секцию.

Критик
Делал так на несекционированных логах, когда их по чуть-чуть переносил в архив. Работало вполне нормально на довольно большом объеме.
Определение "большой объём" - это когда удаление данных за сутки начинает превышать сутки :-)

А так то да, если можно просто удалить, чего зря городить код?
19 май 20, 18:42    [22135994]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
alexeyvg
selis76
но как я понимаю bcp индексы не сохраняет потом их ребилдить отдельно - неудобно, но решаемо
Я же в секции загружал. Как описал выше.
То есть bcp в таблицу-кучу, потом строю кластерный индекс, потом переключаю секцию.
.... ?

А как боролись с объемом файлов данных которые порождает BCP - ? клали в папки типа Compressed folders?
20 май 20, 13:15    [22136328]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
alexeyvg
пропущено...
Я же в секции загружал. Как описал выше.
То есть bcp в таблицу-кучу, потом строю кластерный индекс, потом переключаю секцию.
.... ?

А как боролись с объемом файлов данных которые порождает BCP - ? клали в папки типа Compressed folders?
Надо было, но как то не заморачивался :-) На суточную порцию места хватало.
Потом, перед копированием, коненчно, сжимал.
20 май 20, 13:56    [22136357]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
uaggster
Member

Откуда:
Сообщений: 1030
Интересно, а так - будет работать:
1. Создаем таблицу в другой БД.
2. Создаем к ней алиас в основной БД
3. Переименовываем таблицу в основной базе.
4. Создаем view в основной базе, и называем его как таблицу в основной базе:
Select * from таблица_в_основной_базе
Where ключ_по_которому_разрезана_таблица between всё_что_относится_к_основной_таблице
union all
Select * from алиас
Where ключ_по_которому_разрезана_таблица between всё_что_относится_к_архивной_таблице

И в дальнейшем везде оперируем только view.
Ну и, собственно, загрузка старых данных - это bcp в рядомстоящую таблицу в архивной базе, а потом alter table switch.
22 май 20, 07:48    [22137448]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
uaggster
Интересно, а так - будет работать:
1. Создаем таблицу в другой БД.
2. Создаем к ней алиас в основной БД
3. Переименовываем таблицу в основной базе.
4. Создаем view в основной базе, и называем его как таблицу в основной базе:
Select * from таблица_в_основной_базе
Where ключ_по_которому_разрезана_таблица between всё_что_относится_к_основной_таблице
union all
Select * from алиас
Where ключ_по_которому_разрезана_таблица between всё_что_относится_к_архивной_таблице

И в дальнейшем везде оперируем только view.
Ну и, собственно, загрузка старых данных - это bcp в рядомстоящую таблицу в архивной базе, а потом alter table switch.
Да, конечно.
Сиквел на это специально рассчитан, такая вьюха называется "Секционированное представление" (на разных серверах "Распределённое секционированное представление"), и сервер оптимизирует обращения к отдельным его "частям".

После появления секционирования этим представлениям стали меньше уделять внимания, но раньше это был единственный способ секционирования.
22 май 20, 10:47    [22137537]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
Подскажите пожалуйста способы наиболее эффективного бэкапа для партицированных таблиц, которые используют файловые группы в циклическом режиме
Т.е. заниматься частым архивированием только активной партиции
+ иметь возможность 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
17 мар 21, 13:43    [22296190]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
Подскажите пожалуйста способы наиболее эффективного бэкапа для партицированных таблиц, которые используют файловые группы в циклическом режиме
Т.е. заниматься частым архивированием только активной партиции
...
Я понял что можно переводить в режим read only только файловые группы и архивировать именно файловые группы, но я не вижу команд для архивирования просто партиций . Просто при циклическом переключении файловых групп неудобно их переводить в Read only и обратно (нужно следить скриптом)
SQL Server 2019
Под архивированием вы подразумеваете бакап?
Без отдельных файловых групп никак.
Сиквел не имеет функциональности (как и любые другие СУБД) делать бакап и рестор отдельных секторов в файле базы данных.
selis76
+ иметь возможность truncate данные одной партиции и восстановить ее без манипуляций со switch
"Транкейт партиции без манипуляций со switch" по моему появился в последних версиях.
"Восстановить" нельзя, т.к. нельзя бакапить и рестроить отдельные сектора в файле БД, как я уже говорил.
17 мар 21, 14:36    [22296236]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
komrad
Member

Откуда:
Сообщений: 5699
alexeyvg

"Восстановить" нельзя, т.к. нельзя бакапить и рестроить отдельные сектора в файле БД, как я уже говорил.

для полноты картины, можно восстанавливать страницы файла
RESTORE DATABASE { database_name }   PAGE = 'file:page [ ,...n ]'...
17 мар 21, 14:42    [22296241]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
komrad
alexeyvg
"Восстановить" нельзя, т.к. нельзя бакапить и рестроить отдельные сектора в файле БД, как я уже говорил.

для полноты картины, можно восстанавливать страницы файла
Не, ну это ковыряние ручками, а не бакап-рестор.
Тут команда называется "RESTORE DATABASE", да, но при этом но не является рестором базы (или её части) из бакапа...
17 мар 21, 14:49    [22296252]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
alexeyvg

Под архивированием вы подразумеваете бакап?
Без отдельных файловых групп никак.

Да именно бэкап. База является буфером который наполняется последовательно , и поэтому хотелось сократить время резервного копирования + иметь возможность оперативно убирать \восстанавливать старые партиции.
Но видимо придется делать обычную схему - полный бэкап раз в неделю + инкрементальный + дневной бэкап логов.
А с архивными партициями управляться через BCP , конечно неудобно что в MS SQL нет удобного механизма подключения и отключения архивных партиций а только через BCP + Switch .
17 мар 21, 16:19    [22296326]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35143
Блог
selis76,

подумайте над архитектурой вашего ПО, к примеру, если уйти от хранения xml в СУБД, то и куча ваших проблем также исчезнет
17 мар 21, 16:35    [22296343]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8690
selis76,

наиболее эффективный путь - это перенос "секций" в отдельную базу + секционированное представление.
17 мар 21, 16:41    [22296348]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3041
selis76
alexeyvg

Под архивированием вы подразумеваете бакап?
Без отдельных файловых групп никак.

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


посмотрите тут Partial Backups
18 мар 21, 08:56    [22296602]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
Надо сказать 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
28 май 21, 22:18    [22328700]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
для switch in приходится городить констрейнт
Да, это в МС не доделали, конечно.
Но всё равно же быстрее, чем вставка/удаление обычными insert delete.

selis76
Получается если чтото историческое хочешь отправить в архив проще
Отличный вариант - секционированные представления и много баз (эмуляций секций).
Намного производительней, проще, гибче, и надёжней. Притом всё это несравнимо, не "чуть чуть"
Но с партициями тоже можно кое как работать, если приноровиться.
28 май 21, 22:49    [22328709]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8690
selis76,

зачем гонять из архива секции, я выше написал магические слова - "секционированное представление". Достаточно изменить представление, чтобы архив стал доступен. Читаете, но не видите... И Алексей об этом же пишет.
28 май 21, 23:28    [22328717]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
Владислав Колосов
selis76,

зачем гонять из архива секции, я выше написал магические слова - "секционированное представление". Достаточно изменить представление, чтобы архив стал доступен. Читаете, но не видите... И Алексей об этом же пишет.


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

Учитывая это решил, что заставлять оптимизатор Microsoft работать правильно сложнее чем загнать архивные данные через insert если нужно.
31 май 21, 14:14    [22329336]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Кесарь
Member

Откуда:
Сообщений: 651
selis76,

а что мешает вам сделать два представления, для архива и для текущей базы? А уже потом соединят их результаты так или иначе.
31 май 21, 14:18    [22329340]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Alexander Us
Member

Откуда:
Сообщений: 1158
selis76,

Похожая проблема на oltp базе.

Пока что делаю топорно пару раз в год:

- создаю новую пустую таблицу
(в некоторых случаях переливаю часть данных, создаю индексы, синхронизтрую последние записи)

- переименовываю обе, чтобы вставки шли в новую

- копирую старую в архив и удаляю

Это работает, так как данные в этих таблицах не меняются.

Партиционированные виды не применяю, т.к. не выполнить условие по PK.

Партиционированные таблицы (скользящее оконо) не применяю, так как:
таблица периодически меняется, в случае ошибки с организацией скользящего окна ляжет приложение
т.е. что бы что то изменить, нужен человек с опытом, нужно тестировать - т.е. затраты времени на небольшую сервисную функцию.
31 май 21, 15:02    [22329377]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
selis76
1) Чтобы соединить два набора данных (архивная + актуальная таблицы) и это будет View с union причем таблиц из разных баз одного инстанса . Т.е. для оптимизатора это будут по сути разные таблицы с разными индесами (даже если там все логически одинаково) и он наверняка будет всегда делать поиск по обоим всегда
Всё правильно вы описали, то есть всё будет работать так же, как для секционированной таблицы.
selis76
2) Мне может понадобится восстановить более чем один диапазон из архива из разных бэкапов.
Вы неправильно поняли.
Вы можете иметь произвольное количество архивных копий, и подсоединять их, модифицируя само представление. Можете включить один архив в представление/ можете все сразу, варианты ограничены только вашей фантазией и железом.

Например, у вас есть база с транзакциями за активный период, и базы за прошедшие месяцы.
Архивные базы хранятся на ленте.
Вы копируете с ленты баз за феврали с 2001 по 2010 год, потом оп - за 1 мс меняется представление ,у вас доступны активный период, плюс феврали за 10 лет.

С секционировнаной таблицей для того же самого нужно будет дополнительно восстанавливать бакапы, плюс делать чек констрэйн. Присоединять секции снаружи там нельзя, чек-констрейн оно тоже почему то не сохраняет при отоединении.
selis76
Учитывая это решил, что заставлять оптимизатор Microsoft работать правильно сложнее чем загнать архивные данные через insert если нужно.
А вы пробовали? По моему, он однаково работает с секционированными представлениями и таблицами. Если может, обращается к одной секции (таблице), если не может, то ко всем
Кроме того, не преувеличивайте негативные последствия обращения ко всем секциям, там накладные расходы проявляются только для огромного потока сверх-лёгких обращений.
31 май 21, 17:56    [22329473]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Кесарь
Member

Откуда:
Сообщений: 651
alexeyvg,

я правильно понял, что вы предлагает менять код на проде в онлайн режиме, так сказать? Это универсальный совет или только для случая ленточных баз? С ними я дела не имел, поэтому специфики не знаю. Мы на лентах только бекапы хранили, и то это было 15 лет назад.
1 июн 21, 11:30    [22329730]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
selis76
Member

Откуда:
Сообщений: 387
Еще один подводный камень с партициями.
Нужно сделать массовую загрузку данных в несколько свободных партиций, а для этого нужно отключить все индексы кроме кластреного (для скорости)
И вот что получается - ребилд по партициям можно сделать , а 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 с этим, по опыту использования там всегда продуманней механизмы были
11 июн 21, 15:48    [22334476]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
Кесарь
Это универсальный совет или только для случая ленточных баз?
Это совет для тех случаев, когда использование секционирования не помогает решить задачу, а секционированные представления помогают. Только лишь совет не забывать про секционированные представления.
11 июн 21, 23:07    [22334604]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35143
Блог
selis76,

Вообще-то все "некурильщики" загружают в отдельные пустые таблицы, которые потом получают нужные индексы и переключаются в основную таблицу. Если лень пересоздавать кластерный индекс, то можно направить в таблицу уже отсортированный поток
12 июн 21, 13:33    [22334698]     Ответить | Цитировать Сообщить модератору
 Re: Партицирование с возможностью убрать партицию в архив  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31906
Критик
Вообще-то все "некурильщики" загружают в отдельные пустые таблицы, которые потом получают нужные индексы и переключаются в основную таблицу.
selis76 и пишет, что так делает, но удобнее было бы иметь возможность работать непосредственно с секцией, и я с этим полностью согласен.
12 июн 21, 13:49    [22334699]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Microsoft SQL Server Ответить