sage sql gateway with executive dashboard installation and

Sage 300 Construction and Real Estate

Sage SQL Gateway with Executive Dashboard

Installation and Reference Guide

This is a publication of Sage Software, Inc.

Copyright © 2015 Sage Software, Inc. All rights reserved.

Sage, the Sage logos, and the Sage product and service names mentioned herein are registered trademarks or trademarks of Sage Software, Inc. or its affiliated entities. Microsoft® SQL Server®, Windows Vista, and the Windows Vista logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. SAP® Crystal Reports and SAP® Crystal Dashboard Design are the trademarks or registered trademarks of SAP AG in Germany and in several other countries. All other trademarks are the property of their respective owners.

Your use of this document and the Sage product(s) described herein is governed by the terms and conditions of 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. Except as expressly stated in those terms and conditions, the information in this document is provided by Sage "AS IS" and Sage disclaims all express, implied or statutory warranties of any kind, including but not limited to the warranties of merchantability, fitness for a particular purpose or of non-infringement. No implied license is granted to you under any Sage intellectual property or trade secret right. Sage reserves the right to revise, supplement or remove information in this document at any time without notice to you or others.

Revision Date: March 25, 2015

Sage SQL Gateway Installation and Reference Guide Contents

© 2015 Sage Software, Inc. 3

Contents

Welcome to Sage SQL Gateway

About this guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Installation and Configuration

Review system requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Important Information about SQL Server 2012 and .NET Framework 3.5 . . . . . . . . . . . 8Prepare servers for Sage SQL Gateway . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11Install Sage SQL Gateway and the Executive Dashboard Add-on . . . . . . . . . . . . . . . . 14

Replication and Security

Replicate the backup database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Replicate production data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27Set Up Security in Sage SQL Gateway . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29Install Executive Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38What Happens During Replication? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Appendix

Supported Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Time Required for Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45System Specifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46About SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47To Install SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48Checklist: Implementing Configuration 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56Checklist: Implementing Configuration 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Checklist: Implementing Configuration 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Contents Sage 300 Construction and Real Estate

4 © 2015 Sage Software, Inc.

Sage SQL Gateway Installation and Reference Guide Welcome to Sage SQL Gateway

© 2015 Sage Software, In 5

Welcome to Sage SQL Gateway

Sage SQL Gateway lets you take advantage of the reporting and business-intelligence capabilities provided by Microsoft® SQL Server®, by copying your Sage 300 Construction and Real Estate data from the Pervasive database management system into a SQL Server data warehouse.

With Sage SQL Gateway, you can also install Sage Executive Dashboard, which lets your leadership team quickly view key performance indicator graphs and summaries at a glance each day.

If you have more than one Sage 300 Construction and Real Estate company folder, you can merge your data folders into a single data warehouse in SQL Server, enabling you to summarize data from all company folders in Executive Dashbaord and any reports you develop.

NOTE: Sage SQL Gateway does not replicate Estimating, Purchasing, Inventory, or Service Management data. If you use these Sage 300 Construction and Real Estate modules, you will not be able to include data from these areas in your data warehouse.

About this guide

This reference guide provides the setup and configuration information you need to implement Sage SQL Gateway to work with Executive Dashboard. The material applies to system administrators or information technology specialists who will complete the server configuration tasks.

“Installation and Configuration” takes you through the installation and configuration steps. In some situations, you might need to refer to additional configuration details, which are provided in the Appendix starting on page 43.

“Replication and Security” then walks you through the steps to test replication.

The Getting Started with Report Development document (available in the Technical Support Knowledgebase) provides information to help your report designers get started developing reports on the data warehouse.

Welcome to Sage SQL Gateway Sage 300 Construction and Real Estate


Where to Get Additional Information

Technical Assistance

With all Sage Business Care Plans, you can take advantage of the Sage Customer Portal. Log on at http://www.sagecre.com/customerlogon to search the Technical Support Knowledgebase, which contains the latest information about application features.

Depending on your Sage Business Care Plan, you might also have access to phone support. See Technical Assistance for Sage 300 Construction and Real Estate for more information about assistance.

Training Opportunities

Sage University offers online training sessions to help you get the most out of your Sage software. Visit www.sageu.com or email us at [email protected] to learn about training classes that Sage adds throughout the year. You can also call us at 1-877-724-3285. Contact your business partner to learn about local training options.

http://www.sagecre.com/customerlogon

http://cdn.na.sage.com/sagemail/zCRE/Technical_Assistance_S300CRE.pdf

http://www.sageu.com

Sage SQL Gateway Installation and Reference Guide Installation and Configuration


Installation and Configuration

This section guides you through the installation and configuration of Sage SQL Gateway.

Review system requirements

Server Configurations

Review the configurations listed under “Supported Configurations” on page 43. Verify that your planned implementation meets one of the options shown.

System Specifications

Full system requirements for Sage SQL Gateway are listed in “System Specifications” on page 46. If you install Sage SQL Gateway on your accounting server, this will increase the workload on your hardware processor and RAM. Review the requirements and verify that your systems have the necessary resources.

Microsoft SQL Server

Sage SQL Gateway requires a SQL Server instance configured properly to perform replications. The Sage SQL Gateway installation wizard can install and configure a Microsoft® SQL Express 2012 instance for you. We recommend you select this option if SQL Express fits your needs.

WARNING: With SQL Express, your data warehouse (and any other databases on the instance) must be no more than 10 gigabytes, and limitations on CPU and RAM usage apply. See “About SQL Server” on page 47 for more information, or if you plan to install SQL Server independently of Sage SQL Gateway.

Task Page

❑ Review system requirements 7

❑ Prepare servers for Sage SQL Gateway 11

❑ Install Sage SQL Gateway and the Executive Dashboard Add-on 14

Sage SQL Gateway Sage 300 Construction and Real Estate


Important Information about SQL Server 2012

and .NET Framework 3.5

SQL Server 2012 requires .NET version 3.5 with SP1. Depending on your operating system and configuration, you might need to enable this version on the computer on which you’ll install SQL Server.

Windows Server 2012

FOR MORE INFORMATION: You can find more information in the Microsoft article: Enable .NET Framework 3.5 in Windows Server 2012. If this link doesn’t work, go to Technet.microsoft.com and search for “Enable .NET 3.5 on Server 2012.”

On Windows Server 2012, .NET 3.5 is installed but not enabled. To enable it, in Server Manager, click Manage > Add Roles and Features. Select Role-based or feature-based installation.

Select the check box next to .Net Framework 3.5 Features, and complete the wizard.

http://technet.microsoft.com/en-us/library/dn482071.aspx

Technet.microsoft.com



Windows Server 2008 R2

FOR MORE INFORMATION: You can find more detailed information in the Microsft article: Enable .NET Framework 3.5 in Windows Server 2008 R2. If this link doesn’t work, go to Technet.microsoft.com and search for “Enable .NET 3.5 on Server 2008 R2.”

On Windows Server 2008 R2, .NET 3.5 is installed and enabled by default, but it is an optional feature and might have been disabled on your system.

In the Initial Configuration Tasks window, look for the .NET 3.5.1 listing next to Customers This Server > Features. If it is not present, click Add features. Select the chck box for .NET Framework 3.5.1 and continue through the wizard.

http://blogs.msdn.com/b/sqlblog/archive/2010/01/08/how-to-install-net-framework-3-5-sp1-on-windows-server-2008-r2-environments.aspx




Windows 8

FOR MORE INFORMATION: You can find more detailed information in the Microsft article: Installing the .NET Framework 3.5 on Windows 8 or 8.1. If this link doesn’t work, go to Technet.microsoft.com and search for “Enable .NET 3.5 on Windows 8.”

On Windows 8, .NET 3.5 is isntalled but not enabled. To enable it, open the Control Panel and click Programs and Features Click Turn Windows Features on or off, and select the check box for .NET Framework 3.5.

Windows 7

FOR MORE INFORMATION: You can find more detailed information in the Microsft article: Enable .NET 3.5 in Windows 7. If this link doesn’t work, go to Technet.microsoft.com and search for “Enable .NET 3.5 in Windows 7.”

On Windows 8, .NET 3.5 is isntalled but not enabled. To enable it, open the Control Panel and click Programs and Features Click Turn Windows Features on or off, and select the check box for .NET Framework 3.5.

http://msdn.microsoft.com/en-us/library/hh506443%28v=vs.110%29.aspx

http://msdn.microsoft.com/en-us/library/hh506443%28v=vs.110%29.aspx


https://social.msdn.microsoft.com/Forums/vstudio/en-US/ad6a41e8-8b3e-4d45-9a7a-b2626f0089e4/enable-net-35-in-windows-7?forum=netfxsetup

https://social.msdn.microsoft.com/Forums/vstudio/en-US/ad6a41e8-8b3e-4d45-9a7a-b2626f0089e4/enable-net-35-in-windows-7?forum=netfxsetup




Prepare servers for Sage SQL Gateway

Sage 300 Construction and Real Estate Tasks

Complete the following tasks in Sage 300 Construction and Real Estate. Some of these steps require all users to be logged out of the software for an extended period. Also, you’ll need administrative permissions in the software to set up the necessary security if you use the security feature in Sage 300 Construction and Real Estate.

Upgrade Sage 300 Construction and Real Estate if necessary

Determine whether you need to upgrade Sage 300 Construction and Real Estate. See page 46 for the minimum supported version.

Upgrade your server, data folders, and workstations if necessary. (Or, you can set up a testing environment for Sage SQL Gateway if you are not ready to upgrade yet.)

Make a backup of each company folder you will replicate.

You’ll use these backups for testing.

Configure security

If Security Administration has been enabled in Sage 300 Construction and Real Estate, complete the following steps.

1. Add the backup folders to the Open Company window. (In Desktop, select File > Company > Open Company and click Specify Folders.)

FOR MORE INFORMATION: See Sage 300 Construction and Real Estate’s Help topics on Security Administration for details about setting up users and roles.

2. In Sage Desktop, select Tools > Security Administration and create a security user to be used with Sage SQL Gateway.

a. Clear the User must change password check box.

b. Select the Password never expires check box.

c. Do not associate this account with a Windows user.

d. Save the user.

NOTE: If you will replicate more than one company folder, we recommend that you use a different Gateway user for each folder—but this is not required. Continue setting up users as needed.



3. Create the role to be used, with the following settings:

a. On the Tasks tab, select the ODBC > Read Access check box. No further task permissions are needed for this role.

b. To avoid incomplete data replication, do not clear any check boxes under Read Access.

4. Open the user or users you just created, and assign each to the Gateway role you established in step 3.

5. If you use record or file security, we recommend that you enable access to all files and records for this user to avoid incomplete data replication.

6. Enable this user to access all company folders to be included in replication.

Run File Doctor on the backup folders.

To do this, open Sage Desktop and go to Common Tasks > Tools > File Doctor. Select the Fix and compact bad files check box, and then click Start.

Sage SQL Gateway server tasks

Complete these additional tasks on the server on which you will install Sage SQL Gateway. (This could be the same as the accounting server or a different server.) You’ll need administrative permissions.

1. If you are not installing Sage SQL Gateway on the accounting server, Verify that you are using one of the configurations shown on pages 43 to 44.

If necessary, verify that the workstation edition of Sage 300 Construction and Real Estate is installed and upgraded to a supported version.

2. Apply all available updates to the operating system.

3. Enable the TCP Client Protocol with these firewall exclusions:

Incoming TCP SQL Server and SQL Browser traffic must be allowed.

Outgoing SMTP port traffic if you will set up email notifications.

NOTE: Instructions and the specific settings to configure will differ based on your firewall application and current settings.



Microsoft SQL Server machine tasks

Complete these tasks on the machine on which you will install SQL Server. (This could be the same as the accounting and Sage SQL Gateway servers or a different server.

1. Verify that the .NET Framework 3.5 with Service Pack 1 is installed and enabled. See “Important Information about SQL Server 2012 and .NET Framework 3.5” on page 8.

2. Verify that the operating system is at a supported version for the SQL Server version you will use. For Windows 7 and Windows Server 2008 R2, Service Pack 1 is required for SQL Server 2012.

3. Establish firewall exclusions to allow incoming TCP SQL Server and SQL Browser traffic.

NOTE: Instructions and the specific settings to configure will differ based on your firewall application and current settings.



Install Sage SQL Gateway and the

Executive Dashboard Add-on

Important Information

If you have an earlier installation of Sage SQL Gateway, you must uninstall all components before installing the new version. Open Control Panel > Uninstall a Program and remove Sage SQL Gateway and any associated plugins (such as the Sage 300 Construction and Real Estate 12.1 Plugin for Sage SQL Gateway).

The Technical Support Knowledgebase article for Sage SQL Gateway contains 3 download links. We recommend that you select an option that includes SQL Express 2012 if SQL Express suits your needs. All of the links include Sage SQL Gateway and the Executive Dashboard add-on.

NOTE: The Executive Dashboard add-on is required if you plan to use Executive Dashboard. The add-on is installed on the server and only needs to be installed once. Executive Dashboard itself must be installed on each workstation that will access it. See “Install Executive Dashboard” on page 38.

The options are:

Sage SQL Gateway with Microsoft SQL Express 64-bit (recommended). This option installs a SQL Express instance for you, configured exactly as it needs to be for Sage SQL Gateway.

Sage SQL Gateway with Microsoft SQL Express 32-bit (recommended if you use a 32-bit operating system). This option installs a 32-bit SQL Express instance for you, configured exactly as it needs to be for Sage SQL Gateway.

Sage SQL Gateway Standalone. This option does not install Microsoft SQL Express for you. Select this option only if you plan to install and configure a separate instance of SQL Server on your own, using the instructions that start on page 48.

WARNING: If you plan to install SQL Server yourself, see “To Install SQL Server” on page 48 for important instructions.



Install Sage SQL Gateway and the Executive Dashboard Add-on

NOTE: If you plan to use Executive Dashboard, you’ll need to install the add-on and enable it on the server. Later you’ll install Executive Dashboard on individual workstations.

1. At the Sage SQL Gateway server, download the appropriate installation file using the link in the Technical Support Knowledgebase article.

2. When the download is complete, double-click the executable file.

3. We recommend that you select Install Executive Dashboard Add-on, as well as the Sage SQL Gateway option. If you selected a download that includes SQL Express installation, you see that option as well. You can install SQL Express and Sage SQL Gateway in either order.

TIP: If you are installing Sage SQL Gateway on a different machine than SQL Server (as shown in “Configuration 2” on page 43 or “Configuration 3” on page 44, you can run the installation selecting only Install SQL Server 2012 Express on the SQL Server machine.

4. Follow the prompts to install the software.

If .NET Framework 4.5.1 is not already present, it is installed as well. You’ll see the Setup successful message when the installation is finished.

You might also need to enable .NET Version 3.5. See “Important Information about SQL Server 2012 and .NET Framework 3.5” on page 8.



NOTE: You might be required to restart the server after installing Sage SQL Gateway.

5. Apply all available updates to the operating system, SQL Server, and .NET. See “Important Information about Automatic Updates from Microsoft” on page 16.

Install SQL Server or SQL Express

If you did not elect to allow the Sage SQL Gateway installation wizard to install SQL Express for you, make sure that the SQL Server instance you will use with Sage SQL Gateway is installed and configured properly. Following the instructions starting on page 48 carefully.

Important Information about Automatic Updates from Microsoft

You should apply all security updates to SQL Server as they become available, but you’ll need to time these so they don’t interfere with replication. Sage SQL Gateway is installed as a service, which in turn depends on services installed with SQL Server or SQL Express. If the SQL Server Browser or SQL Server (Gateway Instance) services are stopped, Sage SQL Gateway cannot replicate data.

If Windows Update is allowed to update SQL Server or any program on which it depends (such as the operating system or .NET), the process stops all SQL Server services in order to install the updates. This causes the Sage SQL Gateway service to be stopped.

Once the SQL Server updates are installed, Windows Update restarts the SQL Server services—but it does not restart dependent third-party services such as Sage SQL Gateway. The next scheduled replication will fail if this happens.

To avoid this issue, set Windows Update to download updates but not install them. This lets you choose when to install updates, so that you can re-start the services as needed. The services that must be running are: SQL Server Browser, SQL Server (Gateway Instance), and Sage SQL Gateway.

To set this option:

1. From the Control Panel, open Windows Update.

2. Click Change Settings.

3. Select Download updates but let me choose whether to install them.

4. Click OK.



Configure Sage SQL Gateway

1. Open Sage SQL Gateway (Start > All Programs > Sage > Sage SQL Gateway > Sage SQL Gateway Administration Utility).

2. In the Sage SQL Gateway Login window, enter the name of the SQL Server instance that you will use to store your data warehouse.

If you selected the automated SQL Express installation, the instance name is ServerName\SAGESQLGATEWAY. Use the format shown here.

3. Select Use my Windows credentials if you selected the automated SQL Express installation, or if your Windows login is assigned to the SQL Server’s sysadmin server role for the instance. (If you do not select this check box, you will be prompted for credentials when you click Log On.)

4. Click Log On, and click Yes when you see this message.

The configuration process creates a configuration database, which will hold information about your data warehouses and other Sage SQL Gateway settings. It also creates a SQL Server server login to be used by the Sage SQL Gateway service.

5. When you see this message, click Yes to enable the Executive Dashboard add-on.

TIP: Enabling the add-on requires that any pre-existing data warehouses be deleted. To avoid a delay from this issue, we recommend enabling the add-on when you first install Sage SQL Gateway.



6. Because you don’t yet have a data warehouse, the Add Warehouse window opens. Enter a name for the data warehouse. (This might include data merged from more than one Sage 300 Construction and Real Estate data folder.

The first time you replicate data, we recommend that you use a backup of your data folders to establish timing and address any errors.

7. Clear the Scheduled for replication check box while you test the replication. You can enable scheduling once you finish testing.

8. Select both Classic Views and Modern Views while testing, to ensure the most thorough troubleshooting. (These options are explained more fully in the section “When configuring the data warehouse, two options are available for the transformed data views: Modern or Classic. You can select both options.” on page 8.)

NOTE: If you clear the check boxes for both Classic Views and Modern Views, you can still run “replication” but your data warehouse will contain only some administrative tables such as users and security roles.

9. Click Next. The Add Data Source window opens. A data source is a Sage 300 Construction and Real Estate company folder.

10. Enter the name of the first company you want to replicate.

NOTE: The Name you enter here is used to identify entries from this data folder in the data warehouse. If you will replicate more than one folder, we recommend you enter a meaningful name such as the company’s name in Sage 300 Construction and Real Estate.



11. Enter the Folder Location for the data folder, exactly as it appears in the Sage 300 Construction and Real Estate Open Company window. For example, if it the folder is listed as a UNC path in the Open Company window, it must also be listed this way in the Add Data Source window.

12. If you use security, enter the user name and password for the user that you set up when preparing the accounting server (page 11).

13. Click Verify. This verifies that your data source is accessible with the credentials you supplied, and also that enough space is available on the hard drive to extract your company folder to the server.

14. Click Add.

15. If you need to add more data sources to this warehouse, click Add again and continue until you are finished.

16. Notice that the Exclusions column indicates Yes when you first add a data source. By default, certain security and log tables are exluded. These are extremely large tables that can significantly impact the time needed for replication. However, you can choose to include them if you wish.

Paths to data must match exactly



17. To change the exclusions list, click Exclude Tables. Select or clear check boxes based on the tables you want to exclude. (If you select a box the table will be excluded.)

18. When you are finished, click OK in the Table Exclusions window. Notice that the changes you make to exclusions apply only to the data source that is selected when you click Exclude Tables.

In this window, the exclusions have been removed from the first data source, but not from the second one—hence the different indications in the Exclusions column.



19. If you want Sage SQL Gateway to send automated email messages regarding the status of nightly data replication, click Notifications.

This window lets you set up email notifications so that individuals can receive messages pertaining to the replication—whether it completed successfully, completed with warnings, or failed due to errors.

20. Click Configure to enter outgoing email server details. The From address is not monitored, so use an address such as [email protected]. The information you enter in this window is applied to all data warehouses.

21. Enter the information and click OK.

22. Click Add to set up individual recipients.



TIP: The recipient information is applied only to the data warehouse selected at the top of the window.

23. In the Add Recipient window, select the types of messages to be sent. Warnings do not cause the replication to fail, but the contain information about conditions that might affect reports. Errors cause the replication to fail.

NOTE: The Security panel lets you grant accss to users by role. As with email notifications, security settings are applied per data warehouse. Some settings depend upon having a successful replication in place, so wait to set them up until the production warehouse is in place.

If you have more than one data warehouse, email recipients must be set up for each one.



About Executive Dashboard

Before members of your organization can connect to your data warehouse using Executive Dashboard, you need the replicated data in your warehouse. Although you can install Executive Dashboard now, you won’t be able to test the connection to the data yet.

The next section explains the steps for replicating data and installing Executive Dashboard.

Sage SQL Gateway with Sage Executive Dashboard Replication and Security


Replication and Security

Sage SQL Gateway is configured, and you’re ready to run a test replication. This section walks you through the following steps.

Replicate the backup database

TIP: The amount of time required for a replication depends on several factors. See “Time Required for Replication” on page 45 for more information.

The first time you replicated a database, all data is copied. Subsequent replications copy only tables that have changed since the last successful replication, so the time required for ongoing nightly replication will be shorter.

1. In the Sage SQL Gateway Administration window, click Status on the left.

2. Verify that the correct Warehouse is displayed in the dropdown, and then click Replicate Now.

Task Page

❑ Replicate the backup database 25

❑ Replicate production data 27

❑ Set Up Security in Sage SQL Gateway 29

❑ Install Executive Dashboard 38

Replication and Security Sage SQL Gateway


3. Wait for the replication to finish. (This could take a significant amount of time.) If errors or warnings are encountered during replication, they are reported in the Sage SQL Gateway event log.

This is an example of a replication log showing an unsuccessful replication. In this example, replication failed because the data folder had not been upgraded to a supported version of Sage 300 Construction and Real Estate.

This is an example of the replication log showing the messages when replication is successful. Once you have a successful replication, you’re ready to configure the utility for live data replication.



A note about Custom Descriptions

If your Sage 300 Construction and Real Estate data folders use custom descriptions for any of the fields shown in this table, you might need to take steps to ensure that your data is summarized correctly. Open File > Company Settings > Custom Descriptions and check each of these fields.

If you find that these fields have been customized, you can obtain a custom script from the Technical Support Knowledgebase that will correct any discrepancies. You’ll need to edit the script and place it in a specific location so that it runs after each replication.

FOR MORE INFORMATION: To find the script and instructions for applying it, see the Technical Support Knowledgebase article “How do I update Gateway so Custom Descriptions are recognized on Dashboard Reports?”

Replicate production data

Once you have verified that the test replication was successful, you’ll re-configure Sage SQL Gateway to replicate your production data. You’ll need to run File Doctor on your production data folder, which could take a long time, depending on the size of your data folders.

1. Make sure that all users are logged out of Sage 300 Construction and Real Estate.

2. Run File Doctor on each of the company folders you will replicate. (Make sure you have a backup of your data before running File Doctor.)

3. Return to the Sage SQL Gateway Administration window. You can either add a new warehouse to point to your production data folder, as shown here, or you can change the data sources within the current warehouse.

Custom description Default value

AP Retainage retainage

AR Adjustment adjustment

AR Cash receipt cash receipt

AR Customer customer

AR Finance Charge finance charge

AR Invoice invoice

TX Tax tax

https://support.na.sage.com/selfservice/viewdocument.do?externalId=44402

https://support.na.sage.com/selfservice/viewdocument.do?externalId=44402



4. Click Add Warehouse.

5. Name the warehouse and schedule it for daily replication if needed.

WARNING: Verify that the timing of Windows updates, backups, and other server maintenance does not conflict with the replication time frame. See “Important Information about Automatic Updates from Microsoft” on page 16.

6. Indicate the types of views you want to see in the data warehouse.

The Modern Views check box must be selected if you plan to use Executive Dashboard. It is selected by default.

You can also select the Classic Views check box. These options are explained more fully in “Classic and Modern Views” on page 40.

7. Click Next.

8. Add the data sources.



9. Sage SQL Gateway can send automated email messages regarding the status of nightly data replication to recipients you set up. See page 17 for details if you have not already set this up.

The next section explains the security feature so you can grant access to the data warehouse to others in your organization.

Set Up Security in Sage SQL Gateway

Sage SQL Gateway’s Security tab lets you set up Users and Roles corresponding to individuals in your organization who will access the data. You then assign Features to roles and assign roles to users.

The roles you see in this window depend on whether you have enabled the Executive Dashboard add-on. The list also varies based on whether you have successfully replicated your data warehouse, and whether your Sage 300 Construction and Real Estate folder has Accounts Receivable, Job Cost, or Property Management data.

In this example, the Executive Dashboard add-on has been enabled and the database has been replicated.

What are Features?

The features selected for a role determine what kind of information is available to users assigned to the role.

Report viewers and designers

Report viewers are individuals who can view reports based on the data warehouse. Report designers are individuals who can make necessary modifications to the data



warehouse in SQL Server as part of their report design work. These two types of individuals need different levels of access to the data warehouse.

Report designers must make some specific modifications to the data warehouse in SQL Server as part of their report development. A special role is available for you to assign to report designers to ensure that they have the necessary permissions.

Plan roles to assign to report viewers

You’ll set up report viewers and designers as users in Sage SQL Gateway and assign them to roles. If you enabled the Executive Dashboard add-on, you’ll see some pre-set roles corresponding to related reports. These roles are appropriate for report viewers—and you can create others in addition.

If you did not enable Executive Dashboard, roles specifically for report viewers are not present. You’ll need to strategize with your team about what these roles should include and set them up in Sage SQL Gateway.

NOTE: Users and roles are set up for individual warehouses. Roles and users that you set up in your first warehouse will not be able to see data from your second warehouse unless you add them to it.

Add users and roles to Sage SQL Gateway

Replicate your data at least once before you start this process. You will need some elements of the data warehouse to be in place in order to complete the setup.

1. In the Sage SQL Gateway Administration window, click Security.

2. On the Users tab, click Add. Notice that you can select either Windows Authentication or SQL Authentication.

WARNING: If you select SQL Authentication and the user name you enter in this window matches an existing server login in the SQL Server instance, the password you enter re-sets the password for that SQL Server login (including all databases and objects to which it has access). Be sure to inform the user of the new password, as it will be needed to access any component of the instance.



3. Enter a user name. If you are using SQL Authentication, enter a password. The check box allows you to make this user active. (If you clear the check box, the user is inactive.)

4. Click Add. The user now appears in the list.

5. If the user will be a report designer, select the SageReportDesigner check box. (See “What is the SageReportDesigner Role?” on page 33 for details about this role.)

6. If you enabled the Executive Dashboard add-on, you see several other roles. For report viewers, select one of the additional roles, or you can create new ones.

You don’t need to assign report designers to any of the additional roles, as they already have the corresponding access.



7. To add custom roles, click the Roles tab, and then click Add.

8. Enter a role name and description. The check box indicates that the role is active. (If you clear the check box, the role will be inactive.)

9. Click the Features tab.

If you have replicated your data, you can assign individual features to roles.



What is the SageReportDesigner Role?

The ability to see all default views in the data warehouse (meaning all views created by Sage SQL Gateway).

Read-only permission to all views under the schema corresponding to the Classic or Modern setting.

Read-only permission to the Security schema’s LastExtraction view.

Access to the structure—but not the data—of the tables in the appropriate schema. This allows report designers to evaluate table columns for possible indexing, but not to run reports directly on the tables.

Permission to create custom schemas, views, and indexes in the data warehouse, as well as new tables within custom schemas.

Which features should I assign?

If you enabled the Executive Dashboard add-on, the list of features in a replicated warehouse contains the roles and features shown in the table on page 34. These correspond to the reports a user will be able to see in Executive Dashboard. You can use the roles provided, or you can create custom roles and assign features in any combination.

When viewing Executive Dashboard reports, in some cases you can click a report to see a more detailed breakdown of summaries. For example, if you view an AP open invoice summary, you can click it once to see the vendor summaries, and click a second time to see invoice details.

However, if you configure a role to include the AP Open Invoice Summary Bar Graph report, but you don’t include the reports at the second or third levels, users assigned to that role will not be able to see the next level.

This table indicates the “drill-down” reports (those that you can click to see a more detailed view) and the “drill-up” reports (those that direct to the report when clicked). For example, the AP Open Invoice Vendor Summary shown on the previous page has one drill-down report, AP Open Invoice Detail. It also has several drill-up reports, including AP Open Invoice Summary Report.



Role and Report Description

Accounts Payable role

AP Open Invoice Detail

This report shows the amount your company owes a specific vendor in each 30-day “aging” period. It also includes a column showing the remaining retainage payable.

Drill-up: AP Open Invoice Vendor Summary

NOTE: This report derives parameter values from the drill-up report. If you place this report directly on a dashboard, by default it shows no data because the Vendor parameter is empty.

AP Open Invoice Summary Bar Graph

This bar graph shows the dollar amount (not percentage) your company owes vendors in 30-day increments.

Drill-down:

AP Open Invoice Vendor Summary


AP Open Invoice Summary Pie Chart

This pie chart shows the percentage of the total dollar amount owed vendors for each 30-day time period.

Drill-down



AP Invoice Summary Snapshot

This report shows the dollar amount and percentage that your company owes vendors in each 30-day “aging” period.

Drill-down




This report shows the amount your company owes each of your vendors in each 30-day “aging” period as well as the remaining retainage payable.

Drill-up:

AP Open Invoice Summary Bar Graph

AP Open Invoice Summary Pie Chart

AP Open Invoice Summary Snapshot

Drill-down: AP Open Invoice Detail



Accounts Receivable Role

This role and reports appear only if you have Accounts Receivable entries in your data folder.

AR Aging Customer Summary

This report shows the amount due from each of your customers in each 30-day “aging” period as well as remaining retainage due.

Drill-up:

AR Aging Summary Bar Graph

AR Aging Summary Pie Chart

AR Aging Summary Snapshot

Drill-down: AR Aging Detail

AR Aging Detail This report shows the amount due from a specific customer in each 30-day “aging” period as well as remaining retainage due.

Drill-up: AR Aging Customer Summary

AR Aging Summary Bar Graph

This bar graph shows the dollar amounts past due from your customers in 30-day increments.

Drill-down: AR Aging Customer Summary

AR Aging Summary Pie Chart

This pie chart shows the dollar amounts past due from your customers for each 30-day time period.


AR Aging Summary Snapshot

This report shows the dollar amount and percentage past due from your customers in each 30-day “aging” period.


AR Receivable Aging Trend Percent

This graph shows the trend over the past several months of past due amounts owed by your customers as percentages of the total owed. Each line represents a 30-day “aging” period.


AR Receivable Trend Value

This graph shows the trend over the past several months of past due amounts owed by your customers. Each line represents a 30-day “aging” period.


Cash Management Role

CM Bank Balances This report shows the current register balance as posted in Cash Management. This report does not have drill-down or drill-up options.




Job Cost Role

This role and reports appear only if you have Job Cost entries in your data folder.

JC Cost Code Breakdown

This report shows estimated and actual costs for a specific job, broken out by cost code.

Drill-up: JC Job Summary

NOTE: This report derives parameter values from the drill-up report. If you place this report directly on a dashboard, by default it shows no data because the Vendor parameter is empty.

JC Job Cash Flow Trend

This graph shows the job cash flow trend (based on posted job cost transactions) for one or more jobs over a specified number of prior periods. This report does not have drill-down or drill-up options.

JC Job Profit Summary Percent

This graphic shows the in-progress profit percent for jobs as of the specified date and the prior month as well as an arrow indicating the change from the prior month.

Drill-down:

JC Job Summary


JC Job Profit Summary Value

This graphic shows the in-progress profit for jobs as of the specified date and the prior month as well as an arrow indicating the change from the prior month.

Drill-down:

JC Job Summary


JC Job Profit Trend This graph shows the in-progress project profit trend (based on posted job cost transactions) over the specified number of months.

Drill-down:

JC Job Summary


JC Job Profit Trend Percent

This graph shows the in-progress project profit trend as a percent (based on posted job cost transactions) over the specified number of months.

Drill-down:

JC Job Summary





JC Job Summary This report shows summary Job information regarding the job, Cost, billing, over/under and projected profit and margin.

Drill-up:

JC Job Profit Summary Percent

JC Job Profit Summary Value

JC Job Profit Trend

JC Job Profit Trend Percent

Drill-down: JC Cost Code Breakdown

Property Management Role

This role and reports appear only if you have Property Management entries in your data folder.

PM Aged Receivable Bar Graph

This bar graph shows the amounts past due from your tenants for each 30-day “aging” period.

Drill-down: PM Aged Receivable Detail

PM Aged Receivable Detail

This report shows the amount due for each of your properties from each of your tenants (by lease and by unit) in each 30-day “aging” period.

Drill-up:

PM Aged Receivable Bar Graph

PM Aged Receivable Pie Chart

PM Aged Receivable Summary Snapshot

PM Aged Receivable Pie Chart

This pie chart shows the amounts past due from your tenants for each 30-day “aging” period.


PM Aged Receivable Summary Snapshot

This report shows the dollar amount and percentage past due from your tenants in each 30-day “aging” period.


PM Leasing Summary Snapshot

This report shows the leased status of your properties for the month. This report does not have drill-down or drill-up options.

PM Property Occupancy Summary Percent

This graphic shows the weighted average occupancy percent as of the specified date as well as the occupancy percent for the last month and the trend. This report does not have drill-down or drill-up options.

PM Property Occupancy Trend

This graph shows the occupancy trend as a percent for the various square footage types over several months. This report does not have drill-down or drill-up options.




Install Executive Dashboard

Executive Dashboard can be installed on any computer that can connect to the SQL Server instance used by Sage SQL Gateway. Sage 300 Construction and Real Estate does not have to be installed on the machines that have Executive Dashboard.

Once you have completed a successful replication, follow these instructions to install and open the program.

When you verify that you can connect to the data warehouse, you can then notify the individuals in your organization who will use Executive Dashboard that it is ready for use.

TIP: The separate document Install Sage Executive Dashboard is a form that you can fill out and distribute to Executive Dashboard users.

1. Double-click the SageExecutiveDashboardSetup.exe file, and follow the prompts to install the program.

2. When the installation is finished, open it by selecting Start > All Programs > Sage > Sage Executive Dashboard.

3. In the login window, enter the SQL Server instance name and the warehouse name.

4. Select Use my Windows credentials if your Windows login information is set up with access to the data warehouse. If you do not select this check box, you will be prompted for credentials when you click Log On.



What Happens During Replication?

The replication of your Sage 300 Construction and Real Estate data folders into the data warehouse follows the Extract-Transform-Load (ETL) process typical of many automated data migrations. Sage SQL Gateway extracts the data from Pervasive, transforms it (by merging data folders and tables), and then loads it into the data warehouse. In this example, three Sage 300 Construction and Real Estate company folders are replicated: Industrial, Commercial, and Residential.

With each nightly replication, the data warehouse is deleted and a new one created in its place. At any given time, the data warehouse contains a snapshot of your data as of the last successful replication.

If replication fails for any reason, the data warehouse is rolled back to the last successful replication. The date and time of the last successful replication is stored in the data warehouse, so can access this information when needed.

Staging Databases(SQL Server)

Source Data(Pervasive)

Industrial

Commercial

Residential

Data Warehouse (SQL Server)

ExtractSource data is extracted from Pervasive and pulled into SQL Server.

TransformStaging data tables containing similar data are merged.

LoadThe transformed data is written to the data warehouse.



Classic and Modern Views

Classic view results in table and field names that match the data as seen through a Timberline Data ODBC connection, such as when creating an SAP Crystal Reports design. Classic views will be most familiar for those accustomed to designing Sage 300 Construction and Real Estate reports. Classic views are stored in the dbo schema.

Modern view results in merged tables with adjusted names. Modern views are required if you plan to use Executive Dashboard—and future product enhancements will also require Modern views. These views are stored in the Sage300 schema.

Database size

Custom features and views

As part of report development, your report designers will create scripts that add custom views and features to the data warehouse, storing them in this location on the Sage SQL Gateway server:

C:\ProgramData\SAGE\SageSQLGateway\CustomScripts

The replication process will run any scripts stored in this folder as the last step in replicating.



Adding secure objects to the database

Work with your report designers to complete this sequence of steps. This process is explained fully in the Getting Started with Report Development document.

In SQL Server Management Studio:

Create a Custom Schema

a new schema in order to store custom views in one easily accessible group.

Create Custom Views

the underlying database query for each report that needs to be re-designed. From this query, a corresponding view is stored in the custom schema.

Enable Custom Views as Features

Report viewers must be able to access the custom view. a stored procedure that grants the required access to a database role as a feature. The replication maintains the feature in replications and future upgrades.

In the Sage SQL Gateway Administration window:

Create or Modify Custom Roles

Once custom views have been enabled as features, roles and assign the new features to them. Alternately, existing custom roles can be modified.

Assign Users to Custom Roles

Report viewers are assigned to the roles so that they can access new features.

In the report design tool:

Base Reports on Custom Views

The custom views are used as the basis for report designs to be developed in reporting tools such as SAP Crystal Reports. Report viewers who have access to the view can now view the reports.

Sage SQL Gateway with Sage Executive Dashboard Appendix


Appendix

Supported Configurations

The following configurations are supported with Sage SQL Gateway and the Executive Dashboard add-on. The Executive Dashboard application can be installed on any computer that can connect to the SQL Server instance.

Configuration 1

NOTES:

• If you install SQL Server Standard or Enterprise, you might need to adjust memory usage to avoid performance degradation. This is explained on page 55.

• With this configuration, no additional expense is incurred if you use SQL Express, or if a Standard or Enterprise license is already available.

Configuration 2

NOTES:

• See “Checklist: Implementing Configuration 2” on page 56.

• In this configuration, data travels over the network (from the Pervasive database on Machine 1 to the SQL Server database on Machine 2). This can impact performance.

• If Machine 2 runs on Windows 7 or 8, you can use SQL Express or SQL Server Standard. If you use the latter, consider capping the maximum memory available to the instance. (See “Adjust Memory Usage in SQL Server” on page 55.)

Installed on this machine:• Windows server or client OS• Sage 300 CRE Server• SQL Server or SQL Express• Sage SQL Gateway and the

Executive Dashboard add-on

Sage 300 CRE data

SQL data warehouse

Installed on Machine 1:• Windows server OS• Sage 300 CRE Server• Sage SQL Gateway and the


Installed on Machine 2:• Windows server or client OS• SQL Server Standard or

SQL Express

Appendix Sage SQL Gateway


Configuration 3

NOTES:


• On Machine 2, the workstation edition of Sage 300 Construction and Real Estate must be installed.

• This configuration requires a workstation installation of Sage 300 Construction and Real Estate.

Configuration 4

NOTES:


• This configuration incurs double the network traffic required for replication.

• Machine 2 requires a workstation installation of Sage 300 Construction and Real Estate.

Installed on Machine 1:• Windows server OS• Sage 300 CRE Server

Installed on Machine 2:• Windows server OS• SQL Server or SQL Express• Sage SQL Gateway and the

Executive Dashboard add-on• Sage 300 CRE Workstation

Installed on Machine 3:• Windows server or client OS• SQL Server or SQL Express

Installed on Machine 2:• Windows server OS• Sage 300 CRE Workstation• Sage SQL Gateway and the





Time Required for Replication

The amount of time required for replications depends on several factors:

The amount of memory (RAM) available to SQL Server. If you use SQL Express (the version installed by the Sage SQL Gateway wizard), the amount of RAM that can be accessed is limited by the program itself. If you use the Standard or Enterprise editions of SQL Server, you can adjust the memory cap to a suitable amount suitable. (See “Adjust Memory Usage in SQL Server” on page 55 for more information.)

The server’s processor (CPU) speed can significantly reduce replication time. The Sage 300 Construction and Real Estate User Guide lists the minimum processor as a dual-core 2.4 GHz. If you installed Sage SQL Gateway and SQL Server on the same server as the Sage 300 Construction and Real Estate server, a more robust processor will result in better replication times.

If you selected a configuration that distributes the Sage SQL Gateway components across more than one computer (Configurations 2 through 4 list in “Supported Configurations” on page 43), your network bandwidth will be a factor in replication time. Configuration 4 incurs double the network traffic as Configurations 2 or 3.

Finally, the size of your database is an important factor in replication time. Moving closed jobs and transactions to an archive folder, or excluding history or other tables from replication, can improve performance.



System Specifications

Depending on the configuration you select, verify that all systems in your Sage SQL Gateway implementation have sufficient resources.

For performance and reliability, we strongly suggest solid state hard drives (SSD) with at least 100 MB/s read-write speed for SQL Server database storage.

On the computer that runs SQL Server, sufficient hard disk space must be available for additional copies of your Sage 300 Construction and Real Estate database created during replication.

The amount of free space needed will depend on the current size of your database plus anticipated growth.

When you replicate your data, selecting both Classic and Modern views will increase the amount of free space needed. (See page 40.)

This table shows the minimum requirements for the Sage SQL Gateway server, not including Sage 300 Construction and Real Estate.

WARNING: The User’s Guide for Sage 300 Construction and Real Estate lists minimum server hardware and resources. If you install SQL Server and Sage SQL Gateway on the production server, extra hard disk space and RAM will be needed. Verify that your current server has sufficient resources for the additional load.

Sage SQL Gateway Requirements

Operating System If Sage 300 Construction and Real Estate 13.1 Rev 5 is installed, you can use:

Windows 7 (32bit or 64bit)Windows 8 (32bit or 64bit)Windows Server 2008 R2Windows Server 2012If Sage 300 Construction and Real Estate 14.1 is installed, you can also use:

Windows 8.1 (32bit or 64bit)Windows Server 2012 R2

NOTE: Apply all security updates to the operating system.

SQL Server or SQL Express

SQL Server 2012 with Service Pack 1 or

SQL Server 2014

NOTES:

• Apply all security updates to SQL Server.

• SQL Server 2012 requires Service Pack 1 to be applied to Windows 7 and to Windows Server 2008 R2.

• It also requires that .NET Framework 3.5 with Service Pack 1 be enabled. See page 8 for more information.



About SQL Server

We strongly recommend that you allow the Sage SQL Gateway installation routine to install and configure the SQL Server instance for you (as described on page 14). If you decide to install SQL Server yourself, read this section and follow the installation instructions carefully.

Do not use an Existing Instance

Sage SQL Gateway has special server collation requirements, so existing instances are unlikely to be configured correctly. Also, Sage SQL Gateway routinely creates and deletes warehouse and intermediary databases as part of normal processing. For this reason, do not use an existing SQL Server instance with Sage SQL Gateway.

If you already have SQL Server installed on your server, plan to install a new instance or select an installation option that installs SQL Express for you (explained on page 14.

Microsoft .NET Framework

Version 3.5 with SP1 is required by SQL Server 2012.

Version 4.51 is required by Sage SQL Gateway, and is installed if not already present.

NOTE: Apply all security updates to .NET.

Microsoft Windows Installer

Version 3.5 is automatically installed during Sage SQL Gateway installation if not already present.

TCP/IP Communication protocol used by Sage SQL Gateway.

Firewall Exceptions Create an exclusion for the incoming SQL Server traffic. To use automatic notifications, also create an exclusion for the outgoing email server.

Sage 300 Construction and Real Estate

Version 13.1 Rev 5 or later is required. Earlier versions will not work with Sage SQL Gateway.

Executive Dashboard Requirements

Operating System Windows 7 (32bit or 64bit)Windows 8 (32bit or 64bit)Microsoft Windows Server 2008 R2Microsoft Windows Server 2012NOTE: Apply all security updates to the operating system.

Other settings You do not need to install Sage 300 Construction and Real Estate, Sage SQL Gateway, or SQL Server on computers that will run Executive Dashboard.

Sage SQL Gateway Requirements



Supported Versions of SQL Server

We support SQL Server 2012 with Server Pack 1 and SQL Server 2014. The 32-bit or 64-bit options are supported, and you can select the Express, Standard, or Enterprise editions.

NOTE: We do not support SQL Server versions 2008 or 2008 R2.

Selecting the Express, Standard, or Enterprise Edition

Choosing the SQL Express edition for Sage SQL Gateway can be a cost-effective way to implement the utility, but you should consider the limitations of the no-cost editions. Databases larger than 10 gigabytes are not supported, and the Express edition is limited in the amount of CPU and RAM resources it can access.

If the combined size of the Sage 300 Construction and Real Estate company folders you will use with Sage SQL Gateway is currently near or above the 10 gigabyte limit, you’ll need to purchase the Standard or Enterprise edition of SQL Server.

To Install SQL Server

The sequence and content of the SQL Server installation screens vary according to the version and edition you select. Start the installation and follow the wizard. Several of the screens shown in this document require special settings, and they might not appear in the sequence shown.As you proceed through the installation wizard, verify that you have established the required settings for the current screen before moving to subsequent screens.

WARNING: The most critical configuration step for Sage SQL Gateway’s SQL Server instance is the server collation setting. Carefully follow the instructions for “Server Collation” on page 51. Collation settings are irreversible; if you do not set them correctly during installation, you will need to re-install the instance.

Instance Configuration

Setup role

Feature selection

Service accounts

Server collation

Account provisioning



Setup Role

In the Setup Role window, select SQL Server Feature Installation.

WARNING: If you select All Features With Defaults, your instance settings will not be compatible with Sage SQL Gateway.

Feature Selection

In the Feature Selection window, the options vary based on the SQL Server edition. The minimum features required for Sage SQL Gateway are:

Database Engine Services

Management Tools - Basic



Instance Configuration

You can use a default or named instance as the Sage SQL Gateway instance. A specific name is not required—you can assign any name to it.



Server Collation

WARNING: This is the most critical configuration step for Sage SQL Gateway’s SQL Server instance. Follow these instructions exactly. If you do not set the collation correctly during installation, you will need to re-install the instance.

On the Server Configuration > Collation tab, the collation must be set to Latin1_General_CS_AS. This must be set at the instance level.

1. In the Server Configuration > Collation window, click Customize.

2. Select Windows collation designator and sort order.

3. In the Collation designator list, select Latin1_General.

4. Select the following two check boxes:

Case-sensitive

Accent-sensitive



Service Accounts

On the Server Configuration > Service Accounts tab, provide an account name for each of the services. If this is a named instance, set the Startup Type for the SQL Server Browser service to Automatic.

Account Provisioning

1. On the Database Engine Configuration > Account Provisioning tab, select Mixed Mode.

2. Enter a password for the SQL Server system administrator’s account (sa.)

3. Click Add Current User to add the current Windows account as a SQL Server administrator. (This Windows user must have permissions to access the default data and backup locations.)

4. Additionally, click Add to add another user as an administrator.



Configure TCP/IP

After the installation is finished, configure the communication protocol.

1. Open the configuration manager by selecting Start > All Programs > Microsoft SQL Server [2012 or 2014]> Configuration Tools > SQL Server Configuration Manager.

2. Expand SQL Server Network Configuration.

3. Click the instance associated with Sage SQL Gateway.

4. Verify that the TCP/IP protocol is Enabled, and disable all other protocols. When you make changes to the protocols, you’ll see this message:

5. Click Yes. You’ll restart the services when you are finished.

6. In the same window, expand SQL Native Client 11.0 Configuration and click Client Protocols. Repeat steps 4 and 5.

7. To restart the SQL Server services, open the Services window. Depending on your operating system, you can find this by selecting Start > Administrative Tools > Services; or by selecting Start > Control Panel and typing Services in the search field.

8. Scroll down to the SQL Server services and restart each service for the Sage SQL Gateway instance.



Set the Default Data Location

1. In SQL Server Management Studio, right-click the database engine and select Properties.

2. Click Database Settings.

3. Next to Data, Log, and Backup, select the location for your data warehouse files. For best performance, we recommend that you:

Set Data, Log, and Backup locations on 3 separate physical drives.

Store the production database (Data) and log (Log) on SSD hard drives.



Adjust Memory Usage in SQL Server

When you install the Standard or Enterprise editions of SQL Server, the instance is configured to use all available memory on the server by default. To avoid performance degradation of the operating system, SQL Server, or other functionality on the server, cap the memory available to SQL Server.

WARNING: Only system administrators with experience managing servers should change memory usage settings. The appropriate maximum memory usage will be unique to your situation, as it depends on the total hardware resources available and the other functions performed by this server.

To adjust memory usage:

1. In SQL Server Management Studio, right-click the database engine and select Properties.

2. In the Server Properties window, click Memory.

3. Set the Maximum Server Memory as needed.

TIPS:

• The Maximum Server Memory reflects the amount of memory that SQL Server potentially can use—not the amount of memory available to the computer.

• This number is expressed in megabytes (MB). The default value for this field—more than 2 billion—is equivalent to two terabytes (TB) of memory. A reasonable entry for a server with 8GB RAM is 6,144 MB, or 6 GB, leaving 2 GB for the operating system.

2 billion MB = 2,000 GBTypical servers might have 8 or 16 GB RAM (approximately 8,000 or 16,000 MB). Enter a number in the appropriate range to effectively limit SQL Server. For example, entering 6,144 limits SQL Server to 6 GB.



Checklist: Implementing Configuration 2

1. On Machine 1, complete “Sage 300 Construction and Real Estate Tasks” on page 11, and “Sage SQL Gateway server tasks” on page 12.

2. On Machine 2, complete “Microsoft SQL Server machine tasks” on page 13.

3. If you will install SQL Server 2012 on your own, install it now on Machine 2 (page 48).

Otherwise, download the Sage SQL Gateway installation file and open it. Select only the last check box and complete the SQL Express installation.

❑ Sage SQL Gateway❑ Executive Dashboard Add-on✔ SQL Server 2012 Express

4. On Machine 1, open the Sage SQL Gateway installation file. If you see a SQL Express option, clear the last check box and complete the installation.

✔ Sage SQL Gateway✔ Executive Dashboard Add-on❑ SQL Server 2012 Express

5. On Machine 1, open Sage SQL Gateway. Enter [Machine 2]\sagesqlgateway (or the applicable instance name if you installed SQL Server on your own).

6. Select the check box to use your Windows credentials, and click Log on.

7. If the logon fails, verify each item shown here. The first list applies to Machine 2, and the second list applies to Machine 1.

8. Click Yes to the messages about configuring the instance and enabling the Executive Dashboard add-on. Configure warehouses as needed.

Installed on Machine 1:• Windows server OS• Sage 300 CRE Server• Sage SQL Gateway and the


Installed on Machine 2:• Windows server or client OS• SQL Server Standard or

SQL Express




1. On Machine 1, complete “Sage 300 Construction and Real Estate Tasks” on page 11.

2. On Machine 2:

Complete “Sage SQL Gateway server tasks” on page 12, including the client installation of Sage 300 Construction and Real Estate.

Complete “Microsoft SQL Server machine tasks” on page 13.


4. Download the Sage SQL Gateway installation file and open it. If you downloaded a SQL Express option, you see three check boxes. If you’ve already installed SQL Server 2012, clear the last check box. Otherwise, leave it selected and complete the installation.

✔ Sage SQL Gateway✔ Executive Dashboard Add-on✔ SQL Server 2012 Express

5. Open Sage SQL Gateway. Enter [Machine 2]\sagesqlgateway (or the applicable instance name if you installed SQL Server on your own).


7. If the logon fails, verify each item shown here. (Both lists apply to Machine 2.)



Installed on Machine 2:• Windows server OS• SQL Server or SQL Express• Sage SQL Gateway and the

Executive Dashboard add-on• Sage 300 CRE Workstation




1. On Machine 1, complete“Sage 300 Construction and Real Estate Tasks” on page 11.

2. On Machine 2, complete “Sage SQL Gateway server tasks” on page 12, including the client installation of Sage 300 Construction and Real Estate.

3. On Machine 3, complete “Microsoft SQL Server machine tasks” on page 13.


Otherwise, download the Sage SQL Gateway installation file and open it. Select only the last check box, and complete the installation.

❑ Sage SQL Gateway❑ Executive Dashboard Add-on✔ SQL Server 2012 Express

5. On Machine 2, open the Sage SQL Gateway installation file. If you selected a SQL Express option, clear the last check box and complete the installation.

✔ Sage SQL Gateway✔ Executive Dashboard Add-on❑ SQL Server 2012 Express

6. On Machine 2, open Sage SQL Gateway.

7. Enter [Machine 3]\sagesqlgateway (or the applicable instance name if you installed SQL Server on your own).


Installed on Machine 3:• Windows server or client OS• SQL Server or SQL Express

Installed on Machine 2:• Windows server OS• Sage 300 CRE Workstation• Sage SQL Gateway and the





9. If the logon fails, verify each item shown here. The first list applies to Machine 3. The second list applies to Machine 2.


sage sql gateway with executive dashboard installation and

Documents