Sage SQL Replicator Setup Guide (Version 18.1)

Certified course curriculum

Sage 300 Construction and Real Estate

Sage SQL Replicator Setup Guide

(Version 18.1)

Important Notice

The course materials provided are the product of Sage. Please do not copy, reproduce or disburse

without express consent from Sage.

This material is provided for informational purposes only. Accordingly, Sage does not provide

advice per the information included. The use of this material is not a substitute for the guidance of

a lawyer, tax, or compliance professional. When in doubt, please consult your lawyer, tax, or

compliance professional for counsel. The Sage product(s) described herein is/are governed by the

terms and conditions per the Sage End User License Agreement ("EULA") or other agreement that

is provided with or included in the Sage product. Nothing in this document supplements, modifies,

or amends those terms and conditions.

and service names mentioned herein are the trademarks of The Sage Group plc or its licensors. All

other trademarks are the property of their respective owners.

06/2018

Sage SQL Replicator Setup Guide (Version 18.1)

Table of contents

Sage SQL Replicator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5

Prepare to implement Sage SQL Replicator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Install SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

Sage SQL Installer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Install SQL Server using the Microsoft Installer . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

Important information about SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Install SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Create a backup system administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Replicate data into SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23

Sage SQL Replicator configuration settings and replication . . . . . . . . . . . . . . . . . . . 23

About the replication process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Restarting replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Backing Up and Restoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34

Backing up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Restoring from a backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

Sage SQL Replicator Setup Guide (Version 18.1)

Sage SQL Replicator

Sage SQL Replicator is a tool that copies your Sage 300 Construction and Real Estate data from the Pervasive data

management system into a SQL Server database. The replicated data includes your Purchasing, Inventory, and

Service Management data if you use those products.

Why is copying your data into SQL Server useful to you?

 In many cases, using the SQL Server data for reporting results in improved processing performance. When you

generate Crystal reports in your on-premises Sage 300 Construction and Real Estate applications, you can use

the Use SQL check box to switch the data source from the Pervasive data to the SQL Server data.

 Sage SQL Replicator lays the foundation for accessing your data through Sage Mobile solutions. It is required

whether you use Sage Construction Central for Mobile Reports or Mobile Dashboards.

The replication process works in the background and it can continuously synchronize the Pervasive and SQL Server

databases. This means you can work in the Sage on-premises and mobile solutions without interruption and always

get up-to-the-minute information.

If there are databases for which continuous synchronization is not needed, you can specify when and how often to

run the replication.

This reference guide provides the setup and configuration needed to implement Sage SQL Replicator in System

Administration (Sage 300 Construction and Real Estate version 18.1). The material applies to system administrators

or information technology specialists who will complete the server configuration tasks.

In this chapter, you learn about preparing for the Sage SQL Replicator implementation. Information the next

chapters, include:

 How to install SQL Server.

 How to configure Sage SQL Replicator and begin the replication process.

 What happens during the replication process.

NOTE: If you are implementing the 17.1 version of Sage SQL Replicator, download the setup guide for that version

from the Product Documents web page.

Sage SQL Replicator Setup Guide (Version 18.1) Prepare to implement Sage SQL Replicator

Prepare to implement Sage SQL Replicator

Before implementing Sage SQL Replicator:

 Verify that your server configuration meets the recommended technical specifications.

 Evaluate whether you will use SQL Server Express or full SQL Server.

 Upgrade to Sage 300 Construction and Real Estate version 18.1.

If you use the Canadian or Australian edition of Sage 300 Construction and Real Estate, you must also:

 Specify the regional settings on the machine where your Sage software is installed.

 Create a domain Windows account for use with Sage SQL Replicator.

System requirements for version 18.1

Installing SQL Replicator to use with Sage 300 Construction and Real Estate adds to the demands on your server

resources. Review the18.1 technical specifications documented in the Sage Support Knowledgebase to ensure

that your server meets the recommended configuration in terms of the operating system and hardware.

NOTE: Solid State Drive (SSD) is required for the server if it hosts both Sage 300 Construction and Real Estate and

SQL Server.

Microsoft SQL Server edition

SQL Replicator requires a dedicated instance of SQL Server which can be installed on the accounting server or on

a different server. Later, in “Install SQL Server” on page 11, you will learn how to use the Sage SQL Installer to install

and configure the instance.

SQL Server comes in different editions.

 SQL Server Express Edition is available at no additional cost—you can download it from Microsoft or let the

Sage SQL Installer download it for you.

 SQL Server Standard Edition and SQL Server Enterprise Edition, both full SQL Server, are purchased

separately.

Check with your IT vendor to determine which edition best suits your organization based on the features, the size

of your database, and processing needs.

Please be aware that SQL Server Express Edition has limitations in terms of CPU and RAM usage—each database

can be no larger than 10 GB. See this Microsoft article for more information. Even if the size of your Pervasive

company folder is under 10 GB, it does not mean that you can use SQL Server Express. This is because the

replication process adds views and indexes to the SQL Server database, increasing its size.

To check whether the size of your Pervasive data is suitable for using SQL Server Express, follow these steps:

1. On the accounting server, browse to the location of your Sage 300 Construction and Real Estate company

folders.

2. Open the first company folder you want to replicate, and select the POIVData, PVData, and SMData folders and

make a note of the size of these three folders.

NOTE: You might not have all of these folders. The POIVData folder will only be present if you use Purchasing and

Inventory. The SMData will only be present if you use Service Management.

Sage SQL Replicator Setup Guide (Version 18.1) Prepare to implement Sage SQL Replicator

3. Right-click the selected folders and select Properties.

4. Next, browse to the location of the MASTER_QXM folder. This is in your installation directory, which is in the

following location by default:

C:\ProgramData\Sage\TIMBERLINE OFFICE\9.5\Accounting\Global\PVData

5. Right-click the MASTER_QXM folder and select Properties.

6. Make a note of the size of the folder, and add it to the others.

(POIVData) + (PVData) + (SMData) + (MASTER_QXM)

 If the total sum is under 6 GB, you can use SQL Server Express.

 If the total sum is greater than 6 GB, you must use full SQL Server (Standard Edition or Enterprise Edition).

Sage SQL Replicator Setup Guide (Version 18.1) Prepare to implement Sage SQL Replicator

Additional requirements for Canadian and Australian editions

If you have the Canadian or Australian edition of Sage 300 Construction and Real Estate installed, Sage SQL

Replicator requires a Windows user with the Region set to Canada or Australia. This enables the country-specific

fields in Accounts Payable and Payroll to be replicated. This account must have local administrative privileges on

the accounting server, and must also be added to the sysadmin SQL Server role when you install SQL Server.

Set the regional settings

1. In the Windows Control Panel, go to Regions.

2. On the Location tab, verify that the appropriate county is selected. Change it and click Apply if needed.

3. On the Administrative tab, select the New user accounts check box if not already selected, and then click OK.

4. Click OK again to close the Region window.

If you changed the country setting in step 2, you’ll need to create a new Windows user so it will have the correct

regional settings. You’ll use this Windows ID when you configure replication.

Next, you create a domain Windows account to use with the replication process.

Sage SQL Replicator Setup Guide (Version 18.1) Prepare to implement Sage SQL Replicator

Create a domain Windows account to use with replication

Create a Windows domain account to be used for running replication and add this account to the following roles and

groups:

 The local Administrators group on the accounting server.

 The local Administrators group on the computer where the SQL Server instance is installed.

 The sysadmin server role on the SQL Server instance—see step 17 of the topic "Install SQL Server using the

Microsoft Installer" on page 19.

Upgrade your Sage software

Follow the steps in the Installation Checklist and User’s Guide to prepare for and upgrade to version 18.1. These

documents are posted on the Product Documents web page.

Next, you install SQL Server. The instructions are in the next chapter.

Sage SQL Replicator Setup Guide (Version 18.1) Prepare to implement Sage SQL Replicator

Sage SQL Replicator Setup Guide (Version 18.1) Sage SQL Installer

Install SQL Server

SQL Replicator requires a dedicated instance of SQL Server which you can install using the Sage SQL Installer, a

tool that comes with your Sage software.

In this chapter, you learn:

 How to use the Sage SQL Installer to perform a Basic and an Advanced installation.

 The required configuration settings for the instance when you use the SQL Server Installation Center.

 How to use SQL Server Management Studio to create a backup system administrator for your SQL Server

instance.

Before you proceed with the installation, make decisions on the following:

 Whether to install the SQL Server instance on your Sage 300 Construction and Real Estate accounting server

or on a different server.

 If you will use SQL Server Express or full SQL Server. (See “Microsoft SQL Server edition” on page 6.)

 The file locations for the instance and the data.

Sage SQL Installer

The Sage SQL Installer is automatically installed with Sage 300 Construction and Real Estate. If you are installing

the SQL Server instance on another server (not the accounting server), install the Sage SQL Installer tool on that

server.

WARNING: The Sage SQL Installer installs the SQL Server instance to work correctly with Sage SQL Replicator.

We strongly recommend you use it to install SQL Server.

 To install the Sage SQL Installer on a remote server

1. In the extracted installation files for Sage 300 Construction and Real Estate 18.1, browse to this location:

AccountingServer\Install\Prerequisites\SUSI.

2. Copy the file SUSI.exe to the server on which you will install the SQL Server instance for SQL Replicator.

3. On the computer on which you will install SQL Server, double-click SUSI.exe to install the Sage SQL Installer.

Sage SQL Replicator Setup Guide (Version 18.1) Sage SQL Installer

Install the SQL Server instance for SQL Replicator

1. Go to the computer on which the SQL Server Instance will be installed and log on as a user with local

administrative permissions. During the installation, this Windows user will be added to the SQL Server

sysadmin server role.

2. In the Windows Start menu, go to the Sage Administration group and click Sage SQL Installer.

3. Leave Sage 300 CRE selected and click Next. You might see a warning indicating that your server doesn’t meet

the minimum hardware requirements. We don’t recommend continuing unless your server meets all

requirements.

4. Click Next if you are ready.

5. Select the Automatically download check box if you want the installer to download the latest version of

SQL Express 2016 SP1 with Advanced Tools. If you already have the installation file, browse to its location.

Alternately, if you have installation media for the Standard or Enterprise edition of SQL Server 2016 SP1, select

Microsoft SQL Server 2016 from the dropdown and browse to the installation file.

Sage SQL Replicator Setup Guide (Version 18.1) Sage SQL Installer

6. Click Next. In the next window, select the type of installation.

 Selecting Basic installs SQL Server on the server’s system drive (usually the C: drive).

 Selecting Advanced takes you to Microsoft SQL Servers installation interface so that you can customize

options as you move through the wizard, such as the location of the instance root directory.

7. Click Next, and wait for the file to be downloaded (if you selected that option). The remaining steps depend on

whether you selected the Basic or Advanced installation option.

8. When the installation is complete, you can install Microsoft SQL Server Management Studio as an option—see

“Install SQL Server Management Studio” on page 21.

 Basic installation

1. The next window shows the name for the new instance. By default, this is SAGE300CRE, but you can change it.

2. Enter or generate a password for SQL Server’s sa (administrative) user. In addition to sa, the Windows user

name for the person currently logged in will be added to the sysadmin server role.

WARNING: Be sure to record the sa password and save it for your records.

3. Click Next, and wait while the installer creates and configures the SQL Server instance.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

 Advanced installation

You are taken to Microsoft’s SQL Server’s installation wizard where you can change the prefilled settings in each of

the configuration windows.

The options that are entered are the default settings for a Sage SQL Replicator instance. See “Install SQL Server

using the Microsoft Installer,” on page 14 for detailed information about using the SQL Server Setup wizard.

WARNING: Using the Sage SQL Installer’s Advanced installation option, the server’s default collation is set at

SQL_Latin1_General_CP1_CS_AS. Do not change this setting!

Install SQL Server using the Microsoft Installer

In some cases, you may want to install SQL Server yourself rather than allowing the Sage SQL Installer to install for

you. To install an instance compatible with Sage SQL Replicator, follow these instructions.

NOTE: If you are installing SQL Server using the Advanced option of the Sage SQL Installer, skip to step 4.

1. Log on to the computer on which you will install the SQL Server.

2. In the Windows Start menu, go to the Microsoft SQL Server 2016 group and open SQL Server 2016

Installation Center.

NOTE: If you used the Sage SQL Installer to download SQL Server Express (see step 5 of the topic "Install the SQL

Server instance for SQL Replicator" on page 12), locate the file called SQLEXPRADV_64_ENU.exe and double-

click the file to extract the contents. When the files are extracted, open the folder location, and double-click

Setup.exe to open the SQL Server Installation Center.

3. In the SQL Server Installation Center window, click Installation on the left, and then click the first option,

New SQL Server stand-alone ... in the list. When prompted, provide the location for the installation media.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

4. We strongly recommend that you select the option to Use Microsoft Update to check for updates. Click Next.

5. The next window shows the results of the setup rules check. You can ignore warnings about the firewall. If any

elements of the check failed, you’ll need to fix those issues before you can continue. Click Next.

6. In the Installation Type window, select to perform a new installation of SQL Server. Click Next.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

7. In the License Terms window, select I accept the license terms, and then click Next.

8. In the Feature Selection window, at a minimum you must select these check boxes. You can select others if

you wish.

 Database Engine Services

 SQL Server Replication

 Full-Text and Semantic Extractions for Search.

9. You can change the Instance root directory to another local drive or location on your server if you wish. For

example, if an SSD is available, using that drive for your root directory can improve performance considerably.

10. Click Next.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

11. In the Instance Configuration window, we recommend that you install a Named instance using the name

SAGE300CRE but you can use a different name.

WARNING: Avoid using Default instance. Because your Sage software does not support sharing its instance with

other SQL Server based software solutions, if you use the default instance, you will need to install other SQL based

solutions to different instances.

12. Click Next.

WARNING: The settings in the next window are critical to installing a SQL Server instance that will work with SQL

Replicator. Configure this window exactly as instructed.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

13. In the Server Configuration window:

 On the Service Accounts tab, leave the default options as they are.

 Click the Collation tab. The collation under Database Engine must be set to

SQL_Latin1_General_CP1_CS_AS.

 If you are not using the Advanced option of the Sage SQL Installer, you must click Customize and

select the correct SQL collation.

 If you are using the Advanced option of the Sage SQL Installer, the correct SQL collation is already

selected by default.

WARNING: SQL_Latin1_General_CP1_CS_AS is the required collation setting for Sage SQL Replicator. Note

that the last characters are “CS_AS” which stands for “case-sensitive, accent-sensitive.”

14. When you are finished with the Server Configuration window, click Next.

15. In the Database Engine Configuration window, you must select Mixed Mode. This mode is required for SQL

Replicator.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

16. Enter and confirm a password for the administrator (sa) account.

17. The windows user logged in to the computer will be added automatically as an administrator. Click Add to add

additional administrators. If you use the Canadian or Australian edition of Sage 300 Construction and Real

Estate, add the Windows domain account (as described on page 9) to this list as well.

18. On the Data Directories tab, the default location reflects the Instance root location you selected in step 9. You

can change this to an SSD drive if one is available. This does not have to be a local drive.

Sage SQL Replicator Setup Guide (Version 18.1) Install SQL Server using the Microsoft Installer

19. On the TempDB tab, you can add and remove files to store the tempdb database on an SSD drive if available.

20. Click Next. The next window shows installation progress. When it is finished, click Close.

Sage SQL Replicator Setup Guide (Version 18.1) Important information about SQL Server

Important information about SQL Server

SQL Server is installed as a Windows service. If services related to SQL Server are stopped, SQL Replicator cannot

replicate data.

Install SQL Server Management Studio

SQL Server Management Studio is useful tool for configuring, managing, and administering all components within

SQL Server. It is not installed as part of the database engine installation. We recommend that you download and

install SQL Server Management Studio if it is not already installed on your server.

WARNING: This procedure will require you to restart your accounting server.

1. In your web browser search for “Download SQL Server Management Studio.”

2. Click the link to the Microsoft page and download the program.

3. When the download is finished, double-click the file and follow the steps in the wizard to install SQL Server

Management Studio. When the installation is finished, you’ll be required to restart your server.

Create a backup system administrator

We recommend that you add a backup system administrator to your SQL Server instance. Your backup

administrator should be set up as a user in Sage 300 Construction and Real Estate and linked to a Windows ID. This

user should also have local administrative permissions on the accounting server.

1. When the server has re-started, open SQL Server Management Studio and log in to the Sage 300 Construction

and Real Estate instance.

2. Under Security, right-click Logins and select New Login.

Sage SQL Replicator Setup Guide (Version 18.1) Create a backup system administrator

3. Next to Login name, enter the domain and Windows ID of your backup system administrator.

4. On the Server Roles page, select the sysadmin server role.

5. Click OK to save your changes and close the Login - New window.

6. Repeat these steps if this instance is on a different machine than the accounting server and you created a new

Windows domain account as described on page 9.

Sage SQL Replicator Setup Guide (Version 18.1) Sage SQL Replicator configuration settings and replication

Replicate data into SQL Server

After upgrading your Sage software to the latest version and installing a dedicated SQL Server instance, you are

ready to copy your data to SQL Server. Sage SQL Replicator creates a separate SQL Server database for each

Pervasive database that you replicate.

NOTE: While the replication is in process, users can continue to work in Sage 300 Construction and Real Estate

without interruption.

In this chapter, you learn:

 How to set up the replicator configuration and start the replication process.

 What happens during the replication process.

 How Sage 300 Construction and Real Estate security is passed to the replicated SQL Server data.

Sage SQL Replicator configuration settings and replication

The controls for Sage SQL Replicator are located in System Administrator (in the Sage Administration group).

You must first define the configuration settings for each company before beginning the replication.

1. Log on to the Sage 300 Construction and Real Estate accounting server as a user with local administrative

permissions. The Windows user name must also have the sysadmin role in SQL Server.

NOTE: The user who used Sage SQL Installer to install the SQL Server instance is automatically assigned the

sysadmin role.

2. On the Windows Home page, go to the Sage Administration group and click System Administrator.

3. In the Log On window, you must enter the credentials for a Sage 300 Construction and Real Estate user that

has the Application Administrator role.

4. If the connection window does not open, click Connect.

 In the box next to Server name, select the name of the instance that you created—see page 13.

 For Authentication, select Windows Authentication. Your Windows domain and user name are

automatically entered for the User name.

 Click Connect.

Sage SQL Replicator Setup Guide (Version 18.1) Sage SQL Replicator configuration settings and replication

5. Because you have never replicated your data before, you are prompted to create the configuration database.

Click Yes, and wait while the database is created.

6. When finished, the Replicator Configuration tab shows the list of companies in your Open Company list.

7. If you will access the data through Sage Construction Central for Sage Mobile services, click Link to Mobile.

NOTE: Your mobile website will be available once the initial replication is finished.

8. Locate the first company that you want to replicate and click the row to select it.

Sage SQL Replicator Setup Guide (Version 18.1) About the replication process

9. Use the On-Demand check box to indicate how often the Pervasive and SQL Server databases are

synchronized.

 Clear the On-Demand check box for continuous synchronization between the Pervasive and SQL Server

databases. This ensures up-to-the-minute information in both your on-premises applications and Sage

Mobile solutions. Clear the On-Demand check box for company data folders that you will access through

Sage Mobile solutions.

 Select the On-Demand check box if you want control over the replication frequency. After the first replication

is completed, click Start to replicate again. Or, use the Windows Task Scheduler to specify when the

replication will run again.

10. In the box next to Run-As Account:

 For U.S. editions of Sage 300 Construction and Real Estate, select Default.

 For Canadian and Australian editions of Sage 300 Construction and Real Estate, select Other. Next, enter

the Windows credentials for the user whose region is set to the appropriate country— see “Additional

requirements for Canadian and Australian editions” on page 8.

NOTE: The Run-As Account is company-specific. It does not automatically apply to all the companies in the list.

11. Click Save Configuration if you changed the configuration setting for any company.

12. To begin replication, select the company from the list and click Start. You can click View Status to open the log

see the progress.

13. Repeat these steps for each company you want to replicate. You do not need to wait for the replication for one

company to finish before starting another.

14. If you will access the data through Sage Construction Central for Sage Mobile services, you must follow the

instructions in the Sage Construction Central Setup Guide to grant security permissions and licenses to users,

and deploy your mobile website through Microsoft Azure Cloud Services.

WARNING: If you use the replicated data with Mobile Reports or Mobile Dashboards, when you back up your

Pervasive data, you must also back up the corresponding SQL Server database. You can use the Backup feature

in the System Administrator—see “Backing Up and Restoring” on page 34

About the replication process

The first time that you configure the settings for Sage SQL Replicator, the following databases are added to your

SQL Server instance:

 SageCREGlobalConfiguration

 SageApplicationTelemetry

 Izenda

Sage SQL Replicator Setup Guide (Version 18.1) About the replication process

For each Pervasive company that you replicate, a database named after the company is added to the instance. In

the example below, GoldCoastGroup and TimberlineConstruction are companies replicated into SQL Server.

For each company database, the replication process creates a series of database tables, views, and settings for

security. While the process is in progress, users can continue to work in Sage 300 Construction and Real Estate

applications without interruption. When you replicate the data for the very first time, you cannot access the SQL

Server database until the replication is finished.

The length of time it takes to complete the initial replication depends on the size of your database and your server

hardware. In System Administrator > Replicator Configuration tab, you can click View Status to open the log

see the progress.

Sage SQL Replicator Setup Guide (Version 18.1) About the replication process

When the replication begins, schema information is read from the Pervasive data.

Once all the data from the Pervasive schema is read, the information is saved and a new database is created in SQL

Server.

Next, the replication enters BC (Bulk Copy) mode and copies all the information from the Pervasive schema into the

SQL Server database, beginning with the company’s customizations and security setup, and then continuing with

all the database tables. DC (Dynamic Bulk Copy) validates the schema to see if it has changed before it completes

the bulk copy operation.

Sage SQL Replicator Setup Guide (Version 18.1) About the replication process

Once the bulk copy cycle is finished, row-level security (RLS) is applied. Next, various views are created for

reporting purposes. The views are categorized into four groups:

 Std views (based on the Pervasive DSN table naming option of Standard descriptions).

 Custom views (based on the Pervasive DSN table naming option of Custom descriptions).

 Dict views (based on the Pervasive DSN table naming option of Dictionary names).

 Reports views.

The process creates a Windows scheduled task for nightly database maintenance to optimize performance, such

as recalculating statistics and clearing out temp files. If needed, you can edit the time when it occurs by accessing

the task in the Windows Task Scheduler.

NOTE: Users can access the SQL Server data without interruption during database maintenance.

The initial replication is finished when you see an entry with the text “Database <your database name” is ready” in

the Data column.

Sage SQL Replicator Setup Guide (Version 18.1) About the replication process

If you chose not to use On-Demand replication (see page 25), replication enters SJ (SQL Journal) mode where it

looks for changes by continuously scanning the database.

NOTE: Beginning in version 17.1, each Pervasive database table has two additional fields, Row_ID and

Row_Version. The replication process uses these fields to identify changes to the data.

When replication finds differences in the Row_ID and Row_Version fields since the last scan, it copies the changes

to the SQL Server database. They are reflected in the status log by insertions (I), updates (U), and deletions (D).

WARNING: For data integrity, when you back up your Pervasive data, you must also back up the corresponding

SQL Server database. You can use the Backup feature in the System Administrator—see “Backing Up and

Restoring” on page 34

Sage SQL Replicator Setup Guide (Version 18.1) Restarting replication

To see the background replication processing, open Windows Task Scheduler and click the Sage 300 CRE folder.

Each company folder is represented by its own task in this window. If you stop replication for a company, it changes

the status to disabled.

Restarting replication

The following actions require you to stop and start replication on all company folders. (In System Administrator,

click Stop and then Start for each company folder.)

 Changes to custom descriptions.

 Changes or additions of custom fields.

 Creating a new file, such as an archive file.

 Changes to file or record security.

When replication starts, it reads and stores the database schema. Changes to the schema after replication has

started are not picked up until the next time replicator is re-started. This could result in some columns or tables being

skipped by replication.

NOTE: You need to stop and restart the replication when you upgrade your Sage software.

Sage SQL Replicator Setup Guide (Version 18.1) Security

Security

In order to allow users access to the SQL Server data through reports or Mobile solutions, you must grant the

appropriate task permissions in Sage 300 Construction and Real Estate Security Administration.

The replication process enforces Sage 300 Construction and Real Estate security settings in the SQL Server data.

Any user, role, task, company, and record permissions established in Sage 300 Construction and Real Estate are

respected when users access the SQL Server data through Sage Construction Central, reports, or in any other

manner.

The initial replication process copies the security settings into the SQL Server instance. When you make changes

to the security setup afterwards, such as adding new users, you must manually synchronize the changes with the

SQL Server data. In System Administrator, on the Replicator Configuration tab, click Sync Security.

Sage SQL Replicator Setup Guide (Version 18.1) Security

Sage 300 Construction and Real Estate user names and roles, along with their task and company permissions are

copied to each SQL Server database.

For each Sage 300 Construction and Real Estate user name, a SQL Server login is created with SQL Server

Authentication. The passwords (in Sage and in SQL Server) are automatically synchronized when the user logs into

Sage 300 Construction and Real Estate for the first time after replication. This lets you use your Sage 300

Construction and Real Estate credentials whenever you access the SQL Server data in on-premises Sage and third

party applications such as Microsoft Excel or Access.

NOTE: User names and passwords are case-sensitive.

Sage SQL Replicator Setup Guide (Version 18.1) Repair Sage-managed SQL system accounts

Anyone who accesses the SQL Server data through Sage Construction Central must have a Windows user name

associated with their Sage 300 Construction and Real Estate user setup. This is because Sage Construction Central

requires Windows authentication—you log on using your Windows credentials.

Repair Sage-managed SQL system accounts

Sage creates several different SQL system accounts to manage the functions used by SQL Replicator and Mobile

Intelligence solutions. In some cases, these Sage-managed SQL accounts may become out of sync with SQL

Replicator and the Mobile features, causing the processes to stop.

If the Sage Worker Engine stops and you are unable to start it or you are directed by Sage Support, click the Repair

Services button in the System Administrator to repair the Sage-managed SQL system accounts used by SQL

Replicator, Mobile Reports, and Mobile Dashboards.

Sage SQL Replicator Setup Guide (Version 18.1) Backing up

Backing Up and Restoring

If you implement SQL Replicator, you’ll need to use the new System Administrator utility to configure and run your

regular backups. Even if you don’t implement SQL Replicator at this time, we recommend that you use System

Administrator’s backup and restore tools after upgrading to version 17.1 or later. When you use System

Administrator to back up your data, the following areas are included in a single, zipped backup file:

 Files in the Program Files (x86)\Sage\Timberline Office\9.5 folder, such as custom reports, inquiries, formula

files, and application settings. The WinInst folder is not included.

 SQL Server databases generated by SQL Replicator, including the SageCREGlobalConfiguration database.

 Certain registry keys from numerous locations on your server that store system preferences are included.

 You can include files from other network locations with your backups; for example, a shared folder for

attachment files.

These items are essential to successfully restoring to a new installation of Sage 300 Construction and Real Estate.

WARNING: If you use Sage SQL Replicator to replicate your data and you need to back up your data, you must

use the System Administrator backup utility. Do not use SQL Server Management Studio to back up and restore

your databases.

Backing up

SQL Replicator does more than simply copy your data into SQL Server. Your databases contain production data

related to Sage Construction Central and attachment files in addition to the Pervasive data, and this information is

required to remain synchronized in order to restore successfully from a backup.

 To back up your data

1. In System Administrator, click Backup.

2. Select the check box next to the folders you want to back up.

Backing up Mobile Projects

3. By default, the Files selection is set to All. This includes files such as print files or attachments. You can change

the selection to Data only to exclude these files for a smaller backup file.

NOTE: If you choose Data only, system files in the Program Files (x86)\Sage\Timberline Office\9.5 folder are

still included with the backup.

4. To add additional folders to include in the backup, click Add Folder and select the folder.

Sage SQL Replicator Setup Guide (Version 18.1) Backing up

5. Click Browse to select backup location.

6. For the time of day to run regular backups, select a time when the data will not be in use.

NOTE: When the backup starts, the replication tasks are paused. They resume when the backup is complete.

7. Click Save Configuration to preserve your changes.

8. Click Back Up Now to create an immediate backup.

 To view the backup results

After a backup is complete, the backup folder contains the following folders and files.

 The info.txt and log.txt files to check for messages related to the backup. These files indicate the time the

backup was created and the contents.

 The CompanyData folder contains the Pervasive data, including Purchasing, Inventory, and

Service Management.

 The ProgramData folder contains your Sage 300 Construction and Real Estate program files.

 The SQL Databases folder contains the .mdb and .ldb files associated with your replicated data, as well as the

SageCREGlobalConfiguration database. The configuration database contains setup information that applies to

your installation.

Restoring from a backup Mobile Projects

NOTE: As with any backup, we recommend that you validate and test the backup to ensure that the process is

working properly and that you have usable backups. Environmental issues and files in use can affect the integrity of

a backup.

Restoring from a backup

WARNINGS:

• Only persons familiar with the Pervasive and SQL Server file systems should restore Sage 300 Construction and

Real Estate data from backup. Incorrectly restoring data can cause data integrity issues.

• If you share Pervasive files between folders—for example, a single General Ledger file specified in

File Locations for multiple company folders—you must restore all folders that share the data files.

Depend on the scenario, the steps for restoring your data will differ. Are you restoring the data:

 To replace the current live data?

 To a different location for testing or archiving?

 For data and server migration?

The steps for each scenario are documented in article 86066 of the Sage Support Knowledgebase.