Monday, March 19, 2012

Database Schema Conversion

Hello all,

I am doing some research on database conversions. Currently, I am
interested in any information that would help me convert a database from
one schema to another. This could be changes as minimal as adding a
field to a table, or as large as deleting tables and changing
relationships. Unfortunately, my experience with SQL Server is minimal.
I know how to do a lot, but I do not know a lot of intricacies that
most experts know. I know how to add tables, delete them, alter
relationships, add fields, work with stored procedures, take care of
security, etc. I also know how to backup, restore, etc.

The type of information I am looking for could be:

1) Open source software that performs conversions
2) Tutorials/books/<any reference> that would assist me in learning
what I must to complete this task.
3) Third party software that could be used on a large scale and
wouldn't resort in unnecessary licensing cost if I was to deploy on this
large scale.

I greatly appreciate any information that could be provided me.

To give you guys an idea of my experience level:

I've been programming C# and .NET for a year now. I've also had
extensive experience in object-oriented design. I've worked with visual
basic, cobol (did I mention this? LOL), asp.net, php, javascript, and
several other programming languages on an extensive basis. While
programming is my speciality, I've strayed away from database work until
now. I would greatly appreciate any assistance in researching this matter.

Thanks ahead guys,

ShockHello Shock
Glad to hear that you are researching on databases. Databases
conversion is indeed an interesting topic. I havent, yet, heard about
any open source data conversion tools. There may be some but their
credibility cant be assured - although some of them may work fine.
In any case, if one intends to develop his own one, then there are
many factors that have to be considered. First comes the database
architectures which often vary with one database to another, even
though the working principles may be the same. As an example, suppose
if one tries to convert an SQL Server database to an Oracle one, the
database storage format of both becomes the primary issue. What i mean
to say that if there are 'N' number of databases you will have to
study all those 'N' architectures.
As yet i am not sure that there are any good books dealing with
data conversions in a satisfactory manner.
On the contrary, one also can use the conversion tools that exist.
Data Transformation Services(DTS) that comes with SQL Server is an
efficient tool that can be used for this purpose, provided that the
target database has an OLEDB provider or an ODBC driver. Other tools,
by other database vendors do exist.
And indeed, it is a very big topic, which can have an entire college
semester devoted to it.
Do let me know if you have something valuable to tell me. I am
always eager to learn about such interesting ideas.

With Regards
Debashish|||Hi
You may want to look at: http://www.aspfaq.com/show.asp?id=2442

MSDE does come with bcp which can be used to import/export large amounts of
data or if you are just doing a installation then a database can be shipped
and attached or restored from a backup. You can also incorporate MSDE into
you own installation routine.

John

"Shock" <no@.way.com> wrote in message
news:10ne3q4rb7bvicf@.corp.supernews.com...
> Hello all,
> I am doing some research on database conversions. Currently, I am
> interested in any information that would help me convert a database from
> one schema to another. This could be changes as minimal as adding a
> field to a table, or as large as deleting tables and changing
> relationships. Unfortunately, my experience with SQL Server is minimal.
> I know how to do a lot, but I do not know a lot of intricacies that
> most experts know. I know how to add tables, delete them, alter
> relationships, add fields, work with stored procedures, take care of
> security, etc. I also know how to backup, restore, etc.
> The type of information I am looking for could be:
> 1) Open source software that performs conversions
> 2) Tutorials/books/<any reference> that would assist me in learning
> what I must to complete this task.
> 3) Third party software that could be used on a large scale and
> wouldn't resort in unnecessary licensing cost if I was to deploy on this
> large scale.
> I greatly appreciate any information that could be provided me.
> To give you guys an idea of my experience level:
> I've been programming C# and .NET for a year now. I've also had
> extensive experience in object-oriented design. I've worked with visual
> basic, cobol (did I mention this? LOL), asp.net, php, javascript, and
> several other programming languages on an extensive basis. While
> programming is my speciality, I've strayed away from database work until
> now. I would greatly appreciate any assistance in researching this
matter.
> Thanks ahead guys,
> Shock|||debashish wrote:
> Hello Shock
> Glad to hear that you are researching on databases. Databases
> conversion is indeed an interesting topic. I havent, yet, heard about
> any open source data conversion tools. There may be some but their
> credibility cant be assured - although some of them may work fine.
> In any case, if one intends to develop his own one, then there are
> many factors that have to be considered. First comes the database
> architectures which often vary with one database to another, even
> though the working principles may be the same. As an example, suppose
> if one tries to convert an SQL Server database to an Oracle one, the
> database storage format of both becomes the primary issue. What i mean
> to say that if there are 'N' number of databases you will have to
> study all those 'N' architectures.
> As yet i am not sure that there are any good books dealing with
> data conversions in a satisfactory manner.
> On the contrary, one also can use the conversion tools that exist.
> Data Transformation Services(DTS) that comes with SQL Server is an
> efficient tool that can be used for this purpose, provided that the
> target database has an OLEDB provider or an ODBC driver. Other tools,
> by other database vendors do exist.
> And indeed, it is a very big topic, which can have an entire college
> semester devoted to it.
> Do let me know if you have something valuable to tell me. I am
> always eager to learn about such interesting ideas.
> With Regards
> Debashish

Debashish,

Thanks for the info. One area that isn't an issue is converting from
one database type to another (i.e. Oracle to SQL Server). Basically, I
will just be opening a database that holds data and making changes to
it, like adding a table, field, or adjusting a relationship. I'm going
to look more into the DTS this week, so if I come up with some good
stuff I"ll be sure to post it.

Thanks for the info!

Shock|||Shock <no@.way.com> wrote in message news:<10ne3q4rb7bvicf@.corp.supernews.com>...

> 3) Third party software that could be used on a large scale and
> wouldn't resort in unnecessary licensing cost if I was to deploy on this
> large scale.

I think I could help most here. My company has already written a
utility to create a relational schema from a hierarchical data format,
and could modify it to handle other transformaions as well. It's
something that could be done very cost effectively on both the small
and large scale. Feel free to give me a call.

Charles Churchill II
Account Executive
Datatek, Inc
800 536-4835 ext. 145
1 919 425-3145 (international)
churchil@.datatek-net.com|||Charles Churchill wrote:

> Shock <no@.way.com> wrote in message news:<10ne3q4rb7bvicf@.corp.supernews.com>...
>
>>3) Third party software that could be used on a large scale and
>>wouldn't resort in unnecessary licensing cost if I was to deploy on this
>>large scale.
>>
>
> I think I could help most here. My company has already written a
> utility to create a relational schema from a hierarchical data format,
> and could modify it to handle other transformaions as well. It's
> something that could be done very cost effectively on both the small
> and large scale. Feel free to give me a call.
> Charles Churchill II
> Account Executive
> Datatek, Inc
> 800 536-4835 ext. 145
> 1 919 425-3145 (international)
> churchil@.datatek-net.com

Charles,

Thanks for the info, once things get rolling I'll do just that!

Shock

No comments:

Post a Comment