I’m using Orchard
CMS, and on initial setup, the default setting is to use
SQL Server Compact. I’m not too knowledgeable about different database types and performance.
SQL Server Compact be able to handle a website getting 1,000+ hits? Assuming all else with the hosting provider is exceptional (bandwidth, up-time, etc).
I have not used SQL Server Compact, but because of its design, there are warnings in the SQL Server 2012 documentation about concurrency issues.
See: http://technet.microsoft.com/en-us/library/ms171845(v=sql.110).aspx for warnings on:
Inconsistent analysis (nonrepeatable reads).
If you need a free and much more functional version of SQL Server, consider using SQL Server Express. Its limits are considerable above the Compact edition. You can lookup its limitations.
SQL Server Compact is mostly used for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients.
There are lots of features not supported in
SQL Server Compact check differences and with lack of these features hosting provider with exceptional bandwidth, up-time, etc will not help in terms of performance and requirement too.
For website getting 1000+ hits, you can use SQL Server Express Edition, for which data limit is up to 10GB and free to use.
You can consider other edition if you are looking for database of size more than 10GB.
I am a SQL Server DBA – I am biased towards SQL Server. However if the budget is limited, I would suggest trying a mature open source database, perhaps MySQL, PostGreSQL or MariaDB.
Performance isn’t your real issue here. The security of your data and the safety of strong ACID transactions are what you should really prioritise. How are you going to backup your data with SQL Server Compact? Do you intend to replicate your data to a reporting database? What if your instance of SQL Server Compact goes down, can you provide high availability?
The other enormous advantage of PostgreSQL and MySQL is the huge community of people using these and willing to share their knowledge / experience. We have this in SQL Server as well, but not with SQL Server Compact 🙁