Solved

How to merge two Sybase databases into one in a case where a business has acquired another

Posted on 2008-11-02
6
962 Views
Last Modified: 2013-11-15
Hello,

I am stuck with a difficult task. I work for a company that uses an IP Management program with a back end database called Sybase 12.2 ASE.
Both companies use this program but both contain information of their own.
My task is to somehow get Company A's data into company B's database.
Are there any tools or a way to achieve this?
I assume I will have an issue with duplicate primary keys. If that is the case, will this be a show stopper?
Also, is there a sql command that I can run to compare all the data from Company A to Company B to identify if there will be any record conflicts? If so, how?
Mind you, I am not a DBA expert at all, I was just given this task and I want to prove myself and secure my position better.

Any help would be grateful
0
Comment
Question by:richsark
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 22861675
Well, since there is not Sybase ASE 12.2, I believe you are actually talking about 12.5.2   You can check this by logging into the database with something like ISQL and doing a SELECT @@version.

As far as magic tools to do all this for you, I'm afraid you are out of luck.  The task you have in front of you will range from not to bad to a huge pain in the butt.  It depends primarily on the data model and how the application assigns keys.  The good news is that there are probably no show stoppers but it could get pretty complicated.

The first thing to do is evaluate the data model.  I would get a hold of the Sybase Windows Client installation CD for 12.5.2.  On it you will find a free copy of PowerDesigner Physical Architect.  Install it and then reverse engineer the database into a model.  There you can beging to get a feel for how the beast works.  Things like how much declarative referential integrity is in place and whether the keys are assigned by the application or if it uses identity columns in the database.

From the data model you can also get an appreciation for how the data is organized.  For instance, you want the data from two organizations loaded into the same database.  Do you need them to stay seperate organizations or was this a merger and now everything is going to be in one organization?

This is a fairly significant project you are undertaking.  It is not possible to give you step by step instructions because there are just too many variables.  I can tell you that you are going to have to develop those DBA skills in a big hurry.  My company does this kind of thing all the time.  If you want to talk more in general terms, you can find my contact information in my profile.

BTW, step one is to build a development environment.  You are going to need a machine with enough disk to hold both databases twice plus at least two dumps of each.

Regards,
Bill
0
 
LVL 1

Author Comment

by:richsark
ID: 22861772
Hello Bill, and thanks for your reply.

You are right, its Sybase12.5.2. In Reference to your question, I would want them in the same organization. Right now its two separate servers in two different locations and both instances have different organizations.  

Perhaps, I can start small and simply start with Objects, forward and reverse.

Are you able to provide me a sql script to start comparing?

Also, would DBArtisan help me in any way?

Thanks
0
 
LVL 19

Expert Comment

by:grant300
ID: 22863937
Any decent query tool will help.  My guess is that before it is all over with, you are going to be writing a bunch of stored procedures to do the data conversions.

DBArtisan is O.K. though I prefer an older version of RapidSQL Pro.  7.3.4 was the last good version before they decided to rewrite everything and really buggered it up, at least for Sybase folks.

There are also some other choices.  Aquafold Aqua Data Studio is pretty good and just keeps getting better.  It is only $395 instead of $995 for RapidSQL.  You can also get a copy of Sybase Workspace.  I think it is still free until the end of December or you can join the ISUG (International Sybase Users Group) for $99 and get full licensed copies for free.

I have no idea what you mean by Objects, forward and reverse.

I can't give you a magic SQL script or query unless/until I know what the tables and keys look like.  If, by some highly unlikely miracle, all of the primary keys contain the ORGANIZATION field, you will not have any collisions.  Otherwise, you can be pretty well assured that there will be problems.  Most well designed databases use IDs on each record; integer or numeric fields that uniquely identify each row in the table.  As such, I cannot imagine you won't have duplicates.  What is more, if they are identity columns (auto-number), you have to pull some tricks in order to assign new ones and get them into the target tables.

Also, you can't do any SQL on the data until you get it all in one place.  Again, you have to start with a development environment, create two databases, one for org-A and one for org-B, dump the production databases and restore them onto the development DBs.  Then you can write queries to see where you key collisions are since you can refer to tables from different databases (in the same server of course) in a single query.

For instance....(snippet 1)  This will give you a list of the rows in TableX that have the same primary key.  Now that may be way more information than you can swallow; you may just want the count.  For that, see snippet 2...

Of course, all this gets more complicated when you have composite primary keys.  You may also have both IDs and Unique Business Keys on the same tables.  Either one or neither of them may be defined as the primary key.  In fact, any unique index in the database schema needs to be evaluated.

Of course, none of this can be done or makes any sense without first looking at the data model.  You really need to get a copy of PowerDesigner 10 and reverse engineer it before you even know what to start looking at.

Regards,
Bill

SELECT < Identifying information from both tables >

  FROM ORGA.dbo.TableX AX

  JOIN ORGB.dbl.TableX BX

    ON AX.PrimaryKeyField = BX.PrimaryKeyField
 

SELECT AX.PrimaryKeyField PKF

  FROM ORGA.dbo.TableX AX

UNION ALL

SELECT BX.PrimaryKeyField

  FROM ORGB.dbl.TableX BX

GROUP BY PKF

HAVING COUNT(*) > 1

Open in new window

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:richsark
ID: 22864137
Hello, Excellent info Sir. You referenced Snippet 2? I dont seem to see it. Just Snippet 1 ( unless its combined and I did not see it :)

If I get you the Schema, could you see if this is even possible?

Perhaps rather than trying to merge the whole database.. I can start with a step by step and start with IP addresses that contain Host names and reverse records, reverse records are IP to host and fwd is name to IP.

I also need to query if there are any resource records that reference the above and if they any way conflict with either database.

I know these terms maybe more than you need to know, but perhaps it may shed some light.

I looked you up and sent you an email, perhaps I can send you the schema there?

I really appreciate your help ( or anyone's help for that matter) as this is high level and I really need guidance.

Thanks

0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 22864357
Two statements in the same "snippet" I guess is a better way to put it.  They don't let you attach more than one.

Well, now I have a bit better idea of the subject matter.  IP is an overloaded term; I made the incorrect assumption you were talking about "Intellectual Property" information, perhaps for a law firm or a clearing house of some kind.


Yes, if I had the schema, I could begin to evaluate the practicality and scope of your project.  Feel free to send it and I will take a look at it.  Monday is a bit crammed full right now but I will try to get back to you in the evening.

Regards,
Bill
0
 
LVL 1

Author Comment

by:richsark
ID: 22866551
Hello,

great, I will email it to you rather than post it.

Lets touch base later in the evening Monday

Regards,
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax 12 63
Space Delimited Sql File 4 70
Determine next b-weekly date 12 56
Trouble connecting to SqlServer database 4 33
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

17 Experts available now in Live!

Get 1:1 Help Now