I've been writing about SQL Server Profiler frequently over the past few weeks, so you're probably aware that I think Profiler is a great tool that's under-utilized by development and DBA teams. Several readers have sent me interesting ideas about integrating Profiler into the development environment, so this week I want to share two of those ideas with all of you.
The first suggestion comes from a reader who is a DBA for a busy eBusiness test-and-production environment in which granting sa privileges to developers isn't permitted because the organization's management team isn't willing to take the risk. The reader writes, "Granting developers sa privileges on their own server, as you suggested, is a valid solution, but it's not cost effective for us. So, I'd like to suggest a compromise that's worked for us. We run a Profiler trace all day, every day, during business hours. Profiler stores the data in a table that anyone can view. This lets everyone see the high CPU/read transactions that occur throughout the day. Of course, events, data columns, and filters for tracing should be decided upon within each environment. Our trace is run in both production and test environments. When a specific stored procedure requires further traces, a developer asks a DBA to start another trace that specifies that stored procedure--also storing the results in a table for easy querying."
In "SQL Server Profiler: For Developers, Too" ( http://www.winnetmag.com/article/articleid/43268/43268.html ), I argued that Profiler should become an integral part of the development and unit-testing process. I still hold that the risks involved with granting developers sa access are eclipsed by the benefits of giving developers access to trace data. However, this reader's solution is a reasonable compromise for shops that can't, or won't, grant sa access to developers.
Another interesting idea comes from a reader who writes, "In my company, we go out of our way to blur the lines between development, administration, and support. All our development leads have full administrative access to both our production and development environments, with the understanding that developers have as much responsibility for the well being of those systems as the administrators. Proper communication and coordination between everyone on the team brings many benefits to the table, including more robust applications, better testing scenarios, quicker problem resolution, improved relationships between development and administration, and a deeper resource pool to pull from when major support problems arise. We also require all developers to directly support any application they develop for 6 weeks after moving the application to production. When you have to clean up your own messes and interact with the users affected by those messes, accountability and responsibility improve. Creating silos of authority only leads to silos of communication, and I would rather risk a developer overstepping his bounds with a production system than consistently rolling out applications that under-perform--or don't perform at all--because a developer lacks knowledge about how users implement the applications they develop."
I love this idea! I completely agree that blurring the lines between traditional support, development, quality assurance, and administration roles is bound to create a better end product. And I suspect that developers become more vigilant when they know they're going to eat their own dog food in production for 6 weeks.
On a different note, in "In the Express Lane with SQL Server" (http://www.winnetmag.com/article/articleid/43234/43234.html ), I told you about SQL Server 2005 Express Technical Preview. Until a product manager at Microsoft pointed it out, I didn't realize that the Express Technical Preview came out 2 weeks before SQL Server 2005 Beta 2 was released to Microsoft Developer Network (MSDN) subscribers. Now, SQL Server 2005 Express Technical Preview is no longer available--there's only Express Beta 2. The bits of the two versions are different, but the Microsoft Web site doesn't spell that out clearly. If you have the SQL Server 2005 Express Technical Preview, you should uninstall, and download Express Beta 2. Microsoft doesn't support a direct upgrade from Technical Preview to Beta 2.