I recently developed an application using Linq to Sql against SQL Express for my development environment. Initially, I was under the impression that it would be running in a SQL 2K5 production environment, but at my first deliverable discovered that I was going to have to port back to SQL 2000 prior to delivery.
I thought at first that the only real changes I would have to make is reducing my varchar(max) types to the 2K5 upper limit and continue on, but discovered some pretty severe limitations to using Linq To SQL against SQL 2000.
To access my LTS objects and methods, I wrapped everything up into a data layer that returned either single enties or IQueryable for multiple result sets, this in turn was consumed by my business layer which generally converted to lists or something that made a little more since to the presentation. On my port back, IQueryable seems to be where I ran into the problems. I was running into errors like the following:
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.
Cannot translate expression to SQL for this server version.Translation would contain an expression of type Text, NText or Image in a SELECT DISTINCT clause
Cannot translate expression to SQL for this server version
Using SQL 2000 as the back-end doesn't allow me to do complex queries when the results contain nText or binary data as the derived sql makes extensive use of DISTINCT. Against SQL Express I had become accustomed to chaining together multiple IQueryable methods and then calling my datacontext to execute a single method. Now, I found myself having to make use of .ToList in virtually every complex query that involved a table at any join level that contained a BLOB. Lots and lots of junk across the wire...
I still don't have a workable solution to allowing IQueryable to be replacement for collections and am in the process of combining methods anywhere that IQueryable no longer makes sense which is tedious and hopefully will be rendered unnecessary.
That was a lot of lead up to what I really wanted to post - The limitations of SQL Server Express...
For ages (at least several months), I have been developing against SQL Express and find it easy to use, light weight and very capable. However, I had never before considered the possibility that SQL Express could be a viable dbms, especially in a production environment. My application is complex, but not huge.
The limitations of SQL Express are simply resource limits and not functional limits. As long as my db is smaller than 4GB in size (if I manage my log files well, there is no reason it shouldn't be) and doesn't require more than 1 CPU and 1 GB of RAM, SQL Express should be a fully capable and possibly even desirable alternative to the enterprise editions of even SQL 2005. These are not hardware limits, but resource limits constrained by the application itself. This means that SQL Express could probably live nicely on a web server and that the db service, out of the box, wouldn't grab an excessive share of resources.
Using SQL Express would help me to avoid extensive re-writes and would allow the client to install without any additional software purchases. I covet any feedback on your experiences using SQL Express in a production environment and any feedback in general about this post.
In addition, here are some links I found useful to finding the source of my problem in the first place:
H