- Transparent Data Encryption
- Change Data Capture
- Resource Governor
- Data Auditing
- Sparse Columns
- Backup Compression
- Table and Index Partitioning
- Filestream Data Storage
- FileTable
- HIERARCHYID Data Type
- Spatial Data Support
- Multiple Active Result Sets (MARS)
- SQL Server Utility
- Managed Self-Service BI
- Master Data Services (MDS)
- Centralized Management Console (CMC)
- SQL Server Reporting Services (SSRS) Add-in for SharePoint
- Policy-Based Management
- Extensible Key Management (EKM)
- Data-Tier Applications (DAC)
1. Transparent Data Encryption
Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 that encrypts the entire database. It encrypts the entire database using a symmetric key called the Database Encryption Key (DEK), which is protected by an asymmetric key called the Service Master Key (SMK). It uses an encryption algorithm to encrypt the data so that it can only be read by authorized users. TDE helps protect data stored in the database from unauthorized access and ensures that the data is not exposed in the event of a physical security breach.
2. Change Data Capture
Change Data Capture (CDC) in SQL Server 2008 is a feature that captures and tracks changes to data in a database table. It captures both the data before and after the change and stores it in a separate table. This allows businesses to track the changes made to their data over time and make use of the change audit data in their data analysis. It is especially useful for applications that need to continually synchronize with external data sources.
3. Resource Governor
Resource Governor is a feature in SQL Server 2008 that allows you to manage and control the use of resources in a SQL Server instance. It allows you to set limits on the amount of CPU, memory, and other resources that can be used by certain workloads or individual logins. This helps to ensure that one workload or login doesn't consume too many resources and starve others.
4. Data Auditing
Data auditing in SQL Server 2008 is the process of monitoring the changes that are made to the data in a database. It allows users to view information about who accessed the data, what they changed, when they changed it, and even why they changed it. Data auditing is important for compliance and security purposes, as it can help ensure that data is being used correctly and securely.
5. Sparse Columns
Sparse columns are a special type of column that are optimized for storing data in a table where most of the values in the column contain null values. This type of column reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. Sparse columns are especially useful when there are a large number of null values and when the space saved is more important than the extra time required for retrieval.
6. Backup Compression
Backup compression in SQL Server 2008 is a feature that compresses the data stored in a backup file to reduce the storage space needed and to improve the speed of backup and restore operations. It compresses the data prior to writing it to disk, resulting in fewer disk I/O operations and thus faster backups and restores.
7. Table and Index Partitioning
Table and Index Partitioning in SQL Server 2008 is a feature that enables users to divide large tables into smaller, more manageable parts, known as partitions. This feature can significantly improve query performance, as well as manageability of large databases. It also enables users to improve scalability, availability, and manageability of the database by allowing for the partitioning of tables and indexes across multiple filegroups. This allows for the data to be stored on different physical devices, as well as across multiple databases, which can lead to improved performance and availability.
8. Filestream Data Storage
FileStream Data Storage in SQL Server 2008 is a feature which provides streaming access to data stored in the NTFS file system. It allows applications to store unstructured data, such as documents and images, directly in the file system without having to convert it to a database format. It also provides a way to access this data directly, without having to transfer it into the database. FileStream Data Storage also provides methods for controlling access to the data, including securing it with the same security features available in NTFS.
9. FileTable
FileTable is a new feature in SQL Server 2008 that allows you to store and manage files and documents in a special type of table in the database. It provides a Windows-like hierarchical folder and file structure, making it easier to manage files within the database. It also allows for easy integration with Windows file system, providing easy access to files using Windows applications.
10. HIERARCHYID Data Type
The HIERARCHYID data type is a system data type in Microsoft SQL Server 2008 that allows for the representation of hierarchical data in a table. It is a variable-length, binary data type that can be used to store values such as file paths, organizational structure, and other hierarchical data. The HIERARCHYID data type is optimized to support hierarchical queries.
11. Spatial Data Support
Spatial Data Support in SQL Server 2008 is the ability to store and query data that is related to a location or shape. This data can include points, lines, and polygons, which can be used to define geographic features such as roads, rivers, cities, and countries. This support also includes SQL Server spatial functions and types, which can be used to analyze spatial data.
12. Multiple Active Result Sets (MARS)
Multiple Active Result Sets (MARS) is a feature in SQL Server 2008 that allows the execution of multiple batches at the same time in a single connection. This feature provides better performance and scalability when working with multiple result sets. MARS enables applications to process query results as soon as they become available, without waiting for the entire result set to be returned. It also allows applications to send multiple batches to the server at the same time, allowing for more efficient use of network and server resources.
13. SQL Server Utility
SQL Server Utility is a built-in feature of Microsoft SQL Server 2008 that allows database administrators to manage multiple instances of SQL Server from a single interface. It provides a unified management platform for database administrators to manage, monitor, and configure all their SQL Server instances from a single console. It also provides a unified view of all the SQL Server instances that are running in a given environment, allowing administrators to quickly identify and troubleshoot any potential issues.
14. Managed Self-Service BI
Managed Self-Service BI in SQL Server 2008 is a feature that allows users to create and modify their own BI solutions, such as reports and dashboards, without requiring IT support. It is designed to give non-technical users the ability to create and customize their own reports and dashboards without the need for IT personnel. With this feature, users can create their own reports and dashboards based on data stored in a SQL Server database. They can also share their reports with other users, and create custom visualizations.
15. Master Data Services (MDS)
Master Data Services (MDS) is a feature of Microsoft SQL Server 2008 and later versions that provides an integrated platform for creating, managing, and deploying master data in an organization. It provides a central repository for master data that can be used to store and manage a single version of the truth for an organization's master data. MDS also provides data stewardship capabilities, which allows organizations to define business rules and workflow to ensure the quality of master data. Additionally, MDS provides a web-based user interface to view, update, and report on master data.
16. Centralized Management Console (CMC)
The Central Management Console (CMC) is a graphical user interface that enables you to manage multiple SQL Server services and instances from a single location. It is included in the SQL Server 2008 Management Studio, and provides a graphical view of all the SQL Server services and instances on a single server or across multiple servers. With the CMC, you can easily access, monitor, configure, and manage all the services and instances. You can also use the CMC to manage SQL Server Agent jobs, database backups, and other administrative tasks.
17. SQL Server Reporting Services (SSRS) Add-in for SharePoint
SQL Server Reporting Services (SSRS) Add-in for SharePoint in SQL Server 2008 is a feature that integrates SSRS reports into a SharePoint environment. It provides a way for a SharePoint site to display reports created with the SSRS report designer and hosted on a report server. It also allows users to browse, search, filter, and interact with reports from within the SharePoint site.
18. Policy-Based Management
Policy-Based Management in SQL Server 2008 is a feature that allows administrators to create and enforce policies to maintain a consistent and compliant configuration across multiple servers or databases. It allows you to set up rules for configuration and security settings, and then automatically apply those rules across multiple servers. This helps to ensure that all servers are configured consistently, making it easier to identify and troubleshoot problems.
19. Extensible Key Management (EKM)
Extensible Key Management (EKM) in SQL Server 2008 is a feature that enables the use of third-party key management solutions. This feature provides an extensibility platform that allows customers to integrate their existing key management solutions with SQL Server. With EKM, customers can store their encryption keys in an external, secure key store and manage them in a secure manner. This helps them to further protect their encrypted data and comply with industry and government regulations.
20. Data-Tier Applications (DAC)
Data-Tier Applications (DAC) in SQL Server 2008 is a feature of SQL Server that allows the database administrator to package and manage the database schema and related objects, such as logins and database objects, as a single unit. This feature enables the database administrator to deploy and manage the database in a more consistent and reliable way. DACs can be used to deploy a database from one environment to another, and to manage database upgrades and schema changes.
