How to stay flexible?

I'm currently using CFMX with an SQL Server 2000. However, I'm moving to
more cost efficient solutions such as ASP.NET and some other database, which
I haven't determined yet. The database will probably be MSDE or MySQL. I
don't do many transactions, so either of those two choices should be fine.
In general, are there any rules to keeping your database very flexible?
Meaning, it won't be difficult for me to move everything from SQL Server to
MSDE. Or from MSDE to MySQL. Are there any general pointers or guidelines
that can be followed if I'm unsure how long I may stay with some particular
database vendor?
Same goes for the front end and application level logic. I don't want
either to be tied into a specific database vendor. Not sure how possible
any of these are but any comments are welcome.
Thanks,
BrettSQL Server to MSDE and back is a no-brainer. They are the same product. Goin
g to other vendors, your
best bet is to confine to ANSI SQL. Note that some vendors do not support AN
SI SQL (also, there are
different revisions of the ANSI SQL standard). Study up on the candidates an
d see what level of
compliance to the ANSI SQL standard they have.Then try to confine your code
to that.
http://www.mimer.com/ has a SQL Validator, might be worth checking out.
But be aware that you will be missing out on a lot of good stuff. Things lik
e stored procedures,
triggers, a lot of the system functions to mention a few things in SQL Serve
r which aren't ANSI SQL
compliant (and nor are Oracle's and possibly DB2's either).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brett" <no@.spam.com> wrote in message news:%23%23ciH72PFHA.4028@.tk2msftngp13.phx.gbl...[co
lor=darkred]
> I'm currently using CFMX with an SQL Server 2000. However, I'm moving to
more cost efficient
> solutions such as ASP.NET and some other database, which I haven't determi
ned yet. The database
> will probably be MSDE or MySQL. I don't do many transactions, so either o
f those two choices
> should be fine.
> In general, are there any rules to keeping your database very flexible? Me
aning, it won't be
> difficult for me to move everything from SQL Server to MSDE. Or from MSDE
to MySQL. Are there
> any general pointers or guidelines that can be followed if I'm unsure how
long I may stay with
> some particular database vendor?
> Same goes for the front end and application level logic. I don't want eit
her to be tied into a
> specific database vendor. Not sure how possible any of these are but any
comments are welcome.
> Thanks,
> Brett
>[/color]|||There is no conversion moving from SQL to MSDE as they are essentially same
engine. However, moving from SQL Server to MySQL or any other product is a
different matter. First and foremost, it depends on what you are moving. If
you
have no stored procs, user defined functions or user defined data types, the
n
moving to anything else should not problem. The data is generally the easies
t
thing to move. It's everything other than the data that is a challenge.
Thomas
"Brett" <no@.spam.com> wrote in message
news:%23%23ciH72PFHA.4028@.tk2msftngp13.phx.gbl...
> I'm currently using CFMX with an SQL Server 2000. However, I'm moving to
more
> cost efficient solutions such as ASP.NET and some other database, which I
> haven't determined yet. The database will probably be MSDE or MySQL. I d
on't
> do many transactions, so either of those two choices should be fine.
> In general, are there any rules to keeping your database very flexible?
> Meaning, it won't be difficult for me to move everything from SQL Server t
o
> MSDE. Or from MSDE to MySQL. Are there any general pointers or guidelin
es
> that can be followed if I'm unsure how long I may stay with some particula
r
> database vendor?
> Same goes for the front end and application level logic. I don't want eit
her
> to be tied into a specific database vendor. Not sure how possible any of
> these are but any comments are welcome.
> Thanks,
> Brett
>|||MySQL Server is probably the top candidate as it is supported very widely.
MSDE isn't. If in my current application I make use of stored procedure
calls to SQL Server 2000, I suppose the best way to make the application
available for a database switch is to just put all of the database calls
into some type of encapsulation. Inside of the encapsulation will be either
a stored procedure call or a query, depending on the database. That sound
right?
Thanks,
Brett
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O20NqZ3PFHA.3196@.TK2MSFTNGP12.phx.gbl...
> SQL Server to MSDE and back is a no-brainer. They are the same product.
> Going to other vendors, your best bet is to confine to ANSI SQL. Note that
> some vendors do not support ANSI SQL (also, there are different revisions
> of the ANSI SQL standard). Study up on the candidates and see what level
> of compliance to the ANSI SQL standard they have.Then try to confine your
> code to that. http://www.mimer.com/ has a SQL Validator, might be worth
> checking out.
> But be aware that you will be missing out on a lot of good stuff. Things
> like stored procedures, triggers, a lot of the system functions to mention
> a few things in SQL Server which aren't ANSI SQL compliant (and nor are
> Oracle's and possibly DB2's either).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brett" <no@.spam.com> wrote in message
> news:%23%23ciH72PFHA.4028@.tk2msftngp13.phx.gbl...
>|||Yep, this is what I would recommend. Keeping the SQL code ANSI compliant wil
l limit you very much
(to the level where you can't use stored procedures against he major DBMS ve
ndors). Encapsulate the
database code and you can exploit the DBMS to the level you feel comfortable
with and which makes
sense to your project.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brett" <no@.spam.com> wrote in message news:OFK$vl3PFHA.2132@.TK2MSFTNGP14.phx.gbl...darkred">
> MySQL Server is probably the top candidate as it is supported very widely.
MSDE isn't. If in my
> current application I make use of stored procedure calls to SQL Server 200
0, I suppose the best
> way to make the application available for a database switch is to just put
all of the database
> calls into some type of encapsulation. Inside of the encapsulation will b
e either a stored
> procedure call or a query, depending on the database. That sound right?
> Thanks,
> Brett
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O20NqZ3PFHA.3196@.TK2MSFTNGP12.phx.gbl...
>