MS SQL Server is Microsoft’s product for delivering database functionality. It is used for storing and retrieving information from different applications. It is considered a relational database server and uses T-SQL and ANSI SQL for querying languages. In this article we will focus on the 2008 edition. This is available in 6 different editions as follows:
- Express – intended to be used as a stand-alone solution for lightweight applications; it supports just 1GB of memory, one CPU, and a maximum database size of 4GB (extended to 10 GB in SQL Server 2008 R2)
- Workgroup – intended for small applications; it supports 2 CPUs and 4GB of memory
- Standard – intended for the general purchasing audience and most applications; it has all the main functionalities and supports 4 CPUs and unlimited memory
- Enterprise – exceeds the features provided by the Standard edition by offering Database mirroring and snapshots, online indexing, online page restores, and distributed partitioned views, along with some advanced features for business intelligence (BI); (limited to support only 8 CPUs in SQL Server 2008 R2)
- Web – intended for large web applications, supports 4 CPUs and unlimited memory
- Developer – Essentially the same product as the Enterprise edition, but it’s free and is licensed to be used in a test environment only, never in production
- Compact – intended for embedded environments; it’s free
As you can see the main differences between the versions are the supported resources. Let’s take a look at the features that SQL 2008 offers:
- SQL Server Integration Services (SSIS) – offers the ability to extract/transform/load (ETL) data. In essence, it allows data movement by performing data manipulation and transformation of the data and delivering it to one or more data destinations.
- SQL Server Reporting Services (SSRS) – is responsible for creating custom reports which summarize the information in an easy-to-understand format for the business users. The reports can be created in two ways. The first is with Report Designer which gives you unlimited options but requires technical knowledge of the structure of your data and how to use Visual Studio. The second option is to use Report Builder which is much simpler, and is targeted mostly at business users, not to the actual developers.
- SQL Server Analytical Services (SSAS) – is used for Online Analytical Processing (OLAP) analysis. It analyzes the data in advance, which does consume some additional space and computing power, however this significantly reduces the response time for future queries.
- Business Intelligence (BI) – uses all the features mentioned above to help make intelligent decisions using the existing data. It organizes the raw data in such a way that it can make sense to the decision makers. It can show the management why a lower, or a bigger rating, of the company is reached and what the rating will look like in the foreseeable future.
- Support of unstructured data – this is a critical functionality in today’s businesses where data comes in many forms, not only tables. There are pictures, videos, and documents, all needing to be categorized. They are all now supported through the FILESTREAM data.
- Full-text search – can be used by developers for performing full-text search queries against character based data stored in an SQL table.
- PowerPivot for Office 2010 (introduced with SQL Server 2008 R2) – allows you to interact with SQL directly from Excel
All these amazing features come at a price. The three scenarios for licensing SQL Server are as follows (cited from Microsoft):
- Server plus device client access license (CAL). Requires a license for the computer running the Microsoft server product, as well as CALs for each client device.
- Server plus user client access license (CAL). Requires a license for the computer running the Microsoft server product, as well as CALs for each user.
- Processor license. Requires a single license for each CPU in the operating system environment running SQL Server. This license includes unlimited client device access.
Stay tuned to Monitis for more articles on MS SQL Server.