In an earlier blog I talked about the difference in mentality between the typical developer and the typical DBA. One of the characteristics of the DBA is the distrust of the Microsoft Wizards. “Give me the command-line and the keyboard and I will be fine. Forget the mouse and I just don’t trust those Wizards”. Microsoft has gone a long way to change that perception with some productive management tools and Wizards that are better in SQL Server 2008. But what if you really do want to find out what those wizards are doing under the hood? Let’s see…
In my last blog I talked about the Database Mirroring Security Configuration Wizard. This is a good example. It sets up the endpoints and security necessary for a Database Mirroring partnership and appears to make everything work as if by magic. That’s great when it works. But as I discussed, when things go wrong you are left in the dark. Black Magic, I suppose. First of all, there is no “undo” option so you cannot go back to before you tried to setup Database Mirroring. So you would have to know which objects were created and know the syntax to go ahead and drop those objects.
Enter the SQL Profiler. This monitoring tool is designed to trace any activity that occurs on a SQL Server database server. It is typically used to monitor performance and provide troubleshooting evidence during poor performance or error conditions. You can trace all activity on a SQL Server, or you can filter by user, by application, by database or whatever you want. The tool has been around for a long time to trace SQL queries on a Database Engine but since 2005 we have been able to trace MDX queries on an Analysis Server too. You can also replay a trace so that you can perform accurate “before and after” analysis of performance. You can capture a trace to a file or to a relational table depending on how much trace information you will be capturing.
This is great for monitoring your own applications or maybe for monitoring that 3rd party package that you have no source code for. It will even trace Stored Procedure and Trigger code if your application uses it. But it also can be used to find out what the management tools are doing under the hood, like SQL Server Management Studio and its Wizards.
When I was testing the Database Mirroring Security Configuration Wizard (and not getting the partnership to start), I turned on a trace just before starting the wizard and watched exactly what Transact-SQL was being submitted to the server on my behalf. I could see the exact CREATE ENDPOINT statements and the exact ALTER DATABASE statements on Principal, Mirror and Witness. (I had three traces running concurrently, one per SQL Server). Then when I got some advice from Sweden, the solution showed exactly what was missing. A couple CREATE LOGIN statements and a few GRANT CONNECT statements provided by the wizard and all was well. If I choose, I can then proceed to use the Transact-SQL captured to develop my own scripts for automation.
But then again, maybe I should have started with a script in the first place. I guess I am just a DBA at heart after all.
Cheers
Brian
Recent Posts:
