The Scenario
You evaluated SQLClue some time ago and determined the application provided valuable tools that your data center could use to improve reliability and availability. The organization has been automatically archiving server configurations to the SQLClue Repository database for a few months.
Because of past issues with using the SQL Server Profiler in production and the limited amount of time you had available for the evaluation, you decided not to deploy the SQLClue Query Baseline component. Only the SQL Configuration Archive component and the SQL Runbook components are installed. This gives you the tools you need for monitoring DDL and configuration changes on each SQL Server as well as an effective knowledge and information sharing mechanism to help build a common and shared monitoring strategy not to mention a foundation for data center business continuity moving forward.
The SQL Configuration Archive will also serve as a central resource for rollback and recovery of database object scripts. You have been able to show that assemblies, function, stored procedures, triggers and permissions can quickly, easily and reliably be reverted from the archive when necessary. This will be a great time saver for the developers. Now they need only address data and table structural changes when producing the mandatory rollback script for any database development work.
The CTO, being the exceptional leader she is, has recognized the value the archive brings to the business. Recently she had a junior web developer create a page on the Intranet that includes some outputs from an existing SQLClue stored procedure along with data points from the SQL Server Health and History tool (SQLH2) and Network performance data points from a POWERSHELL script provided by the Network Administrator. A new release of SQLClue was deployed to production over night on January 16th. You were in charge of the rollout and were certain that the QA team had completed the established regression testing before the new version was moved to production.
Today, the CTO needed to access the Intranet page while discussing an urgent matter only to discover that the page will not load and returns the error, "System.Data.OleDb.OleDbException: Data type mismatch in criteria expression" to the browser
She comes to you to get the necessary information because you had helped the developer identify the query to use. You are able to run the query the Intranet page calls in SQL Server Management Studio (SSMS) and quickly give her the information she needs. While stepping away to return to her discussion, she thanks you and tells you to make sure that Intranet page is fixed.
The Study
Of course , the first thing you do is try the query on the development integrative test environment. Sure enough, the same error.
Since the query executed without problem for you, you are almost certain that the problem is not in the database, but to be absolutely certain before interrupting the developer you decide to take a minute to crack open the SQL Clue Administrator’s Console to see what happened in the SQLClue database during the release deployment. Eight clicks later you discover the problem that might have been elusive and costly to troubleshoot for the junior developer.
Click 1: You open the SQLClue Administrator’s Console from the SQLClue Host’s program group
SQLClue will let you do an enormous amount of database administrative research and analysis without having to connect to the SQL Instance under consideration. Since you have long been the organizational champion against ad hoc access to production databases, it has helped your cause to show that you are playing by the rules.
Click 2: You open the "Reports" menu
Click 3: Then you open the "Change History by Date" report
Click 4: You expand the SQL Instance the CTO was querying when the problem occurred
As evidenced by the release scheduling document now saved in the SQLRunbook, release deployments on the Intranet occur after 6PM to avoid any disruptions for internal users during the business day. The SQLClue archive process is scheduled to pick up all changes from the queues on each server about 9AM each morning to assure that all changes that occur overnight have visibility to team at the beginning of the day. There are not supposed to be changes during the day. However, in the event you suspect there have been unauthorized changes, the archive can be manually started at any time. You and a few others also have the ability to browse the queue on any target server to see what has changed without kicking off an archive when necessary. In any event, for the purpose of researching this issue, you know that Intranet changes for January 16th will appear in the archive of the 17th.
Click 5: You select the archive date containing the SQLClue release deployment
You immediately get a sinking feeling when you see the changes listed for that day. For some reason there are multiple changes recorded for the stored procedure that you just ran for the CTO. Once the flush leaves your face, you realize you must investigate further.
If only your pulse would slow…
Click 6: You select the highest versions shown for the deployment date
Almost as quickly as the panic set in, you begin to calm as you see that the stored procedure is fairly simple and straight forward. You notice it queries a User Defined Type (UDT). You have always believed that CLR Integration was evil and that UDTs were the most insidious of the seed that could be born from CLR Integration. And it is consistent with the error the CTO received.
Maybe that is it! CLR Integration really is the devil. Back to the depths of despair you slide.
Almost without thinking, you select the option on the report to compare the version shown with the most recent version on the SQL Instance.
Click 7: You compare the version shown with the current (latest) version on the SQL Instance.
What’s this? There is a difference between the stored procedures, but it appears to have nothing at all to do with the UDT. Looks like a DATETIME column…
Click 8: You select the icon that maximizes the SQLClue compare output panels
To give yourself a better view of the difference you use the icon at the bottom of the Compare Tab to collapse the input panels of the form, giving all available window real estate to the output panels. And there it is, the change that is breaking the Intranet page and wasting the CTOs valuable time: a column that used to return a date as a string now returns a date.
You recognize that you cannot change a query shipped with a third party application and as a s the DBA you have no access to the ASP page. As a quick test, you modify the query in the development test environment to return a string like it used. e voilà! The Intranet pages works as designed. You revert the stored procedure you just illegally modified in the integrative test environment – there has to be some privileges to being a DBA - and file a bug report.
(And if you would have forgotten to revert, someone would have been able to find the out of sync database object using SQLClue compare and your name would be all over it.)
To wrap things up, you contact QA and discover that the developer had not completed the necessary steps to get the web page added to the regression test script. Consequently, the web page was not tested during the SQLClue release rollout. You get the page added to the test script and gently let the original developer know by email that you have taken care of this requirement – no interruption necessary.
Thanks to SQLClue, in eight (8) clicks and under three minutes you responded to a high profile business emergency and solved the precipitating business problem. Another job well done! Now off to the kick boxing class to meet the woman of your dreams.