|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
How to: Enable SQL Server 2005 Debugging
Updated: July 2010
This topic applies to:
Pro and Team
Does not apply
Command or commands hidden by default.
If you have the required software, you can enable Transact-SQL debugging by performing these steps. For more information, see Software Requirements.
The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging Transact-SQL code only, no remote setup is required.
To debug remote SQL/CLR code, you must install the Remote Debugging Monitor (msvsmon.exe) on the remote SQL Server 2005 computer. Running the SQL Server setup program with the correct options will install the Transact-SQL debugging components.
Additional setup procedures are required for each SQL Server in Microsoft Visual Studio 2005 and for the database connection that is used in a debugging session. For more information, see How to: Enable SQL Debugging For a Project, How to: Enable CLR Debugging For a Connection, and How to: Enable Multi-tier Debugging.
To enable SQL debugging
Install the Remote Debugging Monitor on the SQL Server 2005 machine. For more information, see How to: Set Up Remote Debugging.
Set up SQL Server permissions for debugging. For more information, see How to: Set SQL Server Permissions for Debugging.
We recommend that you debug Transact-SQL code on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session has ended or the transaction is committed or rolled back.
If the SQL Server version is SQL Server 2000, configure DCOM for SQL debugging on the server for SQL debugging. For more information, see How to: Configure DCOM for SQL Server 2000 Debugging.
If you are debugging on Windows XP Service Pack 2, you must do the following to configure the Internet Connection Firewall to allow remote debugging. For instructions and details, see How to: Set Up Remote Debugging.
On the Visual Studio host machine, you must add Devenv.exe to the Exceptions list and open the TCP 135 port.
On the remote (SQL Server) computer, you must open the TCP 135 port and add sqlservr.exe to the Exceptions list. If your domain policy requires network communication to be performed through IPSec, you must open the UDP 4500 and UDP 500 ports.
When debugging SQL Server 2005, you do not have to manually start the Remote Debugging Monitor (msvsmon). If you have SQL/CLR debugging enabled and step into or hit a breakpoint, the debugger will automatically start it. If you are debuggingSQL Server 2000, see How to: Run the Remote Debugging Monitor for manual startup instructions.