Да просто из опыта узнать - потестировать разное, замерить, посопровождать, сравнить разные подходы - что проще, быстрее и надежнее, а что сложнее, медленнее, плохо масштабируется

2 базы на одном сервере, из одной в другую

Нужно базу целиком перетаскивать или отдельные таблицы?

Какой объем

Как часто

Целиком или инкрементально

Я это через процу сделаю , там не так много строк до 100 т. Каждый день

Андрей, прочитайте правила канала, уважайте коллег, объединяйте сообщения

А Если бд уедет на другой сервак?

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

Спасибо

Я ответил весьма примерно. Ваш вопрос очень большой и широкий.

Там ещё с точки зрения производительности и безопасности они удобны

Говорю же - вопрос большой. Спрашивать тут это сродни "когда используют СУБД"

Давайте порассуждаем. Какие преимущества у SSIS? ИМХО - скорость создания и низкие требования к навыкам программирования на начальном этапе. Недостатки - отдельная подсистема, отдельное потребление ресурсов, порой большее, чем у хранимок за счёт двойного кеширования. Хранимки - если знать что писать, то можно довольно гибко реализовать процесс ETL/ELT прямо в СУБД. В большинстве случаев ресурсов потребуется чуть меньше. Недостатки - не очень удобно тащить данные между серверами, чуть-чуть труднее сделать BULK LOAD. Нужна своя инфраструктура обработки ошибок и логгирования.

легче пересесть на брокер и получить балансировщик прямо на базе

про хранимки.. Но иногда люди не понимают как писать хранимки и это плохо.

if else if if if else else if if if

При массовой загрузке как поможет Service Broker? Мне кажется, он удобен для асинхронного OLTP больше. Не видел SB в ETL и DWH. Можешь рассказать чуть подробнее?

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