List of tables which must be configured for SmartNumbering

When configuring an Outscaling scenario which includes multiple CMS server instances, each with their own database and subscribed to one another, it is required to configure SmartNumbering on the tables listed below. As part of configuring SmartNumbering, it is also required (for SQL Server) to remove the identity from the tables listed below.

 

Tablename Remark
Contents  
ContentTypes  
AccessTypes  
AccessTypeLevels  
UserGroups  
UserActions  
UserRoles  
UserActionsMenu  
UserPaths  
FileTypes  
MimeTypes  
Thesauri  
ThesTerms Optional, but preferred.
ThesRelationTypes  
ThesTermDataTypes  
ThesTreeTypes  
osServerInstances  
osDatabaseInstances  
osFilesystemInstances  
osDatasources  
osScheduledEvents  
ContentRelationTypes  
Status  
ItemPropertyDefinitions Only when ItemProperties has been installed/is in use.
ItemPropertyDatatypes Only when ItemProperties has been installed/is in use.
CookieUsage 1.4 build 9 and higher
CookieUsageApplications 1.4 build 9 and higher
CookieUsageCategories 1.4 build 9 and higher
CookieUsagePatternTypes 1.4 build 9 and higher

 

Notice that the in iXperion 1.2 introduced table AllUsers is not included in this list. That's because this table is already created without an identity on the Nr column and with a trigger in place which always uses SmartNumbering.

However, you do need to configure SmartNumbering for AllUsers when dealing with multiple CMS server instances and you want to be able to manage the users/visitors on more than one server instance.

Then you need to run the stored procedure pr_ConfigSmartNumbering on all database(s) but one for the table AllUsers, each time specifying a numberrange which is below 1.000.000 (e.g. 10.001 - 20.000, 20.0001 - 30.000). Notice that internally used SmartNumber ranges for Users and Visitors are added to the SmartNumberRanges table automatically, so you don't need to set them yourself.

SQL Script

The SQL script shown below can be used to configure SmartNumbering on the tables listed above.
This script uses a number range suitable for the primary cms server instance. Update the @rangestart and @rangeend variables (e.g. 1000001 and 2000000 respectively) when running the script on a subscriber instance.

SQL CopyCode image Copy Code
DECLARE @rangestart int, @rangeend int;
SET @rangestart = 1;
SET @rangeend = 1000000;
 
exec pr_ConfigSmartNumbering 'Contents', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'ContentTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'AccessTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'AccessTypeLevels', @rangestart, @rangeend, 0, 'Nr';
exec pr_ConfigSmartNumbering 'UserGroups', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'UserActions', @rangestart, @rangeend;
-- range-end for UserRoles cannot exceed 32767 so use smaller number range
exec pr_ConfigSmartNumbering 'UserRoles', @rangestart, 5000;
exec pr_ConfigSmartNumbering 'UserActionsMenu', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'UserPaths', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'FileTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'MimeTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'Thesauri', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'Thesterms', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'ThesRelationTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'ThesTermDataTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'ThesTreeTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'osServerInstances', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'osDatabaseInstances', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'osFilesystemInstances', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'osDatasources', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'osScheduledEvents', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'ContentRelationTypes', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'Status', @rangestart, @rangeend;

-- for AllUsers, use ranges of 1-10000, 10001-20000, etcetera
exec pr_ConfigSmartNumbering 'AllUsers', 1, 10000;
 
exec pr_ConfigSmartNumbering 'CookieUsage', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'CookieUsageApplications', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'CookieUsageCategories', @rangestart, @rangeend;
exec pr_ConfigSmartNumbering 'CookieUsagePatternTypes', @rangestart, @rangeend;