یافتن تفاوت‌های جدول‌های SQL Server و داده‌ها با استفاده از PowerShell

سوال: هر چند وقت یکبار لازم است داده‌های موجود در دو جدول را که ممکن است در SQL Server‌های متفاوتی باشند، مقایسه کنم. برای مثال، در یک محیط توزیع شده با SQL Server replication ، ما یک ناشر مرکزی و چندین مشترک داریم. گاهی اوقات می‌خواهم مطمئن شوم که محصولات مشابهی که بروز رسانی شده‌اند، در تمام سایت‌های مشترکین از نظر قیمت، ضمانت و مشخصات و … یکسان هستند. در محیط‌های چند سروری، زمان‌هایی وجود دارند که لازم است جدول‌ها را در سرورهای مختلف مقایسه کنم که بخشی از SQL Server replication نیستند.

 

راهکار
نحوه مقایسه داده‌ها در جدول‌های SQL Server ( یا مقایسه ساختارهای جدول) مدتهاست که مورد بحث و گفتگو قرار دارد. غیر از یوتیلیتی TableDiff، اکثر راهکارها مبتنی بر T-SQL هستند، اما انعطاف‌پذیری لازم را ندارند. برای مثال، می‌توانیم این نواقص را در آن‌ها ببینیم:
– ما می‌خواهیم جدول‌ها را در سرورهای مختلف مقایسه کنیم جاییکه سرورهای لینک شده مجاز نیستند ( کاری که tablediff می‌تواند انجام دهد).
– وقتی جدولی دارای یک ستون با نوع داده‌هایی مثل [text] ،[image] باشد، برخی راهکارهای T-SQL کار نخواهند کرد.
– وقتی جدول‌های بزرگ ( مثلاً با بیش از ۲۰۰ هزار رکورد) داریم، زمان زیادی طول می‌کشد.
– اگر طرح جدول متفاوت باشد، یوتیلیتی TableDiff کار نخواهد کرد.
اکثر اوقات ما فقط به یک بله و خیر نیاز داریم تا بفهمیم آیا رکورد‌ها در جدول یکسان هستند یا خیر. اگر Yes بود به کارمان ادامه می‌دهیم و اگر No بود باید برای حل مسئله مصالحاتی را انجام دهیم.
الگوریم این راهکار به شرح زیر است:
– تخلیه اطلاعات از دو جدول به دو فایل داده (data file) با استفاده از BCP queryout
– خواندن دو فایل داده و دریافت مقدار هش MD5 هر فایل با کلاس .Net MD5
– مقایسه مقادیر هش MD5 برای این که مشخص شود یکسان هستند و سپس Yes (یعنی داده‌های در هر دو جدول یکسان هستند) در غیر این صورت No
مزایای این روش آن است که ما می‌توایم تمام معیارها را پارامتربندی کنیم، مثل نام نمونه SQL Server، نام پایگاه اطلاعاتی، پرس و جو و غیره و از همه مهمتر این که به تمام نواقص راهکارهای T-SQL که در بالا عنوان کردیم بپردازیم.
داده‌های نمونه
ما برخی از جدول‌ها و داده‌های نمونه را آماده کرده‌ایم.

use mssqltips;

— create two tables, the only difference is [Salary] column data type
if object_id(‹dbo.s›, ‹U›) is not null
drop table dbo.s;
create table dbo.s (id int identity primary key, [Name] varchar(50), [DOB] datetime, [Address] varchar(200), [Salary] decimal(8,2));

if object_id(‹dbo.t›, ‹U›) is not null
drop table dbo.t;
create table dbo.t (id int identity primary key, [Name] varchar(50), [DOB] datetime, [Address] varchar(200), [Salary] decimal(9,2));

GO

— populate the two tables with sample data
insert into dbo.s ([Name], [DOB], [Address], [Salary])
values
(‹Mark›, ‹۱۹۸۰-۰۱-۰۱›, ‹۱۱۱ ABC street, Seattle, WA›, ۱۰۸۸۸۸٫۱۱)
,(‹James›, ‹۱۹۷۰-۰۱-۰۱›, ‹۲۲۲ XYZ street, Vancouver, BC›, ۱۲۲۳۳۳٫۲۲)
,(‹Jenny›, ‹۱۹۸۲-۱۱-۰۱›, ‹۳۳۳ DEF Avenue, London, ON›, ۸۸۹۹۹٫۳۳)
,(‹Mary›, ‹۱۹۹۰-۰۲-۰۲›, ‹۴۴۴ GHI Road, Edmonton, AB›, ۷۷۷۷۷٫۴۴)
,(‹John›, ‹۱۹۸۸-۰۵-۱۱›, ‹۵۵۵ JKL Blvd, Houston, IL›, null);

— copy the exact records from source table to target table
insert into dbo.t ([Name], [DOB], [Address], [Salary])
select [Name], [DOB], [Address], [Salary]
from dbo.s
go

این کد PowerShell است، شما می‌تواید آن را به یک ماژول اضافه کنید. برای راحتتر شدن کار می‌توانید کد را در پنجره PS ISE کپی کنید و آن را اجرا نمایید.

<#
.Synopsis
Compare two sql tables or two result sets from sql queries
.DESCRIPTION
Compare two sql tables or two result sets from sql queries
.EXAMPLE
Compare-QueryResult -SourceServer . -SourceDB MSSQLTips -SourceQuery ‹select * from dbo.s order by id› -TargetQuery ‹select * from dbo.t›

.EXAMPLE
#we compare value only between the two query resultsets.
Compare-QueryResult -SourceServer . -SourceDB MSSQLTips -SourceQuery ‹select * from dbo.s order by id› -TargetQuery ‹select * from dbo.t› -ValueOnly

.INPUTS
-SourceServer: A sql server instance name, default to local computer name
-SourceDB: A sql database on $SourceServer, default to ‹TempDB›
-SourceQuery: A query against SourceServer\SourceDB (mandatory)

-TargetServer: A sql server instance name, default to $SourceServer
-TargetDB: A sql database on $TargetServer, default to $TargetDB
-TargetQuery: A query against TargetServer\TargetDB (mandatory)

-ValueOnly: compare value only, so if $SourceQuery get a value of 10 from column with datatype [int], and $TargetQuery get a value of 10 from a colum with datatype [bigint], the value are the same.
-TempFolder: this is a folder where we need to put temporary files, default to c:\temp\. If you do not have this folder created, an error will occur.

.OUTPUTS
A string value of either ‹Match› or ‹UnMatch›;
#>

#Requires -Version 3.0

function Compare-QueryResult
{
[CmdletBinding()]
[OutputType([string])]

Param
(
# Param1 help description
[Parameter(Mandatory=$false,
Position=0)]
[string] $SourceServer = $env:ComputerName ,

[Parameter(Mandatory=$false)]
[String] $SourceDB=›tempdb›,

[Parameter(Mandatory=$true)]
[String] $SourceQuery,

[Parameter(Mandatory=$false)]
[String] $TargetServer=››,

[Parameter(Mandatory=$false)]
[String] $TargetDB=››,
[Parameter(Mandatory=$true)]
[String] $TargetQuery,

[Parameter(Mandatory=$false)]
[switch] $ValueOnly,

[Parameter(Mandatory=$false)]
[String] $TempFolder=›c:\temp\›

)
begin
{
[string]$src_result=››;
[string]$dest_result=››;
}
Process
{
if (-not (test-path -Path $TempFolder) )
{
Write-Error «`$TempFolder=[$TempFolder] does not exist, please check !»;
return;
}

#prepare the necessary variables
[string]$source_bcp_file = «$TempFolder\src_bcp_out.dat»; # the output file is hard-coded here, but you can change it to your own needs
[string]$target_bcp_file = «$TempFolder\dest_bcp_out.dat»;

if ($TargetServer -eq ‹›) { $TargetServer = $SourceServer;}

if($TargetDB -eq ‹›) {$TargetDB = $SourceDB;}

#bcp data out to files
if ($ValueOnly)
{
bcp «$SourceQuery» queryout «$source_bcp_file» -T -S «$SourceServer» -d $SourceDB -t «|» -c | out-null;
bcp «$TargetQuery» queryout «$target_bcp_file» -T -S «$TargetServer» -d $TargetDB -t «|» -c | out-null;
}

else
{
bcp «$SourceQuery» queryout «$source_bcp_file» -T -S «$SourceServer» -d $SourceDB -t «|» -n | out-null;
bcp «$TargetQuery» queryout «$target_bcp_file» -T -S «$TargetServer» -d $TargetDB -t «|» -n | out-null;
}

#create MD5
[System.Security.Cryptography.MD5] $md5 = [System.Security.Cryptography.MD5]::Create();

#read source file
[System.IO.FileStream] $f = [System.IO.File]::OpenRead(«$source_bcp_file»);

#hash the src file
[byte[]] $hash_src = $md5.ComputeHash($f);
$f.Close();

#read the target file
[System.IO.FileStream] $f = [System.IO.File]::OpenRead(«$target_bcp_file»);

#hash the target file
[byte[]] $hash_dest = $md5.ComputeHash($f);

$src_result=[System.BitConverter]::ToString($hash_src);
$dest_result=[System.BitConverter]::ToString($hash_dest);

#cleanup

$f.Close();
$f.Dispose();
$md5.Dispose();

del -Path $source_bcp_file, $target_bcp_file;

#compare the hash value

}
End
{
if ($src_result -eq $dest_result)
{ write-output ‹Match›}
else
{ Write-Output ‹UnMatch›; }

}
} #Compare-QueryResult

 

پس از اجرای اسکریپت در یک پنجره ISE ، یک پنجره ISE جدید باز کنید و کد زیر را اجرا نمایید ( فرض کنید سرور هدف و پایگاه اطلاعاتی شبیه منبع هستند، بنابراین لازم نیست که در اینجا به صراحت اضافه شوند).

#After running Compare-QueryResult.ps1, we can run the following test in a new window
#we will get «UnMatch» because [Salary] columns have different datatype
Compare-QueryResult -SourceServer . -SourceDB mssqltips `
-SourceQuery «select * from dbo.s» -TargetQuery «select * from dbo.t»;

#now let›s compare VALUE only, we will get ‹Match›
Compare-QueryResult -SourceServer . -SourceDB mssqltips -ValueOnly `
-SourceQuery «select * from dbo.s» -TargetQuery «select * from dbo.t»;

 

خواهید دید که وقتی اولین تابع cmdlet را اجرا می‌کنید، “UnMatch” را باز می‌گرداند، به این خاطر که ستون‌های {حقوق یا Salary } در dbo.s و dbo.t متفاوت هستند. یکی، نوع داده‌های اعشار (۸,۲) و دیگری اعشار (۹,۲) است، گرچه هر دو جدول دقیقاً تعداد سطرها و مقادیر یکسانی دارند.
بهرحال، وقتی شما دومین تابع cmdlet را با پارامتر
–ValueOnly ( فقط مقایسه مقادیر) اجرا می‌کنید، ما “Match” را دریافت می‌کنیم.

حال به مثال دیگری می‌پردازیم. ما در ادامه یک پرونده را در dbo.t بروز می‌کنیم و سپس cmdlet را با پارامتر –ValueOnly مجدد اجرا می‌کنیم و انتظار داریم “UnMatch” را دریافت کنیم. حال ببینیم چه اتفاقی می‌افتد.

— if I update the [Salary] for Mark from 108888.11 to 108888.12
update t
set [Salary]=108888.12 — originally is 108888.11
from dbo.t
where [Name]=›Mark›;
Re-run the second cmdlet in the PS ISE Window.
Compare-QueryResult -SourceServer . -SourceDB mssqltips -ValueOnly `
-SourceQuery «select * from dbo.s» -TargetQuery «select * from dbo.t»;

 

همانطور که انتظار داشتیم “UnMatch” را دریافت کردیم.

کاربرد: چندین سناریو برای کاربرد تابع PoweShell برای پارامتر –ValueOnly وجود دارند:
۱- بدون استفاده از پارامتر –ValueOnly ، اگر Match بازگردد، یعنی دو جدول دقیقاً یکسان هستند هم در طرح جدول و هم در داده‌ها ( به عبارت دیگر تعداد ستون‌ها و نوع داده‌ها). اگر UnMatch بازگردد، می‌تواند بیانگر اختلاف داده‌ها یا اختلاف فراداده‌های جدول باشد، بنابراین باید مرحله بعد را اجرا کنیم
۲- اگر cmdlet را با پارامتر –ValueOnly دوباره اجرا کنیم، اگر Match بازگردد، یعنی این که فراداده‌های جدول متفاوت است و اگر UnMatch باز گردد، یعنی این که داده‌های جدول واقعاً متفاوت هستند اما همچنان نمی‌توانیم متفاوت بودن فراداده‌های جدول را رد کنیم.
۳- اکثر اوقات، ما می‌دانیم که طرح‌های جدول یکسان هستند و ما فقط مراقب تطبیق داده‌ها هستیم. در این موارد، استفاده یا عدم استفاده از –ValueOnly فرقی ندارد.
۴- در آزمایشی که ما انجام دادیم، اگر در جدول dbo.s یک ستون varchar(N) باشد، در مورد ستون مربوطه در dbo.t ، حتی اگر نوع داده‌ها varchar(N+10) باشد، این تابع PS (بدون –ValueOnly )هنوز فکر می‌کند که آن‌ها یکسان هستند. این رفتار شبیه یوتیلیتی TableDiff است.

خلاصه
در این مبحث راه جدید مقایسه‌ جدول‌ها را با تخلیه داده‌های جدول به فایل‌ها از طریق یوتیلیتی BCP و سپس دریافت مقادیر هش MD5 فایل‌ها، نشان دادیم. فرمت فایل‌های داده‌ها با پارامتر–ValueOnly کنترل می‌شوند، در صورت استفاده از پارمتر –ValueOnly ، ما از پارامتر
–c برای BCP.exe (تمام ستون‌ها به عنوان نوع داده‌های character تلقی می‌شوند) استفاده خواهیم کرد، در غیر اینصورت از پارامتر –n
( تمام ستون‌ها به عنوان نوع داده‌های native تلقی می‌شوند) برای BCP.exe استفاده می‌کنیم.
در مورد PoweShell که در میان جامعه مدیران پایگاه اطلاعاتی محبوب است، نیاز به توابع PoweShell داریم که در مقایسه با استفاده از T-SQL به تنهایی، کارها را آسانتر می‌سازند.