Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Compare Data between to servers based in Id's Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 20, Visits: 147
Hi All,

I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass from a sql server table, I need to identify the corresponding ID related data for each column in syn or not. If the data is not in syn then I need to maintain that data with column name in another table.

Can Any one help me on the above requirement.

Thanks
Post #1421187
Posted Tuesday, February 19, 2013 5:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
It sounds like you are looking into writing a package that will something very similar to what redgate SQL Data Compare tries to do.

Can you please clarify these statements:

I need to identify the corresponding ID related data for each column in syn or not.

What do you mean by "for each column in sync or not"?

If the data is not in syn then I need to maintain that data with column name in another table.

Are you saying that if you compare two rows with an ID of 10 are the same but column1, column3 and column5 are not matching that you want to store 3 rows in some "audit table" that shows:

ID ColumnName
-- -------------------
10 column1
10 column3
10 column5


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1421581
Posted Wednesday, February 20, 2013 10:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 20, Visits: 147
Thank's for you Replay.

The scenario is

Server 1:

ID Address
-------------------
1 USA
2 UK
3 IND

Server 2:

ID Address
----------------
1 Singapur
2 UK
3 IND

My goal is to get the the out of sync data from the servers

In the above example, In server1 we have value USA for Id 1 and in server2 we have value singapur.

I have the Ids based on Ids I Need to check the data.

Expected result is:

ID ValueInserver1 Valueinserver2 DiffColumnname
--------------------------------------------------------------------
1 USA Singapur Address






Post #1422397
Posted Thursday, February 21, 2013 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
What is the purpose of capturing the results in a table with the column name that differs? I am not seeing a clear and easy way to do this with the out-of-the-box components SSIS provides unless you consider writing some .NET code in Script Tasks or Components to do the comparisons. Seriously, have you had a look at redgate SQL Data Compare?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1422522
Posted Thursday, May 09, 2013 11:02 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 225, Visits: 700
You could add a checksum column on to apply to the columns wher you think the changes may occur. This will identify your row Id's that are out of sync. It is not a perfect application

http://www.sqlservercentral.com/Forums/Topic776261-148-1.aspx
From there it is just a matter of writing some code to get the data in the format you want, maybe using a table variable to hold the two different values with the same ID.
Post #1451269
Posted Wednesday, May 15, 2013 4:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 69, Visits: 89
there are loads of ways to achieve this..

How many rows of data are we talking about in the tables? if we're talking a few thousand and you are able to add a linked server then just join on the id's and add a where clause that compares the second column.

If you're not able to add a linked server, then bcp/ssis/use openrowset to copy one set of data to a temp table on the other server to do the compare.

If you have millions/billions of rows to compare then, you will need to do a few more steps, like ssis across the records, maybe in batches and you'd probably want to index the joining column, but then the tecnique is the same.

hope this helps.
Post #1453285
Posted Wednesday, May 15, 2013 4:34 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:06 PM
Points: 225, Visits: 700
If your goal is just to keep the two tables in sync you could just use transactional replication from the source to the subscriber (whichever server is which). if they both need to update each other, meaning the updates are not always in the same direction, you can have transactional replication with updating subscriber. If there is more than one subscriber you then can try using merge replication.
Post #1453288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse