Споделянето на свързана информация между изолирани системи става все по-важно за организациите, тъй като им позволява да подобрят качеството и достъпността на данните. Има много ситуации, в които е полезно да имате набор от данни, който е наличен и последователен в повече от един сървър на директории. Ето защо познаването на общите методи за изпълнение SQL Server синхронизирането на данни е важно.
Наличността и последователността на данните могат да бъдат постигнати чрез процесите на репликация на данни и синхронизиране на данни. Репликацията на данни е процес на създаване на едно или повече излишни копия на база данни с цел толерантност към грешки или подобряване на достъпността. Синхронизирането на данни е процес на установяване на съгласуваност на данни между две или повече бази данни и последващите непрекъснати актуализации за поддържане на споменатата последователност.
В много организации синхронизирането на данни в различни системи е едновременно желателно и предизвикателно. Можем да намерим много случаи на използване, при които трябва да извършим синхронизиране на данни:
- Миграция на база данни
- Редовно синхронизиране между информационните системи
- Импортиране на данни от една информационна система в друга
- Преместване на набори от данни между различни етапи или среди
- Импортиране на данни от източник, който не е от база данни
Няма уникален начин или единодушно съгласуван метод за синхронизиране на данни. Тази задача се различава от отделен случай и дори синхронизирането на данни, което на пръв поглед трябва да е просто, може да бъде сложно поради сложността на структурите на данни. В реални сценарии синхронизирането на данни се състои от много сложни задачи, които могат да отнемат много време за изпълнение. Когато се появи ново изискване, специалистите по бази данни обикновено трябва да изпълнят целия процес на синхронизация. Тъй като няма стандартни начини за това, освен репликацията, внедряванията на синхронизация на данни рядко са оптимални. Това води до трудна поддръжка и по-големи разходи. Внедряването и поддръжката на синхронизирането на данни е толкова трудоемък процес, той сам по себе си може да бъде работа на пълен работен ден.
Можем да внедрим архитектура за задачи за синхронизиране на данни ръчно, евентуално с помощта на Microsoft Sync Framework, или да се възползваме от вече създадени решения в инструменти за управление на Microsoft SQL Server. Ще се опитаме да опишем най-често срещаните методи и инструменти, които могат да се използват за решаване на синхронизирането на данни в бази данни на Microsoft SQL Server и ще се опитаме да дадем някои препоръки.
Въз основа на структурата на източника и местоназначението (например бази данни, таблици) можем да разграничим случаите на използване, когато структурите са сходни или различни.
Източникът и дестинацията имат много сходни структури
Това е много често случаят, когато използваме данни на различни етапи от жизнения цикъл на разработката на софтуер. Например структурата на данните в тестовата и производствената среда е много сходна. Общото изискване е да се сравняват данни между базата данни за тестване и производство и да се импортират данни от продукцията в базата данни за тестване.
Източникът и дестинацията имат различни структури
Ако структурите са различни, синхронизирането е по-сложно. Това е и по-често повтаряща се задача. Често срещан случай е импортирането от една база данни в друга. Най-честият случай е, когато даден софтуер трябва да импортира данни от друг софтуер, който се поддържа от друга компания. Обикновено импортът трябва да се изпълнява автоматично по график.
Използваният метод зависи от личните предпочитания и сложността на проблема, който трябва да решите.
Независимо от това колко сходни са структурите, ние можем да изберем четири различни начина за решаване на синхронизирането на данни:
- Синхронизация с помощта на ръчно създадени SQL скриптове
- Синхронизация с помощта на метода за сравняване на данни (може да се използва само когато източникът и целта имат сходна структура)
- Синхронизация с помощта на автоматично генерирани SQL скриптове - нужда от търговски продукт
Източникът и дестинацията имат еднакви или много сходни структури
Използване на ръчно създадени SQL скриптове
Най-простото и досадно решение е ръчното писане на SQL скриптове за синхронизация.
Предимства
- Може да се изпълнява от инструменти с безплатен и отворен код (FOSS).
- Ако таблицата има индекси, това е много бързо.
- SQL скриптът може да бъде запазен в съхранена процедура или да се изпълнява периодично като работа за SQL Server.
- Може да се използва като автоматичен импорт, дори при непрекъснато променяни данни.
Недостатъци
- Създаването на такъв SQL скрипт е доста досадно, тъй като за всяка таблица обикновено са необходими три скрипта:
INSERT,UPDATEиDELETE. - Можете да синхронизирате само данни, които са налични чрез SQL заявки, така че не можете да импортирате от източници като CSV и XML файлове.
- Трудно е да се поддържа - когато се промени структурата на базата данни, е необходимо да се модифицират два или три скрипта (
INSERT,UPDATE, а понякога иDELETE).
Пример
Ще направим синхронизация между таблицата Source, с колони ID и Value, и таблицата Target, със същите колони.
Ако таблиците имат един и същ първичен ключ и целевата таблица няма първичен ключ с автоматично увеличаване (идентичност), можете да изпълните следния скрипт за синхронизация.
@медиен екран и (макс. ширина:
-- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID) Използване на метода за сравняване на данни
В този метод можем да използваме инструмент за сравнение между източници и целеви данни. Процесът на сравнение генерира SQL скриптове, които прилагат разликите от базата данни източник към целевата база данни.
Съществуват редица програми за сравнение и синхронизиране на данни. Тези програми използват най-вече същия подход. Потребителят избира източника и целевата база данни, но други алтернативи могат да бъдат резервно копие на DB, папка със SQL скриптове или дори връзка със система за контрол на източника.
По-долу са най-популярните инструменти, които използват подхода за сравнение на данните:
В първата стъпка данните се четат или се четат само контролни суми за по-големи данни от източника и от целта. След това се изпълнява процесът на сравнение.
Тези инструменти предлагат и допълнителни настройки за синхронизиране.
Трябва да настроим следните опции за конфигуриране, които са необходими за синхронизиране на данните:
Ключ за синхронизиране
По подразбиране първичният ключ или UNIQUE използва се ограничение. Ако няма първичен ключ, можете да изберете комбинация от колони. Клавишът за синхронизиране се използва за сдвояване на редове на източника с редове на целта.
Сдвояване на таблица
По подразбиране таблиците се сдвояват по име. Можете да промените това и да ги сдвоите според собствените си нужди. В софтуера dbForge Data Compare можете да изберете SQL заявката да бъде източник или дестинация.
Процес на синхронизация
След потвърждаване инструментът сравнява източниците и целевите данни. Целият процес се състои от изтегляне на всички източници и целеви данни и сравняването им въз основа на определени критерии. По подразбиране се сравняват стойности от еднакво именувани таблици и колони. Всички инструменти поддържат картографиране на имена на колони и таблици. Също така, има възможност да се изключат IDENTITY (автоинкремент) колони или за извършване на някои трансформации преди сравняване на стойности (кръгли типове с плувка, игнориране на регистъра на знаците, третиране NULL като празен низ и т.н.) Изтеглянето на данни е оптимизирано. Ако обемът на данни е голям, се изтеглят само контролни суми. Тази оптимизация е полезна в повечето случаи, но изискванията за време за извършване на операции се увеличават с обема на данните.
В следващата стъпка има SQL скрипт с генерирани миграции. Този скрипт може да бъде запазен или стартиран директно. За да бъдем в безопасност, можем дори да направим резервно копие на базата данни, преди да стартираме този скрипт. Инструментът ApexSQL Data Diff може да създаде изпълнима програма, която изпълнява скрипта на избрана база данни. Този скрипт съдържа данни, които трябва да бъдат променени, а не логиката как да ги промените. Това означава, че скриптът не може да се стартира автоматично, за да осигури повтарящ се импорт. Това е най-големият недостатък на този подход.
но крипто се счита за сигурност и там няма управленски усилия
Предимства
- Разширени познания по SQL не се изискват и могат да бъдат направени чрез GUI.
- Имате възможност да проверявате визуално разликите между базите данни преди синхронизиране.
Недостатъци
- Това е усъвършенствана функция на търговските продукти.
- Ефективността намалява при прехвърляне на огромни обеми данни.
- Генерираният SQL скрипт съдържа само разлики и поради това не може да се използва повторно за автоматично синхронизиране на бъдещи данни.
По-долу можете да видите типичния потребителски интерфейс на тези инструменти.
Синхронизирайте с автоматично генериран SQL
Този метод е много подобен на метода за сравнение на данните. Единствената разлика в сравнение с предишния метод е, че няма сравнение на данни и генерираният SQL скрипт не съдържа разлики в данните, а логика за синхронизация. Генерираният скрипт може лесно да бъде запазен в съхранена процедура и може да се изпълнява периодично (например всяка вечер). Този метод е полезен за автоматичен импорт между бази данни. Ефективността на този метод е много по-добра от метода за сравняване на данни.
Синхронизацията чрез автоматично генериран SQL се осигурява само от SQL Database Studio .
SQL Database Studio предоставя подобен интерфейс на метода за сравняване на данни. Трябва да изберем източника и целта (бази данни или таблици). След това трябва да настроим опции (клавиши за синхронизиране, сдвояване и картографиране). Има функция за създаване на графични заявки за настройка на всички параметри.
Предимства
- Не се изискват задълбочени познания по SQL.
- Можете да настроите всичко в GUI доста бързо.
- Полученият SQL скрипт може да бъде записан в съхранена процедура.
- Може да се използва като автоматичен импорт - като работа за SQL Server.
Недостатъци
- Това е усъвършенствана функция на търговските продукти.
- Разликите не могат да бъдат проверени ръчно преди синхронизация, тъй като целият процес се изпълнява в една стъпка.
Бенчмаркове за ефективност
Тестов случай
Две бази данни (A и B), всяка от които съдържа по една таблица с 2 000 000 реда. Таблиците са в две различни бази данни на един и същ SQL Server. Този тест обхваща два екстремни случая: 1) Изходната таблица съдържа всички 2 000 000 реда и целевата таблица е празна. Синхронизацията трябва да осигури много INSERTS. 2) Изходните и целевите таблици съдържат 2 000 000 реда. Разликата е само в един ред. Синхронизацията трябва да осигури само един UPDATE.
какво е прототипирането в дизайна
RedGate Data Compare се нуждае от 3 стъпки:
- Сравнете
- Генериране на скрипт
- Изпълнете скрипт в целевата база данни
ApexSQL Data Diff се нуждае от 2 стъпки:
- Сравнете
- Генерирайте скрипт и стартирайте скрипт в една стъпка
SQL Database Studio извършва цялата синхронизация в една стъпка. По-долу са посочени времената за синхронизация, в секунди. В колоната с надпис „индивидуални стъпки“ са продължителностите на стъпките за синхронизация, изброени по-горе.
| Дело А. много ВЪВЕЖДАНЕ | Случай А. много INSERT (отделни стъпки) | Случай Б. АКТУАЛИЗИРАНЕ един ред | Случай Б. АКТУАЛИЗИРАНЕ един ред (отделни стъпки) | |
|---|---|---|---|---|
| SQL Database Studio | 47 | 5 | ||
| RedGate Data Compare | 317 | 13 + 92 + 212 | 2. 3 | 22 + 0 + 1 |
| Разлика в данните на ApexSQL | 188 | 18 + 170 | 26 | 25+ |
Долната е по-добра.
Същият тест, но базите данни са на различни SQL сървъри, които не са свързани през свързан сървър.
| Дело А. много ВЪВЕЖДАНЕ | Случай А. много INSERT (отделни стъпки) | Случай Б. АКТУАЛИЗИРАНЕ един ред | Случай Б. АКТУАЛИЗИРАНЕ един ред (отделни стъпки) | |
|---|---|---|---|---|
| SQL Database Studio | 78 | 44 | ||
| RedGate Data Compare | 288 | 17 + 82 + 179 | 25 | 24 + 0 + 1 |
| Разлика в данните на ApexSQL | 203 | 18 + 185 | 25 | 24 + 1 |
| dbForge Data Compare | 326 | 11 + 315 | 16. | 16 + 0 |
Долната е по-добра.
Обобщение
От резултатите е очевидно, че RedGate и Apex не се интересуват дали базите данни са на един и същ SQL сървър, тъй като алгоритъмът за синхронизация не зависи от SQL Server. SQL Database Studio използва собствени функции на SQL Server; следователно резултатът е по-добър, когато базите данни са на един и същ сървър.
Източникът и дестинацията имат различна структура
Има и ситуации, когато една широка таблица трябва да бъде синхронизирана в много малки свързани таблици.
Този пример се състои от една широка таблица SourceData, която трябва да бъде синхронизирана в малки таблици Continent, Country и City. Схемата е дадена по-долу.
Данните в SourceData могат да бъдат като тези на изображението по-долу.
Използване на ръчно създадени SQL скриптове
Таблица за синхронизиране на скриптове със скриптове
INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent; Синхронизиране на таблица на градски скриптове
INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id; Този скрипт е по-сложен. Това е така, защото записите в таблиците Country и Continent трябва да бъдат намерени. Този скрипт вмъква липсващи записи в City и запълва ContryId правилно.
UPDATE и DELETE скриптове също могат да бъдат написани по същия начин, ако е необходимо.
Предимства
- Нямате нужда от търговски продукти.
- SQL скриптът може да бъде запазен в съхранена процедура или да се изпълнява периодично като работа за SQL Server.
Недостатъци
- Създаването на такъв SQL скрипт е трудно и сложно (за всяка таблица обикновено са необходими три скрипта -
INSERT,UPDATEиDELETE). - Много е трудно да се поддържа.
Използване на външни инструменти
Този вид синхронизация (широка таблица в много свързани таблици) не може да се направи с метода за сравняване на данни, тъй като е фокусиран върху различни случаи на употреба. Тъй като методът за сравняване на данни създава SQL скрипт с данни за вмъкване, той няма пряка възможност да търси препратки в свързани таблици. Поради тази причина приложения, използващи този метод, не могат да бъдат използвани (dbForge Data Compare за SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).
SQL Database Studio обаче може да ви помогне да създавате автоматично скриптове за синхронизация. На снимката по-долу има елемент, наречен Редактор за синхронизиране на данни в SQL Database Studio.
Редакторът прилича на добре познатия конструктор на заявки и работи по много подобен начин. Всяка таблица трябва да има дефиниран ключ за синхронизация, но има и дефинирани връзки между таблици. На снимката по-горе има картографиране и за синхронизация. В списъка с колони (долната част на изображението) има колоните на таблицата City (за други таблици е подобно).
какво е индустрията за красота
Колони
- Документ за самоличност - Тази колона не е картографирана, защото е първичният ключ (генерира се автоматично).
- CountryId - Тази колона е дефинирана като препратка към таблицата.
- Име - Тази колона се попълва от колона City в изходната таблица (широка таблица).
Колони CountryId и Name са избрани като клавиши за синхронизация. Ключът за синхронизация е набор от колони, които уникално идентифицират ред в таблицата източник и цел. Не можете да използвате първичния ключ Id като ключ за синхронизация, защото това не е в таблицата източник.
След синхронизирането ето как изглеждат таблиците:
В горния пример имаше една широка таблица като източник. Съществува и често срещан сценарий, когато изходните данни се съхраняват в няколко свързани таблици. Връзките в SQL Database Studio не се дефинират с помощта на външни ключове, а имена на колони. По този начин е възможно и импортиране от CSV или Excel файлове (файлът се зарежда във временна таблица и синхронизацията се стартира от тази таблица). Добра практика е да имате уникални имена на колони. Ако това не е възможно, можете да дефинирате псевдоними на тези колони.
Предимства
- Лесно и бързо за създаване
- Лесна за поддръжка
- Може да се запише в съхранена процедура (съхранената процедура се записва с данни, необходими за отваряне на синхронизацията в редактор по-късно)
Недостатъци
- Търговско решение
Сравняване на решенията
Синхронизирането на данни се състои от последователност от INSERT, UPDATE или DELETE команди. Има няколко начина за създаване на последователности от тези команди. В тази статия разгледахме три опции за създаване на синхронизиращи SQL скриптове. Първият вариант е да създадете всичко ръчно. Това е осъществимо (но отнема твърде много време), изисква сложно разбиране на SQL и е трудно да се създаде и поддържа. Вторият вариант е да се използват търговски инструменти. Разгледахме следните инструменти:
- dbForge Data Compare за SQL Server
- RedGate SQL Data Compare
- Apex SQL Data Diff
- SQL Database Studio
Първите три инструмента работят много подобно. Те сравняват данните, позволяват на потребителя да анализира разликите и могат да синхронизират избрани разлики (дори автоматично или от командния ред). Те са полезни за тези сценарии на използване:
- Базите данни не са синхронизирани поради различни грешки.
- Трябва да избягвате репликация, докато прехвърляте данни между среди.
- Необходими са отчети за сравнение на данни в Excel или HTML.
Всеки инструмент е обичан по една или друга причина: dbForge има страхотен потребителски интерфейс и много опции, ApexSQL се представя по-добре от останалите, а RedGate е най-популярният.
Четвъртият инструмент, SQL Database Studio, работи малко по-различно. Той генерира SQL скриптове, които съдържат логика за синхронизация, а не промени. Ефективността също е страхотна, тъй като цялата работа се извършва директно върху сървъра на базата данни, така че не е необходим трансфер на данни между сървъра на базата данни и инструмента за синхронизация. Този инструмент е полезен за следните случаи на употреба:
- Автоматични миграции на бази данни, където базите данни имат различна структура
- Импортиране в множество свързани таблици
- Импортиране от външни източници XML, CSV, MS Excel
Свързани: Ръководство за миграция на Oracle към SQL Server и SQL Server към Oracle
Разбиране на основите
Кои инструменти са на разположение за синхронизиране на база данни?
Можете да използвате инструменти като RedGate Data Compare, ApexSQL Data Diff и dbForge Data Compare, които използват метода за сравняване на данни. Можете да използвате SQL Database Studio, което автоматично генерира SQL скриптове за многократна употреба.
Как да сравнявам данните от базата данни?
Сравнението въз основа на данни може да се извърши автоматично с помощта на инструменти на трети страни, които имат своята цена. Като алтернатива можете да напишете сравнителни SQL скриптове, които да ви показват разлики, но това отнема много повече време и е по-малко за многократна употреба.
Как да синхронизирам бази данни в SQL Server?
Можете ръчно да синхронизирате SQL бази данни, като пишете SQL скриптове (INSERT, DELETE, UPDATE) или можете да използвате инструменти на трети страни, които имат цена. Инструментите на трети страни работят чрез сравняване на данни и генериране на синхронизиращи SQL скриптове или можете да настроите логика за синхронизиране и да генерирате SQL скриптове въз основа на нея.
