Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Партицирование с возможностью убрать партицию в архив  [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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить