SQL server enterprise edition needed for 24/7 applications?

[Mark R]

I was looking at a new business app, and the vendor took us to a reference site.

After all the presentations, one of the things that the reference user was whining about was the fact that they had to upgrade all their database servers to SQL server 2008 enterprise with it's eye watering per-CPU core license cost, because SQL standard was unable to run the app continuously without the need to take the database for daily maintenance.

I've never heard of this before, and I used to develop stuff on SQL server standard, and never had to take the database down for backup, etc.

However, it's been nearly 8 years since I last used a full SQL server, as these days I find the personal edition more than adequate.

I'm just wondering whether the hosts had misinterpreted something. Clearly if we can avoid buying 16 cores worth of SQL enterprise licenses, it will make a huge difference to the overall capital cost.

The vendor did say SQL enterprise was needed in their spec documents, but the reps couldn't give a sensible answer as to why it was needed.

 

[darktubbly]

We run SQL 2008 R2 Standard and Enterprise for various apps, and provide 24/7 high availability on both sets of systems. The only difference when it comes to availability that I have seen is if you are doing index rebuilding, where EE offers online rebuilding. There is no downtime with SE, but your queries could be blocked on a table if an index rebuilding operation takes a long time.

The only logical reason for requiring EE that I can think of right now is if the software vendor is using partitioning, which only comes with EE. From an HA standpoint, you can use clustering (so long as your OS is EE) or mirroring for high availability for multiple or single databases, respectively.

Also, I believe 2012 is the first version of SQL Server which goes to core licensing. 2008 still uses socket-based licensing, unless you are purchasing through some kind of Software Assurance where you actually buy 2012 and are allowed downgrade rights to 2008. Yeah, the sticker shock for 2012 with cores wasn't fun.

Maybe you can ask to speak to one of the vendor's techs to see why they require EE?

 

[ForumMaster]

as an oracle dba (compare the prices, oracle starts at about $47,000 per core for the EE) i'll ask you this.

do you need it? the companies make the money from the licensing.
check this link

how big is your database going to get?
do you need partitioning? or can you get around it?
(for example, in oracle i can create tables for each month and then create a master view that selects all the tables using a union all. so long as the query selects the distinct feature of the table, the optimizer only "selects" the needed tables. i'll admit i'm not sure if this is the same in sql server, but i'm pretty sure.)

check the memory limits. do you need high availability?
do you really need to rebuild your indexes online all the time?

and then the final consideration: what is the cost of downtime.

say that you are down for 5 minutes. say for an hour. what is the cost to the business? $100? $50,000? only that can really tell you if you should go for the EE edition. the EE edition no doubt can give you better availability. but it's the last 3 percent.

the standard edition will be fine for %97 of the time. if you need %99.99 of the time, go EE. if you need better, you're using the wrong platform.

 

[KB]

All version allow online backup, but what darktubbly mentioned is right. You need Enterprise Edition for online index rebuilds. During an index rebuild the table can be locked which can not only block queries but updates and inserts too. The big question is do you really need 99.999% uptime or can you rebuild indexes one a month at a designated time? How much data will you be producing? You mentioned that the personal edition was adequate. Does this mean you produce very little data and may not even need to rebuild indexes?

http://technet.microsoft.com/en-us/library/cc645993.aspx#High_availability

 

[Mark R]

Yup. It was index rebuilds.

Basically due to the apps complexity and strict audit compliance, it runs a ton of queries for every operation, many of which are complex triggered/relational updates, and then there is the audit log.

With up to 2000 concurrent users (at the reference customer) making updates the the DB, you get major database fragmentation before long, hence the vendor had recommended that they run full database compaction and index rebuilds daily.

We won't be using anywhere near that workload, probably only 250-500 concurrent users, so it may be that we can get away without running routine rebuilds, and simply announce some planned downtime every now and again to run them, if performance gets problematic. Although, this is supposed to be a 24/7 app, but if it's 1 hour a month, planned, that could probably be acceptable.

 

[Phynaz]

If the software vendor says you need EE, then you get EE.

Why would you "try to get away with" anything in a business environment is beyond me.

 

[ForumMaster]

Yup. It was index rebuilds.

Basically due to the apps complexity and strict audit compliance, it runs a ton of queries for every operation, many of which are complex triggered/relational updates, and then there is the audit log.

With up to 2000 concurrent users (at the reference customer) making updates the the DB, you get major database fragmentation before long, hence the vendor had recommended that they run full database compaction and index rebuilds daily.

We won't be using anywhere near that workload, probably only 250-500 concurrent users, so it may be that we can get away without running routine rebuilds, and simply announce some planned downtime every now and again to run them, if performance gets problematic. Although, this is supposed to be a 24/7 app, but if it's 1 hour a month, planned, that could probably be acceptable.

there is a common "myth" there many people believe is true that you often need to rebuild indexes.

with today's modern RDBMS's, there is rarely a case where you need to rebuild your index, and nightly as well is just ridiculous no matter what your vendor says.

you need to understand that an index a physical structure on a disk.
i myself am an oracle dba but it's basically the same.
If I invoke a full table scan, i'll get an event called "db file scattered read".
If I invoke an index scan, I'll get an event called "db file sequential read".

In the first, I use multiblock read and don't care about a specific block (nor where it is).
In the latter, you will always go through the index structure (as many logical IOs) and then go directly to the block in the disk. does not matter where it is.

A rebuild can make the index structure smaller, but it is always maintained. It can sometimes hurt performance as well.
A rebuild forces the database to flush the object from the cache as it is no longer valid.

All in all, you really don't need to rebuild. If you really want to, run the job on weekends.