New Features of Microsoft SQL Server 2008

1. Transparent Data Encryption 
2. Change Data Capture 
3. Resource Governor 
4. Data Auditing 
5. Sparse Columns 
6. Backup Compression 
7. Table and Index Partitioning 
8. Filestream Data Storage 
9. FileTable 
10. HIERARCHYID Data Type 
11. Spatial Data Support 
12. Multiple Active Result Sets (MARS)  
13. SQL Server Utility 
14. Managed Self-Service BI 
15. Master Data Services (MDS) 
16. Centralized Management Console (CMC) 
17. SQL Server Reporting Services (SSRS) Add-in for SharePoint 
18. Policy-Based Management 
19. Extensible Key Management (EKM) 
20. 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. 

Different versions of Microsoft SQL Server; release, code name and release date.

 Version history of Microsoft SQL Server

Server Name	Release Name	Code Name	Release Date
Microsoft SQL Server 2019 (RTM)	SQL Server 2019 (15.x)	Big Data Clusters	November 4, 2019
Microsoft SQL Server 2017 (RTM)	SQL Server 2017 (14.x)	Machine Learning Services	October 2, 2017
Microsoft SQL Server 2016 (RTM)	SQL Server 2016 (13.x)	Stretch Database Release	June 1, 2016
Microsoft SQL Server 2014 (RTM)	SQL Server 2014 (12.x)	Hekaton	April 1, 2014
Microsoft SQL Server 2012 (RTM)	SQL Server 2012 (11.x)	Denali	March 6, 2012
Microsoft SQL Server 2008 R2 (RTM)	SQL Server 2008 R2 (10.5)	Kilimanjaro	April 21, 2010
SQL Server 2008	SQL Server 2008(10.0)	Katmai	August 6, 2008
Microsoft SQL Server 2005	SQL Server 2005(9.x)	Yukon	7-Nov-05
Microsoft SQL Server 2000	Microsoft SQL Server 2000(8.00.194)	Shiloh	7-Aug-00
Microsoft SQL Server 7.0	SQL Server 7.0(7.00.623)	Sphinx	1/24/1998
Microsoft SQL Server 6.5	SQL Server 6.5(6.50.201)	Hydra	15-Dec-95
Microsoft SQL Server 6.0	SQL Server 6.0(6.00.048)	SQL95	12/15/1996
Microsoft SQL Server 4.2	SQL Server 4.21a	NT-SQL	7/7/1995
Microsoft SQL Server 4.0	SQL Server 4.2B (16-bit)	Asterix	22-May-93
Microsoft SQL Server 1.1	SQL Server 1.1 (16-bit)	Pietro	7-May-89
Microsoft SQL Server 1.0	SQL Server 1.1 (16-bit)	Filipi	Mar-89

Automatically Stop Redundant Profiler Trace on Production Server

Understanding the impact of SQLTrace on production Server is very important because we need to keep few traces running all the time to answer customer questions about what was wrong on server whenever client complain about performance. 

The Profiler Trace will always impact server performance. The act of monitoring requires some resources. The key is to know don't have a habit to leave extra Traces running and trap everything on production server. 

To track client reported issues sometimes Developers\ DBAs run SQL Server Profiler Trace on production server and leave them unstopped.

The script schedule a job which run every 30 minutes to stop the redundant traces on server and if any trace is been stopped it generate email using Database Email.

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com  
--- To Stop Unnecessary User Traces With Execution Time > Half Hour

USE [msdb]
GO

/****** Object:  Job [StopUserRedundantTraces]    Script Date: 05/28/2015 09:39:33 ******/
BEGINTRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/28/2015 09:39:33 ******/
IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]'AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category  
  @class=N'JOB'
, @type=N'LOCAL'
, @name=N'[Uncategorized (Local)]'
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'StopUserRedundantTraces',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=2,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'LoginUserName',-- Change values
  @notify_email_operator_name=N'EmailNotifyOperator' -- Change values 
, @job_id = @jobId OUTPUT
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
/****** Object:  Step [StopTrace]    Script Date: 05/28/2015 09:39:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StopTrace',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'/*
Script by: Amna Asif
Purpose: To Stop Unnecessary User Traces With Execution Time > Half Hour
*/

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

DECLARE @TraceID NVARCHAR(255)
Declare @Body varchar(2000)

DECLARE ActiveTraceList CURSOR  
        FOR 
          SELECT tr.id
  FROM sys.traces tr
  WHERE status=1
  AND is_rowset = 1 
  AND is_default<>1--and reader_spid is not null 
  AND ISNULL(path,'''') NOT LIKE ''%ServerDBATrace_%''-- Change values
  AND DATEDIFF(mi,start_time,GETDATE()) >30


OPEN ActiveTraceList
    FETCH NEXT FROM ActiveTraceList INTO @TraceID
    WHILE @@FETCH_STATUS = 0
        BEGIN


              select @Body='''','''')
              from sys.traces 
              where id=@TraceID


              exec ('' sp_trace_setstatus ''+@TraceID+'', 0'')
              exec ('' sp_trace_setstatus ''+@TraceID+'', 2'')


   EXEC msdb.dbo.sp_send_dbmail 
   @recipients=''aa.sc@outlook.com'',
   @subject = ''StopUserRedundantTraces'',
   @profile_name = ''EmailProfile'',-- Change values
   @body = @Body,
   @body_format = ''HTML'' ;



            FETCH NEXT FROM ActiveTraceList INTO @TraceID
       END
    CLOSE ActiveTraceList
    DEALLOCATE ActiveTraceList



',
  @database_name=N'master',
  @flags=0
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Scheduled for 24 hours',
  @enabled=1,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=4,
  @freq_subday_interval=30,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20140130,
  @active_end_date=99991231,
  @active_start_time=0,
  @active_end_time=235959
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N'(local)'
IF (@@ERROR<> 0 OR @ReturnCode <> 0)GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT> 0)ROLLBACK TRANSACTION
EndSave:

GO