These days I don’t do quite as many installs as in year past however do run into the scenario frequently where the client wants to get the shiny new software up and running on the newest iteration of the software but they are reluctant to drop a small fortune on Microsoft SQL Server licensing until they are certain the new version is a good fit for them. In some scenarios I have even seen where the customer already owned the supported version of SQL Server for the Syteline / Infor CloudSuite Industrial (or Business) that they need however that license is currently being used by the current production system and it would be a license violation to install this on the new system even though it was test.
Microsoft is quite generous and allows potential purchasers of their software to take a half year to kick the tires around. In some cases the client may even gain from waiting on purchasing the software in scenarios where the new SQL version is under six months from release allowing the current version to tide them over until they can buy the new one and invoke “downgrade rights.” On the surface this sounds like a no-lose scenario and in many cases this is very true. Microsoft even makes upgrading from evaluation to standard as simple as a very simply and non-invasive task that is available right from the disk known as an Edition Upgrade.
Now that we are aware of the benefits of enjoying an evaluation version it would be remiss if I didn’t cover the less glamorous aspects of using an evaluation edition. First, don’t trust your memory to keep track of when it was installed to make plans on licensing it later on. Microsoft SQL is not going to give you reminders each time you go to management studio that you have X days remaining nor will it give you a countdown when your time is running out. Instead, it will go from functioning as expected to crippled and there is no legitimate way to get a license extension from Microsoft that I have ever seen. What happens if your system is in production when the six months are up and nobody remembered to license the software? At the minimum you will see some unplanned downtime until someone gets their hands on a key. For some companies, each hour of downtime could be a small fortune and management will likely want to know who dropped the ball and in some cases terminate the person for negligence. One past thing to point out about this scenario is that the company doesn’t get much time to shop around for a reasonably priced seller and is often forced to pay full retail price plus a middle tier markup from the reseller. I have even seen cases where the company was so panicked that they went out and bought four packages of two core licensing when the server only had four cores and accidentally bought double what they should have because they would sign off on anything put in front of them if it meant they system was back online; Ouch!
There is a second aspect of going from evaluation edition to standard edition licensing that is even more sinister and could mean downtime even after proper standard edition licenses were applied. When the Configuration Wizard is used to create an initialized application or demo database and it is on evaluation version the logic is different than standard version. The wizard will query the back end server and will see that it has enterprise capabilities and will structure the entire database to use them. Is this a mistake on Infor’s part? Not at all! The evaluation edition is truly letting you evaluate the enterprise version so the wizard is correctly identifying that it has the ability to do an enterprise feature called “database partitioning” which will allow multiple sites to reside within one database however each site gets their own partition.
One might wonder why a partition would ever get created when the configuration wizard only created one site. The answer to this is that the wizard will see the enterprise features and make a business decision that the client will want to harness the power of database partitioning by allowing for future sites even though only one site was created. Now your database will have two partitions: The default partition and the partition that has been created for use by the site you defined in the wizard. Syteline / Infor CloudSuite Industrial (or business) still runs the same way and developers would still write their queries the same way because this is done on the back end not logic driven by user created stored procedures, functions, triggers, or views.
In cases such as the above where the wizard created a database of either of those two types, SQL now flags those databases as having the ability to only run in enterprise. After applying a license key and trying to start up the databases there may be a sinking feeling when one goes back into SQL Server Management Studio (SSMS) and sees those particular databases marked as “suspect.” The IT person may take a sigh of relief knowing that they have done their due diligence by backing up the databases nightly and determine that restoring the most recent backup will get them out of this nightmare. I have to be the bearer of bad news but unfortunately the database is not marked as suspect because somehow it got damaged during the edition upgrade but because the database was previously set to utilize enterprise features and the newly licensed database is now running the standard edition license which is not even a quarter of the price. Now what?
There really is only a few options once the database has enterprise features enabled system wide. The first option is to buy your way out of this by going back to Microsoft and trading in your standard license for enterprise. Your company better have a good amount of money to spend because these are expense. For most companies the cost alone makes this an unrealistic approach. The second approach involves creating a new database on the standard version and them standing up another evaluation version elsewhere and pumping over data via linked server. This solution can be very tough to accomplish because table order of the load is crucial to not violate foreign key constraints. Even when the table order is determined this would likely need to be done with triggers turned off or your data load would take in excess of a month! The last option is restore the database to an evaluation on another server briefly and then either manually or programatically remove the enterprise features until nothing is enabled and then back that up and restore it back to your SQL Standard instance.
That brings up an interesting question because we would need to know how many objects in the databases we would have to rebuild to accomplish this task. Taking a stock Syteline 9 ICS application database on 9.00.30 I did a query and found 3127 objects that would need to be rebuilt. Rebuilding by hand even one database sounds monumental and rebuilding the objects in multiple databases may be considered madness.
If you are running an evaluation edition of Microsoft SQL now you can run the following line on each database to determine which (if any) have enterprise features enabled:
SELECT *
FROM sys.dm_db_persisted_sku_features
If any records are displayed for any database and your evaluation edition hasn’t expired AND YOU DO NOT PLAN ON BUYING ENTERPRISE then this would be a good time to determine what path forward you want to take. There are many scripts on the internet regarding partitioning and I urge all people reading this to NOT BLINDLY RUN EVERYTHING THEY FIND OFF A WEBPAGE unless they understand what the script does and that its applicable to a SyteLine database. If you see scripts that refer to file groups and partition switching this is not a script that would get run to undo this type of partitioning.
I have written many scripts that I make available online to download and I have written ones specifically for putting a SyteLine 9 / ICS database back to a standard version that does not utilize partitioning however because of how dangerous it would be in the hands of someone who doesn’t fully understand the significance of each step I am opting not to make these available because of liability reasons. If you are reading this and have found yourself in a bad predicament please use the contact us form and someone will do our best to call or do a remote session to assist. For those that are proficient in T-SQL the high level steps would include the following:
- Alter the partition function to merge the range defined (found in sys.partition_range_values) The value will be general parameter record of the site
- Rebuild the clustered primary keys and clustered unique constraints
- Rebuild the non unique clustered keys
- Rebuild the non unique non clustered keys
- Rebuild the unique non clustered keys
- Rebuild the primary keys that are non clustered
- Drop the partition scheme (found in sys.partition_schemes) The default is SitePScheme
- Drop the partition function (found in sys.partition_functions) The default is SitePFunction
- Validate no enterprise features remain (select * from sys.dm_db_persisted_sku_features)
- Backup the database and restore it to the standard edition
I had one client ask me if there was a way to modify the behavior of the Configuration Wizard so that it will not create enterprise features for an evaluation edition so this could be avoided from the very beginning. The answer to that is “perhaps” but I have not spent any time looking for such a workaround. I had asked Infor a while back if there was a flag or other parameter that could be used for Configuration Wizard that would override the behavior but at the time was told that this is by design and that no override exists.
Hope this article was useful and will encourage clients to fully understand the implications of using trial software and to be prepared for when it comes time to activate it.
Tony Trus
August 22, 2016 
