Best pratices to manage the "same logical" database on multipledatabase engine types (postgreSQL, SLQServer, etc..)

[sabbadin12]

Hi,
I'm going to work on an application that uses a postgreSQL database so
that it can uses SQLServer 2005.

I think I solved most problems on the programming side, but I still
have some doubts on the DB side regarding how to handle the creation
of the db schema on sqlserver and how to handle the every day dba
work.
1) should I try to use an ER tool like Embarcadero and have its
logical model be the master copy ? (i did some tests, it looks like i
have to massage the SQL produced a littel bit to have sqlserver l ike
it).
2) should I handle two separate sql scripts and update both of them
when something change, Additinally I have to produce "the diff"
scripts for upgrades, so that I should keep in sync 4 scripts.
3) Should I try to develop an XML based DDL syntax (like xml2dll) and
a parsing engine, so that I can handle just 2 scripts (create from
scracth and diff).
4) What should I use to move data back & forth ? SSIS (ex dts) or
something else ?

What do you suggest ?
thank you
Best Regards
Enrico Sabbadin

[Robert Klemme]

On 22.05.2008 09:19, sa********@gmail.com wrote:

Hi,
I'm going to work on an application that uses a postgreSQL database so
that it can uses SQLServer 2005.

I think I solved most problems on the programming side, but I still
have some doubts on the DB side regarding how to handle the creation
of the db schema on sqlserver and how to handle the every day dba
work.
1) should I try to use an ER tool like Embarcadero and have its
logical model be the master copy ? (i did some tests, it looks like i
have to massage the SQL produced a littel bit to have sqlserver l ike
it).

Alternative product that I like quite well: PowerDesigner.

2) should I handle two separate sql scripts and update both of them
when something change, Additinally I have to produce "the diff"
scripts for upgrades, so that I should keep in sync 4 scripts.
3) Should I try to develop an XML based DDL syntax (like xml2dll) and
a parsing engine, so that I can handle just 2 scripts (create from
scracth and diff).
4) What should I use to move data back & forth ? SSIS (ex dts) or
something else ?

5) How will you deal with schema migration?

All valid and good questions. Theoretically the single source solution
(3) looks like the best. Whether it is also optimal in practice depends
on a number of factors. For example, if your change frequency of the
schema is low and / or you need very DB specific features then it might
not be worthwhile to restrict yourself to some tool's DB specific output
and maintain schemes manually. On the other hand, if you need to
maintain a lot different DB products then it may pay off to use the
single source approach. But then you still have the schema migration
issue - unless you can afford to export all data, kill the old schema,
create the new schema and reimport (e.g. with small data volume).

My 0.02 EUR: be skeptical about tools that promise to easily manage a
schema for a number of database products.

Kind regards

robert

[Dan Guzman]

I don't think there is a single Best Practice because much depends on the
volatility of the schema, toolset and skillet.

1) should I try to use an ER tool like Embarcadero and have its
logical model be the master copy ? (i did some tests, it looks like i
have to massage the SQL produced a littel bit to have sqlserver l ike
it).

ER tools vary in their ability to generate scripts that require minimal
customization and some of the script gen features can be complex. If you
need to do a lot of work for your needs, consider evaluating other ER
products That said, diff scripts may require tweaking anyway to address
special considerations (e.g. large production data volumes).

2) should I handle two separate sql scripts and update both of them
when something change, Additinally I have to produce "the diff"
scripts for upgrades, so that I should keep in sync 4 scripts.

I'd keep 4 scripts (new and upgrade for each DBMS) and under source control.
Although the ER model can be considered the master, the model doesn't
mitigate configuration and release management Best Practices.

3) Should I try to develop an XML based DDL syntax (like xml2dll) and
a parsing engine, so that I can handle just 2 scripts (create from
scracth and diff).

IMHO, this should be last resort.

4) What should I use to move data back & forth ? SSIS (ex dts) or
something else ?

We use SSIS for general ETL processes. I've only had one SSIS project that
used PostgreSQL, though. I found the open source OLDB drivers buggy and
ended up using ODBC instead.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<sa********@gmail.comwrote in message
news:de**********************************@34g2000h sh.googlegroups.com...

Hi,
I'm going to work on an application that uses a postgreSQL database so
that it can uses SQLServer 2005.

I think I solved most problems on the programming side, but I still
have some doubts on the DB side regarding how to handle the creation
of the db schema on sqlserver and how to handle the every day dba
work.
1) should I try to use an ER tool like Embarcadero and have its
logical model be the master copy ? (i did some tests, it looks like i
have to massage the SQL produced a littel bit to have sqlserver l ike
it).
2) should I handle two separate sql scripts and update both of them
when something change, Additinally I have to produce "the diff"
scripts for upgrades, so that I should keep in sync 4 scripts.
3) Should I try to develop an XML based DDL syntax (like xml2dll) and
a parsing engine, so that I can handle just 2 scripts (create from
scracth and diff).
4) What should I use to move data back & forth ? SSIS (ex dts) or
something else ?

What do you suggest ?
thank you
Best Regards
Enrico Sabbadin