comparing db 400 GB 250 tables problem with checksum and cancelling compare process

AlexG7

I tried to compare db 400 GB all 250  tables at once    on Server A (source)  with same db on Server B (destination) using option  "checksum"

Server A located  in data center  600 km away from server B 
SQL Data compare ( V13.4) installed on Server B

Step 1
Compare process  started from table (table1) with 42M rows,after 10 minutes  I see that progress is 1%  and press cancel button,nothing happen  so I went to task manager and end task Reg Gate..

Step 2
 Started program again choose compare  only table1 ,applied filter ( where LogDate  >= "2018-10-10 ")  and press compare ...  I see that progress started with compare of ...checksum... and after 10 minutes it was at 1% ,so I cancel it again  and end Red gate task

at this point I got call saying that my Server A  is "Extremely" slow 

Questions
 1. Does  checksum overwrite filtering (WHERE )condition ?  
 2. Does  SQL Data compare always close connection to server (even  if ended using task manager?)


I traced with profiler what happen during sql compare with "checksum" option on

SQL data compare calls 

declare @p4 numeric(29,0)

set @p4=-362941574090

declare @p5 int

set @p5=82696

exec sp_table_validation @table=N'table1',@owner=N'dbo',@rowcount_only=2,@expected_checksum=@p4 output,@expected_rowcount=@p5 output

select @p4, @p5

 
sp_table_validation on 42M table brings Server to "no response" state


 3. Any tips on how synchronize 400 GB  db  with minimum downtime  using data compare without "bringing down" the  servers 

  I already read
 https://documentation.red-gate.com/sdc10/getting-more-from-sql-data-compare/getting-better-performance-out-of-sql-data-compare
Thank you

Alex

Tianjiao_Li

@AlexG7

If you take a backup of the db from Server A, then compare it locally on ServerB, the impact on Server A 
should be eliminated.

Questions
 1. Does  checksum overwrite filtering (WHERE )condition ?  
 2. Does  SQL Data compare always close connection to server (even  if ended using task manager?)

I need to double check the answers to the above questions with the dev team. I'll keep you posted.

Tianjiao_Li

The dev team has confirmed the checksum applies to the whole table not just the subset of table selected by the where clause so if there's a change anywhere in the table it will compare the whole table row by row.

As for point 2 when the task is terminated through task manager any closing of connections is down to the OS.

AlexG7

any feedback from RG team ?