MS SQL Server and JDatabase

[Amy Stephen]

Shortly, I am going to begin working with JDatabase and SQL server. If anyone has any advise, it would be appreciated.

Does the JDatabase solution require an ODBC driver?  Or, does it use a native SQL Server driver?

Does anyone knows of a tool that can automate the transformation of MySQL DDL to Transact SQL?

What do you use for Data Modeling if you are an MS SQL Server DBA?

Hope it works - would really like to stick to PHP and JDatabase instead of getting .NET back out.

Any other advise anyone has is appreciated.

Thanks to those who provided this capability. Very much appreciated.

[Amy Stephen]

Nice blog post at MS with a link to their download that appears to make the SQL Server option more obvious. That might be a good way to get started before building custom code.

http://blogs.msdn.com/b/brian_swan/archive/2012/01/26/joomla-2-5-adds-sql-server-support.aspx

[Mark Dexter]

Sudhi was going to write some instructions for the Wiki about
installing MS SQL Server on a local PC. I thought he had done this,
but I did a search and can't find it. It would be great to get a
simple document up there. I don't think it's hard, but of course I
haven't done it (ha, ha). Mark

[Louis Landry]

Fun stuff.

On Tue, Apr 24, 2012 at 7:32 PM, Amy Stephen <amyst...@gmail.com> wrote:

Shortly, I am going to begin working with JDatabase and SQL server. If anyone has any advise, it would be appreciated.

Enjoy the ride :-)

 

Does the JDatabase solution require an ODBC driver?  Or, does it use a native SQL Server driver?

The JDatabase drivers use the sqlsrv extension for PHP.  My understanding is that Microsoft now has an ODBC driver for MSSQL on Linux that could be leveraged through an ODBC drive that isn't yet written.  Other than that if you are on Linux you'll need to setup http://www.freetds.org/ to use along with the JDatabaseSqlsrv driver at the very least.  On windows itself I'm not sure, but I'd assume that it would just sort of "work".

 

Does anyone knows of a tool that can automate the transformation of MySQL DDL to Transact SQL?

No, but please post back something if you find it.

 

What do you use for Data Modeling if you are an MS SQL Server DBA?

*shudder*

 

Hope it works - would really like to stick to PHP and JDatabase instead of getting .NET back out.

I love me some C#, but couldn't agree more.

 

Any other advise anyone has is appreciated.

Thanks to those who provided this capability. Very much appreciated.

Good luck!

[Sam Moffatt]

On Tue, Apr 24, 2012 at 8:27 PM, Louis Landry <louis...@gmail.com> wrote:
>>
>> Does the JDatabase solution require an ODBC driver?  Or, does it use a
>> native SQL Server driver?
>>
> The JDatabase drivers use the sqlsrv extension for PHP.  My understanding is
> that Microsoft now has an ODBC driver for MSSQL on Linux that could be
> leveraged through an ODBC drive that isn't yet written.  Other than that if
> you are on Linux you'll need to setup http://www.freetds.org/ to use along
> with the JDatabaseSqlsrv driver at the very least.  On windows itself I'm
> not sure, but I'd assume that it would just sort of "work".

SQLSRV is a specific driver that Microsoft wrote for PHP on Windows,
you can't use FreeTDS with it but instead need to use a Sybase powered
connector to use FreeTDS (which isn't in the platform IIRC, there was
one a while back in one of the many branches). If you're running
everything on Windows, using the Web Platform Installer is probably
the best way of getting the entire stack set up and will help out with
getting the SQLSRV PHP driver connected up and IIS as well as a few
other things as well.

>>
>> What do you use for Data Modeling if you are an MS SQL Server DBA?
>>

You might wish to look into the SQL Server Management Studio Express
product. Again, the Web Platform Installer can help you install that
along with the MSSQL database.

Cheers,

Sam Moffatt
http://pasamio.id.au

[Louis Landry]

Ah... I sit corrected. :) Thanks Sam!

[Will Daniels]

On 25/04/12 03:32, Amy Stephen wrote:
> Shortly, I am going to begin working with JDatabase and SQL server. If
> anyone has any advise, it would be appreciated.

I'm not sure what advice I could give you up-front but I know SQL Server better
than any other RDBMS and actually it's one of only two MS products I have any
time for (I still miss Word sometimes for authoring large documents). You should
find it a very capable database that is well-documented and a pleasure to use.

Feel free to ask me for generic help with SQL Server, but I have never used it
with any variant of Joomla!

> What do you use for Data Modeling if you are an MS SQL Server DBA?

That depends more what kind of DBA you are and how you know you work best.
Personally, I can't cope with anything that wants me to define a "project" or a
"solution" before I start :P

So I only ever used the "Query Analyzer" app that came with SQL Server 2000
(even for connecting to newer instances) and managed 7 years of heavy SQL Server
work perfectly fine that way. After putting the DB object list down the left
side pane it makes for fast and efficient working, so much so that I made a
copy-cat in C#/Gtk to use with Virtuoso DB.

The old "Enterprise Manager" (and especially the newer "Studio" apps that
combine the two) are far too slow, cluttered and heavy for my tastes, but they
have more than enough features for data modelling. I don't know any SQL Server
DBA who ever felt a need for more enhanced modelling tools.

We did use the RedGate SQL Compare tools (red-gate.com) for comparing SQL
objects (schema, stored procedures etc.) and data, which worked well for heavy
upgrade work on very large DBs, but I think those tools are commercial and for
smaller databases might not be worth the expense.

> Any other advise anyone has is appreciated.

Where performance is an issue, the NOLOCK hint is invaluable, e.g.:

SELECT foo FROM bar WITH(NOLOCK)

The effect is equivalent to setting READ UNCOMMITTED isolation level, only you
can put it exactly where it's most needed and can help avoid locking up the DB
when doing things like computing aggregates over large datasets and other
long-running queries.

But obviously that makes for less-portable SQL, I mention it mainly because I've
noticed performance seems to be a particular concern of yours. I've always
tended to use it in stored procedures, but not more generic SQL executed from code.

Good luck!

-Will