What Challenges Should I Expect When Using SQL Express for a Production Database?

SQL Express can work in a production environment if your needs are straightforward. It’s ideal for very small databases and light workloads, but keep in mind you won’t have amenities like SQL Agent. Make sure you set up regular backups using Task Scheduler and monitor your database size weekly to avoid any surprises. Also, you'll want to be clear to your management that while it's free, it does require your time and attention to manage it properly.

One major caveat is that SQL Express can be quite limiting, which is why many applications don't officially back it—it's easier for them to avoid the risk of poor performance. Just tread carefully, especially if your application might grow or become mission-critical.

SQL Express comes with some significant limitations. For starters, you're capped at either 1 socket or 4 cores, and it has a maximum of 1410MB of buffer pool memory. Most importantly, each database can only be 10GB in size, and you won’t have any high availability options like failover clustering. A big problem I've noticed is databases reaching that 10GB limit and just stopping. It's crucial to keep an eye on file sizes and ensure they don't balloon out of control.

The vendor seems to suggest SQL Express is viable for their application which isn’t too resource-intensive, but remember there are caps like that 10GB limit you should be aware of. If your use case involves light workloads, you might be fine, but it's a gamble if you're expecting growth.