SQL Server Compact – Will it perform well with heavier traffic sites? [closed]

Posted on

Question :

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.

So, will 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).

Answer :

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:

  • Lost updates.

  • Inconsistent analysis (nonrepeatable reads).

  • Phantom 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 🙁

Leave a Reply

Your email address will not be published. Required fields are marked *