SytelineMaster

About Shared Tables and Master Sites

In a multi-site environment where there are many sites, large amounts of data, and many users, you may want to set up one site as the master site for an intranet. In that case, certain _all tables and user tables can reside only on the master site database and are shared (read and written to through a SQL view) by other sites on the same intranet. No replication needs to occur for the shared _all and user tables, which can greatly improve system performance.

Steps to Set Up a Master Site and Shared Tables for All Sites on an Intranet

Follow these general steps. Refer to the forms and fields for detailed information. You can share _all tables, or user tables, or both types of tables.

Planning

CAUTION: You must plan your multi-site structure carefully before setting up the shared tables. This requires in-depth understanding of the SQL databases, this application’s replication capabilities, and your corporate financial reporting requirements. Refer to the Multi-Site Planning and Replication Reference documents on our support site for more information.

Set Up All Sites on the Intranet

The following steps assume that you have already used the Configuration Wizard during database server installation to link your multi-site databases.

In the Intranets form, specify all intranets to be used in this multi-site system. If you will be sharing _all tables, define an intranet that will include all the sites that share tables. This cannot be an "External" intranet. All the sites on this intranet must use the same version of this application.

Do not define the master site yet – that will be done later.

In each site and entity, use the Sites/Entities form to specify information about this site/entity and the other entities and sites that it relates to. Each site/entity has a record in this form.

On the System Info tab, specify information about the site. Make sure the Intranet Name, Database Name, and Time Zone are set correctly for each of the sites and entities listed on this form.

A list of linked sites displays automatically in the Link Info tab. The local site record shows links used in transactional replication between the currently selected site database and other site/entity databases. On site records other than the local site, the Link Info tab should show only links to the local site.

Use the Replication Categories form to specify tables, stored procedures, and XML documents that should be replicated and to group them into categories. The installation process creates some categories. Do not delete these standard categories. These categories have been created and tested to ensure that they handle the standard system processes. They should meet most of your needs without requiring any changes.

On the Replication Rules form, set up transactional rules for the Site Admin replication category between this site and all the other sites in the intranet, including the site that will be the master site. (Site Admin data includes tables such as site, intranet, and intranet_shared_table.) You may also want to write other rules to replicate certain categories between certain sites. Even if a category contains _all tables that you are sharing, you probably want to write a replication rule for the category. (Any shared _all tables will not be replicated in this case.) Categories may contain additional base tables or stored procedures that are needed to perform certain functions. The rules you need should be determined in the multi-site planning phase.

On the Replication Management form, click Regenerate Replication Triggers. This ensures that site and intranet data is replicated to all the linked sites.

Set Up the Master Site

Log in to the site that you want to make the master site where the shared tables will exist for the intranet.

On the Intranets form, select this site’s intranet. In the Master Site field, select this site to specify it as the master site for the intranet.

Share _All Tables

Log in to the master site.

On the Intranet Shared Tables form, select this site’s intranet.

A list of the _all tables that can be shared displays. (Not every _all table is listed; some are not available for sharing.)

For tables that you want to be shared between all sites on the intranet, select Shared.

Another way to choose the shared tables is to select the replication categories that you want to share. When you select a Replication Category from the drop-down list and click Select by Category, the system marks _all tables in that category as Shared.

When you have selected all the tables you want to share, select Actions > Save.

Click Process to copy information from the tables at the other sites to the master site’s table, delete the table from the other sites (creating views into the master site tables instead), and regenerate the replication triggers for the other sites. If you have selected several tables and have many sites on this intranet, processing may take a while. The Processing Step area displays the system’s progress.

CAUTION: During processing, the selected tables are removed from all sites on the intranet except the master site. Unsharing (rebuilding the tables at the using sites) is time-consuming – so be very sure that you have everything set the way you want it before clicking the Process button.

During processing, the system validates link setup between the master site and the using sites of an intranet. If it finds a problem, an error message displays and nothing is processed; fix the link and then click Process again.

The Processed field indicates which tables have been processed – for example, if shared, they are now resident only in the master site’s database. (Once a row on the form is marked as Processed, subsequent "Process" runs will not reprocess that row.)

After processing all the tables and sites, the system regenerates the replication triggers at the master site.

Share User Tables

Log in to the master site.

On the Intranet Shared User Tables form, select this site’s intranet.

Two lists of tables display:

The top grid lists user tables that can be shared. You cannot edit this list. You cannot select or clear the option for individual tables, with the exception of the AccountAuthorizations or UserGroupMap tables.

The bottom grid lists tables that contain a column whose base domain is UserNames.UserId or GroupNames.GroupId. The bottom grid is used during set up of shared user tables to identify the tables and columns that may need to be updated if records that were formerly defined in the Usernames or Groupnames tables in the non-master site are moved to the master site, but with different UserId or GroupId values.

If you have custom tables that contain a column whose value comes from base domain UserNames.UserId or GroupNames.GroupId, add your custom table and its associated ID column to the Non-Shareable Tables grid. We recommend that your custom tables refer to the Username or Groupname columns, rather than the ID columns, because the distinct list of Usernames and Groupnames across intranet sites is always the same, whether they are stored in shared tables or per site, and therefore no changes are required for data referencing this base domain.

Select Set up shared user tables to select Shared for all tables in the top grid and Update Referenced ID for all tables in the bottom grid. If you want to maintain the AccountAuthorizations table or the UserGroupMap table at each site, clear the Shared check box for those tables.

Click Process to copy information from the tables at the other sites to the master site’s table, delete the table from the other sites (creating views into the master site tables instead), and regenerate the replication triggers for the other sites. If you have many sites on this intranet or many users and groups, processing may take a while. The Processing Step area displays the system’s progress.

During processing, the system validates link setup between the master site and the using sites of an intranet. If it finds a problem, an error message displays and nothing is processed; fix the link and then click Process again.

The Processed field indicates which tables have been processed – for example, if shared, they are now resident only in the master site’s database. (Once a row on the form is marked as Processed, subsequent "Process" runs will not reprocess that row.)

The Status field indicates whether the user tables are shared or not shared.

After processing all the tables and sites, the system regenerates the replication triggers at the master site.

After processing is complete, you must reapply a valid license document on the master site. If the current master site is not enabled for intranet licensing, you must also apply a valid license document on all other sites on the master site’s intranet.

Adding a New Sharing Site (Changing the Intranet Value of a Site)

NOTE: A site’s intranet value cannot be changed if that site is already part of a sharing intranet, or if the site is a master site.

If you want to add a new site to a existing shared tables intranet, follow these steps:

Log in to the site you want to add.

Make sure the Replication Categories and Replication Rules on this site, and on other sites on the shared tables intranet, are set up to replicate Site Admin data to and from this site as desired.

In the Sites/Entities form, change this site’s Intranet value to the name of the shared table intranet and save the record. When you save the record, the system may display a message telling you that this process may take some time. (It copies shared _all table information to the master site and drops the tables from this site.) Saving the record also regenerates the replication triggers at this site and at the master site.

If you have set up rules between other (non-master) sites and this site, log into those sites and regenerate their replication triggers.

Setting Up Shared User Tables at a New Site

When you are already sharing user tables, and you add a new site to the current master site’s intranet, you may also want to set up the new site to share user tables. To do this:

In the Intranet Shared User Tables form, select the Set up per site user tables check box and click Change Setup Option to change the check box label to Set up shared user tables.

Click Process.The status of the shared tables is checked as each site is processed. If sharing has already been set up for a site, no processing occurs for that site, and the process continues with the next site. When it encounters a site that is not already set up, the site is processed.

Replication to Remote Sites

If there are other intranets with sites that want to replicate (not share) _all or user table data to/from sites in the sharing intranet:

For tables that are shared, set up replication categories/rules between the master site and the sites on the other intranets.

For tables that are not shared, set up replication categories/rules between any/all of the sites in the shared intranet and the sites on the other intranets.

Example

Intranet 1:

Site A (master site. Item_all table is shared)

Site B

Intranet 2:

Site C

Site D

If Site B needs visibility into Site D’s item data, replication rules should be set up from Site D to Site A.

If Site D needs visibility into Site B’s item data, replication rules should be set up from Site A to Site D.

If Site C needs visibility into Site B’s customer data (not a shared table), replication rules should be set up from Site B to Site C.

If a Shared _All Table Has Schema Changes

If one of the shared _all tables at the master site has a schema change, you will need to update the views into the _all table at the user sites on the intranet. To do this:

Log in to the master site and go to the Replication Management form.

Click the Regenerate Views to Master Site button.