So the other day I had a brilliant idea that I wanted to start writing a blog. It was brilliant because I normally don’t care much for writing. I never really enjoyed the composition classes in high school or college and I certainly don’t keep any sort of journal. The extent of my writing is generally limited to e-mails that I send for work or when trying to plan a birthday party with my family. Yet, here I am, writing my first blog post. Brilliant.
I started my blogging journey in an attempt to figure out where to host my blog. I was a bit lost as to where to start looking. I’ve read a ton of blog posts in my various dealings as a software engineer, but had never really thought about how to host one. My girlfriend, Anne (who is on her way to Brazil as I write this…jerk), has a blog hosted by Blogspot. It seems to work ok for her, but I knew that I wanted to self-host my blog since I already have most of the infrastructure in place. I thought about building my own from the ground up, since I am a software engineer, afterall. That seemed like a fun challenge, but I thought there must be a better way. Then I stumbled across that better way.
I was reading a post on my friend Kristof Mattei’s blog. At the bottom of the post, I saw a link that read “Proudly powered by WordPress.” Naturally, I clicked on that link, because that’s what we do with links. We can’t help ourselves. Of course, it took me to the WordPress website. I explored the site and learned that not only did WordPress allow for a self-hosted blog (which I knew based on Kristof’s blog), but it had a ton of pre-built themes, all the features I would need, myriad plug-ins that I may never use, and it was free! It was also actively being used across a LOT of sites around the Internet, so I knew it had to be pretty stable. Yes!
Configuring WordPress to use SQL Server
I downloaded the WordPress installation and unzipped the package. The first thing I looked at was the ‘readme.html’ file. Novel idea, eh? It says to “Open wp-admin/install.php in your browser.” so that I can setup my wp-config.php file and get this party started. Sure thing. After navigating to the install.php file, it tells me that wp-config.php doesn’t exist but that it would be happy to help me with that if I would just click the ‘Create a Configuration File’ button.
I click the button to create the config file, and it takes me to a summary screen telling me all of the things that I’ll need to know during the installation. It also has a button that says ‘Let’s go!’. I think the installer is almost as excited as I am! Remember what I said earlier about clicking stuff? We can’t help ourselves, so Let’s go!
Just as the previous screen warned, the installer is now requesting the various information for my database. As I’m reading through the screen, it’s asking for my MySQL username and my MySQL password. Wait…what? I don’t want to use MySQL. Now, I don’t have anything against MySQL, but I already have a SQL Server installation that I want to use. I consider installing MySQL side by side with SQL Server, and I’m fairly confident that it would have worked fine, but I really didn’t want to clutter things up. Plus, my SQL Server installation already has backups and maintenance stuff configured.
Ok, now what? Bing is your friend. Bing knows SO much stuff! So I Bing it (it doesn’t really have the same ring that ‘Google it’ has, does it?). Sure enough, Bing knows the answer! Well, in this case, Aaron Boundy knows the answer and provides this walkthrough. There’s no point in me recapping the whole walkthrough here, as everything went uneventfully. Just go read Aaron’s post!
So I get done with Aaron’s walkthrough, and eagerly navigate back to the install.php page to restart my configuration. In Aaron’s walkthrough, it mentions that after installing the database abstraction plugin, I should now be able to choose the type of database that I’d like to target. However, in my install.php page, it doesn’t look like anything has changed. Bummer! Back to Bing.
I come across this page on the WordPress site. It’s not nearly as fun as Aaron’s walkthrough. No pictures, minimal descriptions, etc. However, it does have a key bit of information. Rather than installing the plugin to the website root, as Aaron suggests, it recommends installing it into the wp-content/mu-plugins folder. Hmm…ok. In my case, that looks like this.
It also suggests to copy the db.php file from the wp-db-abstraction folder to the wp-content folder. Easy enough.
Finally, it wants me to navigate to “$your_wordpress_url/wp-content/mu-plugins/wp-db-abstraction/setup-config.php” to create my wp-config.php file.
Well that looks familiar, but look more closely, ‘Database type’ is now in the list! We’re getting somewhere. Let’s go!
Sweet! I can now tell WordPress that I want to use SQL Server for my database. After a bit of flailing on my keyboard, all of my data is set and I’m ready to go. Submit!
Success! How awesome is this?!
Installing WordPress
All that’s left is to install the actual WordPress files now. How nice, there’s even a button that will take me to the installer!
Again, some more flailing around on my keyboard and I’m ready to Install WordPress!
We’re done! Hooray!
A quick check of SQL Server, and all of the tables are in place, and some even have a bit of data in them. So, things must be working between the site and the database. Bonus!
Ok, now that we’re in, we can finally get around to doing all the fun stuff that we’ve been wanting to do all along! Things like awesome backgrounds, fun MIDI files, rainbow elephants with trails for a cursor, and blinking labels. I’m so excited!
Missing Posts
Actually, if you click on the Posts -> All Posts menu in the top left, it’ll take you to the Posts page. You’ll note that the links at the top suggest that there’s a Published post already in the system, but there’s nothing listed in the list below.
No matter, we’ll just create our own first post by clicking on the Add New link, adding some content, and then clicking the Save Draft button.
So, now we should have at least one post listed right? I mean, afterall, we did just create one, right? Wrong. Now we just have two posts mentioned in the summary links, but nothing in the Posts list.
As it turns out, there’s apparently a bug in the latest version of WordPress that’s causing this issue. I suspect that before most people stumble across this post the issue will be fixed. However, in the event that’s not the case, here’s the solution (which I found here).
- Open the translations.php file located at <root>wp-contentmu-pluginswp-db-abstractiontranslationssqlsrv in your favorite text editor. It will be helpful if your text editor supports lines numbers.
- Navigate to line 726, and change it from
$pattern = '/LIMITs*(d+)((s*,?s*)(d+)*)(;{0,1})$/is';
to
$pattern = '/LIMITs*(d+)((s*,?s*)(d+)*);{0,1}$/is';
- Save the file.
- Reload your Posts page, and you should be all set!
Special Character Issues
Finally, I learned as I was writing this post, that there’s an issue with backslashes (and possibly other special characters?) when using WordPress with SQL Server. Apparently, when working with MySQL, column names are wrapped with grave characters (`). When using the SQL Server plug-in, the grave characters are supposed to be stripped out. However, it appears that when backslashes are present in the body of the blog post, the characters aren’t stripped out correctly. As a result, the query is sent to SQL Server with some of the column names still wrapped in the characters. This creates an error and doesn’t update the data in the database. What a bummer after you spent all that time working on a post! In fact, I re-wrote parts of this post a number of times before I figured that out.
The issue has been identified at SourceForge. Hopefully it will be fixed soon. For the time being, I used the same workaround that was described at SourceForge.
- Open the sqlsrv.php file located at <root>wp-contentmu-pluginswp-db-abstractiondrivers in your favorite text editor. Again, it will be helpful if your text editor support lines numbers.
- Navigate to line 237. After line 237, insert the following line of code:
$query = str_replace('`', '', $query); // strip [] - breaking queries
- Save the file. You should be set!
Issues
Finally, here are some things that I ran into trouble with as I was installing WordPress.
- Make sure that the folder that has your site in it has provided permissions to IUSR to write to the folder (you can change it back later). Otherwise, it won’t be able to configure your wp-config.php file correctly. Aaron’s post has more specific details about that.
- Make sure that your SQL Server user has the correct permissions on your WordPress database. Again, Aaron’s post has more specific details.
- Don’t forget to copy the db.php file from the wp-db-abstraction folder to the wp-content folder.
- In the event you ran the WordPress installer before doing the database installation/configuration, it may cause issues. This manifested in my first installation as a non-functioning Save Draft button (clicking it would just take me to a page that read “Are you sure you want to do that?”), and missing Preview and Publish buttons. A more accomplished PHPer or WordPresser probably could have found a surgical solution to this. I took the sledgehammer approach and tossed my entire installation and started over. Starting over took me 5 minutes, versus who knows how long to fix it otherwise.
With any luck, this should be the first of many posts as I travel through my journeys as a software engineer (and beyond?). I’ll never be able to thank all of the people that took the time to document their experiences that have helped others. Hopefully sharing my experiences will help you too!