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 | 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; |