NCR Counterpoint has served my clients well over the last ten years. However, as a systems administrator, NCR Counterpoint’s lacks comprehensive technical documentation that other POS systems currently have. I’ve had many instances when I’ve asked our Channel Partner for the documentation on how to solve X problem and the standard response from them is that they don’t have any documentation, and they’ve just learned how to deal with this through years of experience.
NCR’s business model wants you dependent upon their channel partners and not on your own IT staff. In other words, they want you to depend on them for every change, update or issue.
I will be documenting tips and tricks that I’ve learned from using Counterpoint v7 and Counterpoint SQL v 8.x for more than 10 years on this blog in the near future.
Performance & maintenance is not addressed in their documentation. I’ve learned a few tricks on what I need to do to maintain optimal performance of your database.
I’ve separated optimization & maintenance functions into daily, weekly, monthly and quarterly tasks. Depending up on your setup, SQL Server 2008 or SQL Server Express, you may or may not be able to automate these tasks.
Daily Server Maintenance
Backup, backup, backup.
While our servers are set up as RAID 1 (I was handed this situation), I still believe in having a solid, mulch-tiered backup strategy in place. You never know when you’ll have a server knocked out because of a faulty UPS or other act of nature.
Grandfather-Father-Son Backups – Local
We employ the Grandfather-Father-Son method for complete daily backups. We back up the MS SQL Server 2008 each night at the end of the day after all remote stores have replicated and posted using a standard SQL Server maintenance plan. We backup up 7 days worth of data. We then backup up weekly and keep 2 weeks worth of data and we have a monthly backup keeping 2 copies.
Each night, after the complete backup, we compress the full daily backup and send it to our AWS S3 account. There we keep 7 full days of backups in the cloud for an off site copy.
Before anyone comes in to the stores, we have our SQL server backed up to one of our NAS devices in the early AM. This NAS device is a RAID 5 network storage device. We also creates a differential backup once every other hour to minimize any data loss that may occur and send it to the NAS device At most, we would lose 2 hours of transactions at a single location in the event of a catastrophic failure from SQL Sever 2008.
Because our database is only about 10 gigabytes in size and we’re not a 24-hour operation, we also rebuild the index each night, update our statistics and clean up temp files automatically to make sure we’re running at optimal performance.
SQL Server Express 2008 does not allow you to run automated scripts to maintain your database. I use the following SQL script found at the Foliotek Development Blog to rebuild the index’s each night.
-- Ensure a USE statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @pagecount int;
DECLARE @command nvarchar(4000);
DECLARE @page_count_minimum smallint
SET @page_count_minimum = 50
DECLARE @fragmentation_minimum float
SET @fragmentation_minimum = 30.0
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag,
page_count AS page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND index_id > 0
AND page_count > @page_count_minimum;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
-- Loop through the partitions.
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
WHERE object_id = @objectid AND index_id = @indexid;
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
PRINT N'Rebuilding index ' + @indexname + ' on table ' + @objectname;
PRINT N' Fragmentation: ' + CAST(@frag AS varchar(15));
PRINT N' Page Count: ' + CAST(@pagecount AS varchar(15));
PRINT N' ';
-- Close and deallocate the cursor.
-- Drop the temporary table.
DROP TABLE #work_to_do;
PRINT 'ERROR ENCOUNTERED'
I created a stored procedure that is called each night through the built in task scheduler to perform the index rebuild on all the remote locations. I’ll go into details on a future blog as to the exact steps I took to do so. If you don’t have a very busy remote location, you can also perform these by hand every few weeks. This will help with product look-ups.
Typical weekly tasks are housekeeping tasks for the accountant & management rather than for database maintenance. Each Sunday night, we make sure that the following Counterpoint commands are run and post correctly:
Inventory -> Adjustments -> Post All Adjustments
Inventory -> Physical Counts -> Post
Inventory -> Transfers -> Transfer’s In/Transfer Out
Inventory -> Quick Transfer’s -> Post
Purchasing -> Purchase Requests -> Post
Purchasing -> Receiving -> Post
Purchasing -> Adjustments -> Post
Purchasing -> Returns to Vendor -> Post
This gives the general manager a more accurate view of what’s going on in their stores. If you run your books month to month, you can perform these at the end of the month.
Monthly Database Maintenance
Again, much of the monthly maintenance is housekeeping for the management and accounting staff. I typically run the following commands in NCR CounterPoint SQL to clean up our data:
Inventory -> Utilities -> Purge Inactive Items
Inventory -> Utilities -> Recalculate Item Qty’s
Customers -> Utilities -> Purge Inactive Customers
Customers -> Utilities -> Recalculate Customer Balances
System -> Utilities -> Purge Messages
System -> Gift Cards -> Maintain Gift Cards
System -> Store Credits-> Maintain Store Credits
Depending upon what your particular company’s policies you can purge inactive items, customers, gift cards, store credits and make sure that all customer balances are correct. In the companies that I serve, they are typically set to purge items over 5 years old.
I also take the time to pull a copy of our AWS S3 backup and make sure that I can fully restore the database. Typically I do this for a daily backup, weekly and monthly. Backups are only good if you can restore from them.
NCR Counterpoint Year-End Maintenance
NCR Counterpoint requires you to perform a few operations at the end of the year to make sure that you are ready for the new fiscal year. Typically right after the stores have closed and all stores have replicated (I force replication as soon as the post), I have the accountant or myself run the following reports:
Inventory -> Reports ->Valuation
Customer -> Reports -> Aging
System -> Gift Cards -> Reports
System -> Gift Cards -> Store Credits -> Reports
System -> Accounting -> Reports -> Unvouchered Receiving
Finally you’re going to have to make sure that you have the new calendars set up in NCR Counterpoint for the next fiscal year. You can find them at the following menu location:
Setup -> System -> Calendars
If you do not have your next fiscal year set up, you’ll need to do so. You will have to set your Seasons, Month’s and Weeks. Typically if you press the “Set Default Dates” button, you’ll have everything set up automatically, correctly. This can be done ahead of time.
Finally be sure and change your current fiscal year at:
Setup -> System -> Quick Setup
There you’re going to need to change the current fiscal year to the new fiscal year.
Follow these guidelines and you can remove 90% of the maintenance related headaches when dealing with NCR Counterpoint. If you have anything additional to ad, that you do in your routine, let me know in the comments.
Studio 1909 provides NCR CounterPoint consulting & database management for small to medium sized retailers. For more information, send us a message.