In the computer world, everything is about
being "better, faster and cheaper"
than the competition - and SQL Server 2008 offers many new features to save
energy, time and money. From programming to administrative capabilities, this
version of SQL Server tops all others and it enhances many existing SQL Server
2000/2005 features.
Among the most significant changes
introduced in the areas of high availability and scalability in SQL Server 2008
are the following:
Note:
Points with RED color will be more relevant points
for the database developers.
· Database
Mirroring - Allows running hot-standby system
closely synchronized with the primary source. This provides an extension of log
shipping functionality, which existed in SQL Server 2000, with a number of
additional enhancements, such as low-latency, automatic failover and failback, and
two-way synchronization.
· Online
Restore - Provides the ability to restore data
without taking a database offline, which was the case in earlier versions of
SQL Server. Users are only prevented from accessing data that is being
restored.
· Failover
Clustering - Even though this is not a new feature,
its SQL Server 2008 implementation offers significant improvements, such as
eight-node clustering (in combination with Windows 2003 Server Enterprise
Edition) and support for failover of Notification Services, Analysis Services,
and a number of SQL Server Agent related tasks (such as replication or job
management and processing).
·
T-SQL (Transaction SQL) enhancements
- T-SQL is the native set-based RDBMS programming language offering
high-performance data access. It now incorporates many new features including
error handling via the TRY and CATCH paradigm, Common Table Expressions, which
return a record set in a statement, and the ability to shift columns to rows
and vice versa with the PIVOT and UNPIVOT commands.
· Table Partitioning - Provides the
ability to partition tables across file groups in a database, which optimizes
operation on large tables. Divide and
conquer - Dividing a terabyte table can be as effective as dividing an enemy,
in both cases you will win the battle. The divided data sets can reside on
separate physical servers, thus scaling out and lowering costs and improving
performance. So we can say, Partitioning is database process where very large
tables & indexes are divided in multiple smaller & manageable parts.
SQL Server 2008 allows to partition tables using defined ranges and also
provides management features and tools to keep partition tables in optimal
performance.
Data of partitioned tables & indexes is
divided into units that can be spread across more than one filegroup in a DB.
The data is partitioned horizontally, so that groups of rows are mapped into
individual partitions. The table or index is treated as a single logical entity
when queries or updates are performed on data. All partitions of a single index
or table must reside in same DB.
· Multiple Active Result Sets (MARS) - MARS
allow a persistent database connection from a single client to have more than
one active request per connection. This should be a major performance improvement,
allowing developers to give users new capabilities when working with SQL
Server. For example, it allows multiple searches, or a search and data entry.
The bottom line is that one client connection can have multiple active
processes simultaneously.
·
Database
snapshot and snapshot isolation - Snapshots
generate a read-only view of the underlying database, which can be used, for
example, to quickly recover data after unintentional or erroneous change. Note
that a snapshot is different from a copy, since it occupies only the space
required to contain changes applied to the database after it has been created,
greatly limiting storage requirements. Snapshot isolation provides parallel
access to the last committed row in a database, which can be used to eliminate
blocking issues when dealing with users operating simultaneously on the same
data set.
· Replication
- Its SQL Server 2000 implementation has been enriched by the introduction of a
new peer-to-peer topology, the ability to replicate via HTTP and HTTPS (to
accommodate secure communication over the Internet), and cross-platform
replication from Oracle databases.
·
HTTP
endpoints - You can easily create HTTP endpoints via
a simple T-SQL statement exposing an object that can be accessed over the
Internet. This allows a simple object to be called across the Internet for the
needed data.
· SQL
Service Broker - Provides functionality of asynchronous
message routing and guaranteed delivery, intended primarily for scenarios
involving complex, simultaneous, distributed, and interdependent data
processing tasks (common in e-commerce applications). In essence, this is a
message queuing mechanism native to SQL Server 2008, which can be configured
and managed using extensions to the T-SQL data manipulation language.
· SMTP
mail - Sending mail directly from SQL Server
2000 is possible, but challenging. With SQL Server 2008, Microsoft incorporates
SMTP mail to improve the native mail capabilities. Say "see-ya" to
Outlook on SQL Server!
·
Fast
Recovery - Allows connections to a database when
bringing it on-line as soon as its transaction log has been rolled forward (in
previous versions of SQL Server, connections were permitted only after
incomplete transactions had been rolled back).
· CLR (Common Language Runtime) - The next major enhancement in SQL
Server 2008 is the integration of a .NET compliant language such as C#, ASP.NET
or VB.NET to build objects (stored procedures, triggers, functions, etc.). This
enables you to execute .NET code in the DBMS to take advantage of the .NET functionality.
It is expected to replace extended stored procedures in the SQL Server 2000
environment as well as expand the traditional relational engine capabilities.
·
Auditing:
SQL Server Audit is a new feature of SQL Server 2008 that lets you create
customized audits of Database Engine events. SQL Server Audit uses extended
events to record the information for the audit and provides the tools and
processes you must have to enable, store, and view audits on various server and
database objects.
·
Backup
Compression: Backup compression was introduced in SQL
Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is
supported by SQL Server 2008 R2 Standard and all higher editions. Every edition
of SQL Server 2008 can restore a compressed backup. You can change the backup
compression behavior for an individual backup, backup job, or log shipping
configuration.
By default, backup compression significantly increases
CPU usage, which can adversely impact concurrent operations. You can create
low-priority compressed backups in a session whose CPU usage is limited by
Resource Governor.
· Change Data Capture: Change data
capture offers an effective solution to the challenge of efficiently performing
incremental loads from source tables to data marts and data warehouses. Change
data captures insert, update, and delete activity applied to SQL Server tables,
and makes the details of the changes available in an easily consumed relational
format. The change tables used by change data capture contain columns that
mirror the column structure of a tracked source table, along with the metadata
needed to understand the changes that have occurred. For more information, see
Basics of Change Data Capture.
· Change
Tracking: SQL Server change tracking allows
applications to obtain incremental changes to user tables. Where two-way
synchronization is required, change tracking also allows applications to check
for data conflicts. With change tracking integrated into SQL Server 2008,
developers no longer have to create complicated custom change-tracking
solutions.
Prior to the integration of change tracking
capabilities into SQL Server, developers often created custom change tracking
solutions that used a combination of triggers, timestamp columns, other
additional columns, and additional tables. Now, developing synchronization
applications is easier and faster.
· Data
Collector: SQL Server 2008 introduces a data
collector that you can use to obtain and save data that is gathered from
several sources. The data collector provides data collection containers that
you can use to determine the scope and frequency of data collection on a SQL
Server system. For more information, see Introducing the Data Collector.
· Central
Management Servers: SQL Server 2008 introduces a new
method of administering multiple servers by enabling you to designate Central
Management Servers. An instance of SQL Server that is designated as a Central
Management Server maintains a list registered servers.
· Service
Broker Nodes in Object Explorer: Object Explorer
has a new node for Service Broker conversation priorities.
· Hot
Add CPU: SQL Server 2008 supports dynamically
adding CPUs to a running system. Adding CPUs can occur physically by adding new
hardware, logically by online hardware partitioning, or virtually through a
virtualization layer.
·
New Data Types: SQL Server 2008's added few new
data types:
§ Date and Time:
Four new date and time data types have been added, making working with time
much easier than it ever has in the past. They include: DATE, TIME, DATETIME2,
and DATETIMEOFFSET.
§ Spatial:
Two new spatial data types have been added - GEOMETRY and GEOGRAPHY.
§ HIERARCHYID:
The HIERARCHYID data type is used to enable database applications to model
hierarchical tree structures.
§ FILESTREAM:
FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX)
data type that allows unstructured data to be stored in the file system instead
of inside the SQL Server database.
No comments:
Post a Comment