Writings

Migrating a WordPress Site from MySQL to SQL Server/SQL Azure

If you’re currently a WordPress user, there’s a 99.9999% chance you’re running on MySQL, even though you may be running PHP on Windows. Luckily, migrating your WordPress site to SQL Server or SQL Azure should only take a few hours. Here are the steps involved:

1. Create a New Installation Running WordPress on SQL Server/SQL Azure

2. Migrate Themes

3. Migrate Plugins and Widgets

4. Export/Import Content

A Case Study

My first experience doing a migration from MySQL to SQL Server or SQL Azure was for the Remix Russia keynote delivered by Eric Rudder. Now, I’m in the process of migrating my own WordPress blog (which I’ve been running on a shared LAMP hosting account for years) to Windows and SQL Server.

In this article, I’ll take you through the process.

image

Let’s get this party started.

1. Create a New Installation Running WordPress on SQL Server/SQL Azure

First, we need to get a default instance of WordPress running on SQL Server stood up; then we’ll go through the steps of customization. Check out the Getting Started page and the Installing WordPress on SQL Server article to get the default instance up.

Once you’ve completed this step, your site should look something like this:

image

I didn’t have to do anything special to get this running on my Discount ASP hosting account. I just created the database, FTP’d the files and ran through the standard WordPress install.

Make sure to update the Settings in the WordPress admin for things like Permalinks, Tagline, etc.

2. Migrate Themes

The first step in migrating themes is to copy the wp-content/themes folder to your new site via FTP.

image

How difficult it is to migrate your Theme depends on how it’s built. I’m using a theme that stores its settings within the wp_options table, so I needed to migrate the settings by hand.

To do this,we need to go into Appearance and Activate our theme.

image

In order to migrate the theme settings manually,open up the Theme options on your current site in the left window and the settings on the new SQL Server site in the right window. It’s a piece of cake to run through these and move over the settings.

image

Your new site should start to take shape.

image

3. Migrate Plugins and Widgets

Migrating Plugins and Widgets might take a bit of work. WordPress stores the settings for these within the wp_options table, and because we’re migrating our blog over by standing up a new instance, we’ll need to migrate Plugin and Widget settings manually.

NOTE: Plugins and Widgets are an area for potential incompatibility with WordPress on SQL Server. This patch works by intercepting the MySQL statements and converting them to be TSQL compatible with SQL Server/SQL Azure. You can read this article for more architecture details if you’re interested. OmniTI does a great job making sure this works for the core WordPress operations, which should cover the majority of database operations a plugin attempts to execute. There are two scenarios where the database abstraction layer may not work correctly: Either the SQL Server patch just hasn’t been built to handle a certain database statement or the plugin is written incorrectly and accesses the database directly instead of using the WordPress database execution functions. The main point is that once you’ve migrated over your Plugins and Widgets, be sure to do some testing before moving your WordPress on SQL Server site into production.

Plugins

First, make sure you have already copied over the wp-content/plugins folder to your WordPress on SQL Server site.

image

Open the Plugins section of your WordPress admin for your current site and the new site side-by-side. Then go through and activate the Plugins you want to continue using.

image

Make sure to configure the Plugins that require additional steps. For example, you’ll need to copy your Akismet API Key, etc.

image

Widgets

Migrating Widgets uses the same process as Plugins: Just open the WordPress admin side-by-side and migrate necessary settings.

image

Now your site should be ready for content.

image

5. Export/Import Content

Migrating your content with WordPress is straightforward, thanks to the Import/Export feature built into WordPress. The first step is to export your content from your current site using the Export feature under the Tools menu in the WordPress admin.

image

This will generate an XML file containing all of your Pages, Posts, Comments, Custom Fields, Categories and Tags.

SNAGHTML24900c6

Now, all you need to do is switch to your new WordPress on SQL Server site and use the Import feature under the Tools menu. Make sure to select a WordPress Import.

image

Now just browse to the XML export from the step above and click Upload file and import.

image

Now WordPress will parse the XML file and ask you for a couple more details. You can decide to either create new users or map to existing users. You’ll probably want to select the Import Attachments option.

image

Once the import is complete, WordPress will provide a log of the results.

image

If you selected to Import Attachments above,WordPress will pull images, etc. into Media for your new WordPress site.  Alternatively, you could copy over the wp-content/uploads folder.

Now that your Pages and Posts are in your WordPress on SQL Server site, you may need to tweak some UI elements. For example, I needed to add the Pages and Categories to the header navigation.

Your site should now run WordPress on SQL Server!

image

The only thing left to do is update the DNS settings to point to my new host.

Conclusion

I spent some time researching a “better” way to do a WordPress migration but I believe that when moving to a different database platform, going through the steps in this article is probably a good idea. If you have alternate suggestions, please let me know.

I’m really excited to be running WordPress on SQL Server. Give it a try and let us know if there’s anything we can do to help!

Comments 43 Comments - Join the conversation

  1. [...] recently wrote an article over at http://wordpress.visitmix.com about my experience migrating my blog over to WordPress on [...]

  2. [...] Migrating a WordPress Site from MySQL to SQL Server/SQL Azure [...]

  3. [...] Migrating a WordPress Site from MySQL to SQL Server/SQL Azure [...]

  4. Meni says:

    Just curious, what will be the cost of hosting a blog on Azure, say, for almost no visitors, and for 1,000 usrs/day?

  5. Sajay says:

    What are the supported version for the export import using WXR?
    The import just shows completed but nothing actually imports.

  6. [...] Note: I’m just showing how to migrate a WordPress database as an example. If you actually want to run WordPress on SQL Server, see this post by Zach Owens: WordPress on Microsoft. [...]

  7. [...] Note: I’m just showing how to migrate a WordPress database as an example. If you actually want to run WordPress on SQL Server, see this post by Zach Owens: WordPress on Microsoft. [...]

  8. [...] I had a chance to play with the SQL Server Migra­tion Assis­tant for MySQL when it was in beta a few months ago. It did a great job then of migrat­ing a sim­ple MySQL data­base, but the team has added lots of func­tion­al­ity since the first beta release (includ­ing sup­port for migrat­ing stored pro­ce­dures). The down­load includes a help file with doc­u­men­ta­tion that will give you an under­stand­ing of other fea­tures the team has added. What I’ll do here is walk you through the “Hello World”-type usage of the assis­tant. I’ll use SSMA to move a MySQL Word­Press data­base to SQL Server. ‘Note: I’m just show­ing how to migrate a Word­Press data­base as an exam­ple. If you actu­ally want to run Word­Press on SQL Server, see this post by Zach Owens: Word­Press on Microsoft. [...]

  9. boxy says:

    hey guys i found a great get paid survey site where you can make cash quickly. check out goo.gl/hqcR

  10. [...] Migrate a WordPress Site From MySQL to SQL Server/SQL Azure [...]

  11. Sara Swift says:

    Damn, pretty great article. Where can I get this subscription?

    Sara Swift
    camera scramblers

  12. Arjen says:

    How to deal with plugins? Do all plugins work with your ””WordPress on Microsoft”” solution?

  13. nicole says:

    Has anyone tried this with SQL Express?

  14. Katherine Karver says:

    It is very interesting for me to read the article. Thanks for it. I like such topics and everything that is connected to this matter. I definitely want to read a bit more soon.

    Katherine Karver
    escort woman

  15. [...] I chose to implement this utilizing the Windows Azure VM role.  With an on-premises Hyper-V server, I created a virtual image of Windows Server 2008 R2 with IIS and installed WordPress.  The install wizard automatically implemented database schema, making the database portion of setup very easy.  During the setup wizards, all I had to do was provide the location and credentials for my SQL Azure database running in the cloud.  Walkthroughs of how to accomplish that are here. [...]

  16. [...] I chose to implement this utilizing the Windows Azure VM role. With an on-premises Hyper-V server, I created a virtual image of Windows Server 2008 R2 with IIS and installed WordPress. The install wizard automatically implemented database schema, making the database portion of setup very easy. During the setup wizards, all I had to do was provide the location and credentials for my SQL Azure database running in the cloud. Walkthroughs of how to accomplish that are here. [...]

  17. [...] out how to Migrate to Windows and SQL Server by Zach Owens who is moving his blog to Windows and SQL [...]

  18. [...] out how to Migrate to Windows and SQL Server by Zach Owens who is moving his blog to Windows and SQL [...]

  19. [...] Azure and SQL Server Azure. Zach Owens is an evangelist for Microsoft who is supporting this and blogs all about it. It sounds interesting but I get the sense that this is just some sort of Microsoft pet project and [...]

  20. Rusty says:

    Any idea why the patch and the distribution files have been pulled? All the links and readme,.txts are there, but download files missing.
    Microsoft”s .MSI installer is still availablbe, but that will not run on my Windows Server 2008 host.
    Good article and I hope there”s some hope for making this available as I was about to install WordPress and really would prefer to use the SQLServer thats already running.

  21. Jeramy says:

    Excellent post. I was checking constantly this blog and
    I”m impressed! Very useful information specially the last part :) I care for such information much. I was looking for this certain information for a very long time. Thank you and best of luck.

  22. [...] I chose to implement this utilizing the Windows Azure VM role. With an on-premises Hyper-V server, I created a virtual image of Windows Server 2008 R2 with IIS and installed WordPress. The install wizard automatically implemented database schema, making the database portion of setup very easy. During the setup wizards, all I had to do was provide the location and credentials for my SQL Azure database running in the cloud. Walkthroughs of how to accomplish that are here. [...]

  23. Hi there! This article could not be written any better! Looking at this post reminds me of my previous roommate! He always kept preaching about this. I most certainly will forward this information to him. Fairly certain he will have a great read. Many thanks for sharing!

  24. I like the helpful information you provide
    in your articles. I”ll bookmark your blog and check again here frequently. I”m
    quite certain I”ll learn a lot of new stuff right here! Best of luck for the next!

  25. Very soon this site will be famous among all blogging people, due to it”s nice articles

    Also visit my website :: how to get minecraft for free

  26. Hello, Neat post. There’s an issue with your site in internet explorer,
    could test this? IE nonetheless is the market chief and a large section of other folks will pass over your excellent writing
    because of this problem.

  27. Thanks for the step-by-step instructions. Keep sharing such posts.

    Sunrise PC Support

  28. I am extremely impressed with your writing skills and also with the layout on your blog.
    Is this a paid theme or did you customize it yourself?
    Either way keep up the nice quality writing,
    it’s rare to see a great blog like this one these days.

    My page :: Free Minecraft Server

  29. However, some researchers think TRPML1 channels iron ions, so when it malfunctions,
    the result is cellular iron deficiency. I am going to break down the important elements you will need
    to know so as to select the best supplements to develop
    on that lean muscle you are looking for. Chlorogenic acid is a
    naturally-occurring phytochemical found mainly in fruits, vegetables, and
    coffee.

  30. You’re required to merely recapitulate and amalgamate
    the work other scholars have done and published in your specific area of research.
    Like I was saying earlier, as a hijabbed Muslim woman, I realized that many misconceptions about my faith
    had to do with people’s misunderstandings around how Islam
    supposedly treated its women. You can buy academic writing services there at affordable rates and rest assured about the quality.

  31. Thanks for sharing nice post.

  32. When and how the blueprint fabricated its way out of the United States and into China or Korea is aswell unknown.
    The ‘Teen Mom’ retweeted the words after the photo leaked online.
    One other point that may be cool is the fact that variety of like a sponge that arrives through the
    ocean, this natural latex liquid when it hardens forms tiny pinholes that breathe.

  33. I have read so many content regarding the blogger lovers but this piece of writing
    is genuinely a fastidious article, keep it up.

  34. Hiya! Quick question that’s entirely off topic. Do you know how to
    make your site mobile friendly? My site lookss weird when browsing from my apple iphone.

    I’m trying to find a templte or plugin that might be able to correct this problem.
    If you have any recommendations, please share.
    Cheers!

  35. […] Migrating a WordPress Site from MySQL to SQL Server/SQL Azure – WordPress on Windows If you’re currently a WordPress user, there’s a 99.9999% chance you’re running on MySQL, even though you may be running PHP on Windows […]

  36. Thanks for dischssing your thoughts on % meta_keyword %.
    Regards!

    Also visit mmy webpage: car navigation

  37. seo says:

    Contact: Many online marketing companies aare located in different
    countries, namely India. An SEO firm is a company that specializes
    in making websites be onn top of results off search engines
    like Google. Yet if it is a top leveel domain, plus if I am
    prety confident in itss spelling, I will definitely type them out.

  38. mal seizures says:

    hi!,I love your writing so a lot! percentage we be in contact extra approximately your post on AOL?
    I need an expert in this area to solve my problem. May be
    that’s you! Having a look forward to see you.

  39. I savor, result in I found exactly what I used to be having a look for. You’ve ended my four day lengthy hunt! God Bless you man. Have a great day. Bye

  40. Fastidious respond iin return of this issue with real arguments and
    telling everything concerning that.

  41. youtube.com says:

    I’m trying to get my blog site to rank for some targeted keyword phrases yet I’m not seeing really
    good gains. Many thanks!

    My blog … best fantasy football money leagues (youtube.com)

  42. Thanks for your personal marvelous posting!

    I actually enjoyed reading it, you’re a great author.I will make sure to bookmark your
    blog and will come back later on. I want to encourage you to continue your great job,
    have a nice day!

Add a Comment