I don't want the whole of Drupal (Drupal itself plus SQL) on the same server. I need Drupal on a normal LAMP setup connecting to a db hosted on a separate server using MS SQL. Is it possible?
I've seen there's a MS PHP drive for Drupal to work, but the drive is a Windows executable file, so I guess it won't work on a Linux box.
Alternatively, is there any difference in Drupal itself for it to be able to connect to MS SQL?
Out of the box there is practically no support for MSSQL. Drupal 7 there was an attempt to support it driven by the Commerce Guys, but that work migrated to a contrib module and is not in core. Here is a quick rundown of compatibility.
Drupal 6: Not possible
Drupal 7: Possibly with the MSSQL Layer module called SQLsrv. But be prepared for issues when using contrib modules, some will not work because many modules write out SQL commands and run them with db_query instead of the abstraction layer for performance reasons - especially complex queries.
Drupal 8: Not possible out of the box. There is a third party module that provides a driver for MSSQL.
If you really want to get the most out of Drupal with as little headache as possible, run MySQL or even MarianDB on Windows.
Drupal 7 and 8 have full MSSQL Support.
Currently, PHP7 is also supported and Linux Support for PHP7 is comming soon.
My recommendation is that - if you are using MSSQL - go for a full MS software stack.
To install Drupal on SQL Server see this tutorial:
http://www.drupalonwindows.com/en/blog/installing-drupal-windows-and-sql-server http://www.drupalonwindows.com/en/blog/installing-drupal-8-windows
By default Drupal supports MySQL, PostgreSQL and SQLite. But you can try to extend existing classes and write your own for MsSQL (or find some existing). I don't think that MsSQL can work on Linux, but maybe via emulator.
