Using WebMatrix to Migrate mojoPortal from SQL CE to SQL Server

Using WebMatrix to Migrate mojoPortal from SQL CE to SQL Server

In a previous article, Using WebMatrix to Run the SQL CE Version of mojoPortal, I explained how to run the mojoPortal package for SQL CE using WebMatrix. I created some pages and content in the site and changed the skin. I could upload the files now to hosting and continue to run the site using SQL CE and later if my traffic grows or I am not satisfied with the performance, I can easily migrate to SQL Server. I would download the files again from the server to make sure I have the latest content and database file and then I would follow the steps in this article to migrate to SQL Server. For the purposes of this article I'm just using the same folder installation of the mojoPortal SQL CE package from the previous article, but if you downloaded your site from hosting the procedure would be the same, just browse to your folder with WebMatrix and open it to get started.

On a side note, you could create sites and content on your local machine and upload it fully populated, but keep in mind that once you have done that, you should not continue adding content to your local copy and then upload again, because depending on what features you have enabled there may be new content added to the site and database once it is on public hosting, or you may have added content yourself after deploying to the host. So you need to be careful not to upload the database file again or it would overwrite the file on the server and you will lose content. Once the mojoPortal setup page runs, it creates a SQL CE database file /App_Data/mojodb.sdf, so be careful not to overwrite that file if you decide to re-deploy other files or additional files.

1. Open the folder in WebMatrix, then click the Database Tab button on the bottom left, then click on the mojodb.sdf file to highlight it, then click the "Migrate" button in the top toolbar.

2. The Migration window allows you to specify or create the SQL Server the database that you will migrate to. Enter appropriate settings and click the "OK" button.

3. The migration for me only took a few seconds, I wasn't sure it even worked until I opened SQL Server Management Studio and confirmed that the database was there and it has the data. So now we have the tables and data migrated, but there are still a few more things to do, because the SQL Server version of mojoPortal uses stored procedures whereas the SQL CE version does not, and the SQL Server version expects nvarchar(max) fields in places where we use ntext for SQL CE. Also the WebStore feature is not supported in SQL CE so those tables don't exist in our migration. So far we have the data, but before we can use it we need to correct the data types and add the missing stored procedures. You'll need to create a separate installaiton of mojoPortal using SQL Server and the same version of mojoPortal that you migrated from so that we can get the needed things from there.

First using SQL Management toools go into the migrated database and go to design view on each table and wherever you see fields of type ntext, change them to nvarchar(max)

Next using the database for a clean new installation of mojoPortal using SQL Server, script off the ws_* tables (these are the WebStore tables), and script off all the stored procedures.

Run those scripts on your migrated database to create the missing tables and stored procedures.

4. Set the connection string for your SQL Server database in Web.config or user.config in the <appSettings section <add key="MSSQLConnectionString" value="server=yourservername;UID=yourdatabaseusername;PWD=yourdatabaseuserpassword;database=yourdatabasename"/>

5. Copy the mojoPortal.Data.dll, mojoPortal.Features.Data.dll, and WebStore.Data.dll from the bin folder of the mojoPortal package for SQL Server into the bin folder beneath the site folder in WebMatrix (ie wwwroot/bin in my example), overwriting the existing files.

6. Finally run the site again by clicking the Run button in WebMatrix. If you sign in and visit Administration > System Information, you can confirm that you are now running with SQL Server instead of SQL CE.