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

Откуда: Москва
Сообщений: 196
Добрый день.

Веб-разработке нужна обрезанная копия рабочей базы с возможностью авто-обновления её при изменении схемы продуктива, от пользовательских данных в таблицах нужен минимум (типа топ 100). Сервера разные.

Я вижу реализацию похожего через однократное восстановление полной копии рабочей базы с последующей очисткой пользовательских данных до приемлемого размера. Потом уже можно настроить создание доп. копий из неё на убиение и тесты.
В этом варианте будут и все объекты и связи нужной БД, нужное количество данных в таблицах. Из минусов - изменение структуры нужно будет накатывать на шаблон. Одновременно плюс - может будут всё на тестовой пробовать сначала, а не часть на тесте, часть на проде.

Но рассматриваю и другие варианты. Например, говорят, что когда-то пробовали сделать вариант с обновлением схемы с продуктива, без бекапов - восстановления.
Не очень понимаю, как именно думали такое реализовать. Тот же SQL Server Import and Export позволяет перенести данные таблиц и вьюх, но по умолчанию, вроде, только часть данных нельзя проставить. Возможно через вариант с t-sql и топ (100) вместо графики можно обойти это. Но если настроить авто-режим обновления и внести глобальные изменения на проде, то без переделки вроде перестанет отрабатывать. Тестовый запуск все никак не закончится, а то ещё не уверен перенесет ли синонимы, процедуры и т.д. Насколько помню, скорее - нет.

Кроме варианта с однократным восстановлением полной копии и последующими с ней действием можно ли как-то получить желаемое?
29 мар 19, 11:31    [21846901]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4364
Danion
Добрый день.

Веб-разработке нужна обрезанная копия рабочей базы с возможностью авто-обновления её при изменении схемы продуктива, от пользовательских данных в таблицах нужен минимум (типа топ 100). Сервера разные.

Я вижу реализацию похожего через однократное восстановление полной копии рабочей базы с последующей очисткой пользовательских данных до приемлемого размера. Потом уже можно настроить создание доп. копий из неё на убиение и тесты.
В этом варианте будут и все объекты и связи нужной БД, нужное количество данных в таблицах. Из минусов - изменение структуры нужно будет накатывать на шаблон. Одновременно плюс - может будут всё на тестовой пробовать сначала, а не часть на тесте, часть на проде.

Но рассматриваю и другие варианты. Например, говорят, что когда-то пробовали сделать вариант с обновлением схемы с продуктива, без бекапов - восстановления.
Не очень понимаю, как именно думали такое реализовать. Тот же SQL Server Import and Export позволяет перенести данные таблиц и вьюх, но по умолчанию, вроде, только часть данных нельзя проставить. Возможно через вариант с t-sql и топ (100) вместо графики можно обойти это. Но если настроить авто-режим обновления и внести глобальные изменения на проде, то без переделки вроде перестанет отрабатывать. Тестовый запуск все никак не закончится, а то ещё не уверен перенесет ли синонимы, процедуры и т.д. Насколько помню, скорее - нет.

Кроме варианта с однократным восстановлением полной копии и последующими с ней действием можно ли как-то получить желаемое?



В SSIS есть блоки по копированию базы. Потом в пакете урезаете нужные записи.

Есть CASCADE, чтобы можно было посносить данные махом.

ALTER TABLE T2
ADD CONSTRAINT fk_employee
FOREIGN KEY (employeeID)
REFERENCES T1 (employeeID)
ON DELETE CASCADE;
29 мар 19, 17:51    [21847452]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30700
Danion
Кроме варианта с однократным восстановлением полной копии и последующими с ней действием можно ли как-то получить желаемое?
Вообще первое, что приходит в голову - репликация. А совсем не копирование, обрезание и т.п.
29 мар 19, 18:11    [21847482]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30700
Danion
нужна обрезанная копия рабочей базы
А, обрезанная - имеется в виду "по данным", а не "часть таблиц"?
Тогда репликация не тот вариант.
29 мар 19, 18:13    [21847483]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Владислав Колосов
Member

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

вы штаны через голову надеваете. Ведите разработку в проекте VS SSDT, а не на сервере и проблем не будет с развертыванием версий.
29 мар 19, 22:51    [21847627]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33228
Блог
Danion
Веб-разработке нужна обрезанная копия рабочей базы


Причину скажите, от этого и будет зависеть ответ.
29 мар 19, 23:10    [21847636]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

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

вы штаны через голову надеваете. Ведите разработку в проекте VS SSDT, а не на сервере и проблем не будет с развертыванием версий.
Ему же не надо "разворачивать версии", ему нужна копия с обрезанными данными. Независимо от способа работы с проектом, например, в VS
30 мар 19, 09:41    [21847706]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Владислав Колосов
Member

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

нууу существуют практики загрузки продуктивных справочников в развернутую пустую базу при помощи SSIS пакета. Такой пакет создается максимум за рабочую неделю. Пользовательские же данные формируются непосредственно при тестировании.
30 мар 19, 13:43    [21847780]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

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

нууу существуют практики загрузки продуктивных справочников в развернутую пустую базу при помощи SSIS пакета. Такой пакет создается максимум за рабочую неделю. Пользовательские же данные формируются непосредственно при тестировании.
Это какая то сферическая разработка в ваакуме :-)

Для разработки (и тестирования как части процесса) нужна нормальные, полноценные данные.
"Сформировать при тестировании" их слишком сложно, так никогда не делают, потому что для этого придётся повторить работу пользователей за достаточно большое время.

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

Очевидно, у ТС никакой секретности нет, так зачем тратить огромные деньжищи?

Видимо, размеры базы немаленькие, раз они не хотят просто использовать копию продуктовой базы (что самое правильное); ну, тогда можно обрезать, причём скрипт обрезания скорее всего будет очень прост, ведь наверняка подавляющее большинство данных лежат в небольшом числе таблиц.
30 мар 19, 15:50    [21847851]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33228
Блог
alexeyvg
Видимо, размеры базы немаленькие, раз они не хотят просто использовать копию продуктовой базы (что самое правильное); ну, тогда можно обрезать, причём скрипт обрезания скорее всего будет очень прост, ведь наверняка подавляющее большинство данных лежат в небольшом числе таблиц.


Если база действительно большая, то шринк может длиться неделями, что наверняка больше периода обновления тестовой базы.
30 мар 19, 16:32    [21847871]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30700
Критик
alexeyvg
Видимо, размеры базы немаленькие, раз они не хотят просто использовать копию продуктовой базы (что самое правильное); ну, тогда можно обрезать, причём скрипт обрезания скорее всего будет очень прост, ведь наверняка подавляющее большинство данных лежат в небольшом числе таблиц.

Если база действительно большая, то шринк может длиться неделями, что наверняка больше периода обновления тестовой базы.
Конечно, есть и другие варианты. Можно и перелить, конечно.
Но это уж совсем большая база должна быть, что бы шринк делался неприемлемо долго... А писать скрипты для переливки потребует неслабых затрат.
30 мар 19, 17:49    [21847908]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
vikkiv
Member

Откуда: London
Сообщений: 2608
Реализация желаемого сценария зависит от сложности физической модели (связи и некоторые пункты из ACID) а так-же целей (использования)
которые преследует копия/её создание (какие именно свойства требуются от копии) если например делать топ хххх то вполне вероятно будет
нарушение consistency и из-за проблем с несоответствием foreign key на разных концах так что получится вылет с ошибкой.

в общем T-SQL скрипт как раньше и говорили нужно будет писать под цели (разработка на репрезентативных выборках / анализ
процессов, dev отчетности, data profiling , просто меньший объем для большего быстродействия при разработке и пр.)

а так самый простой вариант как уже выше указали - синхронизация схемы (репликация, ну или полностью снести/накатить,
сравнение и изменение только отличающихся объектов да добавление/чистка отсутствующих будет более муторно)
и наполнение данными (алгоритм зависит от целей как указанно выше) - т.к. обычно truncate или delete.. на измененных объектах
с восполнением недостающих или измененных полей - еще та проблема требующего индивидуального подхода в зависимости от случая
30 мар 19, 21:25    [21847992]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Massa52
Member

Откуда:
Сообщений: 373
Danion,
У нас используются Prod и Test среды. Тестовая база создавалась из Prod полным беккапом,
потом на тест запустили долгоиграющий скрипт, который порционно удалял данные.
Ну и когда тестовая скукожилась до разумных размеров, при этом поток свежих данных поступал и
на продакшен и на тест одновременно. Тем самым достигалось совпадение на обеих базах отчетов на глубину в 3 месяца.
Ну и в конце поставили джоб который ежедневно на тестовой базе устаревшие данные.
31 мар 19, 02:57    [21848122]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30700
Massa52
У нас используются Prod и Test среды. Тестовая база создавалась из Prod полным беккапом,
потом на тест запустили долгоиграющий скрипт, который порционно удалял данные.
Ну и когда тестовая скукожилась до разумных размеров, при этом поток свежих данных поступал и
на продакшен и на тест одновременно. Тем самым достигалось совпадение на обеих базах отчетов на глубину в 3 месяца.
У нас используются Prod, Test и Dev среды
Dev делался как у вас Test, но далее не поддерживался синхронно (процедура время от времени повторяется, раз в год, например).
А Test просто восстанавливается из бакапа Prod, потому что бакап всё равно надо проверять, ну и заодно можно надёжно обкатывать скрипты деплоя, и тестировать новую версию, а так же ковырять сложные ошибки.
Считаю такой вариант оптимальным, кроме разве что очень больших баз, для которых тестовое восстановление бакапа никогда не делается, в связи с невозможностью предоставить для этого сервер.
31 мар 19, 15:48    [21848288]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Сруль.
Member

Откуда:
Сообщений: 119
Примерил эту задачку на себя.
Возьмите поправку, что я всё стараюсь делать по дурному.
Если бы мне надо было отлавливать изменения в структуре таблиц,
сделал бы так
Связал бы маленькую базу с большой линк сервером.
Вспомнил бы простую комманду

select * into table2 from table1

table2 создаётся и повторяет структуру table1.

Значит перед запуском (ну понятно, что table1 тянется через линк
сервер со всеми своими сокращениями) надо сделать глобальный дроп или ренэйм.
Прогнать скрипт с индексами и типа всё.

А софты, т.е. процедуры, вью, функции я бы автоматике не доверил.
Тем более, что массивно их не меняют, так от случая к случаю.
1 апр 19, 09:36    [21848669]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Danion
Member

Откуда: Москва
Сообщений: 196
Спасибо всем за ответы.

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

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

Размер базы 1 ТБ+, есть таблицы 200+млн строк, для проверки столько не нужно. При этом хотят при необходимости быстро создавать новые базы и удалять после. Вот вариант с одной шаблонной базой, с которой будут делаться клоны и показался условно подходящим.

"если например делать топ хххх то вполне вероятно будет
нарушение consistency и из-за проблем с несоответствием foreign key на разных концах так что получится вылет с ошибкой" а вот это может вылезти... foreign key на таблицах есть.

Massa52,
"при этом поток свежих данных поступал и
на продакшен и на тест одновременно." А это через что достигаете?

alexeyvg,
Вроде похоже должно выйти на Ваш вариант.

С SSIS работал немного, есть смысл в этом направлении копать с данной задачей?
1 апр 19, 09:48    [21848679]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2374
Danion
Спасибо всем за ответы.

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

на репле можно настроить фильтр
1 апр 19, 12:08    [21848818]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30700
StarikNavy
Danion
Репликация же подразумевает, что базы синхронизируются, а тут тест по размеру таблиц должен быть урезан.
на репле можно настроить фильтр
Это очень непросто сделать, учитывая, что таблиц много, и они связанны.

Разве что основной объём приходится на пару таблиц, тогда да. Поставить фильтры на них, а остальное пусть будет необрезанным.

Хотя повторю своё ИМХО, лучше вариант, описанный выше, без вот этих сложных плясок. По крайней мере, если нет достаточно точно подсчитанной большой выгоды.
1 апр 19, 12:43    [21848846]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Massa52
Member

Откуда:
Сообщений: 373
Danion
Massa52,
"при этом поток свежих данных поступал и
на продакшен и на тест одновременно." А это через что достигаете?

Данные идут с удаленных компов с таймстемпами(критерий для Top (100...)) на сервер.
Все загружается по FTP или в некоторых случаях по SFTP.
Далее приложение - Importer распихивает все это в базу.
В случае использования Test сервера или еще какого нить сервера - те же данные направляются и на эти сервера.
И там происходит то же самое и там естественно свой импотретр, чтобы распихивать данные.
Бывает, что на каком нибудь из серверов отваливается соединение.
Для нас это не критично и как только соединение восстанавливается, все нормализуется.
1 апр 19, 12:45    [21848848]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Danion
Member

Откуда: Москва
Сообщений: 196
Честно говоря, не хочется городить репликацию на прод. сервере, где для баз данных используется Always On. Вместе их никогда не использовал и не хочется на проде что-то из подводных камней поймать.

По размеру выделяется десяток таблиц, но лям+ строк во многих, а столько не нужно.
1 апр 19, 12:52    [21848852]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
tunknown
Member

Откуда:
Сообщений: 748
Danion
Кроме варианта с однократным восстановлением полной копии и последующими с ней действием можно ли как-то получить желаемое?
Если используете Enterprise на боевом и Developer на тестовом, то, возможно, имеет смысл посмотреть на Piecemeal Restores. Если данных так много и нет противопоказаний, то можно включить партиционирование на боевом и забирать и восстанавливать на тестовом только одну-две последних партиции. С readonly придётся дополнительно разбираться. Хотя метод сомнительный.
1 апр 19, 16:47    [21849143]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
tunknown
Danion
Кроме варианта с однократным восстановлением полной копии и последующими с ней действием можно ли как-то получить желаемое?
Если используете Enterprise на боевом и Developer на тестовом, то, возможно, имеет смысл посмотреть на Piecemeal Restores. Если данных так много и нет противопоказаний, то можно включить партиционирование на боевом и забирать и восстанавливать на тестовом только одну-две последних партиции. С readonly придётся дополнительно разбираться. Хотя метод сомнительный.

сразу видно человека,
не имевшего дела ни с секционированием,
ни с Piecemeal Restore.
1 апр 19, 17:31    [21849207]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
Yasha123,

Насколько ваше сообщение полезно для ТС?
Вопрос риторический...
1 апр 19, 23:27    [21849551]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Владислав Колосов
Member

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

нууу существуют практики загрузки продуктивных справочников в развернутую пустую базу при помощи SSIS пакета. Такой пакет создается максимум за рабочую неделю. Пользовательские же данные формируются непосредственно при тестировании.
Это какая то сферическая разработка в ваакуме :-)

Для разработки (и тестирования как части процесса) нужна нормальные, полноценные данные.
"Сформировать при тестировании" их слишком сложно, так никогда не делают, потому что для этого придётся повторить работу пользователей за достаточно большое время.

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


Не совсем так, базы как эффективнее тестировать как "коня в вакууме", на синтетических данных по написанным тест-кейсам. Экономится масса времени, т.к. разработчик отходит от "метода тыка" к вполне осознанным проверкам с понятными требованиями. Но к конструировании модели данных должны предъявляться самые строгие требования, не от принятой повсеместно технологии "разрешаем все, а там посмотрим". Скорее "запрещаем всё, а там посмотрим". Для тестирования объемов и вариаций существуют генераторы данных. Недешевые, но и не заоблачно дорогие. Тем самым разработка может вести полностью независимо от продуктовой системы и удалённо. Я не настаиваю, я говорю о том, что это возможно и это работает и, более того, снижает риски. Но требует определённой подготовки и желания этим заниматься.
1 апр 19, 23:49    [21849562]     Ответить | Цитировать Сообщить модератору
 Re: Обновляемая обрезанная копия рабочей базы  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
Danion,

Как можно синхронизировать изменения схемы без накатки Alter.... скриптов - без понятия.
А для данных можно и впрямь linked Server заюзать.
Я бы завёл список таблиц для полного копирования и тех, от которых пару тыщ строк всего надо. И генерил бы Dynamic SQL на основе этих метаданных. Типа merge для одних таблиц и truncate... Insert... Для пользовательских. Заодно можно в метаданных указать top сколько вы хотите.
Повторюсь, это я предполагаю, что с изменениями схемы уже как-то разобрались :D
1 апр 19, 23:53    [21849566]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить