Обмен данными между базами данных SQL

Ответ 1

Есть много способов решить эту проблему. Я бы настоятельно рекомендовал решения 1, 2 или 3 в зависимости от потребностей вашего бизнеса:

• Транзакционная репликация: если общая база данных - это учетная запись, и вы хотите предоставить версии данных только для чтения для разделения приложений, то вы можете реплицировать основные таблицы, возможно даже только основные столбцы таблиц, на каждый отдельный сервер. Одной из сторон этого подхода является то, что вы можете реплицировать столько подписных баз данных, сколько захотите. Это также означает, что вы можете настроить, какие таблицы и поля доступны для подписчиков, исходя из их потребностей. Поэтому, если для одного приложения нужны пользовательские таблицы, а не таблицы поставщиков, вы подписываетесь только на таблицы пользователей. Если другим нужны только таблицы поставщиков, а не пользовательские таблицы, вы можете подписаться только на таблицы поставщиков. Еще один недостаток заключается в том, что репликация синхронизируется, и вы всегда можете повторно инициализировать подписку, если возникнет проблема.

  Я использовал транзакционную репликацию, чтобы вывести более 100 таблиц из хранилища данных для разделения нисходящих приложений, которым нужен доступ к агрегированным данным из нескольких систем. Поскольку наш хранилище данных обновлялось почасовым расписанием из источников данных зеркал и журналов, производственные приложения имели данные из многочисленных систем в скользящем окне от 20 до 80 минут каждый час.

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

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

  Хотя вы упомянули, что являетесь магазином Microsoft SQL Server, помните, что другие RDBM имеют аналогичные технологии. Поскольку вы говорите о MS SQL Server конкретно, обратите внимание, что транзакционная репликация также позволяет реплицировать в базы данных Oracle. Поэтому, если у вас есть несколько из них в вашей организации, это решение все еще может работать.

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

• Зеркала: если вы хотите сделать базу данных доступной почти в режиме реального времени на нисходящих серверах, вы можете настроить до два асинхронных зеркала. Таким образом, я интегрировал данные с CRM-приложением. Все чтения поступали от присоединений к зеркалу. Все записи были перенесены в очередь сообщений, которые затем применили изменения к центральному серверу производства. Недостатком этого подхода является то, что вы не можете создать более двух асинхронных зеркал. Вы не хотите использовать синхронные зеркала для этой цели, если вы также не планируете использовать зеркала для аварийного восстановления.

• Системы обмена сообщениями: если вы ожидаете иметь множество отдельных приложений, которые нуждаются в данных из одной центральной базы данных, тогда вам может понадобиться для рассмотрения корпоративных систем обмена сообщениями, таких как IBM Web Sphere, Microsoft BizTalk, Vitria, TIBCO и т.д. Эти приложения создаются специально для решения этой проблемы. Они имеют тенденцию быть дорогостоящими и громоздкими для реализации и поддержки, но они могут масштабироваться, если у вас есть глобально распределенные системы или десятки отдельных приложений, которые все должны совместно использовать данные.

• Связанные серверы: Похоже, вы уже подумали об этом. Вы можете предоставить данные через связанные серверы. Я не считаю, что это хорошее решение. Если вы действительно хотите пройти этот маршрут, подумайте о настройке асинхронного зеркала из центральной базы данных на другой сервер, а затем настройте соединения с подключенным сервером на зеркало. Это, по крайней мере, уменьшит риск того, что запрос из веб-приложений вызовет проблемы с блокировкой или производительностью в вашей центральной производственной базе данных.

  IMO, связанные серверы, как правило, представляют собой опасный метод совместного использования данных для приложений. Этот подход по-прежнему обрабатывает данные как гражданина второго сорта в вашей базе данных. Это приводит к некоторым довольно плохим привычкам кодирования, особенно потому, что ваши разработчики могут работать на разных серверах на разных языках с разными способами подключения. Вы не знаете, собирается ли кто-нибудь написать действительно интересный запрос против ваших основных данных. Если вы установите стандарт, который требует полного копирования общих данных на неосновный сервер, вам не нужно беспокоиться о том, пишет ли разработчик плохой код. По крайней мере, с точки зрения того, что их плохой код не будет ухудшать производительность других хорошо написанных систем.

  Есть много и много ресурсов, которые объясняют, почему использование Linked Servers может быть плохим в этом контексте. Неисчерпывающий список причин включает: (a) учетная запись, используемая для связанного сервера, должна иметь разрешения DBCC SHOW STATISTICS или запросы не смогут сделать использование существующих статистических данных, (b) подсказки подсказок не могут быть выведены, если они не представлены как OPENQUERY, (c) параметры не могут быть переданы при использовании с OPENQUERY, (d) сервер не имеет достаточной статистики о связанном сервере, следовательно, создает довольно ужасные планы запросов, (e) проблемы с подключением к сети могут вызвать сбои, (f) любая из этих пяти проблем производительности и (g) страшная ошибка контекста SSPI при попытке аутентификации учетных данных активного каталога Windows в сценарии с двойным ходом. Связанные серверы могут быть полезны для некоторых конкретных сценариев, но не рекомендуется использовать доступ к центральной базе данных вокруг этой функции, хотя это технически возможно.

• Процесс массового ETL: если для веб-приложений приемлема высокая степень латентности, тогда вы можете писать массовые процессы ETL с помощью fooobar.com/questions/105201/..., которые выполняются заданиями агента SQL Server для перемещения данных между серверами. Существуют также другие альтернативные инструменты ETL, такие как Informatica, Pentaho и т.д., Поэтому используйте то, что лучше всего подходит для вас.

  Это нехорошее решение, если вам нужна низкая степень задержки. Я использовал это решение при синхронизации с сторонним размещенным CRM-решением для полей, которые могут переносить высокую задержку. Для полей, которые не могли переносить высокую задержку (основные данные создания учетной записи), мы полагались на создание дублирующих записей в CRM через вызовы веб-сервисов в точке генерации учетной записи.

• Ночное резервное копирование и восстановление: если ваши данные могут переносить высокую степень задержки (до одного дня) и периоды недоступности, вы можете создавать резервные копии и восстанавливать базу данных в разных средах. Это нехорошее решение для веб-приложений, которым требуется 100% времени. Идея заключается в том, что вы берете базовую резервную копию, восстанавливаете ее до отдельного имени восстановления, а затем переименуете исходную базу данных и новую, как только новая будет готова к использованию. Я видел это для некоторых внутренних приложений веб-сайта, но я вообще не рекомендую этот подход. Это лучше подходит для более низкой среды разработки, а не для рабочей среды.

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

Ответ 2

Вы также можете рассмотреть возможность использования встроенной репликации SQL Server между общим хранилищем данных и DB приложения. По моему опыту, он хорошо подходит для двусторонней передачи данных, и там экземпляр таблиц в каждом db, позволяющий использовать внешние ключи (я не думаю, что FK возможны через связанный сервер).

Ответ 3

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

Если ваши цели равны abstract out this common data to a single database but still let the other databases join on these tables, even have keys to enforce constraints, тогда это решение должно работать нормально.

В нижней части экрана вы можете столкнуться с проблемами производительности со связанными серверами, поэтому, если вы предвидите, что база данных получает много трафика, вам может потребоваться изучить фактическое перемещение данных с помощью методов, предложенных Дугом или Мебебером.

Если вы перейдете по пути связанного сервера, я бы рекомендовал читать на OPENQUERY. Существует хорошая статья о OPENQUERY vs 4 идентификаторах частей здесь.

Ответ 4

Взгляните на Microsoft Sync Framework. Вам нужно будет написать приложение синхронизации, но оно может дать вам необходимую гибкость.

Ответ 5

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

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

Вы можете настроить представление или синоним в своих базах данных на общую таблицу на связанном сервере (или другом локальном БД). Я предпочитаю синонимы, если бы представление было всего лишь select * from table.

Синоним таблицы позволит вам запускать DML на удаленном элементе, если у вас есть разрешения.

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

Посмотрите код:

create synonym MyCentralTable for MyLinkedServer.MyCentralDB.dbo.MyCentralTable
go

create function dbo.MyLocalTableFkConstraint (
    @PK int
)
returns bit
as begin
    declare @retVal bit
    select @retVal = case when exists (
                            select null from MyCentralTable where PK = @PK
                        ) then 1 else 0 end
    return @retVal
end
go

create table MyLocalTable (
    FK int check (dbo.MyLocalTableFKConstraint(FK) = 1)
)
go

-- Will fail: -1 not in MyLinkedServer.MyRemoteDatabase.dbo.MyCentralTable
insert into MyLocalTable select -1
-- Will succeed: RI on a remote table w/o triggers
insert into MyLocalTable select FK from MyCentralTable

Конечно, важно отметить, что вы не получите сообщение об ошибке, если вы удаляете ссылку в вашей центральной таблице.