Database Engine Manageability Enhancements
Manageability of the Microsoft SQL Server 2005 Database Engine is simplified with dynamic instance configuration, including dynamic AWE memory management and dynamic I/O and CPU affinity. Security is enhanced with features that include having all permissions grantable and the separation of users and schemas.
Manageability Enhancements
Security: All Permissions Grantable
Security administration is unified by making all permissions grantable by the GRANT statement. In earlier versions of SQL Server, permissions were managed using a mix of instance-level permissions, database roles, and ordinary permissions.
For more information, see Permissions Hierarchy.
Security: Separation of Users and Schemas
The roles of schemas are enhanced in SQL Server databases. All objects in a database are now located in schemas and are not owned by individual users. Each schema can be owned by roles, allowing multiple users to administer database objects. In earlier versions of SQL Server, users could not be dropped from a database without having to reassign the ownership of each individual object that they owned. Now ownership only has to be adjusted for the schema, not each object.
For more information, see Permissions Hierarchy.
Administration: Table and Index Partitioning
Tables and indexes can be divided into partitions based on value ranges. Partitioning makes large tables and indexes more manageable by allowing you to focus management tasks on individual partitions rather than entire collections. Blocks of data can be transferred within the database schema more efficiently, and maintenance operations can be performed against single partitions instead of an entire table or index while maintaining the integrity of the entire unit.
For more information, see Partitioned Tables and Indexes.
Administration: Dynamic AWE Memory Management
The Database Engine now dynamically manages the size of its memory pool when using AWE memory to support large databases. In earlier versions of SQL Server, the memory pool was static when using AWE memory.
For more information, see Managing Memory for Large Databases.
Administration: ATTACH_REBUILD_LOG
The ATTACH_REBUILD_LOG clause enables attaching a database without requiring all of the log files. For example, when detaching a database from a production server for use as a read-only database on a reporting server, the read-only environment will not require all of the log files used in production. ATTACH_REBUILD_LOG lets you copy the database to the reporting server without having to copy over all of the production log files.
For more information, see CREATE DATABASE (Transact-SQL).
Administration: Instant File Initialization
When the Database Engine creates a new database, it can just reserve the space required for the data files and immediately make the database available for use. The actual data pages are not initialized with binary zeroes until the pages are referenced by SQL queries. In earlier versions of SQL Server, a database was not available for use until all the data pages had been initialized with binary zeroes.
For more information, see Creating a Database (Database Engine).
Administration: Disabling Indexes
Indexes can be disabled to aid in administration and diagnostics. When rebuilding a non-clustered index, disabling the index before rebuilding the index significantly reduces the amount of disk space required.
For more information, see Disabling Indexes.
Administration: Reorganizing and Rebuilding Indexes
SQL Server 2005 introduces the ALTER INDEX statement that can reorganize or rebuild indexes or set index options. Indexes can be rebuilt online, pages containing LOB data can be compacted, and partitioned indexes can be reorganized or rebuilt on a single partition basis.
For more information, see ALTER INDEX (Transact-SQL).
Administration: Bulk Copy and Load Format Files in XML
SQL Server 2005 introduces an XML schema that can be used to specify format files as XML documents. As an alternative to using the original non-XML format files, XML format files offer several advantages.
For more information, see Understanding XML Format Files.
Administration: OPENROWSET Bulk Rowset Provider
SQL Server 2005 introduces bulk rowset provider accessed by using the new BULK option of the OPENROWSET function in a SELECT statement, which can then feed into INSERT statements.
For more information, see OPENROWSET (Transact-SQL) and Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).
Administration: Database Mirroring Monitoring
SQL Server 2005 SP1 introduces Database Mirroring Monitor and a set of system stored procedures for monitoring mirrored databases and for setting event thresholds on several key mirroring performance metrics.
For more information, see Monitoring Database Mirroring.
See Also
Concepts
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|