Composr Supplementary: Using SQL Server with Composr

Written by Chris Graham (ocProducts)
This tutorial provides some advice targeted towards Microsoft SQL Server (including SQL Server Express).

General details of Composr's database driver functionality are included in the Using PostgreSQL with Composr tutorial. You may wish to read this first, as we are not repeating the same information here – this will be a much shorter tutorial only covering the specifics of the SQL Server support.

SQL Server has been tested for Composr v10.0.x under a commercial relationship (starting with 10.0.10). We tested on SQL Server Express 2017 across our whole test set and all screens and blocks, with both multi-lang-content on and off. We expect in practice SQL Server 2005+ is supported (we use VARCHAR(MAX)).

SQL Server is a very high quality database backend developed over decades by Microsoft (and formerly, Sybase). It is much more sophisticated than MySQL, although it also tends to be stricter in a number of ways. Usage is likely of most interest to organisations that are standardising on Microsoft technology.

What is and is not supported

The same functionality is supported as is supported on Postgres.

Additionally, you must use utf-8, because the PHP SQL Server extension doesn't allow much configurability.

Installing SQL Server

SQL Server only runs on Windows. You can download SQL Server Express for free, which will work fine. You should install full-text searching (included by default), and when asked select mixed authentication and set a password.

You will need either the PHP sqlsrv extension to be installed (Windows-only, this is an official Microsoft PHP extension), or the PHP odbc extension.

It is recommended to also install SQL Management Studio.

After installing SQL Server

Make sure you have the SQL Server network service started. It will start automatically unless you set it to manual starting when installing SQL Server (or later from Services).

You will need to manually create your database in SQL Server, using SQL Management Studio.

If using SQL Server Express, you need to enable Network connections. Also make sure TCP/IP is enabled on any network interface you're connecting on, in the same area of configuration, "IP Addresses tab". Also set the "IPAll" port to "1433" in here.

You will also need to configure your Firewall. If you disable the Windows Firewall it may block by default. Specifically enable incoming traffic on port 1433.

If you are using Mac or Linux for your web server you will need to use ODBC, as the PHP sqlsrv extension is Windows only. Set up for ODBC is a bit complicated, so we will cover it below.

ODBC

ODBC is an unusual technology when viewed from a current reference point. It does not itself use TCP/IP, it is only an API standard, not a networking standard.
PHP →[compiled in] Some PHP ODBC extension →[software interface] Some ODBC implementation →[configured] Some ODBC driver →[?] Some database server
e.g:
  • PHP Windows →[compiled in] (Inbuilt support on Windows) →[software interface] Windows ODBC →[configured] SQL Server Native Client 10.0 →[network protocol] SQL Server Express
  • PHP Linux →[compiled in] PHP ODBC extension →[software interface] unixODBC →[configured] FreeTDS →[network protocol] SQL Server Express

FreeTDS is basically a unixODBC driver that implements support for Microsoft SQL Server's TDS protocol, which is their TCP/IP database protocol.

An ODBC connection to the database server needs setting up on the webserver. This set up is called a "DSN".

It would be theoretically possible for us to use an ODBC connection string to tell FreeTDS (for example) how to connect through to the SQL Server Express machine, bypassing the need to set up a DSN. However, this would require telling PHP explicitly to use FreeTDS along with all the specific configuration FreeTDS understands, and we don't provide any configuration for that in _config.php because we don't want to bake in assumptions about specific ODBC implementations and drivers.

Setting up an ODBC DSN

Windows
Create a mapping in the ODBC part of control panel:
  1. It will be a System DSN
  2. Use "SQL Server Native Client 10.0"
  3. Set the Name as the same name as your database, to keep things simple
  4. Set the Server as '127.0.0.1' if the database is running on the webserver, else the correct IP address or hostname
  5. Finish

Mac/Linux
For Linux or MacOS, we suggest using unixODBC with FreeTDS (avoid iODBC and Microsoft's own drivers, they are a bit more esoteric). PHP will need to be compiled with unixODBC support.

Make sure the unixODBC and FreeTDS packages are installed via your package manager, or manually compiled and installed. Homebrew works on Mac, but make sure you install FreeTDS like brew install freetds --with-unixodbc.

UnixODBC needs to be connected to FreeTDS by configuring a FreeTDS driver. Create a file named tds.driver.template with a few lines describing the driver:

Code (INI)

[FreeTDS]
Description = v0.63 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
 
Obviously use the correct path to the .so file.
Then run odbcinst -i -d -f tds.driver.template

You will need to use the odbcinst command to create a DSN. Create a file named tds.template with a few lines describing the DSN:

Code (INI)

[cms]
Driver = FreeTDS
Trace = No
Server = 192.168.0.22
Port = 1433
Database = cms
TDS_Version = 7.2
ClientCharset = UTF-8
 
Obviously replace 192.168.0.22 with your actual database server, and replace cms (both times) with your chosen database name.
Version 7.2 is the minimum protocol version supported (consistent with Microsoft SQL Server 2005).
Then run odbcinst -i -d -f tds.template

Full documentation is included in the unixODBC manual.

If you need to edit the template configuration later, you'll find it's just in an odbc.ini file, e.g. /usr/local/etc/odbc.ini. The driver configuration is in odbcinst.ini.

PHP

Be aware that the PHP ODBC extension has been historically very buggy. Your PHP version must have all these bugs fixed:

Installing Composr

When you install Composr select the appropriate SQL Server database driver. Then on the next screen the database name will be your DSN name. The host name entered is not used, so I recommend just saying localhost.

Migration to SQL Server

Migrate via this process:
  1. Commandr can export an SQL dump compatible with any database that Composr supports, assuming you have the PHP extensions needed installed:

    Code

    sql_dump sqlserver
    (yes, you can be running MySQL and export a SQL Server SQL dump!)
  2. Edit the .sql file so the cached_comcode_pages table is a little different, like (with the corrected table prefix):

    Code (SQL)

    CREATE TABLE cms_cached_comcode_pages (
            id INT IDENTITY(1,1) NOT NULL,
            the_zone nvarchar(80) NOT NULL,
            the_page nvarchar(80) NOT NULL,
            the_theme nvarchar(80) NOT NULL,
            string_index nvarchar(MAX) NOT NULL,
            cc_page_title nvarchar(MAX) NOT NULL,
            string_index__text_parsed nvarchar(MAX) NOT NULL,
            string_index__source_user INT NOT NULL,
            PRIMARY KEY (id ASC)
    );
     
  3. Edit the .sql file so the translate table is a little different, like (with the corrected table prefix):

    Code (SQL)

    CREATE TABLE cms_translate (
            id INT NOT NULL,
            LANGUAGE nvarchar(5) NOT NULL,
            importance_level SMALLINT NOT NULL,
            text_original nvarchar(MAX) NOT NULL,
            text_parsed nvarchar(MAX) NOT NULL,
            broken SMALLINT NOT NULL,
            source_user INT NOT NULL,
            _id INT IDENTITY(1,1) NOT NULL,
            PRIMARY KEY (_id ASC)
    );
     
  4. Edit the .sql file to make this substitution:

    Code (SQL)

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='unique__cms_cached_comcode_pages' AND object_id=OBJECT_ID('cms_cached_comcode_pages')) CREATE UNIQUE INDEX unique__cms_cached_comcode_pages ON cms_cached_comcode_pages(the_page,the_theme,the_zone);
     
    →

    Code (SQL)

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='unique__cms_cached_comcode_pages' AND object_id=OBJECT_ID('cms_cached_comcode_pages')) CREATE UNIQUE INDEX unique__cms_cached_comcode_pages ON cms_cached_comcode_pages(id);
     
  5. Edit the .sql file to make this substitution:

    Code (SQL)

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='unique__cms_translate' AND object_id=OBJECT_ID('cms_translate')) CREATE UNIQUE INDEX unique__cms_translate ON cms_translate(id,LANGUAGE);
     
    →

    Code (SQL)

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='unique__cms_translate' AND object_id=OBJECT_ID('cms_translate')) CREATE UNIQUE INDEX unique__cms_translate ON cms_translate(_id);
     
  6. Copy the .sql file generated to a machine you can run SQL Management Studio from, if this is different from your website machine
  7. Open a command prompt
  8. Run this command:

    Code

    sqlcmd -S <server> -d <databasename> -i <filepath>
  9. You then edit Composr's _config.php to have the correct database details.

Setting up a custom user for the database

To set up a custom user using SQL Management Studio:
  1. Add a Login from Security > Logins
  2. Add a User to the database from Databases > Security > Users
  3. Make sure to grant the following roles in the "Membership" tab of the user setup: db_datareader, db_datawriter, db_ddladmin, db_owner

Also make sure that in the main server properties, under the Security tab, "SQL Server and Windows Authentication mode" is set. Otherwise the login won't be able to log in!

Migrating between servers

There are 2 ways to migrate SQL Server databases between servers:
  1. Backups (Tasks → Back Up)
  2. SQL dumps (Tasks → Generate Scripts)

Backups are the easiest way, but beware that you can not export to an older SQL Server version.

SQL Dumps can be a bit tricky. You need to set export options to include data, and there's an option for the compatible version you are porting to. You'll run with sqlcmd. It is a good idea to edit the SQL to make it USE the database you want to import to and not do the CREATE DATABASE, otherwise permissions may trip up and things may end up going into the system master database after failing to create the database automatically.

Full-text search (advanced)

While full-text search is supported, indexing can be a bit spotty. The index builds in the background and has been seen to lag or not build at all during some testing (at least on SQL Server Express). You may disable full-text search via the hidden skip_fulltext_sqlserver option if you want to just use boolean search instead.

Ideally (not importantly) Composr would know the correct stop-word list for SQL Server so it knows whether to direct searches only for a stopword to a non-full-text search. This is controlled in the SQL Server configuration (unlike MySQL, where it's hard-coded, and hence we were able to hard-code in our code also). To provide Composr an accurate full-text search word list you need to override the get_stopwords_list() function.

You may also want to configure the text language.

Technical information for developers (advanced)

The main complexities of SQL Server support, for MySQL developers are:
  1. Points 1, 2, 3, 5, 7, & 8 from the Postgres tutorial
  2. The MySQL LIMIT max,start syntax is not supported. You can do TOP (max+start) though. The Composr query* methods abstract this for 99.9% of cases you may have.
  3. You need to use SQL Server string escaping, not MySQL string escaping. This is very different, it uses doubling of apostrophes rather than backslash escaping.
  4. Unicode strings are put in quotes (as normal in SQL) but then prefixed with N. This is abstracted in the driver's own query interface, so you should not need to worry about it when writing normal code.
  5. (N)VARCHAR(MAX) fields are used rather than (N)TEXT fields, as SQL Server cannot index (N)TEXT fields.
  6. Full-text search indexes requires a unique index on a single column, which restricts what kind of tables you can run it on.
  7. You cannot insert into an identity column explicitly, unless you turn an option off. The database driver handles this automatically though.

See also


Feedback

Please rate this tutorial:

Have a suggestion? Report an issue on the tracker.