DaveWentzel.com All Things Data
Express vs Standard
- a two node cluster (more clustering options require Enterprise)
- Your db is larger than 4 GB. Although, there is a legal way to get around this discussed below.
- You need SSIS and/or SSAS
- The application has concurrency demands. Express runs only 1 processor. But this is misleading...this is a "physical" processor, not a "logical" processor.
Example1: You have a small workgroup application with little concurrency, with a dedicated database server running on a 2 way box. In this case SQL Server will only start one scheduler and the second chip will be unused for sql processing. The other processor can still be used for other OS/application needs. Keep in mind that if you have a multi-core chip that Express will actually use as many logical processors as are allocated to that single socket. More information can be found on this blog post..."CPU" terminology demystified.
Example2: You have a small workgroup application with little concurrency, with a dedicated database server running on a 2 way dual core box. In this case SQL Server will start 2 schedulers, but confine them to 1 chip. The second chip will be unused for sql processing but can still be used for other OS/application needs.
So, we can still get some concurrency using express if we purchase the right hardware...always prefer multi-core over multi-chip.
Remember, most databases and queries will rarely run individual queries using parallelism (in other words, spanning and utilizing extra logical processors). So what does a multicore/SMP system buy you for SQL Server...concurrency...namely the ability to run many simultaneous users and their queries on a single instance (think web applications with many users).
Further, in many applications we explicitly DISABLE parallelism (using MAXDOP 1 on individual problematic queries, or on the entire instance). Why? Because all DBMSs suffer from parallelism problems...queries are rarely written to run parallel and may even run *slower* in parallel.
- RAM. Express is limited to 1GB RAM *per instance*, but this too is misleading. The 1GB is actually for the buffer cache. So, it's not uncommon to see express instances utilizing around 1.5GB (1 for the buffer cache, and the remainder for lock managers, log buffers, connection memory, query memory grants, etc).
Unless your instance has a buffer cache hit ratio under ~95% with express, you still don't need standard.
So, let's say you have 4GB of RAM in your Windows 2003 server, does this mean 2.5 GB will be wasted if we use express? No. Remember, the OS and other applications will require some memory.
But we just bought a server with 16GB of memory to run our low-concurrency workgroup application dedicated sql server. What edition should we use? Well, if the db is less than 4GB anyway, express is the way to go, and you bought *way* too much hardware. Even if your db is 500GB, your buffer cache may never get above 1GB regardless of edition.
Don't forget, if you have over-spec'd hardware you can always run multiple dbs/instances of express on that hardware and therefore better utilize your investment.
Can I have a database larger than 4GB on my Express Instance?
Yes. Very easily in fact. You just need to rethink what your definition of a "database" really is.
Here are some methods to overcome the 4GB size limitation:
- Move your blobs to FILESTREAM storage. The 4GB limit does not apply to FILESTREAM and this works transparently with all applications I've worked with.
- Logically partition your databases and run each logical partition on a separate db/instance/server.
- Use synonyms. A database synonym is an alternate name for a local, or a *remote* database object. The fact that you can build a synonym against a remote db object means you eliminate location affinity in your db. This also means you can legally skirt the 4GB database size limitation of express.
Example: You have an in-house developed workgroup database application with low concurrency demands. Based on this you decide to deploy using Express. Requirements have changed and much more "history" data is required to be online than originally anticipated. Most tables are small, but 2 "history" tables FOO and BAR are each expected to be 2 GB. You need to quickly rectify the problem and there is no budget to purchase sql standard.
Process: Take FOO and BAR offline. Copy the tables to another database (can be on separate servers/instances) and DROP the original tables. In the original database run the following sql:
CREATE SYNONYM FOO FOR <new_server/instance>.<newdb>.FOO
CREATE SYNONYM BAR FOR <new_server/instance>.<newdb>.BAR
Some other changes may be necessary depending on whether there are indexed views/constraints/FKs. But at this point the application can be brought back online. When the application queries FOO or BAR the db server will see the synonym and will call the alternate location instead.