Thread: plug-n-play MS SQL Server replacement

I've been using PostgreSQL for a few months now and am very happy with its
functionality and performance.  Top-notch product that competes favorably
with every DB I've used.

However, most of my tables are still in MS SQL Server 2000 Enterprise.  I
often have to make it my target for purposes of compatibility.  On some
applications, the license costs are killers at around $400/client.

So, here's my thought.  How difficult would it be to build a server daemon
that used PostgreSQL on the backend, and presented it to the network as a
SQL Server compatible DB?

Some of the work is already available in the 'tsql' client.

This must've been thought of before, but does this idea have legs?

Lane,

> So, here's my thought.  How difficult would it be to build a server daemon
> that used PostgreSQL on the backend, and presented it to the network as a
> SQL Server compatible DB?

I think you're on the wrong list for this question.    I take it you're
talking a compatibitily layer, like Rosetta?

I would think that writing a program to broker requests on port 1433 and
re-interpret SQL Server specific syntax would be a managable project for a
good C or Perl hacker.   What would be difficult-to-impossible is:
1) making it scale & perform well (in my experience, MS SQL Server accessed
through Rosetta loses about 40% performance and is very limited in
scalability).
2) Making it integrate well with MS networking and the MS ODBC/OLEDB/ADO/.NET
clients.

Also, if you have T-SQL procedures, you would have to somehow implement T-SQL
for Postgres; translating the procedures is really not an option.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Quoth josh@agliodbs.com (Josh Berkus):
> Also, if you have T-SQL procedures, you would have to somehow implement T-SQL
> for Postgres; translating the procedures is really not an option.

You would be hit by a lot of other similar sorts of "impedances" to
those found with MySQL.

Most conspicuously, MySQL is most likely an imitator of Sybase
(progenitor of the code that Microsoft sells) in having
"autoincrementing columns" as compared to PostgreSQL's notion of
"sequence objects."
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://cbbrowne.com/info/spreadsheets.html
Monday is an awful way to spend one seventh of your life.  -- Unknown

Chris,

> Most conspicuously, MySQL is most likely an imitator of Sybase
> (progenitor of the code that Microsoft sells) in having
> "autoincrementing columns" as compared to PostgreSQL's notion of
> "sequence objects."

Nope.   AUTO_INCREMENT is in the SQL 99 spec.    It's actually well-supported
in the industry.   Eventually we'll probably come up with a command to
emulate it using sequences so that we can have the SQL-compliance points.

Mind you, AUTO_INCREMENT is one of those things that the SQL committee screwed
up on, like the INTERVAL type.

And don't know Sybase, it's a pretty good system with some features we could
do worse than steal.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

On Fri, 2004-02-20 at 20:52, Josh Berkus wrote:
> Chris,
>
> > Most conspicuously, MySQL is most likely an imitator of Sybase
> > (progenitor of the code that Microsoft sells) in having
> > "autoincrementing columns" as compared to PostgreSQL's notion of
> > "sequence objects."
>
> Nope.   AUTO_INCREMENT is in the SQL 99 spec.    It's actually well-supported
> in the industry.   Eventually we'll probably come up with a command to
> emulate it using sequences so that we can have the SQL-compliance points.

Are you sure about that? A search for AUTO_INCREMENT doesn't give me
anything.

The DB2 style sequence GENERATORs are a part of the 2002 draft.

Rod Taylor wrote:
> On Fri, 2004-02-20 at 20:52, Josh Berkus wrote:
>
>>Chris,
>>
>>
>>>Most conspicuously, MySQL is most likely an imitator of Sybase
>>>(progenitor of the code that Microsoft sells) in having
>>>"autoincrementing columns" as compared to PostgreSQL's notion of
>>>"sequence objects."
>>
>>Nope.   AUTO_INCREMENT is in the SQL 99 spec.    It's actually well-supported
>>in the industry.   Eventually we'll probably come up with a command to
>>emulate it using sequences so that we can have the SQL-compliance points.
>
>
> Are you sure about that? A search for AUTO_INCREMENT doesn't give me
> anything.
>

http://troels.arvin.dk/db/rdbms/#mix-identity ?


> The DB2 style sequence GENERATORs are a part of the 2002 draft.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

> > Are you sure about that? A search for AUTO_INCREMENT doesn't give me
> > anything.
> >
>
> http://troels.arvin.dk/db/rdbms/#mix-identity ?

That URL simply makes it clear that AUTO_INCREMENT is in no way the SQL
spec.

Chris

> > http://troels.arvin.dk/db/rdbms/#mix-identity ?
>
> That URL simply makes it clear that AUTO_INCREMENT is in no way the SQL
> spec.

Ahh yea, I was just providing some further input.

>
> Chris
>
>

--
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead

Chris, Rod, etc:

> That URL simply makes it clear that AUTO_INCREMENT is in no way the SQL
> spec.

Sorry!   I was getting IDENTITY and AUTO_INCREMENT mixed up, since IDENTITY is
what SQL Server supports.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco