Tuesday, 17 August 2010

Data import and moving databases

When I am developing, I have a local setup which consists of a laptop to do the development work on and a separate database server. I prefer to develop with SQL server for a number of reasons, primarily it's the one I've got most exposure to and I know it more than any other database.

However, some of my clients are using MySQL database so at first it may seem that there's some data migration that would need to be done to move the data from SQL Server to MySQL.

Before I knew about this little "trick", I'd spent money on software to convert the databases (and most didn't quite work either) but there's a couple of reasons that I now realise why I don't need to have done this.

Firstly, when n2cms is saving data to the database it uses NHiberate to do it. This is great news as it hides the database implementation - I call save on an object and under the n2cms hood it calls NHibernate to save the object to the database. Luckily, to change databases it's a simple change in the web.config and it also allows us to use other database providers such as SQL Server, SQL Lite or MySql.

Secondly, n2cms allows you to export your data from one n2cms website so you can import it into another. This is completely database agnostic as well!

This is something I missed at first, but if you look at the top of the screen you'll see an "Export" button.


Click this and you'll now get the option to import or export data. Export your data and save it somewhere on your disk.


If you are exporting your site to move to another server, just click "Export these items".

Ok, so we've exported the data to our local disk and we now need to to migrate the data to the new server.

Go back into your web.config and change the connection string to your new database server.Start up your n2cms site and you will get the install page.

Install as usual until step 4 and select the "Advanced options" at the bottom of the page.

Select your exported file and n2 will then recreate your site on the new server!

Simple, but absolute genius!

4 comments:

  1. Hi,
    have you ever face a problem with import?
    I try to use it. Export was fine, but import was not.
    When I try to upload and import I get "The connection was reset" at once.

    ReplyDelete
  2. Hi Van

    I've not had any problems at all with the import/export feature and as you can see I regularly go from SQL Server to MySql as well.

    Here's a couple of things to check:-

    1. Are both the export/import sites running the same version?

    2. Is the export file large? If so, I wonder if the file is too large and you may need to change the value in your web.config to allow larger file sizes.

    3. I believe you can export/import parts of the N2CMS tree rather than the full site. Maybe doing this export/import a few times on smaller parts of the tree might work?

    Hope this helps!

    ReplyDelete