Getting ready to upgrade Counterpoint SQL
With many people getting ready to upgrade Counterpoint SQL to the latest version, I thought I would talk about what can be done to prepare. One needs to confirm that the systems have the required prerequisites, as well as clearing any data that can be.
On the systems side, the biggest requirement is that SQL Server be version 2008 R2, at the minimum. Version 2012 is also supported, but later versions are not recommended. They may work without issues, but we are not recommending using versions above 2012 at this point. If you need to upgrade your SQL Server software, you can purchase a 2016 license, and install 2012 as a downgraded install, using that license. That way, you can upgrade to 2016 under the same license should it be necessary, or desirable, at some point in the future. That is the only issue that we have been seeing as far as system requirements for upgrading Counterpoint SQL.
As to the data, purging old data will speed up the upgrade process. In particular, purging the ticket history can make a big difference. Many times purging old ticket history is something that has not been done in a long time, if ever. With many interconnected tables making up the ticket history, the number of records that even a small store can generate overs years can be amazing. During the upgrade of Counterpoint SQL, the ticket history tables will updated with new fields. On very large history tables, this can be very time consuming.
In order to lessen the impact, the upgrade process does not perform the actual ticket history table upgrades. Rather, the tables are move, and empty tables with the changes to the schema are created. These tables are then populated from the data in the old tables, by means of a scheduled job. This job will migrate a number of records each time that it runs. In most cases, it will be scheduled to run frequently, and process a fairly small number of history records, starting with the latest tickets. Thus, over time the entire data set is migrated, and the records most needed for functions such as purchasing based on sales, or replenishment, are migrated first. Then, older tickets, which are usually not needed as much, are migrated later.
By purging the ticket history prior to upgrading, the migration process will obviously complete sooner. Every company is different in what they need for history, so some time should be spent in determining how much history to preserve. Two to three years seems to be the most common, but some wish to retain more, and some less. Once the cutoff date is determined, then the history should be purged up to that date. However, if you are using multi-site, I strongly recommend purging only one or two months of data at a time, and letting those changes replicate to all sites. If several months, or years of data are purged at once, it can severely impact the replication and even bring it down.
Those are the main points to address in preparing to upgrade Counterpoint SQL. Once they are done, it is simply a matter of scheduling a time and doing the upgrade, which usually takes a few hours at each location.