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

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)
declare @p5 int
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

Tagged:

Best Answers

  • Tianjiao_LiTianjiao_Li Posts: 377 Rose Gold 1
    Accepted Answer
    @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.

    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Tianjiao_LiTianjiao_Li Posts: 377 Rose Gold 1
    Accepted Answer
    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.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?

Answers

Sign In or Register to comment.