Porównywanie danych z narzędziem Data Comparison w Visual Studio

Jednym z zadań stojących przed wszelkiego rodzaju deweloperami czy też analitykami danych, którzy pracują z bazami danych SQL Server lub Azure SQL Database jest porównywanie danych. Porównywanie zbioru testowego ze zbiorem testowanym jest podstawową metodą sprawdzania poprawności i raczej nikomu nie trzeba tłumaczyć, że to zadanie możemy wykonać na mnóstwo różnych sposobów. Jednym z rozwiązań jest sięgnięcie po wyspecjalizowane oprogramowanie dedykowane do tego typu zadań, innym podejściem będzie napisanie kawałka kodu w TSQL, a jeszcze innym użycie wbudowanego w Data Tools narzędzia Data Comparison, które omówimy sobie w ramach niniejszego wpisu.

Jak już wspomniałem we wstępie Data Comparison jest narzędziem dostępnym w Visual Studio Data Tools i pozwala na porównywanie danych w widokach oraz tabelach pomiędzy różnymi bazami danych. Aby porównanie mogło być wykonane musimy mieć pewność, że spełnione zostaną następujące wymogi:

porównywane tabele muszą posiadać atrybuty o tej samej nazwie i typie,
porównywane tabele muszą posiadać odpowiadający sobie klucz główny lub unique constraint,
jeśli chcemy porównywać widoki to one również muszą posiadać unique clustered index.

Dodatkowo istnieje opcja porównania tabeli z widokiem ale wtedy muszą one odpowiadać sobie strukturą i nazwą.

Przejdźmy zatem do testowania narzędzia, na samym początku stworzymy obiekty, które będziemy porównywać – umieścimy je w bazach Source oraz Target:

DROP DATABASE IF EXISTS Source
GO

DROP DATABASE IF EXISTS Target
GO

CREATE DATABASE Source
GO

CREATE DATABASE Target
GO

Dane będą pochodzić z AdventureWorks2017 i tabeli Production.Product, a do naszych baz będziemy je ładować poleceniem SELECT INTO:

use AdventureWorks2017
GO

SELECT * 
INTO [Source].[dbo].[Product]
FROM [Production].[Product]
WHERE [ProductID]<450
GO

SELECT * 
INTO [Target].[dbo].[Product]
FROM [Production].[Product]
WHERE [ProductID]>400
GO

Jak możecie zauważyć powyżej dane w bazie Source zawierają wszystkie produkty, które mają identyfikator mniejszy niż 450, a baza Target zawiera produkty z identyfikatorem większym niż 400. Teoretycznie zatem tabele powinny mieć część rekordów wspólnych, a część rozdzielnych. Patrząc na wymogi narzędzia, które wylistowałem wyżej do obu tabel dodamy klastrowany indeks unikalny:

CREATE UNIQUE CLUSTERED INDEX IX_Product ON [Source].[dbo].[Product]([ProductID])
GO

CREATE UNIQUE CLUSTERED INDEX IX_Product ON [Target].[dbo].[Product]([ProductID])
GO

Ponad to dodamy również dodatkowy nieklastrowany indeks unikalny – po co? O tym przekonamy się w dalszej części testu:

CREATE UNIQUE NONCLUSTERED INDEX IX_ProductName ON [Source].[dbo].[Product]([Name])
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_ProductName ON [Target].[dbo].[Product]([Name])
GO

Oprócz różnicy w ilości wierszy nieco zmodyfikujemy dane w źródle wykonując następujące komendy aktualizujące:

UPDATE [Source].[dbo].[Product]
SET 
	DaysToManufacture=2
	,SellStartDate='20100203'
WHERE ProductID IN (401,402)
GO

UPDATE [Source].[dbo].[Product]
SET 
	SellStartDate='20200102'
WHERE ProductID IN (403)
GO

Gdy nasze testowe struktury są gotowe możemy uruchomić Visual Studio i odnaleźć tam okno SQL Server Object Explorer. W oknie tym łączymy się do naszego serwera i klikamy na wybraną bazę danych prawym przyciskiem myszy gdzie możemy z menu kontekstowego wybrać interesujące nas narzędzie:

Możemy je również uruchomić wybierając Tools -> SQL Server -> Data Comparison:

Pierwszą rzeczą jaką będziemy musieli zrobić w oknie które ukaże się naszym oczom to nawiązanie połączenia z bazą źródłową oraz docelową:

Powyższa konfiguracja jest na tyle prosta, że pozwolę sobie ją pominąć gdyż tak naprawdę sprowadza się do wskazania nazwy serwera, bazy danych oraz sposobu uwierzytelnienia. W tym oknie warto zwrócić uwagę na dostępne opcje porównywania:

Different Records – raport wynikowy będzie zawierał informacje o rekordach występujących w obu bazach ale posiadających różne dane,
Only in Source – raport wynikowy będzie zawierał informacje o rekordach występujących tylko w bazie oznaczonej jako źródłowa,
Only in Target – raport wynikowy będzie zawierał informacje o rekordach występujących tylko w bazie oznaczonej jako docelowa,
Identical Records – raport wynikowy będzie zawierał informacje o rekordach występujących w obu bazach posiadających dokładnie te same dane.

Zaznaczamy powyższe opcje według potrzeb (ja zostawiłem wszystkie zaznaczone) i klikamy Next aby przejść dalej. Kolejne okno pozwala nam wybrać indeks unikalny po którym ma nastąpić porównanie. Ja stworzyłem wcześniej dwa takie indeksy aby pokazać, że w takim przypadku mamy wybór. Oprócz tego możemy wybrać atrybuty, które chcemy porównywać – tutaj warta wspomnienia jest złota zasada mówiąca o tym, że zaznaczamy tylko to co nas interesuje ponieważ ma to wpływ na długość trwania całej operacji. Ja zostawiłem porównywanie po indeksie klastrowanym oraz chce porównywać wszystkie obiekty:

Po kliknięciu Finish narzędzie porówna dane i zwróci nam następujący raport:

Z powyższego raportu możemy wywnioskować, że:

trzy wiersze się różnią,
79 wierszy istnieje w źródle ale nie ma ich w tabeli docelowej,
376 wierszy istnieje w tabeli docelowej, a nie ma ich w źródle,
46 wierszy jest identycznych.

Przy każdej z tych liczb mamy odpowiednią operację, która zostanie wykonana na tabeli docelowej aby niejako zsynchronizować ją z tabelą źródłową. Warto zauważyć, że z tego miejsca nie jesteśmy w stanie odwrócić ról tzn. tabela docelowa nie może stać się miejscem docelowym, a źródłowa docelowym – możemy jedynie użyć opcji dostępnej z belki aby na nowo zdefiniować proces:

W dolnej części okna mamy podgląd rekordów, które są takie same, różne itp:

Jak możecie zobaczyć na powyższym zrzucie ekranowym sekcja Different Records czyli różniące się wiersze wyświetla kolumny zarówno z tabeli źródłowej i docelowej, a jeżeli się one od siebie różnią to informacja ta jest zapisana pogrubioną czcionką. Jedynym wyznacznikiem tego, która wartość pochodzi z której tabeli jest mała ikonka w nagłówku.

Mamy zatem raport i co możemy z nim zrobić? Możemy wykonać odpowiednie operacje języka TSQL, które pozwolą doprowadzić “target” do takiego samego stanu jak “source”:

Jeśli w porównaniu uczestniczyłoby więcej tabel niż jedna to moglibyśmy wybrać te tabele, które chcielibyśmy zsynchronizować. W naszym przykładzie zostawmy wszystko tak jak jest i przejdźmy dalej. Na pierwszy ogień klikamy Generate Script… i podejrzymy jakie operacje chce wykonać Visual Studio na bazie docelowej. Całego skryptu nie będę wklejał bo jest on dosyć długi ale poniżej możecie zobaczyć wybrane fragmenty:

/*
This script was created by Visual Studio on 4/4/2020 at 3:12 PM.
Run this script on ..Target (LAPTOP-HMSDMS5O\adria) to make it the same as ..Source (LAPTOP-HMSDMS5O\adria).
This script performs its actions in the following order:
1. Disable foreign-key constraints.
2. Perform DELETE commands. 
3. Perform UPDATE commands.
4. Perform INSERT commands.
5. Re-enable foreign-key constraints.
Please back up your target database before running this script.
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*Pointer used for text / image updates. This might not be needed, but is declared here just in case*/
DECLARE @pv binary(16)
BEGIN TRANSACTION
DELETE FROM [dbo].[Product] WHERE [ProductID]=450
DELETE FROM [dbo].[Product] WHERE [ProductID]=451
DELETE FROM [dbo].[Product] WHERE [ProductID]=452
DELETE FROM [dbo].[Product] WHERE [ProductID]=453
DELETE FROM [dbo].[Product] WHERE [ProductID]=454
DELETE FROM [dbo].[Product] WHERE [ProductID]=455
DELETE FROM [dbo].[Product] WHERE [ProductID]=456

UPDATE [dbo].[Product] SET [DaysToManufacture]=2, [SellStartDate]='20100203 00:00:00.000' WHERE [ProductID]=401
UPDATE [dbo].[Product] SET [DaysToManufacture]=2, [SellStartDate]='20100203 00:00:00.000' WHERE [ProductID]=402
UPDATE [dbo].[Product] SET [SellStartDate]='20200102 00:00:00.000' WHERE [ProductID]=403

SET IDENTITY_INSERT [dbo].[Product] ON
INSERT INTO [dbo].[Product] ([ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]) VALUES (1, N'Adjustable Race', N'AR-5381', 0, 0, NULL, 1000, 750, 0.0000, 0.0000, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, '20080430 00:00:00.000', NULL, NULL, N'694215b7-08f7-4c0d-acb1-d734ba44c0c8', '20140208 10:01:36.827')
INSERT INTO [dbo].[Product] ([ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]) VALUES (2, N'Bearing Ball', N'BA-8327', 0, 0, NULL, 1000, 750, 0.0000, 0.0000, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, '20080430 00:00:00.000', NULL, NULL, N'58ae3c20-4f3a-4749-a7d4-d568806cc537', '20140208 10:01:36.827')
INSERT INTO [dbo].[Product] ([ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]) VALUES (3, N'BB Ball Bearing', N'BE-2349', 1, 0, NULL, 800, 600, 0.0000, 0.0000, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, '20080430 00:00:00.000', NULL, NULL, N'9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e', '20140208 10:01:36.827')

SET IDENTITY_INSERT [dbo].[Product] OFF
COMMIT TRANSACTION

W górnej części okna mamy kilka wskazówek jak działa skrypt czyli:

Wyłącza klucze obce jeśli istnieją,
Wykonuje komendy DELETE
wykonuje komendy UPDATE
wykonuje komendy INSERT
Włącza klucze obce jeśli istnieją.

Warto również zwrócić uwagę, że wszystkie operacje są wykonywane w transakcji. W tym miejscu możemy potwierdzić teorię mówiącą dlaczego wymagany jest unikalny indeks zarówno w źródle jak i tabeli docelowej – po prostu na tej podstawie wykonywane są porównania oraz odpowiednie dostosowywania struktury. Patrząc na powyższy kod może nam się nasunąć jeden wniosek, a mianowicie taki że narzędzie sprawdzi się w przypadku mniejszych tabel, a próba porównania np. 100 tysięcznych tabel może być nieco karkołomna i chyba lepiej wykonać ją za pomocą kodu lub takich narzędzi jak SSIS bo ciężko sobie wyobrazić kilkadziesiąt tysięcy komend INSERT INTO VALUES.

Kolejna opcja dostępna na wstążce czyli Update Target po prostu wykona powyższe komendy na tabeli docelowej, a Export to file zapisze je w pliku z rozszerzeniem sql. W moim teście wykonałem wygenerowane komendy i mimo, że testowa tabela nie zawiera dużej ilości danych to mimo wszystko trwało to kilkanaście sekund. Następnie użyłem dostępnego na belce przycisku odświeżenia:

Uzyskałem w ten sposób odświeżony raport mówiący, że teraz mamy w obu tabelach 128 identycznych wierszy:

Przygotowany przez nas test może zostać zapisany pod postacią pliku z rozszerzeniem dcmp do późniejszego użytku dzięki czemu nie będziemy musieli konfigurować połączeń od początku.

Na sam koniec mały bonus, sprawdziłem w jaki sposób dokonywane jest porównanie, do tego celu wykorzystałem sesję Extended Events i okazało się zgodnie z moimi przewidywaniami Visual Studio wysłało następujące zapytania do obu baz:

SELECT N'[dbo].[Product]', SUM(row_count) 
                         FROM sys.dm_db_partition_stats 
                         WHERE (index_id=0 OR index_id=1) AND object_id=OBJECT_ID(N'[dbo].[Product]')

czyli nic innego jak sprawdzenie ilości wierszy z widoku systemowego sys.dm_db_partition_stats oraz:

SELECT [ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], 
[SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], 
[SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate] FROM [dbo].[Product] WITH (NOLOCK) ORDER BY [ProductID]

czyli pobranie całej tabeli i jej posortowanie po kolumnie klucza. Zaskoczeni? Raczej nie bo porównanie wykonywane jest po stronie narzędzia, a nie bazy SQL. Nie jest to takie oczywiste jak pobieramy dane z jednego serwera jednakże warto pamiętać, że dane mogą pochodzić z kompletnie innych instancji.

To by było na tyle na temat Data Comparison w Visual Studio. Podsumowując warto pamiętać, że jest to dosyć wygodne narzędzie ale sprawdzi się jedynie dla mniejszych tabel powiedzmy do maksymalnie kilkunastu tysięcy wierszy gdzie dane są porównywane po stronie samego narzędzia, a nie serwera. Alternatywą mogą być zwykłe zapytania języka TSQL (jeśli porównujemy dane na tym samym serwerze lub poprzez takie twory jak np. Linked Servers), Integration Services czy chociażby Power Query. Mimo wszystko myślę, że warto zapoznać się z narzędziem gdyż w niektórych sytuacjach może okazać się przydatne pamiętając przy tym w jaki sposób ono działa. Pozdrawiam!