Solved

Database synchronization

Posted on 2011-02-23
6
456 Views
Last Modified: 2012-05-11

Hello group,

I'm having a situation which need to get some advice from experts who have imported data from an existing database (traditional system ) into a different/new database.

Long story short, there is an old IBM machine which is used and still in use of a company. Now, this company has decided to go online but after I've setup the online system has come up with different idea! meaning that they want the new database (MS SQL Server) be synchronized with their DB2.

Of course, as you have guessed both will be dealing with the same product but the layout, structure and tables are totally different.

What is the best approach base on your experience?

correct me if I'm wrong. I'm thinking of creating some tables the same as those on DB2 end and somehow maybe using trigger or ... feed them into new tables.

Assume there is a customer table on DB2 end (let's say db2_Customer) so I will import it into MS SQL Server 2008 and will extract necessary information from temp table into my customer table.

Currently I'm kind of jumping around since time is another issue but will appreciate it experts could give me some advice.


Regards,
Amit
0
Comment
Question by:akohan
6 Comments
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 100 total points
ID: 34961604
If you want to go down the trigger route then here is how to create a linked server so you can directly insert into the destination.

http://www.sqlcoffee.com/Tips0013.htm

Although bear in mind that this will slow your system down and make it dependant on the DB2 system.
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 100 total points
ID: 34961648
Amit,

Keeping two systems on different platforms synchronized is not a simple task. Considering it's two different rdbms makes it even more complex.
You can try to pull it off using triggers, but i think it will get very complicated and hard to maintain
 
What is the level of sync that is required? can there be a lag of 2 hours for example?
Which system gets updated? only db2 or both? (is it a 2 way sync, 1 way sync etc...)
are you able to identify the new data so that you can only synchronize the differences every time and not all the data?
0
 

Author Comment

by:akohan
ID: 34962251

Hello Momi_Sabag,

Thank for your questions I will try to explain the scenario but please ask me questions if I have not covered something:

We have two ends:

1) Online (SQL Server)
2) IBM (DB2)

Online system has all the products info that will be sold online however, DB2 has the same information and also customer information (from 30 years ago!).
The scope of project was that people will register from online end and was assumed that people would then can find product/parts and add them to cart and place an order.

However, now in the last step the person (working with managers who have no technical knowledge) I'm work for has explained that  they must have existing customer information in online (SQL Server) since existing customer can have their special pricing (base on a discount % parameter).


So as you see my input is changing from online registration page to data coming from DB2. Also, I need to find out what is the discount rate for each existing customer that comes from DB2 as well.

The way I see it, it is a one way sync (from DB2 to SQL Server). So when one of those customer places an order I will pass order details and customer information to DB2 using csv file so somebody on their team can suck it up into DB2.

As far as lag,  I guess for now it would be OK 2 hours all they will do they will send an email to customer that order has been placed and ...

Let me know if you have further questions.

Thanks.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 34962339
so if it is only pulling data from db2 to sql server i would use SSIS to perform this movement. by using a job you can schedule the package to run almost continuously
0
 

Author Comment

by:akohan
ID: 34962768

But one thing worries me is that you as a customer by placing an order online should be able to see a history of all orders, but this way since all taxing and shipping calculations will happening on IBM end (since it has its own system) then I only can issue and keep qty and price before tax and shipping.

and updating that again will burden the system.

Any idea?
0
 
LVL 7

Accepted Solution

by:
rmm2001 earned 300 total points
ID: 34989893
Going off of @momi_sabag - SSIS would be an easy-to-setup tool for this. Drag and drop and you're there.

If you want to allow your client to see their historical transaction history (I assume it's stored in DB2), you have a few options.

- Write a SSIS package that pulls the history for that client into some sort of format that the GUI can read/use (so like txt/csv)
- Write a SSIS package that is called when the client enters the site, which pulls his records over to the SQL Server database. After that session is done, those records are deleted from the table. Which would require 2 packages. (I'm not sure if I'm a fan of this)
- Link the servers. So link your DB2 server to SQL Server and you can query [servername].databasename.schemaname.tablename just like you normally would query a "regular" database. This would allow for either database to be updated and the query to reflect it. And minimize the code changes the programmers may need to make. http://support.microsoft.com/kb/222937

See if that works!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now