This project is read-only.

Using a Full SQL Server database

Topics: General, Installing Orchard
Aug 9, 2012 at 7:03 PM

I love Orchard and I have high hopes for using it within my organization. However, I may be up against a hard stop to using it.

I have a small Orchard site we developed in which the performance needed improvement. I had read on a couple of posts that there was a performance boost when migrating the database from sdf to sql server. I also was concerned with PCI compliance if we were to develop more robust applications with Orchard that required (at least) a full sql server database behind a firewall.

I was able to migrate to SQLEXPRESS successfully.  I actually had run into some problems doing another migration with an Orchard site that contained the Featured Item Slider (but I digress).  I then imported all the tables from the EXPRESS database into a full Sql Server database on a server behind our firewall.  The Orchard site loaded like a charm and the performance improvement was immediately noticable. However, when we tried to submit a custom registration page using a submit button, it failed with the following error:

"OOPS. Something went wrong sorry.  An unhandled exception has occurred..."  It had to do with an insert into the "[OurSite].dbo.Orchard_Framework_ContentItemRecord". The error was "Cannot insert the value NULL into the column "Id".  It also mentioned "select SCOPE_IDENT" in the message.

This had worked successfully with the both the sdf and SQLEXPRESS versions. I also generated this error running against full Sql Server locally.  Did I not migrate from express to full sql server properly? If we cannot use a full sql server database, it will be a show stopper for us and Orchard CMS.


Aug 9, 2012 at 7:39 PM

I can't imagine that a full sql server database wouldn't be supported; it's most likely that something went wrong during the database migrations.

To make sure that it works with a full blown SQL Server, simply try this: create a new (empty) database, and setup a new Orchard installation. Set the connectionstring to the new, empty database. Should work like a charm.

Out of curiousity, the error you mentioned, does this happen for any type of content that you create? For example if you created a new Page, do you see the same error?

Aug 14, 2012 at 2:49 PM

Yes, I got errors trying to create another custom page.  Like you mentioned, the database must have not migrated well from SQLEXPRESS to full SQL Server.  I recreated the site using the default recipe and a full-on sql datebase.  When we deployed to the web server with the database on a db server behind the firewall, the app was "orders of magnitude" faster.

I recently got the book "Orchard CMS Up and Running" by John Zablocki (O'Reilly) and am starting through it. I strongly recommend that anyone starting with Orchard become familiar with MVC 3 as-well-as Object Relational Mapping, dependency injection and inversion of control.  I am finding I need this knowledge in order to understand extending Orchard.  I am starting on a module to make popping links to pdfs and excel on other content easier for the admin.  Thanks.