Loading ...
Sorry, an error occurred while loading the content.

1407File - New in SQL Server 2008.htm

Expand Messages
  • knowsql2008@yahoogroups.com
    Feb 1, 2014
    • 0 Attachment
      SQL Server 2008 : Enhancements

      SQL Server 2008: Enhancements


      SQL Server 2008 has host of new features for database administrators, developers, data-warehouse, etc. Below is some of the major enhancements and features are listed below for your quick reference and knowledge.


      Data Encryption


      Transparent Data Encryption

      The database level encryption feature in TDE enables you to encrypt data without affecting applications that consumed the data.

      You can also encrypt an entire database. Both data and the log files of a database are encrypted on the disk and are decrypted when they are read into the memory.

      Typically, database applications attempt to minimize their I/O as it is a costly operation. Hence, tying encryption and decryption to I/O helps existing application design optimizations to minimize the performance impact of encryption.

      TDE provides encryption at a coarser granularity level. SQL Server 2008 provides cell-level encryption. The algorithms used in TDE are available in cell-level encryption; hence, both cell encryption and granular-level encryption offer the same encryption strength.

      Database encryption with TDE is performed by using the database encryption key (DEK). You can use the standard Data Definition Language (DDL) to manage DEK.

      The actual state of encryption can be turned ON or OFF by using the ALTER DATABASE statement and the new clause: SET ENCRYPTION [ON | OFF].


      External Key Management


      The earlier versions of SQL Server enable you to manage encryption keys only within SQL Server.

      However, SQL Server 2008 enables you to manage keys both internally and externally by using third-party products. SQL Server 2008 can support Hardware Security Modules (HSM), which are third-party hardware solutions used to store keys in a separate location from the protected data.

      For example, if you are involved with credit card processing, or with the Payment Card Industry (PCI) compliance, you can use the appropriate third-party hardware security module (HSM) to manage keys externally.


      Security Auditing


      The SQL Trace feature in SQL Server 2005 provides security auditing to verify whether a user has logged on or whether any permission has changed. You can implement security auditing by using triggers. However, SQL Server 2005 does not provide data auditing. SQL Server 2008 provides several methods for data protection in addition to data encryption.


      System or Data Auditing

      Auditing has become an integral part of many database environments. Audit logs must be able to record all events occurring on the database server, while being flexible enough to configure auditing of only required events. In SQL Server 2008, auditing events also provide details of the modified data and the number of failed login attempts.

      In SQL Server 2008, you can take advantage of the comprehensive data auditing to monitor all events at the server and database levels, and across the enterprise. You can also consolidate enterprise-wide auditing records from multiple sources.  In addition, you can deploy auditing settings from one SQL Server to other SQL Servers in your enterprise, which makes an enterprise-wide auditing solution easier to deploy and manage. When auditing is deployed across the enterprise, the Auditing Data Collector consolidates auditing reports to provide a rich analysis of trends across the enterprise.


      Data Protection


      SQL Server 2008 uses encryption to provide secure backups against data disclosure or data tampering. You can limit the number of people allowed to restore a backup and thus ensure that the contents of the backup are reliable.


      A robust permissions hierarchy allows administrators to assign permissions at a granular level by limiting access to only those users who require it. In addition to standard permissions used to grant users with access to data, SQL Server allows greater management flexibility by allowing administrators to assign specific permissions. These permissions are required by assistant administrators to perform only the tasks that have been assigned to them. SQL Server 2008 also provides password complexity and expiration policies that protect your data by deterring brute force attacks. SQL Server 2008 also supports full encryption of sensitive communication between client applications and the server.


      In SQL Server 2008, you can implement auditing by using a simple AUDIT command



      Data Monitoring Features


      Change Data Capture (CDC) records insert, update, and delete activities applied to SQL Server tables and stores the details of the changes in an easily consumed relational format.


      A member of the sysadmin fixed server role enables a database for change data capture. After the database is enabled, members of the db_owner fixed database role enable tables whose change activity is to be captured.


      Resource Management Features


      Data compression is a resource management feature that helps you to compress the database and thereby reduce its size. The key goal of data compression is to reduce the size of fact tables.

      Data compression can be done by using techniques such as page level dictionary, differential encoding, and variable-length encoding of integer and date data types. However, data compression does not store zero values.

      Resource Governor

      SQL Server 2008 introduces the Resource Governor in the database toolset. The Resource Governor imposes resource consumption bounds on users, queries, and applications, and limits the utilization of resources. This tool also provides query progress monitoring.

      The Resource Governor tool manages CPU bandwidth, degree of parallelism (DOP), timeout waits, execution time, blocking time, and idle time. If resource limits are exceeded, the system can be configured to either trigger an event or terminate the process.


      You can get diagnostic information in SQL Server 2005 through database consistency checker (DBCC) and SQL Trace or SQL Profiler. In SQL Server 2008, there are enhancements to SQL Profiler, dynamic management views, Data Definition Language (DDL) triggers, and event notifications. There is also a Windows Management Instrumentation (WMI) operating system for events which use event notifications internally. SQL Server 2008 provides support for a new event system in Windows, called XEvents or Extended Events. XEvents work by creating and activating event sessions with DDL statements. Event sessions deal with items from event packages�events, targets, actions, types, predicates, and maps. You can mix and match the items from different packages in an event session.


      Dynamic management views and functions return server state information that you can use to monitor the health of a server instance, diagnose problems, and tune performance.

      There are two types of dynamic management views and functions:

      • Server-scoped
      • Database-scoped

      Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. Dynamic management functions can be referenced in Transact-SQL statements by using two-part or three-part names. However, dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.

      There are five new dynamic management views to present memory information:

      • sys.dm_os_memory_brokers
      • sys.dm_os_memory_nodes
      • sys.dm_os_nodes
      • sys.dm_os_process_memory
      • sys.dm_os_sys_memory


      Plan Freezing in SQL Server 2008


      Plan freezing or plan guides in SQL Server 2008 optimize the performance of queries. You can create and use plan guides when you cannot or do not want to directly change the text of the query.


      SQL Server 2008 provides a more extensive collection of data relating to performance than earlier versions of SQL Server. The performance data is stored in a centralized data warehouse.


      You can also use policies to identify and react to certain situations. For example, if the CPU load is greater than 85 percent for more than five minutes, you can trigger a more detailed data collection.


      One new tool in SQL Server 2008 for performance analysis is the Performance Dashboard tool that you can use to read saved performance data. In addition, this tool enables you to make historical and baseline comparisons and create action triggers. SQL Server 2008 also provides two features for performance analysis�data profiling and query optimization.


      The Performance Studio tool consists of a suite of tools that you can use for performance monitoring, troubleshooting, tuning, and reporting. In addition, this tool facilitates low overhead data collection and persistence of diagnostics data.

      The Performance Studio tool includes the following server-side features:

      • Data Collector. Provides a general data collection infrastructure for collecting performance and general diagnostics data. It is a configurable, extensible, and unified data collection mechanism that is also low on overhead.
      • Extensible data collection infrastructure. Supports T-SQL-based queries such as DMV data, SQL Trace, Perfmon Counters, and custom collector types.
      • Troubleshooting. Supports out-of-the-box data collections that are required to troubleshoot most common problems for the Relational Engine.
      • SQL Server services. Provides support for other SQL Server services such as analysis, reporting and integration.
      • Dynamic data collection. Enables or disables the data collection programmatically.
      • Additional features.The server-side features include drill-down reports, Management Data Warehouse (MDW), a data repository for baseline and historical comparisons, and aggregated reporting for multiple SQL Server instances.

      The Performance Studio tool includes the following client-side features:

      • SQL Server dashboard
      • Performance monitoring
      • Current and historical data analysis
      • Suggestions for potential performance tuning
      • Data collection sets-based reports
      • MDW-based reports


      Service Broker in SQL Server 2008

      In SQL Server 2005, Service Broker enables internal or external processes to send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). In SQL Server 2008, Service Broker offers better scalability by providing additional features such as priority conversations and new diagnostic tools.

      Priority Conversations

      Service Broker allows the messages of one conversation to be prioritized higher than the priority of another conversation. The priority affects both the sending and the receiving end. On the sending end, the priority affects the order of messages from different conversations. Messages with higher priority are pulled out of the transmission queue first and sent before messages with lower priority. On the receiving end, the priority affects the order in which the next conversation group is chosen through a RECEIVE call. You can apply a priority only to one end of the conversation, because the priority does not travel across the wire.

      Service Broker stores priority information in a system table and allows the use of Data Definition Language (DDL) statements to create or alter the priority. You can configure priority by using a local service name, a remote service name, and a contract. The Service Broker system uses these priority rules to set the priority of a conversation at the time of priority creation. The priority level will not change for the lifetime of the conversation. 

      Diagnostic Tools

      SQL Server 2008 provides a new command-line tool that allows you to easily diagnose both configuration and run-time problems in your Service Broker application. The tool also reports error messages and recommendations on how to fix these problems.

      Some of the problems that the tool can identify are: 

      • Service Broker is not enabled
      • A route is missing

      The diagnostic tools also provide better Management Studio enhancements for the Broker objects. The tools additionally provide actions such as a Create and Alter on all the Service Broker objects in the Object Explorer, and custom actions such as Enable Activation. The grid view option in these tools allows you to view the data present in the queues instead of executing the SELECT statement.

      Clustering in SQL Server 2008 removes this limitation by offering a more flexible clustering configuration. In addition, SQL Server 2008 supports mission-critical application and larger environments by providing support for clusters of up to 16 nodes.

      SQL Server 2008 includes new data types such as large user defined types (UDTs), date and time, FILESTREAM storage, hierarchyid, and spatial data. The T-SQL enhancements include the sparse columns and filtered indexes, and table-value parameters. By using these new data types and T-SQL enhancements, you can manage both relational and non-relational data to build global applications.

      Hierarchyid is a new data type that can store values that represent nodes in a hierarchy tree. The data type, which has a flexible programming model, can be implemented as a Common Language Runtime User Defined Type (CLR UDT). The CLR UDT exposes several efficient built-in methods for creating and operating on hierarchy nodes.

      A user-defined table type represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions.


      SQL Server 2008 provides new data types that can be used to store different kinds of geographical elements. These data types provide properties and methods that you can use to perform spatial operations such as calculating distances between locations and finding geographical features


      The following are the types of spatial data types:

      • Geography Data Type. This data type, defined by latitude and longitude coordinates, provides a storage structure for spatial data. This kind of data is typically used for physical structures, or for geographical features, such as vector data, or for calculating true great circle distances and trajectories.
      • Geometry Data Type. This data type provides a storage structure for spatial data that is defined by coordinates on an arbitrary plane. This type of data is commonly used in regional mapping systems. This data type can also be used for maps and interior floor plans. The geometry data type provides properties and methods that are aligned with the Open Geospatial Consortium (OGC) Simple Features Specification for SQL. This data type can be used to perform operations on geometric data that produce industry-standard behavior.

      In SQL Server 2005, UDTs are restricted to 8,000 bytes and are stored in a single page in system memory. In SQL Server 2008, there is no restriction on the size of a UDT. This new memory model enables you to store large UDTs and provides developers with more flexibility in creating UDTs.

      The file stream data type allows you to store unstructured data directly in the file system. You can use the new storage type VARBINARY(MAX) FILESTREAM to define table columns and store large binary data as files in the file system instead of storing it as a Binary Large Object (BLOB). In addition, you can use T-SQL statements�SELECT, INSERT, UPDATE, or DELETE�to query and modify file stream data.

      Table-value parameters are declared by using user-defined table types. You can use table-value parameters to send multiple rows of data to a Transact-SQL statement or a routine, without creating a temporary table or multiple parameters.

      Table-value parameters are like parameter arrays in Object Linking and Embedding Database (OLE DB) and Open Database Connectivity (ODBC), but offer better flexibility and closer integration with Transact-SQL. These parameters can also be used in set-based operations.

      In SQL Server 2008, you can use table-value parameters to pass an entire set of data into functions and procedures.


      Replication Enhancement in SQL Server 2008

      In earlier versions of SQL Server, the data changes to the replication system activities have to be stopped while adding a new node in a peer-to-peer replication topology. However, stopping system activity may not be feasible in mission-critical databases.

      SQL Server 2008 allows you to add new nodes to the replication topology without stopping system activity. This ensures that large mission-critical databases can function even during the installation of new nodes


    • Show all 78 messages in this topic