Last month, in "SQL Server Profiler: For Developers, Too" (http://www.winnetmag.com/article/articleid/43268/43268.html ), I encouraged you to integrate SQL Server Profiler into your unit-testing process. IT professionals typically think of Profiler as a DBA tool, but in my article, I outlined many benefits to training developers how to use Profiler effectively. I got a lot of positive feedback about that commentary, but I also received some messages such as the following:
"I enjoyed your article about granting developers access to run Profiler during development, but I'm a DBA who holds back sa authority, not only in the production environment, but also in development and quality assurance. I understand that sa permissions are required to run Profiler, but that requirement is what keeps Profiler out of our development process. Do you think Microsoft's SQL Server developers can, or will, do anything about requiring sa permissions for Profiler? Could they create another database or server role that runs only Profiler? I will pass your article along to my supervisor, but his view is, "If developers feel the need to run Profiler, then the DBAs should run it for them." I think you'll understand what the consequences would be if every developer suddenly had the need to trace something."
I agree that Microsoft made a mistake in requiring sa rights to run Profiler. Fortunately, the company will address the problem in SQL Server 2005 by making it possible for users who don't have sa rights to run the tool. However, Microsoft has no plans to address the problem for SQL Server 2000 users, which doesn't help us much today. SQL Server 2005 is still the better part of a year from release, and many of you will probably continue to run SQL Server 2000 for years after SQL Server 2005's release.
So, let's revisit the earlier reader comment. Development shops have many reasons to withhold sa access from everyone but a few, select DBAs. Assume that we all agree about the benefits of letting developers run Profiler, but you're unwilling to grant your developers sa access for the sole purpose of running Profiler. What are your options if you want developers to integrate Profiler into their unit testing and development? Grant developers sa permissions for SQL Server instances installed on their local machines. A lot of unit testing can easily be done on local instances of SQL Server. It's also reasonable to install "safe for developer" instances of SQL Server on development and testing machines. Applying backups (perhaps with log shipping) to the developer-safe machines wouldn't be difficult, and you could grant developers full sa rights to these boxes. These machines would never be the system of record for any live code, so there's minimal risk involved in granting developers sa rights on these instances.
This technique involves some administrative support, and the extra instances can cost additional license fees. But Profiler is too important to arbitrarily deny developers the rights to use it in a safe and reasonable manner. Our reader says that management requires developers to ask a DBA to run a trace for them, but realistically, that model doesn't scale. I firmly believe that the costs and risks involved in not using Profiler far outweigh the risks of granting developers sa rights in a controlled environment.