SQL Server Enterprise vs Standard

SQL Server 2008 R2 comes with a lot of features and functionality. Even the Standard version has a long list of capabilities to set DBAs drooling. But then there’s the Enterprise version. A question that comes up, more often than we’d like, is, “Why would I need Enterprise?” If there are so many features available out of the box with SQL Server Standard, why go to the Enterprise version?

If you want to go into it feature-by-feature, the SQL Server website has a comparison site which allows you to look at the features under various headings and see which versions of the product those features are included in.

But if you don’t want to go through the whole list, here are my thoughts on some key ones.

Master Data Services

As a set of features, Master Data Services comes with SQL Server Enterprise but not with the Standard edition. MDS provides the technical framework of a master data management solution, letting organisations have control over their data. MDS helps companies ensure that their data is consistent, accurate and up to date, even if that data is spread across multiple applications and databases.

As a concept, master data management is a very powerful one. There are few companies who would say that they don’t want to be confident that their data is correct. If you’re building a business intelligence solution, you want to be sure that the data you’re starting from is valid. The best analysis in the world won’t give you reliable answers if you give it figures that are wildly incorrect. MDS can provide a master data database to store an authoritative source of information to be used in applications. It can also provide a framework of checks and rules to ensure that data stays consistent and correct across multiple applications if values are changed in one.

PowerPivot in SharePoint

PowerPivot starts off as a free download add-in for Excel. Users running Excel 2010 can install the client add-in and start creating powerful dashboards and applications using familiar pivot table interfaces that pull data from a range of sources, both on-premise and in the cloud.

When users want to share these PowerPivot files, that’s where the SharePoint add-in comes in. This lets users publish PowerPivot files to galleries in SharePoint where they can be viewed by others in the organisation. It also provides a management dashboard, so IT can control permissions to the files, audit changes and manage the refresh schedule for the data. This allows IT to have control over files produced quickly by end users.

The PowerPivot operational dashboard and SharePoint collaboration layer come with the Enterprise edition of SQL Server.

Auditing

SQL Server Enterprise allows you to set up automatic auditing of changes on your databases. You can have fine grained control over what events get logged. Depending on your data and application, auditing might not be optional. Some industries have regulations regarding data meaning that you need to be able to show who has accessed and amended values in a database. If that’s the case for you, the Enterprise edition is almost essential.

High Availability

For some databases and systems, downtime is not an option. Sometimes, an application going down means that money isn’t coming in. For those applications, a high-availability strategy is needed. SQL Server Standard includes some features to enable high availability, but the Enterprise edition brings more. Features like database snapshots, mirrored backups and fast restore mean that when something goes wrong, a back-up system can be online and in the right state to carry on.

Scalability

SQL Server Enterprise can scale more than the Standard edition. In hard numbers, Enterprise can run on 8 CPUs instead of Standard’s 4, utilising 2TB of memory instead of 64GB. The maximum database size is the same (524PB) for both, but the limits on CPUs and memory will affect the performance of a big SQL deployment. Some of the options around indexing will also make a big difference on performance when the database size and usage gets large.

These are such some examples of areas where SQL Server Enterprise provides a significant advantage over SQL Server Standard. For the full list, check here.