Mathom Solutions Blog-IT

If you remeber from the previous post Speakng the Language of Managment, we needed a write-up to give to Senior Management to get our software approved. Here is a sample that I did for a small purchase for SQL Trace Analyzer that we wanted to better help manage, report and understand the profiler traces.

------------------------------ 

This is the software that we have been talking about and would like to purchase. 

http://www.sqlsolutions.com/tadownload/SQLOnlinePayment/Step4.aspx?sSelected=15,1;36&sProducts=1&sLicenses=15,1

This software allows me to take the trace information that I gather from SQL and from the Performance Monitor on the SQL Server and map them over each other to give us more insight into the performance and troubleshooting of our SQL Servers. 

Problem:

From time to time, the performance of the SQL Servers need to be investigated and, as such, I encounter three major problems:

1. Correctly, rapidly and accurately report/analyze SQL Profiler statistics for clients that are experiencing performance issues that are impacting our contracted SLA terms. I not only manage Product Y's systems but also the SQL Server systems for Product Z, Product X and Product B's ASP versions so I have to do this for various teams and clients with differing SLA's and often under a situation where the client is not happy.   (Impacts multiple clients and SLA's)
2. Correctly and accurately report/analyze SQL Profiler statistics for Performance Reports that will be given out to clients who ask about this in their RFP and RFI process (this would be used in conjunction with a report from the Web load tool). (Impacts future business)
   
3. Correctly and accurately report/analyze SQL Profiler statistics for the ongoing tuning of new development applications (Impacts Accuracy and Responsiveness). 
   

Current Process:

Currently, I have been using a free tool called ClearTrace that just aggregates the data.   As well, I have also loaded the data into my own structures but this is quite time intensive and probably not the best use of my time to develop a new tool.   Finally, I have even tried the Microsoft software called Database Tuning Advisor (DTA) which comes with SQL Server 2005.   

Problems with Current Process:

1. The ClearTrace tool does not allow graphing or historical data so I can't see a before the change and after the change. 
   
2. The ClearTrace tool has limitations in the number of profiler entries it can process.  It can process about 3 million records and it crashes. 
   
3. The ClearTrace tool is unsupported software.
   
4. The manual process is time consuming and not user friendly. 
   
5. The time it takes to develop a new tool internally is time taken aware from customer focused tasks and is time not spent on the customer service hierarchy. 
   
6. The DTA does not provide any statistics, rather it only provides recommendations.  So I am not able to see how many time a process is called or a larger global perspective of the system, you just have to trust Microsoft.  As well, the DTA does not work well for processes that use temp tables and when stored procedures call other stored procedures (nesting)—both of which are common with the Product Y's application.   

1. Provide internal teams with a timely list of prioritized tasks for optimization when a client is experiencing performance issues.
   
2. Provide internal teams with an easy to use step in the development process to be proactive and eliminate potential bottlenecks in future versions.
   
3. Create a performance capabilities document to justify sales claims and to provide to many large clients that our systems can handle large quantities of concurrent users without performance issues for their projects that have short timeframes.      
   
4. Collect performance data to help better understand and plan capacity for growth.  

Test Plan:

I downloaded a trial version of the software and ran the same profiler files through the software application.  I was able to run a smaller sample of Project X's profiler output in about 30 minutes as compared to the ClearTrace run which took about 2 hours to process and manually graph.

ROI/Savings:

Software will cost $495 + annual maintenance of $49.

For the past several months I have done this formally three times for Client A (because of contract requirements) and once for Client S, and numerous times for Product B's ASP clients (listed the various clients but removed for confidentiality) for client performance relate issues and will be doing it formally in the next few months for approximately 10 additional clients.  Finally, we will be adding a performance analysis step as part of the monthly Product Y's monthly software release. 

The formal tests for Client A took 9 hours for the first two test and 6 hours for the last test and and based on the test case, should take about 2 hours with the new software.  I would also estimate a time of about 5 hours for each of the upcoming formal tests.  So just focusing on Client A, we have 24 total hours of time versus and estimate of 6 with the new software.  Based on my hourly rate at $35 that would compute to a savings of around $630.00.

The one off client performance traces take anywhere from 3 to 6 hours depending on the severity and urgency and up to 2 hours on the high side with the new software.  While the variability of time estimate for this scenario is difficult, it would follow that a similar reduction in hours as experienced with the formal tests. 

The upcoming process change, I estimate it will take about 3-6 hours per run under the current process and this will be done monthly and about 1.5 hours each month with the new software.  So again, we should see a similar reduction in the number of hours as experienced with the formal tests.  Since we plan on doing monthly releases, the old process would take a total of 36 to 72 hours or cost about $1260 to $2520 and under the new process cost about $258 in time and generate a savings of $1000 to $2268. 

While the total savings may not appear great in the grand scheme of things it has an ROI ranging from 329% to 585% in the first year.  It is a savings and the more expensive resource can be spent on other more important tasks. 

Thanks and let me know if you need any additional information/help in getting this processed. 

--------------------

So there you have it.  I start off with a short request, explain the business problem, explain the current process and the problems with it,  set the goals that this purchase will accomplish, explain the test plan and finally show the savings from this.  

Good luck and hope this helps you get that software you need! 

Next up, I will show you a template that I have been using to do an indepth analysis for upgrading to SQL Server 2008.  

1. Talk in terms of company benefits. Don't go into detail about how a purchase will save you time and effort. Instead, focus on how it will save the company money. Talk to a few end-users to develop some figures as to how much money you can save.
2. Use the right financials and verifiable numbers. Too many requests talk about "payback," when management wants to know about NPV. They want to know this, because NPV helps them maintain cash flow--the lifeblood of the company. Requests that take guesses at the financial value of the proposed purchase often come out with compelling math as to the value of the purchase. Unfortunately, the numbers behind the math don't add up and that jeopardizes the whole proposal--and your credibility.
3. Identify, and push, hot buttons. What is most important to your managers and others who will approve or reject your request?  While I don’t like acronyms or buzz words, it may help get your project the sight it needs. 
4. Capitalize on trends. Know what is coming up in your company's operations, offerings, and other areas that may affect your request. To find out, just ask the key people in those areas. For example, you may find out if Project X is coming out with a new version. If so, then think of how the people involved in these things might need and what the benefits of the proposed purchase mean to them.  Basically look around the entire company and see where this can benefit more than just you. 
5. Write tight and in the active voice. In the body of your proposal eliminate the wordiness, repetition, adjectives, and fluff. The more succinct your writing, the more power it has and the more likely folks will read and understand it (they are just as busy as you).  Using the active voice increases understanding and retention of the topic and it shows confidence.  For more information see http://www.mindconnection.com/library/writing/activevoice.htm
6. Eliminate judgment statements. Present the information objectively and you imply the reader is intelligent enough to understand it.  Making claims that something is "essential," "imperative," or "a no brainer," and you imply the reader is stupid. You want them on your side...don't alienate them.
7. Use the recommended format. If your company doesn't have a standard format, ask someone in accounting who handles these for a couple of funding requests that succeeded, plus suggestions on format. While you're at it, ask for any other advice that might be helpful--what you gain from his/her experience just might surprise you. Be thankful and show your team spirit and you will create a good ally.
8. Use an executive summary. This is a one-page cover sheet that provides a "thumbnail" of your proposal. It should tell the purpose, have a paragraph about the benefits, and give the key financial parameters (IRR and NPV). (NOTE:  This is dependent on the size of the request.  If you are asking for a $1000 piece of software, it may only need to be a paragraph or so.)

As I am sure you are all aware, database hacking is happening--afterall it's where the crown jewel--you data--are located.  I came across this article today and it was very helpful in terms of how a hacker could get through a system with as little footprint as possible and how to think about protecting your systems. 

Cesar Cerrudo is a Security Consultant and Lead Research for Application Security Inc and he presented recently at Black Hat 2009.  His paper was on SQL Server Anti-Forensics: Techniques and Countermeasures.  The paper talks about anti-forensics (which is about how a hacker covers/removes traces of their activity) and the counter measures you can take to help reduce or mitigate the chances of them reducing their tracks when they have invaded your system. 

This is a must read and make sure to check out his other articles!

I ran across a couple of good links for free e-books about Microsoft SQL Server and figured I should share them.  

Introducing SQL Server 2008 by Peter Debetta, Greg Low, and Mark Whitehorn.  You can learn about the new features SQL Server 2008 including Security and Administration, Performance, Type System, Programability, Storage, High Availability, and Business Intelligence.  

Brad's Sure Guide to SQL Server 2008 by Brad McGehee.   This is about SQL Server 2008 and also includes two of his other books:  How to become an exceptional DBA and DBA Best Practices.  

Mastering SQL Server Profiler by Brad McGehee.  As its name implies, learning all about SQL Server Profiler (2005) and how it can help you make your systems faster.  

Dissecting SQL Server Execution Plans by Grant Fritchey.  Hey, this is an awesome book that talks about how you can read the excution plans and make your system go faster....hey who wouldn't want that?

Hopefully you will find these helpful.   

Also, if you know of any others, please let me know.

Some one asked the other day about source control for SQL Server.  I groaned as I knew what was coming next.  Don't get me wrong, I believe in source code control as it has saved my rear-end many times.  However, none of the tools do exactly what I want and nothing is seamlessly integrated within SQL Server.  Anyhow, here is a list that I have from my research (in no particular order).

SQLVSS is integration solution for SQL Version Control, it scripts database objects into Visual Source Safe, keeps records of different versions of database throughout the development process without storing a copy of the entire database each time. it simplified troubleshooting process by knowing exactly what has changed, and has the ability to roll back changes to prior version of the objects.

DB Ghost tools, in conjunction with any source control system, provide a true change management solution for SQL Server 2000, 2005 and 2008 code. It guarantees a full audit trail and complete database integrity by building and promoting databases directly from a source control system whilst preserving data in the target databases.

Embarcadero Change Manager offers database administrators and developers a powerful set of tools to simplify and automate the database change management lifecycle.

SQL Compare is the industry-standard tool for comparing and synchronizing SQL Server database schemas. 

Change Director for SQL Server provides a powerful automated solution for managing and tracking schema changes to SQL Server databases.

Randolph is a new and unique solution that revolutionizes the way version control and change management is done for SQL Server. It shifts the responsibility for versioning from the users to the software.

SQL Source Control 2003 enables source control of code and documentation of your databases. Full source control and documentation versioning support is possible through integration with Visal Source Safe.

SQLSourceSafe Integrates with both SQL Server and Visual SourceSafe, providing an effective and effortless SQL code version control management system for individuals and project teams to manage SQL Server database objects. It also provides a development platform for developers to work on and execute their SQL objects and scripts.

Good luck and I hope you find something that meets your needs. Oh and if you have any others to add, just us the contact page above and let me know!

Ok, not really but I hate doing things, tedious things, over and over and over and over and over....well you get the picture.  Do you have a procedure or a template of T-SQL that you use all the time?  I do and I have tried SSMS Projects and/or simple SQL files. They worked but just were never very easy as I always had to do File, Open, navigate to the folder, open the file....and so on.  So there has to be a better way.  Well luckily there is.  Within SQL Server Management Studio you can customize shortcut keys. 

How?  Start SSMS and go to Tools, Options,  select the Environment tree node, and then Keyboard and you should see this screen. (same steps for SSMS 2005 and 2008)

Now you can modify the shortcuts to have it run your own stored procedures.  

I am always on the lookout for new tools and when they are free...you can't really complain!

Here are some new ones that are rather interesting so make sure and check them out!

SQL Server 2005 Express Profiler  is a free profiler for the Express Edition of SQL Server.

SQL Scripter is a data export and scripting utility for MS SQL Server.

SSMS Tool Pak  is an Add-In that gives the Management Studio some neat features such as CRUD generation and a whole host of neat little featuers that MS left out.

Sentrigo Hedgehog Standard is a free host-based software solution for real-time database monitoring, auditing and breach prevention.

Red-Gate Log Shipping Monitor is a free tool for collating and summarising SQL Server Log Shipping activity.

I also have a list of several others over at my main website so stop over there and see the full list. 

I have been doing a lot of data migration and disaster recovery synchronization lately and had a need to run DOS commands quite frequently.  It was a pain to switch from SSMS to the command window or prompt and then run the DOS command or even more interesting the batch files I just exported.  So I found a way to run it from within SSMS.  

Type in your command and make sure to prefix it with two exclamation points !!  Then click the SQLCMD button on the tool bar and then hit Execute (F5) and voila, it runs! 

I have been busy building the business and really haven't had time to blog but now that the family is visiting relatives in Russia, I had some time to convert my blog to blogengine.net.

It was very easy to convert my site and I am really impressed with the features, flexible and documentation for this free application!  Kudos to the team that wrote this!