SAFE SQL Technical Briefing: Database Tuning
Today we will briefly discuss some miscellaneous items regarding databases: the heart and soul of all accounting systems. Specifically, we will go over an important tip on improving performance, and ways to monitor how SAFE is being used.

Tune Up, Please?
We have harped on and on about how your database 'learns' how best to serve up data. Well, sometimes it does, and sometimes it doesn't. For some queries, the database needs a bit of hand-tuning for best performance. Big companies keep full-time database administrators on the payroll because they know that such tuning can speed processing many, many times over. But you (or we) can do similar magic yourself in many cases using the built-in Index Tuning Wizard included with SQL Server.

Index Tuning Wizard works by reading a log of all database activities and using that information to optimize the structure of the database for speed, speed, speed. It is run from the Enterprise Manager. There are three main steps:

1. At the beginning of a particular task you wish to improve, open Enterprise Manager, drill down to your Simple Accounting database,  open SQL Profiler, then select 'New Trace'.

(By the way, you could do this first step at the start of the day and simply monitor all activity, but you really don't want to do that, since you will gather so much data that the Wizard will take forever to run. No, instead, start it just before the task you need to improve.)

2. Right. With your Trace started, begin using SAFE to perform the activity which is slow, slow, slow. Do it a few times if there are variations (for example several different Queries on the same report which all run slowly). Close SAFE.

3. Now go back to SQL Profiler and click on File|Stop Trace. Then go to Tools and open the Index Tuning Wizard. Follow the prompts and accept the defaults. When you are asked to 'Specify Workload', select 'My workload file' and select the name of the Trace file you created in Step #1. Click OK and go have coffee.

When you are done, shut down Enterprise Manager and open SAFE. Now try the same task as before. Your reaction should be anything from 'hmmm, somewhat better' to 'Holy Toledo!'.

A typical improvement can be demonstrated in Sales History reporting. Many of you run Sales History Queries using the '$' operator to match sales of items with, for example, the letters 'env' -anywhere- in the description. After all, people are constantly naming their forms slightly differently:

#10 Envelope
Envelope #10
Business Env. #10

You do this by running a Query with a Filter Option like so:

Description $ ENV

Now in older versions of SAFE this could take ages to run and, on some systems simply would not run unless you were sitting at the server console.

But to see the difference in SAFE SQL, we took a database of 50,000 sales orders in SAFE 5.1 and converted it to SAFE SQL. The SAFE 5.1 Query above took 8 minutes to display on an older Pentium 800. The same Query in SAFE SQL took nine minutes to run which was frustrating to say the least. But after running Index Tuning Wizard as shown above, the same query took (get ready) 23 seconds.

Important Note: Index Tuning Wizard usually cannot do any harm to your database or SAFE so long as you follow the defaults. It can usually only help. The worst case scenario, after running it, would be limited improvement. That said, it is always recommended that you completely back up your database before running any SQL Server utility.

Who Did What?
In previous versions of SAFE, you could use our TransLog SAFExtensions to allow you to monitor who is doing what. TransLog is still a good idea, but for larger users, you can gain a lot more flexibility by activating the logging capabilities built into the database. For example, if you want to log whenever user 'MARYANN' attempts to delete a Sales Order, or whenever anyone in your A/P Department updates the Check Register for any reason, you can create 'triggers' within the database to automatically log these specific activities. Note: for your network savvy types, these logs are very similar to the System Log in Windows itself.

If you are familiar with setting up Events to monitor in the Windows System Log, you should be able to set up your own triggers to monitor in the database, or you can have us do so for you. Setting up logging triggers is included in your Premium Support contract, or

Another Note: If you go crazy and try to log every activity in your database, performance can slow significantly. Only monitor activities that you really need to.

In Conclusion
Index Tuning Wizard And Database Logging are extremely powerful tools that your company can use to dramatically improve performance, increase security, and solve any number of mysteries regarding 'who did it!'. Though these are not skills one can develop reading a 'SQL Server For Dummies' book in the bath, they often are simple enough for some dedicated soul in your office to get the hang of with a bit of a hand from us.
 

Microsoft Security Alert!!
The following e-mail has been sent to all registered Microsoft Windows users. We think it speaks for itself. This security flaw is pretty bad and has already affected a large number of our customers. The short of it? Get the update now and patch all your company's PCs.

It has been widely reported in the press and on Microsoft's own web site, that on July 16th we released a critical security bulletin
(MS03-026) and a patch regarding a vulnerability in the Windows operating system. We wanted to make sure that if you were not aware
of this bulletin and corresponding patch that you take a moment to go to http://www.microsoft.com/security/ security_bulletins/
ms03-026.asp to find out if you are running an affected version of the Windows operating system and get the specific information as to
what you need to do to apply this patch if you have not already.

Although we encourage you to pay attention to all security bulletins and to deploy patches in a timely manner we wanted to call special
attention to this particular instance as we have become aware of some activity on the internet that we believe increases the
likelihood of the exploitation of this vulnerability. Specifically, code has been published on several web sites that would allow
someone to spread a worm/virus that takes advantage of the vulnerability in question thereby impacting your computing environment.

Although it is our goal to produce the most secure and dependable products possible, we do become aware of these types of
vulnerabilities. In order to minimize the risks of such vulnerabilities to your computing environment, we encourage you to
subscribe to the Windows Update service by going to http://www.windowsupdate.com and also subscribe to Microsoft's
security notification service at http://register.microsoft.com/subscription/subscribeme.asp?ID=135 if you have not already. By
subscribing to these two services you will automatically receive information on the latest software updates and the latest security
notifications thereby improving the likelihood that your computing environment will be safe from worms and viruses that occur.

We apologize for any inconvenience the implementation of this patch might cause and appreciate you taking the time to update your system.

Thank you,
Microsoft Corporation

 

Ciaran's Corner: MS Blaster!
No this isn't the same Security Alert As In The Previous Article!
Microsoft has identified a significant security hole in SQL Server which allows ne'er do wells to gain access to your database over the internet. This patch has been available for sometime but for whatever reason is not automatically installed with the automatic update features of Windows or SQL Server.

For details on the upgrade procedures for the different program versions that are vulnerable, read Microsoft security bulletin MS03-031 and Knowledge Base article 815495.


Data Recovery!
It was bound to happen some  day. And it happened. We zapped one of our own drives. We formatted the wrong drive. A drive we needed. Now in the past, I would have sent the drive in a foam box to OnTrack Data Recovery Services in  can highly recommend the OnTrack EZ-Recovery program, available on line at www.ontrack.com for $69.95. I tried this, and a few other programs, as an experiment. Surprisingly, they all worked about the same--giving me back 100% of my data. I recommend On-Tracks's program because:

1. Superior reputation (we have used their services for over ten years).
2. Live tech support, in case you need it.
3. A way to 'take it to the next level' in case you need to (ie. you can always ship the disk to them for serious data recovery efforts if EZ-Recovery doesn't do the job.

Til Next Time,

Ciarān Marron
Technical Support Manager
cm@suntowersystems.com

---

THIS DOCUMENT IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY. The information contained in this document represents the current view of Suntower Systems on the issues discussed as of the date of publication. Because Suntower Systems must respond to change in market conditions, it should not be interpreted to be a commitment on the part of Suntower Systems and Suntower Systems cannot guarantee the accuracy of any information presented after the date of publication. INFORMATION PROVIDED IN THIS DOCUMENT IS PROVIDED 'AS IS' WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND FREEDOM FROM INFRINGEMENT. The user assumes the entire risk as to the accuracy and the use of this document. This document may be copied and distributed subject to the following conditions:
1. All text must be copied without modification and all pages must be included.
2. All copies must contain Suntower Systems' copyright notice and any other notices
    provided therein
3. This document may not be distributed for profit.

---

End of E-News From The Suntower, Volume V #15