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.
SQL Server 2008 R2, which ships this month, allows end users to tap into the powerful business intelligence features of SQL Server via tight integration with popular Microsoft applications like Excel.The downside, of course, is that to reap the full functionality of SQL Server 2008 R2 you also need to be running all the latest versions of Microsoft’s product lines – Windows 7, Windows Server 2008 R2, Office 2010, and SharePoint 2010.
SQL Server 2008 R2 boasts a number of new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility that manages multiple SQL Servers.
— PowerPivot allows end users to import, manipulate, analyze and export data, as well as create reports, all using the familiar Excel interface and language.
— Master Data Services is a new and powerful feature designed to help an organization create a structure and hierarchy for data, so that data collected and used across multiple departments, silos and databases is standardized and aligned. This ensures that different databases are interoperable.
— StreamInsight is a new platform integrated with .Net and SQL Server for processing streams of data in parallel, allowing for many simultaneous queries running on live data.
— The data-tier functionality in Visual Studio is designed to make it simple to create n-tier applications that use databases on the back end, and package the entire application including the database as a single unit.
— The SQL Server Utility allows for a single management dashboard across multiple servers and databases.
-SQL Server 2008 Standard Edition provides the relational database capabilities you would expect, as well as basic BI and reporting features. Its notable omissions include PowerPivot, Power View, Master Data Services, advanced auditing, transparent data encryption, columnstore indexes, and other data warehousing features.
SQL Server 2008 Standard Edition also includes support for two-node AlwaysOn failover clusters. Microsoft will also continue to offer the free SQL Server 2008 Express Edition, which includes the new LocalDB feature for lightweight deployments.
Cognosys Provides Hardened images of SQL Server 2008 Standard Edition on the cloud ( AWS marketplace, Azure and Google Cloud Platform).
Deploy SQL Server 2008 Standard Edition securely on cloud i.e. AWS marketplace, Azure and Google Cloud Platform (GCP) .
Click on the respective cloud provider tab for technical information.
SQL Server 2008 R2 Standard edition on Windows Server 2008
SQL Server 2008 R2 Standard edition on Windows Server 2008 on cloud for AWS
10 SQL Server 2008 Features:
Microsoft released the feature rich version of SQL Server 2008 to manufacturing on August 8, 2008. Have you got it installed yet? Possibly, you are still in the mist of migrating to SQL Server 2005. Or maybe you have yet to find any features that would require you to obtain SQL Server 2008. In this article, I’ll cover 10 new features that were introduced with SQL Server 2008. These features are not presented here in any particular order. Possibly one or more of these features will provide you and your organization with some compelling reasons to obtain and installing SQL Server 2008.
1.Geospatial Data Types and Functions
Finally, some Geographic Information System (GIS) capabilities have been incorporated into SQL Server. With SQL Server 2008 two geospatial data types where introduced and a number of geographic functions. The two new data types are geometry and geography. The geometry data type is a planer data type that represents the Euclidean coordinate system, or more commonly called the flat earth model. The geography data type represents ellipsoidal data and represents the round earth model. With these new data types, you can identify a specific point on the earth, or other geographic artifacts like roads, lakes, cities, countries, etc.
With the support for the geometry and geography data types Microsoft provides a number of different types of Open Geospatial Consortium (OGC) methods. These methods allow you to define points, linestrings, polygon, distance, intersection, etc. These GIS enhancements should now allow you to easily incorporate mapping functionality into your applications.
2.Resource Governor
When you are running many different kinds of processes on your SQL Server machine, you need a way to control the resource intensive processes so they don’t consume all the resources of your machine. The Resource Governor is a way to control those processes that are resource hogs. With the Resource Governor, you can limit CPU and Memory resources for those sessions that can be identified as using excessive resources. The Resource Governor uses classifier functions to identify which workload a particular session should use. The classification of a session in turn associates them with Resource Pools. By limiting the amount of CPU and Memory associated with a Resource Pool, you can control the amount of resources classified sessions can consume. By using the Resource Governor available with SQL Server 2008, a DBA can better control resources usage to provide a balanced throughput of all sessions. The Resource Governor is only available with the Enterprise and Developer editions of SQL Server 2008.
3.Policy-Based Management
With Policy Management, you can consistently manage your SQL Server 2008 instances through a set of rules, known as policies. You can write policies that will control things like naming standards, server configuration, import/export requirements, etc. By using a standard set of policies across all your SQL Server instances, you can consistently manage your servers and minimize your administrative costs to enforce those policies.
A policy consists of a condition that is defined against a facet that is checked against the appropriate target SQL Server components associated with the facet. A facet is a single SQL Server component that contains properties. Here is a short list of some of the facets available: Credential, Data File, Database, Index, Login, Name, Schema, Server, Table, User and View. The condition is a logical expression that validates that the properties of the facet meet your policy requirements. By using the condition defined in a policy you can control what is appropriate and not appropriate for a given facet.Policy Management is available in all editions of SQL Server 2008.
4.Table Compression
To help conserve on the amount of disk space consumed by a table and it indexes, Table Compression was introduced in SQL Server 2008. This feature is only available in the Enterprise and Developer editions of SQL Server 2008.
With Table Compression, there are two different types of compression that can be used: Row and Page. Row compression compresses the fixed fields to save additional space in a row. Page compression goes a step further than row compression. It first does row compression and then performs prefix compression and dictionary compression. Prefix Compression is the process of taking reoccurring column prefix values and storing them in a Compression Information (CI) structure stored in the header of a page, and then replaces the actual values with an index to the value stored in the CI structure. Dictionary Compression is the process of taking reoccurring values in a page and replacing them with the index of the value stored in the CI structure.
Since Table compression is done within the database engine, it is completely transparent to the application. From my limited testing of compressing, it appears that 40-60% space savings can be gained by using Table compression. Your compression savings might vary based on the data types you use and data stored in your database tables.
With table compression there is also the possibility to improve the elapsed time of some of your processes. The performance boost occurs because fewer I/Os need to be performed to read the data. Keep in mind that the additional overhead to compress and decompress also is a cost associated with Table compression. Therefore, to improve the elapsed time of your TSQL statement the cost savings associated with performing less I/Os needs to be greater than the additional CPU cost incurred by performing Table Compression.
5.Backup Compression
New in SQL Server 2008 Enterprise and Developer edition is database Backup Compression. With Backup Compression, the database backups are compressed as the backup file is being written. By using compressed backups, you can save valuable disks space if a disk device is the target for your backups. Compressing backup will help minimize the amount of space needed for your backup. This could help if you don’t have a lot of free disk space.
Because less I/O is needed to write compressed database backups to a file, you might see your backup processes run fasters. Keep in mind compressing backups is a CPU intensive process. So if you are running compressed backup statements while other CPU intensive processes are running both the backup and the processes will be affected if you max out the CPU capacity of your machine.
6.Data Collections/Management Data Warehouse
There is a new statistic gathering and reporting mechanism called Data Collection. By using Data Collection a DBA can easily collect, store and manage statistics about their SQL Server instances. These statistics are stored in a database known as the Management Data Warehouse. The Data Collection mechanism does not only collect data but it also purges old data. When you define data collections, you identify how long you want to keep the data. Using this retention period SQL Server has a built in processes to automatically purge your Data Collection data from the Management Data Warehouse.
Out of the box, Microsoft has provided three different system Data Collections. One collection collects statistics about queries that are run. Another one collects disk space information. The last Data Collection collects information about the System performance. You can also define your own custom data collections.
Also provided are some canned reports that report on the data collect by the system Data Collections. These reports allow you to view trend information from the data collected by these system Data Collections. The reports are feature rich with hyperlinks to allow you to drill down from a high-level report to more specific detailed report. Using these canned reports, you can get a good picture of what is going on within your server.
7.FILESTREAM
Much of the data we work with is unstructured data, like images, Word documents, etc. This type of data is typically called binary large objects (BLOBs). Now with SQL Server 2008 you are able use the database engine to manage and store BLOBs as an NTFS file using FILESTREAM. With FILESTREAM, you are able to store BLOBs that exceed 2 gigabytes of space. By default, SQL Server 2008 has disabled FILESTREAM storage. You need to turn it on to take advantage of this kind of storage. Storing your images, Word documents or other BLOBs as a FILESTREAM object allows these objects to be under the control of the database engine. This allows the database engine to backup and restore these objects as part of the database backup/restore process. Also having these objects controlled by the database allows the database engine to also control the security related to these objects. Therefore, users don’t have access to FILESTREAM objects unless they are granted access. FILESTREAM is supported by all editions of SQL Server 2008.
8.New Date and Time Data Types
Finally, Microsoft has realized applications do not always need a time value stored with a date, or a date stored with a time value. With SQL Server 2008 two new data types, DATE and TIME, became available. Now you can use these new data types to store just the component of a point in time that you need, either a DATE or a TIME. Also provided is a new date/time data type known as DATETIME2. DATETIME2 increases the amount of precision you can have on the time portion of your date/time value. With DATETIME2 data types, you can store up to 7 digits of precision with accuracy down to 100 nanoseconds. DATETIME2 is also a variable length field, meaning you can define the number of digits of precision you want to store for the time portion. Allowing you to identify the precision allows you to conserve disk space for your DATETIME2 columns. Lastly, a new DATETIMEOFFSET data type was introduced. This new date type allows you to store date and time values that are time zone aware. This data type also allows you to define the number of digits of time precision you require for your DATETIMEOFFSET values.
9.Transparent Data Encryption
Transparent Data Encryption is just want it sounds like. Transparent Data Encryption is the process of encrypting your databases at rest transparently from the application. The encryption is done as blocks are written to disks, and then are decompressed when they are read back from disk. So within the buffer pool the database engine works the same, since the data is unencrypted in the buffer pool. There is some increased overhead to perform Transparent Data Encryption. As a number of the other new features mentioned here, this feature is also only available in the Enterprise and Developer editions of SQL Server 2008. When you use Transparent Data Encryption your databases data is encrypted at rest. So if you detach a database it is encrypted and can’t be moved to another server unless the encryption keys are also moved to that other server. The database backups are also encrypted, so you can only restore them to other servers if you also backup and restore the encryption keys.
10.Change Data Capture
SQL Server 2008 is now able to track changes to your database overtime using a new feature called “Changed Data Capture”. This new change tracking feature is only available in the Enterprise Edition and Developer edition. Once a table in a database is enabled for change data capture all changes to that table are tracked by storing changes in a change table. The change table will contain one record for every INSERT that can be used to identify column values for the inserted records. Each time a DELETE is performed the change table will contain one record for each DELETE that will show the values in each column prior to the DELETE. When an UPDATE is preformed, against a change data capture enabled table, two records will be created in the change table, one with the updated column values and one with the original column values. By using change data capture, you can track changes that have occurred over time to your table. This kind of functionality is useful for applications, like a data warehouse load process that need to identify changes so they can correctly apply updates to track historical changes over time.
The best new features in SQL Server 2008:
SQL Server 2008 includes 61 new features—before even counting bug fixes and performance improvements. Detailing each feature at length would be difficult, so here are some features that are off the charts on the snazzy scale.
The enhanced data engine
The data engine has undergone significant improvements. One of the most exciting capabilities of SQL Server 2008 is the merge statement, which allows two data sets to be merged. For example, if I have a data set containing rows that I want to push into an existing table, I can use the merge statement to insert rows that don’t exist in the destination table. It’s then possible to update rows that exist in the destination table.
Native compression permitted
SQL Server 2008 Enterprise Edition enables native compression on database and transaction log backups. SQL Server database and transaction log backups are invaluable on servers when:
- Space is at a premium.
- You need to minimize I/O write activity during a backup.
- You want to reduce network bandwidth required when copying a database or transaction log backup across the network.
These database page and row compression capabilities also reduce storage requirements.
Better Reporting Services
The redesigned reporting services feature improves the Report Builder and the reporting templates within Visual Studio or Business Intelligence Development Studio (BIDS). I believe that these improvements are so robust that once you start building reports in Visual Studio 2008, you won’t want to go back. Overall, Reporting Services gives developers more functionality and extensibility while also allowing them to generate reports. Microsoft has added several new report types, including:
- Tablix:Combines a list, table and matrix report and example is below:
- New graphs and charts:Several new chart types, such as axis and formulas, as well as wizards give you greater control over properties.
- Gauges:Allow you to display key indicators as a gauge and embed gauge controls alone in a report or in conjunction with other report types.
You can render reports in Microsoft Word along with renders for .html, .xml, .csv, .tiff, .pdf and Excel. SQL Server 2005 also supported those formats. SQL Server 2008 includes support for nested data regions and sub-reports in Excel.
Previous versions of Reporting Services were designed to scale up to large numbers of concurrent users; however, Microsoft did not anticipate the need to generate very large reports such as those over 1,000 pages. Reporting Services 2008 is no longer dependent on Internet Information Services (IIS), so it can scale to even larger numbers of concurrent users while rendering large multi-page reports. DBAs can define thresholds for how much memory Reporting Services will consume on the machine it runs on.
Streamlined Analysis Services
The most exciting new features in SQL Server Analysis Services 2008 are the improvements made to the Design Wizards. The Cube and Dimension wizards have much more intelligence built into them, enabling them to autosuggest dimensions and hierarchies and give best practices warnings. The warnings help you design high-performance Analysis Services cubes. The wizards also are more streamlined in that they require fewer mouse clicks to build a cube.
SQL Server Integration Services savings
SQL Server 2008 Integration Services (SSIS) ships as a standalone component in SQL Server 2008. So you no longer have to install SQL on the same machine running Integration Services. Running SSIS on a dedicated machine improves performance and eliminates the need to buy multiple licenses.
The following components were also redesigned for optimal performance:
- Lookup transformations have been enhanced.
- net is the preferred method for accessing data; SSIS 2008 is optimized to use ADO.net. Previous versions were optimized to use ole-db and ODBC.
- Pipeline improvements to the SSIS engine optimize scalability and offer better performance with a lower resource footprint.
- The change data capture and change detection features improve the performance of incremental refreshes.
Data profiling allows you to check data quality to see, for example, how dirty your data is. This can be useful in detecting data that’s in an unexpected format. For instance, you might expect data to contain St., Street, Dr., Drive, Ln., Lane—but not Rd. and Road. Data profiler displays such data inconsistencies so you can quantify occurrences and determine if it’s a spelling mistake, a data-entry mistake or a legitimate but unexpected entry.
Policy-based management
Not following best practices is one of the biggest factors limiting SQL Server scalability. Storing database log files and data files on the same drive will cause I/O contention and limit the scalability of all applications that access that SQL Server.With policy-based management, you can configure a set of policies to ensure that all SQL Server instances adhere to a set of best practices. You can configure policy-based management to deny any change to a stored procedure, table, function, database and so on that would make it fall out of compliance. You can also configure policy-based management to report on SQL servers that no longer follow best practices or company standards.
SQL Server 2008 is a complex product with many different components. An installation—especially an upgrade—must be planned to minimize downtime and avoid data loss. Microsoft has built on the setup program of SQL Server 2005 to deliver a more intuitive and robust installation and upgrade experience. SQL Server 2008’s installation wizard clustering component, in particular, was redesigned for stability. The command line installation process has also been significantly reworked. DBAs can now apply cumulative update patches to SQL Server release to manufacturing (RTM) files before beginning the setup process. Developers have demanded the slipstream setup for years.
-Features of SQL Server 2008 Standard Edition
1. Cross-Box Scale Limits
2. High Availability
3. Scalability and Performance
4. Security
5. Replication
6. Management Tools
7. RDBMS Manageability
8. Development Tools
9. Programmability
