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: Wednesday, November 19, 2014 3:00 AM
Points: 21, Visits: 160
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: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
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
Post #1421581
Posted Wednesday, February 20, 2013 10:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:00 AM
Points: 21, Visits: 160
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: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
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
Post #1422522
Posted Thursday, May 9, 2013 11:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:40 PM
Points: 421, Visits: 1,000
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 @ 10:05 AM
Points: 69, Visits: 91
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-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:40 PM
Points: 421, Visits: 1,000
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