I ran a large wiki site (seedwiki.com) against MS SQL Server for about 10 years. We started using the Standard version and moved to SQL Server Express when Microsoft released it.
I also worked with MS SQL Server as a backend to projectkickstart.com, an ecommerce site which I built the shopping cart for and an adminstrative back end that tracks orders, does customer relationship management and generates mass emailings.
After developing Care Management, a client and meal scheduling application for BACS, I moved the data from MS Acess to MS SQL Server to be able to take advantage of the greatly increased speed of stored procedures. Eventually the database for that application was moved from an external dedicated host to inside BACS local network.