SQL Server 2005 – Top 10 Features

Top 10 Features for Database Administration



Database Mirroring

Extend log shipping capabilities with the database mirroring solution. You will be able to use database mirroring to enhance availability of your SQL Server systems by setting up automatic failover to a standby server.


Online Restore

With SQL Server 2005, database administrators are able to perform a restore operation while an instance of SQL Server is running. Online restore improves the availability of SQL Server because only the data being restored is unavailable; the rest of the database remains online and available.


Online Indexing Operations

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. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data.


Fast Recovery

A new faster recovery option improves availability of SQL Server databases. Administrators can reconnect to a recovering database after the transaction log has been rolled forward.


Standards-based Information Access

Any object, data source, or business intelligence component can be exposed using standards-based protocols such as SOAP and HTTP—eliminating the need for a middle-tier listener, such as IIS, to access a Web services interface that is exposed by SQL Server 2005.

SQL Server Management Studio

SQL Server 2005 includes SQL Server Management Studio, a new integrated suite of management tools with the functionality to develop, deploy, and troubleshoot SQL Server databases, as well as enhancements to previous functionality.

Dedicated Administrator Connection

SQL Server 2005 provides a dedicated administrator connection that administrators can use to access a running server even if the server is locked or otherwise unavailable. This capability enables administrators to troubleshoot problems on a server by executing diagnostic functions or Transact-SQL statements.


Snapshot Isolation

Snapshot Isolation (SI) level is provided at the database level. With SI, users can access the last committed row using a transitionally consistent view of the database. This capability provides greater scalability.


Data Partitioning

Data partitioning is enhanced with native table and index partitioning that enables efficient manageability of large tables and indexes.


Replication Enhancements

For distributed databases, SQL Server 2005 provides comprehensive schema change (DDL) replication, next-generation monitoring capabilities, built in replication from Oracle to SQL Server, merge replication over https, and significant merge replication scalability and performance improvements. Additionally, the peer-to-peer transactional replication feature improves support for data scale out using replication.

ULTRAEDIT application – Very Useful for editing text files

Probably one of the best apps out there for editing text files, and being able to do automated actions against files. Versatile and easy to use, UltraEdit is the ideal text, hex, XML, HTML, PHP, Java, Javascript, Perl, and programmer’s editor.


UltraEdit Support


Download User Guide


ULTRAEDIT – Clear Open Files Macro

Good way to clear down all open files and make them empty.  This is useful when cleaning files taken for a replication snapshot from SQL to Oracle (as the idx, sch and pre files are not needed)

Create a new macro in UltraEdit and modify it to contain the following commands:

PlayMacro 1 "ClearOpenFiles"
PlayMacro 1 "ClearOpenFiles"
Find MatchCase "MacroFirstFileMarker"

Save the Macro.

Next time a cleanup of files is needed, load the files into Ultraedit and run the macro. It will loop through all the files until it gets back to the first one.

Granting Permissions to SSIS – ‘Access is Denied’ Error

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

  To configure rights for remote users on Windows Server 2003 or Windows XP

  1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

  2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

  3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

  4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

  5. Right-click on MsDtsServer and select Properties.

  6. In the MsDtsServer Properties dialog box, select the Security tab.

  7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

  8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

  9. Click OK to close the dialog box.

  10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

  11. Close the MMC snap-in.

  12. Restart the Integration Services service.

  To configure rights for remote users on Windows 2000 with the latest service packs

  1. Run dcomcnfg.exe at the command prompt.

  2. On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.

  3. Select the Security page.

  4. Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access – Access permissions include local and remote access, and Launch permissions include local and remote launch.

  5. Close the dialog boxes and dcomcnfg.exe.

  6. Restart the Integration Services service.

Delegation Is Not Supported

SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.

Execute Permission denied error


Grant the dts_operator role on the MSDB database to resolve.