A list of the new features and improvements that are included in SQL Server 2005 Service Pack 1 (916940)

MORE INFORMATION

Analysis Services

• Fixes have been included that improve the performance and stability of queries.
• HTTP connectivity will now support HTTP compression.
• Optimized queries to perspectives are now as fast as queries to the perspective's base cube.

Data programmability

• Client connection time has been significantly improved if failover occurs while the server is using Database Mirroring and the clients are using TCP/IP.

SQL Server 2005 Integration Services (SSIS)

• Usability of the Import/Export Wizard has been improved in multiple-table scenarios.
• The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.
• Interoperability with Analysis Services has been improved. The DataReader source in SSIS supports the System.Object data type by converting columns that have this data type to the DT_NTEXT ssISnoversion data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation.
• Performance has been improved for many transformations, such as the Sort transformation.
• Designer usability has been improved. For example, you can now right-click Dataflow and then click Execute Task to execute only the Dataflow task. You do not have to switch to the control flow to execute only the Dataflow task.
• The Expression Builder dialog box now has a public and documented API. This increased access to expression-related objects will benefit task developers because expressions are important to the way that tasks are interrelated.
• We have added a registry-based policy for handling SSIS package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings.
• In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation.
• In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations.
• To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window.
• You can now add annotations to precedence constraints.

Manageability

Profiler and Replay

• Previously, Profiling Analysis Services showed the time in Coordinated Universal Time (UTC). The time now appears in the user's local time.
• Previously, events that successfully replayed were not being counted correctly. Therefore, incorrect replay statistics were reported to the user. This problem has been fixed.
  
  Note Users may see a lower percentage of replayed events than the percentage of replayed events that is reported in the release version of SQL Server 2005. However, the number of replayed events is correct. You can use this number when you compare replay in SQL Server 2005 with replay in SQL Server 2000.
• Previously, Replay ignored the network packet size that you set when you connected to the network. Instead, Replay always created a connection that had a packet size of 32 kilobytes (KB). Sometimes, large playbacks would cause the server to run out of memory. This problem has been fixed.

SQL Server Agent

• A registry switch has been added that you can use to control encryption between a master server and a target server (MSX-TSX).
• In a master server/target server environment, the SQL Server Agent service that is running under the NetworkService account can be run from either a master server or a target server.
• Database Mail will now run on 64-bit versions of SQL Server 2005 and also on 64-bit operating systems.
• Previously, Agent XML for Analysis (XMLA) jobs over 32 KB were being truncated. This problem has been fixed. The jobs now run correctly.

SQL Server Management Objects (SMO)

• Reliability has been improved in the SMO scripting and transfer features.
• Previously, SMO may have produced incorrect Transact-SQL statements in some scenarios. Additionally, SMO may not have transferred a database in some scenarios. These problems have been fixed.

Maintenance plans

• The maintenance plan feature works when you have either SQL Server Tools or SQL Server 2005 Integration Services (SSIS) installed. In the release version, you had to have installed both of these components.
• You can now use cleanup tasks in a maintenance plan to delete backup files from subdirectories.

SQL Server Management Studio (SSMS)

• You can now install database diagram support objects on databases in SQL Server 2000 Compatibility mode.

Replication

• You can now use database mirroring failover together with replication.
• A new Database Mirroring monitor has been added to SQL Server Management Studio. To start this monitor, right-click a database node, point to Tasks, and then click Launch Database Mirroring Monitor.
• For tables that use cascading referential integrity constraints, such as the ON DELETE CASCADE clause and the ON UPDATE CASCADE clause, SQL Server replication now supports merge operations on precomputed partitions.
• Usability has been improved in the user interface for SQL Server replication.

Reporting Services

New features

• You can now add static images, such as company logos or other graphics, to reports in Report Builder.
• A new data processing extension provides a graphical query designer that you can use to access InfoCubes and queries in Reporting Services reports for SAP NetWeaver Business Intelligence.

Key fixes

• Many bugs have been fixed in the integration of Analysis Services in Report Designer. For example, the report wizard now works correctly with Analysis Services data sources. Additionally, the automated parameter-generation behavior has been improved.
• Large databases no longer cause auto-generation problems for the Report Model Wizard in the Report Model Designer.
• Complex parameter declarations should no longer cause parsing problems in Report Designer.

SQL Server Database Engine

• Database mirroring has been enabled in SQL Server 2005 SP1. Database mirroring is also supported for production deployments. The trace flag for testing database mirroring (t-1400) has been deprecated. Therefore, this flag has no effect in SP1.
• A new Database Mirroring monitor has been added to SQL Server Management Studio.
• Scaling improvements for Profiler let you trace large data volumes on large computers.
• XSD support has been added for some schemas that use non-deterministic content models.
• A new system user-defined function has been added. This function lets you see which OpenXML handles are open.
• Several performance problems were fixed in the Database Engine query optimizer. These changes improve the upgrade process for third-party products that use SQL Server as their back-end database when you upgrade from SQL Server 2000 to SQL Server 2005.
• Support has been added that lets you create unique non-clustered indexes online.
• New Dynamic Management Views (DMVs) have been added to let you monitor Query Execution memory grant status.
• Database Console Commands (DBCC) are now integrated with the Watson application. If a DBCC check detects that a database has become corrupted, the Watson application uploads that error report to Microsoft.
  
  Note For more information about the data reporting and collections process, visit the following Microsoft Web site:

  http://oca.microsoft.com/en/welcome.aspx

  For information about the Microsoft data collection policy for these dialog boxes and about steps that Microsoft takes to help protect privacy, visit the following Microsoft Web site:

  http://oca.microsoft.com/en/dcp20.asp

• Additionally, changes have been made to improve the following:
  • Performance when you compile an XQuery that contains complex XML Schemas
  • Performance when you update XML by using XML Data Modification Language (XML DML)
  • Performance in the dm_exec_query_plan DMV
  • Performance in floating point arithmetic operations
  • Benchmark performance
  • Targeted statistics and cardinality
  • Debugging when you debug a SQL common language runtime (SQLCLR) program that uses parallel threads
  • How the SQL Server Database Engine handles parallelized user-defined functions, aggregates, and user-defined type (UDT) methods
  • TPC-H performance

SQL Server 2005 Express Edition

• SQL Server 2005 Express Edition with Advanced Services is a new downloadable package that contains everything that was available previously in SQL Server 2005 Express Edition, together with the new management tool, SQL Server 2005 Management Studio Express. Additionally, this package includes Full Text Search and SQL Server Reporting Services.
  • Full Text Search: You can use this feature to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases or multiple forms of a word or phrase.
  • Reporting Services: This feature includes Report Server and Business Intelligence Development Studio. You can use this feature to create, to modify, and to run reports on local, relational databases.
  • SQL Server 2005 Management Studio Express: This feature is a free tool that you can use to manage and to administer SQL Server Express Edition.
• SQL Server 2005 Express Toolkit is a separate downloadable package that includes Business Intelligence Development Studio. Business Intelligence Development Studio lets you create and modify server-based reports.

SQL Server 2005 Management Studio Express

• Management Studio Express now has integrated help.
• Previously, Management Studio Express would not start after you installed SQL Server Books Online. This problem has been fixed.
• The Effective Permissions dialog box now appears in SQL Server 2005 Management Studio Express.