Running WordPress on SQL Server

What if I tried to install WordPress on SQL Server instead of MySQL?

I mean, what’s the worst that could happen? How else am I going to blow a Sunday afternoon?

I was curious. What was it actually like?

Unholy UNION?

Wait, why even bother to change the underlying database? What’s the upside?

Why SQL Server?

I’m not qualified to present a MySQL vs SQL Server comparison. Here is my opinion anyway.

First, if you have a pre-existing Microsoft ecosystem and you want a blogging platform — and you also don’t feel like re-inventing the wheel with an .NET Core CMS — WP on MSSQL makes a lot of sense. There are Microsoft technology integration opportunities (e.g. the CLR) galore at the database level.

What about greenfield projects?

One clear advantage SQL Server has over MySQL is in the stored procedure feature set of T-SQL. It’s a full blown imperative programming language. This opens up the ability to add features at a database level in ways not previously available.

Another advantage is in the clear path to deploying (or migrating) a WordPress site to the Azure cloud. Hosting WordPress on Azure is an intriguing alternative to the AWS ecosystem.

Ok, enough jibber jabber.

Installing WordPress

First, you’ll need a local WordPress-capable server. On my Windows dev machine, I use XAMPP.

So it turns out most of the legwork for making WordPress run on SQL Server has already been done. There exists a forked version of WordPress called Project Nami engineered with SQL Server as the application database. Lucky me!

To begin, I downloaded Project Nami 2.3.2, which corresponds to WordPress version 5.3.2. At the time of this writing, it is the most recent release of WP available.

I extracted the installer package into my XAMPP htdocs folder. Then I visited https://localhost/projectnami, expecting the familiar WordPress 5-minute install wizard.

Instead I was greeted by a dramatic error: "Your PHP installation appears to be missing the SQLSrv extension which is required by Project Nami."

No!

Oops, I guess I should have RTFM…

I needed to be able to install the SQLSrv extension for PHP to play nice with SQL Server. We can find the SQL Server PHP extension on its Github page.

I chose version 5.7.1-preview from the releases page. At the time of writing, only the pre-release version supports PHP 7.4, which is included in XAMPP.

I downloaded and extracted the files. I placed both the thread safe and non-thread safe .dlls in the c:\xampp\php\ext folder. See the official Microsoft docs for more information.

To make PHP pick up the new files, I edited the php.ini file located in c:\xampp\php and added lines for both the SQLSrv and the PDO_SQLSrv extensions.

After rebooting the Apache server in the XAMPP control panel, I was able to begin the WordPress 5-minute install.

5 minute install

To be able to complete the WordPress configuration, I needed to do a bit of legwork in SQL Server.

First I needed a database. I named my database WordPress.

USE master
GO

IF NOT EXISTS (
   SELECT [name]
   FROM sys.databases
   WHERE [name] = N'WordPress'
)
CREATE DATABASE WordPress
GO

I also need a login account to authenticate to SQL Server.

Maybe one day I’ll figure out how to authenticate WordPress to SQL Server via AD LDAP auth and finally be able to mark that one off the ‘ol bucket list. But for today, I’m going to just stick with SQL Server auth. I named my user account wp.

IF NOT EXISTS(
   SELECT l.[name]
   FROM sys.sql_logins l
   WHERE l.[name] = N'wp'
)
CREATE LOGIN wp WITH PASSWORD=N'!abcde01', DEFAULT_DATABASE=WordPress
GO

ALTER LOGIN wp ENABLE
GO 

Finally, I needed a user associated to the database for the wp user.

USE WordPress
GO

CREATE USER wp FOR LOGIN wp

EXEC sp_addrolemember 'db_owner', 'wp'
GO

I plugged the credentials into the install wizard…

…and that allowed me to successfully access the database! I Ran the install and then happily…

Warning: error_log(C:\xampp\php\logs\translate.log): failed to open  stream: No such file or directory in  C:\xampp\htdocs\projectnami\wp-includes\wp-db.php on line 1915 

Oh. Oh wait.

That doesn’t look like a resounding success. Quite a few PHP warnings populate the screen. But warnings aren’t errors. It seems like I’d be able to proceed with the install, so I forged ahead.

Yikes. Undeterred, I continued to click the little blue button. I entered my login user credentials and…

I’m not sure what’s up with that warning message. I haven’t bothered to look into it. But otherwise everything seems to have worked.

Project Nami Compatibility Tests

I noticed there’s an update pending.

Let’s see what happens when I run the update.

It works!

In the plugins list a pre-installed Full Text Search plugin is available from the Project Nami team. Interesting. I couldn’t remember if my dev SQL Server had full text search enabled or not. Running this let me find out:

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Hmm, no. Not installed. I’m tempted… but that is a tale for another time.

So what good is a SQL Server-based WordPress install if the ecosystem is busted because of database incompatibility? Let’s install some plugins and try to break this thing.

I tried installing a handful of plugins and themes — an OAuth API plugin, the Astra theme, and some more — and everything seemed to just work. There were no hiccups at all.

Until I tried Query Monitor.

Perhaps unsurprisingly, the Query Monitor plugin didn’t work. The standard dropdown added to the admin bar was completely blank. Given that it’s made for monitoring MySQL queries, this isn’t a shocker. Better disable this one…

Oops. Disabling Query Monitor results in an error message. But when I went back and checked the plugin list, it is disabled successfully. Curious…

Next, I tried installing WP Statistics.

On activation, WP Statistics shows a warning message about unexpected characters in the header, and advised I should disable the plugin if I see unusual behavior. But otherwise it seems to work normally!

WordPress Site Health shows a warning that the database server is severely outdated. Come on now, SQL Server 2017 isn’t severely outdated… Other parts of SiteHealth show that WordPress still believes it’s running on MySQL.

The SEO Framework plugin install, activation and functionality all works exactly as expected.

Closing thoughts

So far I’m really impressed! I went in not quite knowing what to expect, but I’m pleased to say that WordPress on SQL Server is a viable option.

My general feeling after this little bit of testing is that the Project Nami fork should behave the same as vanilla WordPress in about 95% of cases.

There are still a few rough edges during install and operations, but I haven’t seen any showstopping issues. Just make sure you heed the authors’ warnings to avoid plugins intended to interact with a MySQL database, and I think you’ll be okay.

My dayjob involves SQL Server, and I’m much more familiar with it than MySQL. I’m looking forward to exploring this strange and beautiful union more in the future.

Leave a Reply

Your email address will not be published. Required fields are marked *