On June 30, 2010, Scott Guthrie blogged New Embedded Database Support with ASP.NET, announcing that SQL CE 4 (SQL Server Compact Edition) would soon be available. It was officially released as a public CTP (Community Technology Preview) on July 7, 2010 with an announcement on the SQL Server Compact Team blog in a post by Ambrish Mishra entitled Introducing SQL Server Compact 4.0, the Next Gen Embedded Database from Microsoft.
This little database is big news for mojoPortal
Today I am happy to announce a preview release of mojoPortal 2.3.4.7 for SQL CE 4 and ASP.NET 4.0. This package has a pre-configured database and can be deployed under Medium Trust with .NET 4 hosting.(*) This means it is easy to deploy and use even on budget shared hosting where Medium Trust security policy is almost always used. You can download the package mojoportal-2-3-4-7-preview-sqlce-net40-deploymentfiles.zip from the bottom of this page on Codeplex. It is an MsDeploy package, so it can be installed by importing it in IIS, or you can just unzip it and install manually.
When installing web applications like mojoPortal, often one of the more challenging installation issues is setting up the database, for non-techies this is the part that people really struggle with, and even people who consider themselves fairly tech savvy can find themselves struggling to get it working unless they have a background in working with databases and understand all the nuances of connection strings and permissions. Using SQL CE 4 eliminates this challenge completely because the database is just a file on disk. There is no database software to install on the server, so it does not depend on the host having it installed, it only requires .NET 4 hosting. Since we ship a database file already populated with initial data, it is basically zero configuration for the database, you don't have to do anything, it just works.
I'm sure some of you are aware that mojoPortal has supported Sqlite for a long time, and it is also a zero configuration, file based database that many people like a lot. However, it has never worked under Medium Trust and if your site traffic starts to grow there is not an easy way to migrate the data to a more robust database.
|
|
SQL CE |
Sqlite |
|
Zero config deployment |
yes |
yes |
|
Backing up the files backs up the database |
yes |
yes |
|
Works in Medium Trust |
yes |
no |
|
A supported migration path to a more robust database |
yes to SQL Express/Server/SqlAzure |
no convenient migration solutions that I know of |
Now, I'm not yet sure yet how easy the migration of mojoPortal from SQL CE to SQL Server will be. Scott Gu mentioned that they will be shipping migration tools that will make it straight forward to migrate the tables and data from SQL CE to SQL Express, SQL Server or SQL Azure. However, since SQL CE does not support stored procedures and we do use them in the SQL Server data layer for mojoPortal, it means we will also need to make migration scripts available to install the latest stored procedures into the database after migration. So, in theory, the migration process will be to first use the tool that Microsoft will ship to migrate the tables and data, then run the scripts we will make available that contain the stored procedures for a given version of mojoPortal, then you would just deploy the mojoPortal version for SQL Server over your existing mojoPortal for SQL CE installation and set your connection string for the new database. Once the migration tools ship I will verify the process and create documentation with the migration steps.
In summary, I think that the SQL CE package for mojoPortal will make it easy for you to stand up impressive new web sites quickly and easily in budget hosting with the possibility to upscale to a more robust database platform later without too much difficulty. I would say that if you are putting up a site that you are expecting to grow traffic quickly, then you should probably just start out using SQL Express, SQL Server, or SQL Azure. But, how many sites that you put online for your customers really get a lot of traffic? If the answer is not many, then you may find SQL CE is the best choice for many of your projects, reducing costs and time to deployment.
Top Secret Early Access! :-D
Now you may wonder how I managed to have a version of mojoPortal for SQL CE ready so soon after SQL CE 4 was released. The answer is that I had early access to SQL CE 4.0 and access to a bunch of good folks on the IIS and SQL Server teams to help me with guidance and questions and was also able to give them feedback while they were working on this release.
Back in early March I was contacted by Jonathan Hawkins of Microsoft and was invited to an online meeting where they told me what they were planning for SQL CE and why it might be beneficial for mojoPortal to support it, I of course was very excited about it. At the time the alpha bits of SQL CE 4 were not quite ready but I was provided with good help and work arounds that would enable me to work on support for SQL CE using the 3.5 version so I could get started without waiting. So I went ahead at that time and implemented it for the core mojoPortal features (it took me about a week) and managed to get it working as proof of concept. Once that was done I moved on to work on other things not knowing how long it would be until the alpha bits of SQL CE 4.0 would be ready. Finally on June 2nd I got an email that the alpha bits were available, but I was knee deep in other projects right at the time, so it was around June 27-28 when I finally tried the new bits and verified it worked under Medium Trust. Then when Scott Gu blogged about it on June 30, I realized it was going to be public very soon so I scrambled to complete the SQL CE data layer for the rest of the mojoPortal features (except for WebStore and my Add On Products). It took about 1 week to finish that work and by Friday July 9 I was ready to produce a package but decided to wait until Monday to blog about it.
So here we are, it is Monday, and I've uploaded the package on Codeplex, at the bottom of our current release. I've done sanity testing of all the features but not exhaustive testing of every configuration of every feature, so it is possible and perhaps likely there are still a few bugs here and there in the data layer, after all this is our newest data layer and therefore the least tested of all our data layers at this point. That is why I'm calling it a "Preview" release, and I hope you will give it a try and report any problems you may encounter. I will fix them quickly.
I'd like to thank all the folks at Microsoft for giving me this opportunity to work closely with them and get early access to the technology, and I especially thank Jonathan Hawkins, Parasuraman Narasimhan, Radhakrishnan Srikanth, Mohammad Imran Siddique, Himadri Sarkar, and Ambrish Mishra for all their help. It has really been a great experience for me getting to work with these guys and do something with mojoPortal that fit well with cutting edge work these guys have done on SQL CE. I really think they have solved one of the long standing problems in ASP.NET web deployment. For low traffic sites or quick prototyping or proof of concept deployment, or just low budget web sites, this is really going to reduce friction and make it easy to put a site up in minutes.
Technical Notes
It turned out well that I waited for SQL CE 4 before implementing all the mojoPortal features. When I implemented the core features I only had SQL CE 3.5 to work with so in places where I needed to select a page of data, I was having to use some awkward SQL syntax to make it work, it did work but it was difficult to read and write. One of the coolest new things in SQL CE 4.0 is the new syntax for selecting a page of data like this:
SELECT * FROM mp_GeoCountry ORDER BY Name OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
It would be great if someday SQL Server supports an easier paging syntax, as
it is still awkward to get a page of data efficiently in SQL Server, though it is a little easier than SQL CE 3.5 since SQL Server at least gives us stored procedures which provides a few additional strategies for getting a page of data. Anyway, you can guess I like this new syntax, it is much more convenient. Probably since I've worked with so many open source database platforms in mojoPortal, and have learned the major syntax differences between them, and really most of them have had easier ways to get a page of data than SQL Server for a long time, I'm glad to see something that makes a bit more sense. In a nutshell, MySql, PostgreSql, and Sqlite all support the LIMIT x OFFSET y syntax, Firebird has an interesting twist with SELECT FIRST x SKIP y, and SQL Server has always had the SELECT TOP (x) syntax but no equivalent to OFFSET, and this makes us have to jump through syntax hoops to get any efficient way to grab a page of data. So at the moment, in my view, SQL CE 4 has at least one cool syntax thing better than its big brother SQL Server even if it is not as capable in handling large traffic.
The above screen shot shows that the built in mojoPortal query tool can be used with SQL CE.
No need to install it in the GAC
One of the interesting things to me is that it seems like SQL CE 4 must be all managed code, that is, I think it must be implemented purely in .NET. The reasons I think this is because it seems that the database engine must be completely contained in System.Data.SqlServerCe.dll, and this dll just needs to be in the /bin folder, it doesn't even need to be installed in the GAC (Global Assembly Cache). The main reason why Sqlite doesn't work under Medium Trust is because it does some P/Invoke against a native dll written in C. To be allowed to do that the managed dll for Sqlite would have to be installed in the GAC. Since SQL CE can work without being installed in the GAC, I can guess it does not do any P/Invoke and is all managed code. Someone asked about this in the comments of Scott Gu's post, and while he did not specifically answer it, he did mention in comments that they are looking at enabling use of SQL CE in Silverlight which I think lends more evidence to it being fully managed code. I could be wrong though, possibly there are changes in code access security in .NET 4 that makes it possible to use without installing in the GAC.
The idea of being able to use SQL CE as a client side database in Silverlight is pretty compelling to me, so I'll be keeping my eyes open for future announcements. I had once done a proof of concept where I managed to use Sqlite as a client side database in Silverlight via Google Gears, but it had a few issues because database calls had to be marshalled back and forth from javascript to Silverlight via the Html Bridge. It would be much nicer to be able to use SQL CE directly from Silverlight. That would enable some really interesting client side scenarios.
(*) Actually I think SQL CE 4 could probably be used under 3.5 .NET but not under Medium Trust, it requires .NET 4 to work under Medium Trust, and the mojoPortal package for SQL CE is only going to be available for .NET 4.
Consolidation of Data Layers
At the moment we have a separate data layer for SqlAzure, SQL Server, and SQL CE. Back in March when I first began work on SQL CE support in mojoPortal, I blogged Anyone Still Using SQL 2000?, to guage how many people still use it. Going forward we are dropping support for SQL 2000 and then the MS SQL data layer will be modified to make it compatible with SqlAzure, and then we will eliminate the separate data layer for SqlAzure. I "think" the previous release of mojoPortal 2.3.4.5 was still compatible with SQL 2000, but the latest code in the repository is not compatible because we have changed from ntext to nvarchar(max) which is not supported in SQL 2000. So the next release of mojoPortal for sure will not be compatible with SQL 2000. Going forward we will support SQL 2005/2008/SqlAzure and SQL CE.
Follow us on twitter or become a fan on Facebook
