Mr. DiGi's Bookmarks tagged database →  View Popular

Download SQL Server 2008 Feature Pack

The SQL Server 2008 Feature Pack is a separate download from SQL Server software. This collection of stand-alone install packages is designed to provide additional licensed functionality for SQL Server 2008. It contains 17 new redistributable and backward compatibility components and add-on providers. For additional information on each of these components click the download button below.

No more dozens of srv_describe, srv_paramsetoutput, srv_rpcparams, srv_paraminfo, etc!

Just with myXProc do Fields[0].AsInteger := Params.ByName('@IntVal').AsInteger.

Transact-SQL Enhancements

Transact-SQL has long been the basis for all SQL Server programmability. SQL Server 2005 provides many new language capabilities for developing scalable database applications. These enhancements include error handling, new recursive query capabilities, and support for new SQL Server Database Engine capabilities. Transact-SQL enhancements in SQL Server 2005 increase your expressive powers in query writing, enabling you to improve the performance of your code and extend your error management capabilities. The continuous effort that is being put into enhancing Transact-SQL shows a firm belief in its significant role in SQL Server.

Report Builder

Report Builder, a new component of SQL Server 2005 Reporting Services, allows business users to create their own reports using a user-friendly model of their data.

Cross-Tier and Cross-Language Debugging

SQL Server 2005 and Visual Studio 2005 together provide even deeper integration in the area of application debugging.

Enhanced programming model. Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL Server developers.

Enhanced safety and security. Managed code runs in a CLR environment, hosted by the Database Engine. This allows .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server.

User-defined types and aggregates. Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.

Common development environment. Database development is integrated into the Microsoft Visual Studio 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.

Performance and scalability. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

CLR/.NET Framework Integration

With the release of SQL Server 2005, database programmers can now take full advantage of modern programming languages to implement functionality within the server as well as many assemblies in the Microsoft .NET Framework class library.

Online Operations (Index Operations and Restore)

The ability to create, rebuild, or drop an index online is an enhanced feature of SQL Server 2005 that augments the indexing capabilities of earlier versions of SQL Server. The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution.

Fast Recovery

SQL Server 2005 improves the availability of SQL Server databases with a new and faster recovery option.

Database Mirroring

Database mirroring allows continuous streaming of the transaction log from a source server to a single destination server. In the event of a failure of the primary system, applications can immediately reconnect to the database on the secondary server. The secondary instance detects failure of the primary server within seconds and accepts database connections immediately. Database mirroring works on standard server hardware and requires no special storage or controllers. Figure 1 shows the basic configuration of database mirroring.

Availability

Investments in high-availability technologies, additional backup and restore capabilities, and replication enhancements enable enterprises to build and deploy highly available applications. Innovative high-availability features such as database mirroring, failover clustering, database snapshots, and enhanced online operations will minimize downtime and help to ensure that critical enterprise systems remain accessible.

SQL Management Objects

SQL Management Objects (SMO) is a new set of programming objects that exposes all of the management functionality of the SQL Server database. In fact, Management Studio was built with SQL Management Objects. SMO is implemented as a Microsoft .NET Framework assembly.

NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.

Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.

With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.

With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.

SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs)

Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.

Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.

Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.

Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.

Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.

SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.

SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.

Encryption

There are several encryption options; the first is called Transparent
Data Encryption. In SQL Server 2008, the entire database can be encrypted by
the SQL Engine.

Auditing

In addition to the standard auditing of logon /
logoffs and permission changes, SQL Server 2008 allows for monitoring of data changes
or access.

SQL 2000 Support Ends

Mainstream Support for SQL 2000 is coming to an end.

Data Compression

Usually, data compression is associated with general hard disk savings,
and with smaller physical files, backup times are reduced. While this holds
true for SQL Server Data Compression, the main goal is Fact Table size
reduction.

Resource Governor

New in SQL Server 2008 is the Resource
Governor. The Governor is used to restrict users or groups of users from
consuming high levels of resources. Items that can be monitored include CPU
bandwidth, timeout waits, execution times, blocking times, and idle times.

Performance Data

The amount of Performance Counters in SQL
Server 2008 has been expanded compared to earlier versions. IO and memory
usage counters are just a couple of the items that can be collected to monitor
performance. The data collected by the counters is stored in a centralized
data warehouse. Microsoft states that running the default set of performance
related monitors will consume less than 5% of CPU and memory resources.

There is also now a Performance Dashboard
tool that can read saved performance data. In addition, historical and
baseline comparisons can be made and used to create action triggers. For
example, if memory use exceeds a threshold for more than five minutes, a more
detailed data collection can be automatically triggered.

New in SQL Server 2008 is the Performance
Studio. The Studio is a collection of performance tools. Together they can be
used for monitoring, troubleshooting, tuning and reporting. The Data Collector
component of the studio is configurable and low overhead. It supports several
colleting methods including TSQL queries, SQL Trace, and Perfmon Counters.
Data can also be collected programmatically. Once data is collected, there are
drill-down and aggregate reporting options. Microsoft lists these six client
side features of the Performance Studio:

• 
  SQL
  Server dashboard



• 
  Performance
  monitoring



• 
  Current
  and historical data analysis



• 
  Suggestions
  for potential performance tuning



• 
  Data
  collection sets-based reports



• 
  MDW-based
  reports

LINQ

LINQ provides a standard development syntax for accessing data,
regardless of where the data resides. For example, the same syntax can access
either SQL Server or XML data. LINQ is used rather than TSQL inside the
application language, such as C# or VB.

Data Synchronizing Features

The combination of SQL 2008, Visual Studio, and ADO.NET bring together
new methods of creating synchronizing or frequently disconnected applications,
making it easier to create client applications that synchronize with a central
database. SQL 2005 started by providing support for change tracking by using
triggers. SQL 2008 synchronizing is better integrated and optimized.

• 
  Data Integration
  Features such as the MERGE statement, Parallelism, SSIS multiple processor
  improvements, and look up performance improvements.



• 
  Analysis Service
  Improvements including BI Stack performance, Scale out analysis, Block
  computations and Perspectives.



• 
  Microsoft Office
  2007 Integration such as Exporting Reporting Service reports as Word docs, SSRS
  format and font improvements, and the Office Tool Bar.

• 
  Transparent Data
  Encryption allows for an entire database, all tables and data, to be encrypted
  on the fly without application programming.



• 
  Backups can be
  encrypted to prevent data disclosure or tampering.



• 
  Data changes and
  access can now be audited.



• 
  Fact Tables can
  be compressed for performance benefits.



• 
  The Resource
  Governor can prevent runaway resource usage.



• 
  SQL 2008
  supports Hot Plug CPU.



• 
  Performance
  Counters have been greatly expanded.



• 
  Installation has
  been simplified.

Reporting Server

Memory management in SQL Server 2008 Reporting
Service is improved. So running large reports will not consume all available
memory. In addition, report rendering has more consistency than before.

Full Text Search

There are Full Text Search changes in SQL Server 2008
including native indexes, thesaurus files stored as metadata, and the ability
to perform a Backup.

Table Value Parameters

In
previous versions of SQL Server, there wasn’t a native way to pass a table to a
stored procedure. The usual workaround was to pass a large varchar or XML type
and parse through it. Now, in SQL Server 2008, Table Parameters are
available. The following provides a simple example of passing a table into a
Stored Procedure.

CREATE TYPE PartType 
AS Table (PartID varchar(50), Descr varchar(100), createdate datetime);


CREATE PROCEDURE AddPart(@PartList PartType READONLY)
AS
SELECT * FROM @PartList 


DECLARE @PartTable PartType;
INSERT INTO @PartTable values('Part1', N'Table Test', '2007-08-20');
EXEC AddPart @PartTable

Dynamic Development

SQL 2008 leverages the new Dot Net Framework 3.0 with LINQ (Language
Integrated Query). In addition, there is more efficient support for Business Data
Entities along with data synchronization options. Also, there are new ADO and Visual Studio
development options.

Key Features

• Support of SQL Server 2008
• Support of Unicode data
• Rapid database management and navigation
• Easy management of all SQL Server objects
• Advanced data manipulation tools
• Effective security management
• Excellent visual and text tools for query building
• Impressive data export and import capabilities
• Visual Database Designer to handle database structure in a few clicks
• Easy-to-use wizards performing SQL Server administrative tasks
• Powerful tools to make your work with SQL Server as easy as it can be
• Report designer with clear in use report construction wizard
• New state-of-the-art graphical user interface
• More Features