Retail Systems Technical Advice: Dave's Blog

Scheduling Jobs in SQL

2012-02-01T00:08:35Z

Scheduling SQL Jobs

Scheduling SQL jobs can be very useful, both for repeating jobs, and for long-running jobs that execute during off hours.

While SQL jobs can be run using the command OSQL, or the Windows scheduler, there are some advantages to using the SQL Server Agent, and its built in job scheduler.

One of the biggest advantages of using the SQL Server Agent, is that jobs can consist of several steps. When one step completes, the job can go on to the next step, or end, depending on the outcome of the step that just finished.

For example, you could build a job that checks for new orders. If any new orders are found, you could proceed to a second step, and process the new orders. If none are found, you could end the job. Jobs can also proceed if a step fails, and end if it succeeds.

Maintenance tasks, such as backing up your databases, rebuilding indexes, are prime candidates for this type of scheduling. A single job can be created, that goes through all of the maintenance tasks. This job can then be scheduled to run periodically, during off hours, reducing the load impacting your users.

Another good candidate for this, would be a major reorganization of your database. Adding columns to large tables, and populating them with data, can put a load on your server. In this case, the job would be setup, and scheduled to run one time during off hours.

Any SQL commands can be run, as long as there is no user intervention needed. So, take a look through your SQL scripts, especially the ones that you need to run periodically, and consider scheduling them to run automatically.

Contact CCS Retail Systems if you need help creating you SQL scripts and jobs.

Scheduling tasks on Linux

2012-01-18T23:15:11Z

Using Linux Cron

There are many tasks that are done periodically on a Linux system. System cleanup, or monitoring, are common ones, as is backing up your system.

These are all candidates for being scheduled to occur automatically.

The facility to schedule jobs on your Linux system, is cron. First, make sure that cron is installed on your system, and running automatically. Just how this is done, varies depending on the version and dialect of Linux that you are running.

Once cron is installed and running, it is time to schedule some jobs. This is done with the "crontab -e" command. This command takes you into the an editor (normally vi or vim) for your cron tables, which is what cron uses to determine what is to be run and the run schedule. When you exit the editor, the running cron is updated with your changes. It is best to use the crontab editor because, If you edit the cron tables directly, it may not pick up your changes until cron is restarted, typically the next time you reboot.

The editor that is used by the crontab command, is the one identified by your EDITOR environment variable. You should be familiar with it before you try to change the cron entries. Assuming it is vi or vim, you can read the man pages for this utility with "man vim". For a vim tutorial the "vimtutor" command can be used as well.

Each line in the cron table, is a command to be run at a specific time. The exception to this is lines beginning with the pound sign (#), which are comments.

There are basically six fields, separated by spaces.
The first five are define when to run and are the minute, hour, day of the month, month, and day of the week. Any of these may be "*", which means "all". So, a line beginning with "* * * * *" would run each minute, hour, day, etc.
To run at specific times, these fields would have numbers in them. This can be a single number, a list of numbers separated by a comma, or a range. A range is identified by a dash (-).

To run at the top of the hour, you would use "0" for the minute field. To run at 15 and 45 minutes after the hour, you would use "15,45", and to run each of the first five minutes of the hour, "1-5". These numbers would be put in without the quotes.

The range for minutes is 0-59, for hours is 0-23, day of the month 1-31, month 1-12, and day of the week 0-6 (with 0 being Sunday).

So, to run a task at 4:15 PM, the first and the 15th of a month, the command would be "15 16 1,15 * * command". At noon every Friday would be "0 12 * * 5 command".

The command field is everything following the day of the week entry. This can be any executable Linux command. Typically, it is a shell script, containing the commands to be executed. If it is a shell script, make sure that the permissions are set with the execute (x).

Any output will be sent via email to the user for that cron table. I usually prefer to redirect the output to a log file, instead. First, I redirect any errors to the standard output, using 2>&1. Then, I redirect and append the output to a log file, using double greater than (>>).

Put it all together, and you have an entry like:
15 16 1,15 * * command 2>&1 >> /var/log/command.log

This runs the command "command", on the first and fifteenth of every month. Errors are redirected to standard output, and standard output is appended to the file "/var/log/command.log".

This is a quick introduction to cron. If you need help creating cron scripts and scheduling them with crontab contact us at CCS Retail Systems.

Using OSQL to run scripts

2012-01-18T22:19:11Z

Using the Windows oSQL Command

Using the Windows OSQL command instead of SQL Management Studio can have it's advantages. I use OSQL, from a batch file, when I have a script that I want to run against multiple databases, or different servers.

First, to use OSQL, it has to be installed on your computer. If you have SQL Server, or Express, on your computer, then OSQL should have been installed by default. If not, then install the SQL Server Client Tools, from the SQL CD. Do not install the server, just the tools.

OSQL has several options that can be selected. The ones I use the most are Server (-S server), database (-d database), username (-U username), password (-P password), input file (-i inputfile), and output file (-o outputfile).

A typical batch file would look something like "osql -S %1 -d %2 -U %3 -P %4 -i inputfile.sql -o outputfile.txt". If this batch file were named, for example, batchfile.bat, it would then be called with:
batchfile.bat servername databasename username password, where %1 is servername, %2 is databasename, %3 is username, %4 is password. Inputfile.sql and output.txt are hard coded in this example.

The servername, databasename, username, and password, would be the actual values that I wanted to pass to the OSQL command line. The batch file would then run OSQL, using those parameters in order, and running the inputfile.sql (from the -i option), and sending the output to outputfile.txt (from the -o option). You will need help from your administrator to get the security information you need.

A batch file like this, can be used in different ways. It can be run from a command prompt when needed, or it can be scheduled via the Windows Task Scheduler. For example, you could make scheduled entries to run the same script on several different servers. One schedule entry per server, with the appropriate options being passed to it in a command line.

This is just a quick introduction to oSQL. If you are not current on the syntax of the SQL language, try the tutorials at the w3schools.com site.

We write SQL scripts for our customers on a daily basis. Contact us at CCS Retail Systems if you need help.

Running SQL scripts

2012-01-18T22:18:20Z

Creating a SQL Script

There are several methods that can be used to run SQL scripts. Which method is best, depends on the purpose of the script.

If the script is meant to be run one time, or on rare occasion with no set pattern, then running the script directly makes the most sense. It can be run through the SQL Management Studio Express, or from the Windows command line, if OSQL is installed.

The easiest way to write your script, is using the SQL Management Studio. Open Management Studio, and log into the database server. Expand the databases, and right click on the database that you want to run the script on, and select New Query. The right hand side will open a new window for you to write your query.

When writing queries in the Management Studio, as you type, table names and field names will show in a drop down list, based on what you are typing. You can select from these, and save yourself some keystrokes.

Once you have finished your script, and tested it (by using the Execute button in Management Studio Toolbar), save it with a ".sql" extension. By using this extension, if you double click on the script in Windows, it will automatically launch the SQL Management Studio, and load your script. Once you log in to the database server, your script is then ready to be run, by hitting the Execute button.

This is simpler than using OSQL to run your scripts. However, there are some advantages to using OSQL, which I will look at in my next blog.

If you are not current on the SQL language syntax, try the w3schools.com site: http://www.w3schools.com/sql/default.asp

NOTE: If you are creating SQL scripts you should be the Administrator or creating scripts with the Administrator approval. Your SQL Database Server (Engine) should be password protected. You will need this information to get logged in.

We write SQL scripts for our customers almost on a daily basis. If you need help contact us at CCS Retail Systems.

SQL Maintenance

2011-12-31T02:45:15Z

SQL Maintenance

If you have one of the full versions of SQL server, the SQL Management Studio has a section called "Maintenance Plans" that makes scheduling tasks like rebuilding indexes and updating statistics very easy.

From the SQL Management Studio, expand the "Management" section, and you will see "Maintenance Plans" under it. Using the right mouse button, click on "Maintenance Plans", and you will get several options. The simplest, is to choose "Maintenance Plan Wizard".

The Wizard will step you through everything to build, and schedule your plan. To setup a rebuild indexes and update statistics, for example, you would click "Next" until you get to the plan properties page.

On the plan properties, name your maintenance plan, for example "Re-index". It is useful to put a comment in for later reference, such as "Rebuild Indexes and Update Statistics". Leave the option on "Single schedule", and click the "Change" button to schedule the plan. Set the plan to "Recurring" and make sure it is enabled. Set the schedule fields for what best suits your needs. Weekly or daily would be appropriate in this case, depending on how active your database is. See my blog on "SQL Performance" for a discussion on this. Select :OK" when you have the schedule set, and then "Next" to go to the task selection.

For the tasks, select "Rebuild Indexes" and "Update Statistics". The next page allows you to define the order that they process in. Indexes should be rebuilt before the statistics are updated, so the default order is fine. Next, will take you to the properties for each of the tasks. Select "All" for the databases, unless you have a reason to only do specific ones. The default values for the rest of the options should be okay.

Going forward from there, you have the option to save results to a file, or email them. Saving results is always a good idea. Doing another "Next" brings you to the confirmation page, where doing a "Finish" will do the actual building of the maintenance plan, and scheduling it.

That's all it takes to setup and schedule most SQL maintenance tasks. Other things, such as shrinking the database, or checking the integrity can be setup the same way. Other SQL tasks that you do frequently can also be scheduled outside of the Wizard. I will take a look at doing those in a future blog.

Dave.

When your system freezes up

2011-12-31T02:44:38Z

Avoiding File Corruption

No matter how well you maintain your system, there comes the time when it freezes up. When that happens, you really have no choice other than to do a hard reset, by turning the power off, and back on. Since there is an inherent risk that this will result in some file corruption, it should be avoided if at all possible. Still, sometimes it is necessary.

There are many times, however, when your system is not truly frozen, but is overloaded instead. This is more common in a network environment, when users are running software that is server based. In this case, it may appear to be frozen, as users get no response at their workstations. However, what may really be happening, is that the server has become so busy, that it does not have time to respond to the requests from the workstation. The appearance to the users is the same, but true cause is different.

The difference is important, because if it is a matter of server overload, many times the process causing the overload can found and killed. If this can be done, the risk of corrupting your data files is much less than if the system is simply powered down. The reason for this is that when a process is killed, in most cases, it closes the files that it has open as it goes down. If the power is simply shut off, this does not occur.

Check your system for any indications of activity, to determine if it is overloaded as opposed to being frozen. In most cases, an overloaded system will have high disk usage. You may be able to hear the disk drive working, or the disk activity light on the computer may be blinking very fast, or even on solid. If that is the case, then you have a good chance of being able to get on and kill the process.

When a server is overloaded as opposed to being frozen, most times it is possible to get log in, and run utilities to identify the runaway process. It may be very slow responding, but if all possible give it enough time and you will eventually get logged in. Running the utilities to identify the runaway process varies, depending on whether you are on a Windows based machine, or UNIX/Linux. I will go into the utilities used for this in a future blog. For now, just be aware that the utilities will probably also be slow to respond. However, if you can get logged in, and the utilities do respond, you can probably kill the offending process, and free the system back up.

There may be times that the server is so overloaded, that you will not be able to log in and kill processes. In that case, you will have to resort to turning the power off, and risk corrupting files, but you really have no choice at that point. This should be a last resort, and only done if you are not able to get into your system at all.

SQL Triggers

2011-12-30T02:37:09Z

Using SQL Triggers

If you are using software based on SQL (Standard Query Language), such as Microsoft SQL, you have the ability to add triggers. A trigger is an event that is executed (triggered), when records are added, deleted, or changed, in the SQL database. There are many uses for triggers.

One that I did recently, puts records into a table, that is used by a secondary processing system that the customer has. When the sale ticket uses specific pay codes, some of the data needs to be captured, to be used by software that is specific to part of their business. By using a trigger on the tables used by the point of sale, this special table is immediately updated with the information needed by the second, specialized, application. In other words, the second application has immediate access to the sale data, without the need to search through the information on many sales tickets that are not applicable. The performance impact at the time the sale is made, is so minimal that it is not noticeable. The improved performance eliminating the search in the second application is significant.

That is the beauty of a SQL trigger. The processing occurs automatically and, if properly designed, with no noticeable impact to the end user.

Almost any processing can be done in a trigger. In the case above, the payment code is checked, and if it is one of those needed by the second application, the necessary information is captured to a different table, and the sale process then goes on as in all other sales.

Other application of SQL triggers that we have implemented, include re-calculating sales tax rates based on the sale location (a requirement in Washington state, among others), and automatically updating the sale price for repeat customers. There are any number of possible uses for SQL triggers.

While just about any processing can be performed in a trigger, the trigger must be properly designed so that it does not adversely impact the flow of business. For example, in a point of sale system, you would not want to perform processing that could take several minutes. Your customers do not want to wait those several minutes to get their receipt. You could, however, flag the records for the additional processing, and have another procedure that performs the actual additional processing. This additional processing could be made to happen automatically (which will be addressed in a future blog).

Contact CCS Retail Technical Support for Custom CounterPoint SQL and Passport Business Systems SQL programming.

SQL Performance

2011-12-30T02:36:19Z

Improving SQL Performance

There are several factors that can affect the performance of your SQL system. The obvious ones are of course the basic design of your database. A properly designed database system has a minimum amount of redundant data. There is no reason to store the same field of information in several different tables, if that information can be found from a single master table. Keeping such redundancy out reduces the overall database size, which helps with performance.

However, there are other, less obvious, factors that affect your SQL database.

Microsoft SQL in particular, greatly benefits from having indexes rebuilt and statistics updated. Properly designed indexes significantly speed up accessing the information in a table. The issue is that, over time, these indexes become less optimized as new data is added, and old data is deleted. When data in a table is updated, the old data is actually deleted, and the new data inserted, giving the index a double hit.

Recently, I ran a script on several different SQL servers for a customers. On some of the machines, the script ran in under three minutes. On others, it was over 30 minutes. Same script, similar hardware and database, and vastly different performance. The difference was that some servers had been recently had the indexes rebuild

When the SQL indexes are rebuilt, they are again running at a high level of optimization, which helped achieve the short run times mentioned above.

A second major factor, with Microsoft SQL, is updating statistics. MS-SQL keeps statistics on how particular tables are accessed, and these can be used to automatically tune the server to increase performance, also. The impact of rebuilding indexes, and updating statistics, can be major. You can see that in the difference in doing the same processing mentioned above.

How often indexes need to be rebuilt, and statistics updated, depends mostly on how active the database is. In a high activity system, these should be done frequently. Lower activity systems less frequently. My minimum recommendation would be to re-index and update statistics weekly. We have several customers that are setup to do this every night, as they have higher activity levels.

Also, if any major database processing is done, such as updating most or all records in a table, a re-index and update of statistics is called for.

Contact CCS Retail Technical Support for Custom CounterPoint SQL and Passport Business Systems SQL programming.

Some thoughts on training

2011-12-01T01:05:49Z

Hands-on Training Justification

So you just got the latest and greatest software, now what. Obviously, you need to know how to use it. While you can dig through the manuals, and try to figure it out on your own, you would probably benefit much more from hands on training.

There are many advantages to taking a training course:

Being in a dedicated training environment, you are not distracted by the many interruptions in your day.
No stopping and starting, and trying to remember where you were.
You can concentrate on learning your new software, and finding out how it can be used to best suit your needs.
Which of the myriad of setup options best suits your needs?
In a training class you can ask questions from an experienced person.
You can also find out what the implications are of choosing any particular settings, and why one is better than another for your particular needs.
Your questions can be answered while they are still fresh in your mind.
No need to write them down, and then try to remember what the circumstances were that brought on the question in the first place.

Ideally, your training course will be geared to your specific needs. In that case, you will be able to go through all of the setup steps, discussing the options and how they pertain to your needs. You will be able to ask questions, and discuss the options, to be able to choose the ideal setup. Going forward, you will be able to go through the type of processing that you do on a daily basis, to see just how those tasks would be carried out in your live environment.

In short, half a day, or a day, spent in such targeted training, will yield much greater results than spending a day trying to figure it out on your own.

Contact CCS Retail for your training needs.

Dave.

More on creating good passwords

2011-12-01T01:05:10Z

Creating Good Passwords

If you process credit cards in your retail operation, you should be aware of the requirement to change passwords every 90 days. Those of you who do not take credit cards, should change your passwords frequently, too.

I have discussed what makes a good password before. In brief, a mixture of upper and lower case letters, numbers, and special characters, such as !, #, @, etc.

The longer the password the better. Pick a value between 16 and 32 characters. To help you create and use the passwords consider using a keeper program such as Keepass. They will generate passwords for you and allow you to use the clipboard to paste them later rather than trying to enter them on the keyboard. They also defeat key loggers that may be hidden on your system.

There are additional considerations to look at. Recently, I have seen some articles and passwords, and found it very interesting that one of the common passwords is "qwerty". While this may look like a fairly good choice, if you check, it is the string of letters on the third row of your keyboard. The pattern is where the problem lies.

Anything with a pattern is not a good choice for a password. Patterns on the keyboard (such as "qwerty"), and sequence patterns (such as "abcde") make very poor choices. Adding such a pattern to a base word is not much better. Something like "someword123" is little improvement over using "someword" as your password.

Password hacking software is getting more sophisticated all the time. Giving it easy targets, by using predictable patterns for your password, is asking for trouble. Take a little time, and put some thought into it, and you will be able to come up with a good, complex, password that you will be able to remember.

Contact us if you need more help.

Dave.