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 is still another thing to do, because the SQL Server version of mojoPortal uses stored procedures whereas the SQL CE version does not, so far we have the data, but before we can use it we need to add the missing stored procedures. I've included 2 scripts in the mojoPortal package for SQL CE named sqlce-to-sqlserver-migration-mojoportal-core-procs.sql and sqlce-to-sqlserver-migration-mojoportal-features-procs.sql. You need to run those against the database using the SQL Server Management Studio Query tool.
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.
Last Updated 2010-07-25 by Joe Audette