January 6, 2015 at 10:35 pm
Hello and Happy New Year!
I'd like to be able to run a utility that will look at specific tables from to different databases on two different servers (same domain), and produce a script that will create the update/insert sql script will have all the missing or updated rows from both databases, that I can then run against a third database and have all those rows inserted into the tables of a third database.
Can I do this with TableDiff, and can I put it into an SSIS package?
If yes, is there anyone out there can can offer a bit of advice and initial guidance to get me started, please?
I do not have any budget to buy anything third party. It has to be native. If TableDiff will not help me what else could I do? Is there anything in SSIS that can do this? I'm running SQL 2008 R2.
Thanks in advance everyone. And thank you for reading.
Regards,
D.
January 7, 2015 at 8:01 am
Duran (1/6/2015)
Hello and Happy New Year!I'd like to be able to run a utility that will look at specific tables from to different databases on two different servers (same domain), and produce a script that will create the update/insert sql script will have all the missing or updated rows from both databases, that I can then run against a third database and have all those rows inserted into the tables of a third database.
Can I do this with TableDiff, and can I put it into an SSIS package?
If yes, is there anyone out there can can offer a bit of advice and initial guidance to get me started, please?
I do not have any budget to buy anything third party. It has to be native. If TableDiff will not help me what else could I do? Is there anything in SSIS that can do this? I'm running SQL 2008 R2.
Thanks in advance everyone. And thank you for reading.
Regards,
D.
This would depend on your tables being static at the time of comparison, but how about.
1. BCP all data from ServerA.DatabaseX.TableX to a flat file
2. Compare ServerA.DatabaseX.TableX to ServerB.DatabaseX.TableX using the tablediff utility and output the T-SQL file from the comparison that performs the inserts \ updates \ deletes
3. Load the file from step 1 to DatabaseC.TableX using BCP
4. Execute the SQL script from Step 2 on ServerX.DatabaseX.TableX
All of this can done from within SSIS with normal SSIS components.
MCITP SQL 2005, MCSA SQL 2012
January 7, 2015 at 3:47 pm
Hi RTaylor,
Thank you for responding, an interesting path to go down, I believe all the tables will be static when the comparison is made, but change every month. With the BCP part, I think it would mean that I'd have to export every table I need to compare separately and that would be quite a large undertaking on a month by month basis, and may mean produce lots of separate scripts for each table for the third database.
Regards,
D.
January 11, 2015 at 3:42 pm
Hello,
I got a slightly more up to date version of what it is I need to do.
I need to take the latest changes out of a SQL database and put them into a DB of staging tables (which I will create). For this I plan to use CDC, as it looks like it does exactly what I need.
Then I need to compare the data in the staging tables to the tables in another SQL database,and from that produce a script that will have all the updates, inserts, and deletes to go into and update a third SQL database. For this part I thought Tablediff would be the way to go, and use it in a SSIS batch job. OR would a Lookup transform be better? If I used a Lookup transform, I dont think I'd get the script I need.
Would a CDC splitter be an option? I understand that it will split the inserts, updates, deletes into 3 separate outputs, but a major issue but I'd need to merge them into one scripts to send out to the third databases that get updated by it. I'm not sure where that fits in though.
I'd really appreciate any input here, I'm reletively new to SSIS and I'd fee much better if I knew I was going down the right path.
Thanks again for reading and any responses.
Regards,
D.
January 11, 2015 at 6:53 pm
Why not just setup Replication?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 10:40 pm
Hi Jeff,
Thanks for the response, well the third databases/s are not on the same network, the script with the updates has to be sent to them and applied when they get there.
Regards,
D.
January 12, 2015 at 4:45 am
See answers inline:
Duran (1/11/2015)
Hello,I got a slightly more up to date version of what it is I need to do.
I need to take the latest changes out of a SQL database and put them into a DB of staging tables (which I will create). For this I plan to use CDC, as it looks like it does exactly what I need.
Then I need to compare the data in the staging tables to the tables in another SQL database,and from that produce a script that will have all the updates, inserts, and deletes to go into and update a third SQL database. For this part I thought Tablediff would be the way to go, and use it in a SSIS batch job. OR would a Lookup transform be better? If I used a Lookup transform, I dont think I'd get the script I need.
Where will the staging DB live? Will it be in an SQL Server that the can connect to the SQL Server hosting the database you suggest sourcing the CDC Data from? The reason I ask is Jeff's suggestion for replication makes the most sense if these two databases have connectivity. You can either have a full copy of all the data that is in the source or you can have only the changes that have happened since the publication(s) were setup if they are no sync subscriptions..
Would a CDC splitter be an option? I understand that it will split the inserts, updates, deletes into 3 separate outputs, but a major issue but I'd need to merge them into one scripts to send out to the third databases that get updated by it. I'm not sure where that fits in though.
Correct me if I am wrong but the CDC Splitter SSIS component only exists in SQL 2012 \ 2014 and since this is posted in the 2008 part of the forum I assume you are on 2008 or at least R2, in this case you would need to create your tasks to do the same as the splitter functionality yourself.
I'd really appreciate any input here, I'm reletively new to SSIS and I'd fee much better if I knew I was going down the right path.
Thanks again for reading and any responses.
Regards,
D.
This is maybe a bit of push but if you could create even a rudimentary diagram of your servers and databases involved so I can get my head around connectivity I can better advise a potential solution.
MCITP SQL 2005, MCSA SQL 2012
January 12, 2015 at 3:37 pm
Hello, again thanks for getting back, yes your right it is 2008, I had just read about the CDC splitter and you are of right, its for 2012.
Where will the staging DB live?
Yes, will be in the live environment.
Will it be in an SQL Server that the can connect to the SQL Server hosting the database you suggest sourcing the CDC Data from?
The source DB, the staging table, and the database I need to compare with the staging table can all connect with each other.
The reason I ask is Jeff's suggestion for replication makes the most sense if these two databases have connectivity.
You can either have a full copy of all the data that is in the source or you can have only the changes that have happened since the publication(s) were setup if they are no sync subscriptions.
Yes, it does make sense, but I am not sure how I would produce the update,insert, and delete scripts that will be used to update the databases that are not connected? Also, the updates will be intermittent, say every couple of weeks. Perhaps I am missing something? What do you mean when you say 'if they are no synch subscriptions'?
Regards,
D.
January 13, 2015 at 9:05 am
Duran (1/12/2015)
Hello, again thanks for getting back, yes your right it is 2008, I had just read about the CDC splitter and you are of right, its for 2012.Where will the staging DB live?
Yes, will be in the live environment.
Will it be in an SQL Server that the can connect to the SQL Server hosting the database you suggest sourcing the CDC Data from?
The source DB, the staging table, and the database I need to compare with the staging table can all connect with each other.
The reason I ask is Jeff's suggestion for replication makes the most sense if these two databases have connectivity.
You can either have a full copy of all the data that is in the source or you can have only the changes that have happened since the publication(s) were setup if they are no sync subscriptions.
Yes, it does make sense, but I am not sure how I would produce the update,insert, and delete scripts that will be used to update the databases that are not connected? Also, the updates will be intermittent, say every couple of weeks. Perhaps I am missing something? What do you mean when you say 'if they are no synch subscriptions'?
Regards,
D.
It sounds like from your description that the staging DB will either have all the data as it stands in the live database but only for the tables you want to keep synchronised or you only want the changes (Inserts \ Updates and Deletes) that have occurred added to the staging database. Can you confirm which?
A no sync subscription is a subscriber which does not require a snapshot of the published articles in order to initialise the subscription i.e. no existing data is synchronised. Thinking about it, the only time I use these is when you want all new inserts to the table(s) but are not concerned with the updates and deletes, so that may not be suitable.
Will have a further think about it and post back when I have a better plan.
MCITP SQL 2005, MCSA SQL 2012
January 13, 2015 at 3:25 pm
Hello,
It sounds like from your description that the staging DB will either have all the data as it stands in the live database but only for the tables you want to keep synchronised or you only want the changes (Inserts \ Updates and Deletes) that have occurred added to the staging database. Can you confirm which?
This one!
You can see why I thought CDC on the source database would be a good choice, I still think its an option for me. Get the changes into the staging table, then do a Tablediff compare with the second database, and get the script containing the Inserts \ Updates \ Deletes for the third off-the-network databases.
Thanks for the info on the no synch, I'd not heard of that and I do replication quite a lot, must have just not fallen within my scope!
Thanks for all your help and time.
Regards,
D.
January 13, 2015 at 9:57 pm
ignore
January 14, 2015 at 5:47 am
I'm still a bit unclear on what actually you need. This is my understanding:
You have 3 SQL Instances lets call them A, B and C.
Instance A and B are on the same network, instance C is on another network.
The instances are configured as follows:
Instance A has Database1
Instance B has Database2
Instance C has Database3
For specific tables in Database3 you want to at least once per month have the table updated to have all rows for the same table which is a combination of the data in InstanceA.Database1 and InstanceB.Database2.
Is this the correct assumption?
It may be helpful if you could post up the code to create a example DB for each database involved with one table in each which is what you want to compare and merge to the 3rd Database.
There is so many unknowns with your environment and databases \ tables that I can't necessarily give you a good plan to start with.
A perfect example of this being when comparing the tables what will you compare on? Do the tables in InstanceA.Database1 and InstanceB.Database2 use the same primary key value for rows so that you could compare one row to another knowing that the primary key values should always match the same row of data?
MCITP SQL 2005, MCSA SQL 2012
January 14, 2015 at 10:37 pm
You have 3 SQL Instances lets call them A, B and C.
Instance A and B are on the same network, instance C is on another network.
The instances are configured as follows:
Instance A has Database1
Instance B has Database2
Instance C has Database3
For specific tables in Database3 you want to at least once per month have the table updated to have all rows for the same table which is a combination of the data in InstanceA.Database1 and InstanceB.Database2.
Is this the correct assumption?
Yes, bang on.
I had a look at the Primary Keys on a few tables on the DBs (A & B), both DBs contain the same columns, but only on one if the databases is it actually a primary key column, on the other its just a column. This may not be the case for all the tables, but was on the few that I looked at.
Regards,
D.
January 16, 2015 at 4:59 am
Sorry for the slow reply.
How about this:
1. Create a staging database on InstanceA.
2. Copy the tables you require from Database1 into the staging database
3. Use tablediff to compare the tables in the staging database to the tables in InstanceB.Database2
4. Update the staging database in Instance1 with the scripts from the step above
5. Backup the staging database on InstanceA, copy the backup to InstanceC using whatever method you can depending on connectivity
6. Restore the staging database on InstanceC
7. Use the tablediff utility again to compare InstanceC.Staging to InstanceC.Database3 and update the Database3 tables, or just truncate the tables in InstanceC.Database3 and re-load them from the staging database.
MCITP SQL 2005, MCSA SQL 2012
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply