Sage 200 On
Premise
Deployment and
Installation Guide
2015
© Sage (UK) Limited and Sage Hibernia Limited, 2014. All rights reserved.
If this documentation includes advice or information relating to any matter other than using Sage software, such advice or information is provided for information purposes only. While Sage takes responsible steps to ensure the accuracy and completeness of such information, it can only be general in nature. You should therefore take appropriate steps to ensure it is correct and relevant to your circumstances. You should also consider taking professional advice when appropriate, for example to ensure that the results obtained from using the software comply with statutory requirements.
No part of this documentation may be copied, photocopied, reproduced, translated, microfilmed, or otherwise duplicated on any medium without prior written consent of Sage (UK) Limited or Sage Hibernia Limited, publisher of this work.
Use of the software described in this documentation is subject to Sage (UK) Limited or Sage Hibernia Limited's Licence Agreement enclosed with, or displayed by, that software.
Sage, the Sage logo, and other Sage logos and brands are either registered trademarks or trademarks of Sage (UK) Limited and Sage Hibernia Limited, or its holding company, The Sage Group PLC. All other marks are trademarks or registered trademarks of their respective owners.
Sage UK Sage Hibernia Ltd trading as Sage Ireland
North Park 3096 Lake Drive
Newcastle Upon Tyne Citywest Business Park
NE13 9AA Dublin
Issue date: 18/11/2014
1 | Introduction 1
2 | System requirements and prerequisites 3
Supported operating systems 4
Microsoft SQL Server 5
Other supported components 6
Minimum supported specifications 10
Prerequisites 12
General considerations 14
3 | Deploying Sage 200 across a network 17
Sizing your system 18
Sizing your database 24
Installing Sage 200 with other 64-bit web services 26
Installing Sage 200 on Microsoft Small Business Server 2011 29
Installation options for virtualised platforms 31
Installing Sage 200 using Citrix or Terminal Services 32 Supported versions of Citrix and Terminal Services 33
Install Sage 200 CRM on multiple servers 34
Security and firewall settings 38
Database configuration and maintenance 41
4 | Installing Sage 200 45
Create Windows user accounts and groups 47
Set up the Sage 200 Self Service web site internally 56
Install Sage 200 Self Service on an external facing web server 58
5 | Setting up Sage 200 to use for the first time 63
Setting up Sage 200 System Administration for Sage 200 64
Create your configuration database 65
Create users 67
Activate your Sage 200 licence 68
Create your company database 70
Set up user accounts, roles and features 72
Set up online payment processing 75
Set up Purchase Order Authorisation 76
Integrating with Office365 79
Set up the Sage 200 desktop 83
7 | Set up Sage 200 Business Intelligence 85
Using BI with Microsoft SQL Server v2012 86
Set up Business Intelligence in Sage 200 System Administration 88 Configure Microsoft Excel to use the Sage Business Intelligence add -in 89
Set up security for Business Intelligence 90
Set up the BI Administration tool 92
Set up BI in Sage 200 98
Troubleshooting BI 100
FAQs: Business Intelligence 106
8 | Set up Sage 200 Manufacturing 113
Set up and install Sage Graphical Planner 118
9 | Upgrading Sage 200 125
Upgrading checklist 127
Upgrading BI checklist 131
Check your SQL Server settings for Sage 200 CRM 155
Check IIS is installed as a server role 156
Install Sage 200 CRM 157
Configure IIS for Sage 200 CRM 160
Set up Sage 200 Accounts and Sage 200 CRM 162
Set up the integration in Sage 200 CRM 166
Troubleshooting installing Sage 200 CRM 174
Set up Form Launching 178
11 | Upgrading Sage 200 CRM 183
Checklist to upgrade from 2013 R2 to 2015 185
Upgrading Sage 200 CRM - detailed steps 188
Upgrading customised CRM 202
CRM Integration tools 205
Index 211
Introduction
This guide is designed to provide Sage 200 Business Partners and customers with all the information required to install and upgrade Sage 200 2015 for On Premise deployments.
Note:Sage 200 can be installed as an On Premise or Online deployment. The installation
instructions are different depending on your installation method. If you are installing Sage 200 Online, please see the Sage 200 Online Deployment and Installation Guide.
Required Knowledge
To install or upgrade Sage 200 you must have experience in:
n Configuring SQL Server.
n Configuring Microsoft Internet Information Services (IIS).
Please make sure that you read the relevant sections in this guide before you install or upgrade Sage 200 2015.
For new installations
Modules Required sections Notes
All System requirements and prerequisites on page 3
Supported versions of operating systems, SQL server, Microsoft Office, browsers etc.
Deploying Sage 200 across a network on page 17
Advice on how to deploy Sage 200 in a variety of network environments
Modules Required sections Notes
Setting up Sage 200 to use for the first time on page 63
Step-by-step instructions on how to configure the Administration tool to set up users and roles
BI Set up Sage 200 Business Intelligence on page 85
Step-by-step instructions on how to configure Business Intelligence
Manufacturing Set up Sage 200
Manufacturing on page 113
Step-by-step instructions on how to configure Manufacturing
CRM Install and set up Sage 200 CRM on page 153
Step-by-step instructions on how to install CRM and configure the integration between CRM and Sage 200
Install Sage 200 CRM on multiple servers on page 34
Advice on installing CRM across multiple servers and setting up load balancing.
For existing installations
Modules Required sections Notes
All System requirements and prerequisites on page 3
Supported versions of operating systems, SQL server, Microsoft Office, browsers etc.
Deploying Sage 200 across a network on page 17
Advice on how to deploy Sage 200 in a variety of network environments
Upgrading Sage 200 on page 125
Step by step instructions on how to upgrade from a variety of previous versions of Sage 200
CRM Upgrading Sage 200 CRM on page 183
Step by step instructions on how to upgrade from Sage 200 CRM v7.2.
System requirements and prerequisites
Sage 200 is made up of various core and optional modules that integrate with the core product. When determining your system requirements, you must make sure that you have considered the requirements for all the Sage 200 modules that you want to use, as some modules have different system requirements.The application footprint on each of the Sage 200 client machines can be large. Each of the client
machines that run Sage 200, as well as the servers, must meet the minimum specification. SeeMinimum supported specifications on page 10.
W ind o w s 7 ( 3 2 -b it a n d 6 4 -b it ) (P ro fe s s ion a l, E n te rp ris e & U lt imat e E d it ion s ) W ind o w s 8 (3 2 -b it a n d 6 4 -b it ) (P ro a n d E n te rp ris e E d it ion s ) W ind o w s 8 .1 ( 3 2 -b it a n d 6 4 -b it ) (P ro a n d E n te rp ris e E d it ion s ) W ind o w s S e rv e r 2 0 0 8 R 2 (64 -b it ) (S ta n d a rd & E n te rp ris e E d it ion s ) W ind o w s S ma ll B u s ine s s S e rv e r 2011 (S ta n d a rd , P re mi u m & E s s e n tial s E d it ion s ) W ind o w s S e rv e r 2 0 1 2 (S ta n d a rd a n d E s s e n tial s E d it ion s ) W ind o w s S e rv e r 2 0 1 2 R 2 (S ta n d a rd a n d E s s e n tial s E d it ion s ) Sage 200
Sage 200 Server (All modules)
1
1
1
Sage 200 Client (All modules)
4
4
Sage 200 CRM 2
Sage 200 CRM Server
1
1
Sage 200 CRM Client
4
4
Sage 200 Business Intelligence 3
Sage 200 Business Intelligence Server
1
1
1
Sage 200 Business Intelligence Client(including Sage 200 BI Admin Tool)
4
4
Sage 200 Migration Tool
Sage 200 Migration Tool 32-bit
Sage 50 to Sage 200 Data Migration Tool 32-bit 32-bit
32-bit
Notes:
Additional configuration may be required depending on your operating system. Please see the sections relevant to the server operating system you are installing on.
We recommend that you install the latest service packs and Windows Updates for both your client and server operating system and database management system.
All modules in the Sage 200 Suite are 32-bit applications. Where supported on 64-bit platforms, the system runs in WOW64 emulation mode.
The Sage 200 suite does not support Itanium chipsets.
2 | System requirements and prerequisites
Microsoft SQL Server
Supported versions of SQL server
Microsoft SQL Server 2008 R2 SP2 - Workgroup, Standard & Enterprise Editions (32 & 64-bit) Microsoft SQL Server 2012 SP2 - Standard & Enterprise Editions (32 & 64 bit ).
(SQL 2012 Business Intelligence Edition is not supported)
Microsoft SQL Server 2014 - Standard & Enterprise Editions (32 & 64-bit). (SQL 2014 Business Intelligence Edition is not supported).
Note:
We recommend that you install Microsoft SQL Server 2012 and 2014 using the default accounts set up by the SQL Server installation wizard as shown below:
How many licensed seats for SQL server do I need?
The same number as the maximum number of concurrent Sage 200 users you're likely to have.
Prerequisites for Business Intelligence
Note:Sage 200 Business Intelligence is not supported on Workgroup editions of SQL server. This is because Business Intelligence requires Analysis Services.
n Microsoft SQL Server and Analysis Services must be installed on the same machine with the same instance name.
n SQL Server must be configured to use the following collation: Latin1_General_CI_AS SQL Server must be running in Mixed Mode (SQL Server and Windows Authentication).
Other supported components
Supported Editions of Microsoft Office
Microsoft Office 2010 SP3 (32-bit only) - Standard, Professional and Professional Plus Editions Microsoft Office 2013 SP1 (32-bit and 64-bit) – Small Business Premium, Professional Plus and Enterprise Editions
Microsoft Office 365 (32-bit and 64-bit) – Small Business Premium, Professional Plus and Enterprise Editions
Note:Microsoft Office 365 Home and Personal editions are not supported.
The following table explains how Sage 200 functionality integrates with Microsoft Office:
2007 SP3
2010 SP3 2013 SP1 Office 365 Exchange
32-bit 32-bit 64-bit 32-bit 64-bit Desktop Apps
Online Online 2007 2010 2013
Sage 200 App
Excel Integration
N/A N/A N/A N/A
Excel Reporting (new)
N/A N/A N/A N/A
Send to Excel
(from workspaces and lists)
N/A N/A N/A N/A
Opening Attachments
N/A N/A N/A N/A
Create / View Memos
2 | System requirements and prerequisites
2007 SP3
2010 SP3 2013 SP1 Office 365 Exchange
32-bit 32-bit 64-bit 32-bit 64-bit Desktop Apps
Online Online 2007 2010 2013
Send Email (not from Report Designer) Report Designer -output to email Sage 200 Business Intelligence
N/A N/A N/A N/A
Sage 200 CRM
Mail merge N/A N/A N/A N/A
Data Upload N/A N/A N/A N/A
Document Drop
N/A N/A N/A N/A
Outlook Classic Plug in Outlook Exchange Plug In
Supported Editions of Sage Payroll
Browser and Mobile device Support
Sage 200 Self Service
Sage 200 includes customisable workspaces (including Web Timesheets and Expenses (WTE) and Purchase Order Authorisation), which can be accessed in the desktop client and via a web browser. Workspaces have been tested on the following devices and browsers.
Safari on Apple iPad running iOS v7.1.2 Google Chrome on x86 and x64 based PCs.
Microsoft Internet Explorer v10 and v11 on x86 and x64 based PCs.
Microsoft Internet Explorer v10 and v11 on Microsoft Windows 8 and 8.1 tablets running Windows 8 Professional and Enterprise Editions.
Google Chrome on Google Nexus 10 devices running Android v.4.2.1 or greater.
Note:Other devices and browsers may be compatible but have not been tested.
Browser settings
When accessing Sage 200 workspaces in the desktop app or from the Self Service website, you may be asked to login. If you don't wish to be prompted each time, you can set your browser to always use your login credentials:
Internet Explorer
We recommend that you set this for your local intranet zone, however you may choose to set Trusted Sites instead depending on your domain or local security policies.
n Local Intranet zone
a. Open:Internet Options > Security> Custom Level
b.
a. Set userUser Authentication > LogontoAutomatic Logon with current user name and password.
n Trusted Sites
a. OpenInternet Options > Security > Trusted Sites > Sites. b. Add the SelfService server URL e.g. https://<server_name>.
c. OpenInternet Options > Security > Trusted Sites > Custom Level.
d. SetUser Authentication > LogontoAutomatic Logon with current user name and password.
2 | System requirements and prerequisites
n For 32-bit operating systems:
"C:\Program Files\Google\Chrome\Application\chrome.exe" -auth-server-whitelist="localhost,<machine fully qualified domain name>". For example:
"C:\Program Files\Google\Chrome\Application\chrome.exe" -auth-server-whitelist=localhost, sagesvr01.mydomain.com”
n For 64-bit operating systems:
"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" -auth-server-whitelist="localhost,<machine fully qualified domain name>". For example:
"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe" -auth-server-whitelist=localhost, sagesvr01.mydomain.com”
Sage 200 CRM
Internet Explorer v10 (IE 10 Compatibility Mode only) and v11. Chrome (32-bit only)
Safari on Apple iPad running iOS v7.1
Note:Other devices and browsers may be compatible but have not been tested. Some functionality may not be compatible on tablet devices.
Minimum supported specifications
These are theminimumspecifications for each client and server machine in a Sage 200 On Premise deployment. Users with large databases or high numbers of concurrent users will require higher specifications.
Due to the wide variation of companies that use Sage 200, it is not possible to give exact specifications for each system. Factors such as the concurrent number of users, size of the database and estimated growth should all be taken into consideration when sizing a server.
When tasks are processed, CPU and memory resources are used on both the client and the server. Therefore, the specification of both machines will affect the overall performance of the system. SeeSizing your system on page 18andSizing your database on page 24.
Sage 200 installed on a single server
Processor Disk Memory Network
Sage 200 Server
Sage 200 Server with Microsoft SQL Server
Dual-core 2.6GHz or equivalent
7,200 rpm SATA with 4GB disk space after SQL Server is installed.
4GB Gigabit
Ethernet
Sage 200 Server with CRM
Sage 200 Server with Sage 200 CRM Server
Dual-core 2.6GHz or equivalent
7,200 rpm SATA with 4GB disk space after SQL Server is installed.
8GB Gigabit
Ethernet
Sage 200 Server with BI
Sage 200 Server with Sage 200 Business
Intelligence Server
Dual-core 2.6GHz or equivalent
7,200 rpm SATA with 4GB disk space after SQL Server is installed.
8GB Gigabit
Ethernet
Sage 200 Client
Client 2.8GHz Pentium 4
or equivalent (e.g. Dual Core 1.6GHz)
7,200 rpm SATA with 500MB disk space required.
Note:If you are going to run Graphical Planner you must have a minimum of
2GB Gigabit
2 | System requirements and prerequisites
Sage 200 with Sage 200 CRM on a separate server
Processor Disk Memory Network
Server
Sage 200 Server with Microsoft SQL Server
Dual-core 2.6GHz or equivalent
7,200 rpm SATA with 1GB disk space after SQL Server is installed.
4GB Gigabit
Ethernet
Sage 200 CRM Server Dual-core 2.6GHz or equivalent
7,200 rpm SATA with 2GB disk space required.
4GB Gigabit
Ethernet
Sage 200 Client
Client 2.8GHz Pentium 4 or
equivalent (e.g. Dual Core 1.6GHz)
7,200 rpm SATA with 500MB disk space required.
Note:If you are going to run Graphical Planner you must have a minimum of 1GB disk space.
2GB Gigabit
Prerequisites
Before you install Sage 200, you must make sure the required prerequisites are installed on your system. You must also make sure that your installations of Microsoft SQL Server and Microsoft Internet Information Services (IIS) have the required settings.
Pre-requisites
All client and server machines must have regional and language settings set to either
English (UK)orEnglish (Ireland)only.
All server machines must follow Microsoft naming conventions, i.e. server names cannot start with a numeric character or contain underscores. (Microsoft KB 909264)
Microsoft SQL Server :
Must be running in Mixed Mode (SQL Server and Windows Authentication mode). TheServer Collationfor SQL server must be set toLatin1_General_CI_AS.
Machines running Sage 200 must have ac:\drive although Sage 200 can be installed and run from a drive other thanc:\.
Microsoft DotNet Framework versions installed (all server and client machines) : v3.5 Service Pack 1.
v4.0 v4.5.1
These are all included on Sage 200 2015 DVD.
Windows Identity FoundationMicrosoft KB 974405(all server server machines): For Windows Server 2012, this can be added as a feature.
For Windows Server 2008R2, this is included on Sage 200 2015 DVD.
Microsoft Internet Information Services (IIS) v7 or greater.
IIS v7.0 and v7.5 must have the following hotfix appliedKB980368
Note:
The following components are installed automatically if they are not found on your system:
l Microsoft Windows Installer v3.1 l Microsoft Management Console v3.0
2 | System requirements and prerequisites
Required settings for IIS
IIS v7.5 IIS v8 and 8.5
Common HTTP Features Default Document Default Document
Directory Browsing Directory Browsing
HTTP Errors HTTP Errors
HTTP Redirection HTTP Redirection
Static Content Static Content
Application Development .Net Extensibility .Net Extensibility 3.5
.Net Extensibility 4.5
ASP ASP
ASP.NET ASP.NET
ASP.NET 3.5 ASP.NET 4.5
ISAPI Extensions ISAPI Extensions
ISAPI Filters ISAPI Filters
Security Request Filtering Request Filtering
Windows Authentication Windows Authentication
Management Tools IIS Management Console IIS Management Console
Management Tools > IIS 6 Management Compatibility
IIS 6 WMI Compatibility IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility
IIS 6 Metabase compatibility
.Net Framework 4.5 Features WCF Services
General considerations
Before you install Sage 200,you must also consider the following:
n If you are upgrading, you must uninstall your previous version of Sage 200.
n If you are installing Sage 200 on Microsoft Small Business Server 2011 or a server that also hosts other web based applications, such as Microsoft Exchange or Microsoft Terminal Services Gateway, you must run theSage 200 64bit Web Service config.bat (v6.4)before installing Sage 200. This file can be found on the Sage 200 DVD or downloaded from the support area of the sage website.
n If you are using Small Business Server 2011 as your Domain Controller, then new users including the Sage 200 Secured Services and Sage 200 Service users should be created using the SBS Create User Wizard and applying the users template.
SeeInstalling Sage 200 with other 64-bit web services on page 26.
n Sage 200 is designed to run in a domain based network environment. The only scenario where Sage is supported in a workgroup network environment is where all elements of Sage 200 are installed on a single server and accessed by clients on the local area network.
2 | System requirements and prerequisites
n Sage 200 automatically creates the following application pools, websites and bindings. We recommend that you do not change these settings:
Website Application Pool
Binding Path
Sage 200 App Services Sage 200 Services
:10080 (http) :10443 (https)
C:\inetpub\Sage 200 App Services
Sage200Services (Virtual directory under Sage 200 Application services)
Sage 200 Services
n/a C:\inetpub\Sage 200 App
Services\Sage200Services
Sage200SecuredServices (Virtual directory under Sage 200 Application services)
Sage 200 Secured Services
n/a C:\inetpub\Sage 200 App
Services\Sage200SeuredServices
Sage 200 Self Service Sage 200 Self Service
:10082 (http) :10444 (https)
C:\inetpub\Sage 200 Self Service
If you have an application that already uses the ports listed above you must manually install the Sage 200 pre-requisites first. Then you must install Sage 200 from a command line using the following parameters:
For example ifd:\is your DVD drive and ports11443,and11444are you desired ports:
Msiexec.exe /i “d:\sage200.msi” SAGE200ADMINWEBSITEPORT=11443 SAGE200SELFSERVICEWEBSITEPORT=11444
Take care when assigning port numbers as many port numbers are already assigned to other Windows services or applications.
The following table shows the names of the SSL Certificates bound to these ports:
Binding Certificate
:10443 (https) Sage 200 Application Services SSL Certificate_wixCert_1
n TheSage 200 Self Serviceweb site is only accessible from your internal network. This is because the SSL certificates used to secure these sites are based on the machine name and are not accessible externally. Additional configuration is required to make the site accessible externally.
For more information, seeInstall Sage 200 Self Service on an external facing web server on page 58.
n Sage 200 CRMis installed to the default website. This is not configurable. By default, the website is published usinghttpand this is not a secure connection. Additional configuration is required to use CRM over a secure external connection.
Deploying Sage 200 across a network
This chapter provides information and advice when sizing your system and installing the various elements of Sage 200 across different operating systems.n Sizing your system on page 18
n Installing Sage 200 with other 64-bit web services on page 26
n Installing Sage 200 on Microsoft Small Business Server 2011 on page 29 n Installation options for virtualised platforms on page 31
n Installing Sage 200 using Citrix or Terminal Services on page 32 n Install Sage 200 CRM on multiple servers on page 34
n Security and firewall settings on page 38
n Database configuration and maintenance on page 41
Note:Sage 200 2013 contained changes in the way that SQL logins are maintained.
If you want to continue to use an older version of Sage 200, you should use separate instances of SQL for each version of Sage 200 that you deploy.
Sizing your system
A typical deployment of Sage 200 consists of all elements residing on the same server, which also hosts the SQL server instance.
For larger sites with higher user counts and larger transaction volumes this deployment model is not always suitable and may not offer the best performance.
The following diagram shows four different sized deployments of Sage 200 and gives some suggested ‘ball park’ figures for sites that may fit into these scenarios. Due to the diverse nature of businesses that use Sage 200, it is impossible to give exact recommended specifications for each scenario but these figures are based on data gathered from sites already successfully running Sage 200.
3 | Deploying Sage 200 across a network
Small deployments
Recommended for sites:n 4 to 12 concurrent users n Database sizes less than 2GB.
n Not running Sage 200 CRM or Business Intelligence
Recommended Specifications
Notes
Dual core processor The operating system will use one core with Sage 200 and Microsoft SQL Server sharing the other core.
No configuration around setting processor affinity is required and in practice the operating system, SQL and Sage 200 will share the cores, along with other running applications, but a dual core system should give users acceptable performance.
4GB RAM n 2GB for the operating system,
n 1GB for SQL n 1GB for Sage 200.
No configuration around setting memory allocation is required and the operating system, SQL and Sage 200 will share the available memory, along with other running applications.
A minimum of 4GB server memory should give users acceptable performance in this scenario.
7.2K RPM SATA Disk Should be sufficient for databases less than 2GB.
Small deployments with CRM or Business Intelligence
Recommended for sites:n 8 to 12 concurrent users n Database sizes less than 2GB.
Recommended Specifications
Notes
Server to share the other core.
As Sage 200 CRM is a browser based module that can require high levels of server CPU when synchronising data or processing multiple user requests at the same time. The server should have a minimum of a dual core processor but as user count and data size increases towards the top end of this bracket, a quad core processor may be preferable
8GB n 2GB for the operating system,
n 1GB for SQL
n 1GB for Sage 200.
n For BI: 1GB for each data warehouse / analysis services cube.
Sage 200 Business Intelligence uses Microsoft SQL Server Analysis Services which can be a very memory intensive process. For each data warehouse and analysis services cube created, we recommend allocating at least 1GB Ram
10K RPM SAS or SCSI
In a RAID configuration to allow for failover.
Sage 200 CRM and Sage 200 BI will benefit from additional memory and faster access disks
Medium Deployments
Recommended for sites:n 12 to 25 concurrent users n Database sizes less than 10GB.
Where sites have more than 20 concurrent users accessing Sage 200 CRM with similar levels of users accessing Sage 200 Accounts at the same time, it is recommended to split out Sage 200 CRM onto a second server to improve performance.
Recommended Specifications
Server
Specifications
Notes
Server 1
Quad core processor
n Microsoft SQL Server; n Sage 200 database n Sage 200 CRM database.
3 | Deploying Sage 200 across a network
Server
Specifications
Notes
This server is effectively dedicated to handling the SQL requests of the Sage 200 Suite.
8 - 16 GB RAM
n For Sage 200 BI, allow at least 1GB for each data warehouse and
analysis services cube.
Sage 200 Business Intelligence uses Microsoft SQL Server Analysis Services which can be a very memory intensive process. For each data warehouse and analysis services cube created, we recommend allocating at least 1GB additional Ram but this requirement will increase with database size.
n With terminal services, add 1 GB for each remote user session,
up to a maximum of 8 remote users.
For more than 8 remote users use a separate Terminal Services server running the Sage 200 client.
SeeInstalling Sage 200 using Citrix or Terminal Services on page 32
10K RPM SAS or SCSI Disk
n In a RAID configuration to allow for failover
n The SQL data and log files ideally being hosted on different disks
to the operating system.
Server 2
Quad core processor
n Sage 200 server
n Sage 200 CRM installation (not the database),
n Additional services such as Sage 200 Self Service.
4 - 8 GB RAM 7.2K SATA Disk
Large deployments
Recommended for sites:Recommended Specifications
Server
Specifications
Notes
Server 1
2 x Quad core processor
n Microsoft SQL Server;
n Sage 200 database n Sage 200 CRM database n Sage 200 BI
This server is effectively dedicated to handling the SQL requests of the Sage 200 Suite.
16 - 32 GB RAM
n For Sage 200 BI, allow at least 1GB for each data warehouse and
analysis services cube.
Sage 200 Business Intelligence uses Microsoft SQL Server Analysis Services which can be a very memory intensive process. For each data warehouse and analysis services cube created, we recommend allocating at least 1GB additional Ram but this requirement will increase with database size.
n With terminal services, add 1 GB for each remote user session,
up to a maximum of 8 remote users.
For more than 8 remote users use a separate Terminal Services server running the Sage 200 client.
10K or 15K RPM SAS or SCSI Disk
n In a RAID configuration to allow for failover
n The SQL data and log files ideally being hosted on different disks to the operating system.
Server 2
Quad core processor
n Sage 200 Server
n Sage 200 CRM installation (not the database), n Additional services such as Sage 200 Self Service.
16 GB RAM 7.2K SATA Disk
3 | Deploying Sage 200 across a network
Server
Specifications
Notes
Server
RAM 10K RPM SAS or SCSI disk
Allow sufficient memory to allow for 2GB for the operating system and an additional 1GB per remote session running the Sage 200 client, up to a maximum of 12 users per server
Recommended specifications for client machines
In addition to calculating the system requirements for the servers running the Sage 200 Suite, you must also consider the client machines that will be running Sage 200. The application footprint on each of the Sage 200 client machines can be large.
Processing of tasks is carried out using CPU and memory resources on both the client and the server, and the specification of both machines will affect the overall performance of the system. The following table gives an example of this.
Profile Running Year End with 1 million Nominal Transactions
Server A / Client A 3 hrs 24 Server A / Client B 5 hrs 07 Server B / Client B 8 hrs 11
The server and client specifications were as follows;
Server A = 2 x Quad Core 2.4GHz Xeon, 8GB Ram, 15K SAS Disks, Gigabit Network
Server B = 1 x Dual Core 2.6GHz, 4GB Ram, 7.5K SATA Disk, Gigabit Network
Client A = 1 x Dual Core 2.6GHz, 4GB Ram, 7.5K SATA Disk, Gigabit Network
Client B = 1 x Single Core 2.8GHz, 2GB Ram, 7.5K SATA Disk, Gigabit Network
Client machines used to run long batch processes such as Update Waiting Postings and Month and Year End processing should be dual core processor machines with 4GB Ram.
Sizing your database
The Sage 200 database size will depend on a number of factors, not just the number of transactions included within the database. The following examples can be used to estimate a rough minimum expected database size given a specific number of records but be aware that with SQL database padding this size can increase 10 fold. Use these examples to calculate minimum disk requirements only.
The following record sizes are calculated using Sage 200 demonstration data for 8 financial years.
Record Type Record Size (in KB)
Sales Ledger Record 24K
Purchase Ledger Record 15K
Nominal Ledger Record 14K
Stock Record 4K
SOP Order (based on 4 item lines) 44K POP Order (based on 4 item lines) 47K
Sales Ledger Invoice 3K
Purchase Ledger Invoice 4K
Nominal Ledger Journal 2K
To calculate the minimum disk space required, you can take these record sizes and multiply them against the estimated number of records. For example:
Record Type Record Size (in KB) Number of Records Size
Sales Ledger Record 24K 10,000 240,000K
Purchase Ledger Record 15K 10,000 150,000K
Nominal Ledger Record 14K 800 11,200K
Stock Record 4K 16,000 64,000K
SOP Order 44K 12,000 528,000K
POP Order 47K 2,600 122,200K
Sales Ledger Invoice 3K 12,000 36,000K
3 | Deploying Sage 200 across a network
This gives an estimated database size of 1.4GB. The actual size of the database used in this example was 1.2GB. The difference was made up of a variety of factors, such as not all records were fully populated, not all orders had 4 item lines etc.
For further guidance, the following table gives record numbers and associated database sizes for real Sage 200 databases. As you will see, some fit the calculation but some, such as the 6GB and 10GB database examples, are considerably larger than would be expected. This is largely due to database padding and you should allow at least 2 or 3 times more disk space than the minimum calculated to allow for padding and future growth.
No records / transactions
Sales Ledger Records
10,000 1,000 3,500 45,000 300 25,000 28,000
Purchase Ledger Records 10,000 100 600 800 2,000 500 1,000
Nominal Ledger Records 800 100 300 150 600 600 3,000
Stock Records 16,000 400 1,500 16,000 50,000 800 12,000
SOP Orders 12,000 3,500 85,000 125,000 6,000 115,000 350,000
POP Orders 2,600 0 20,000 4,000 14,000 1,700 56,000
Sales Ledger Transactions 12,000 45,000 68,000 220,000 10,000 175,000 950,000 Purchase Ledger
Transactions
14,000 0 15,000 17,000 56,000 10,000 80,000
Nominal Ledger Transactions
140,000 100 420,000 610,000 116,000 295,000 5,000,000
Database Size in Gigabytes
1.2 3.0 4.0 5.5 6.0 10.0 25.0
Real Sage 200 database sizes
As you will also see from the table above, the Sage 200 database can scale up to very large numbers of transactions. The 25GB example has over 5 million nominal transactions.
Installing Sage 200 with other 64-bit web
services
Sage 200 is a 32-bit application which is supported on 64-bit operating systems.
The web service elements of Sage 200 must run in a 32-bit instance of Internet Information Services (IIS). On 64-bit operating systems IIS runs in 64-bit mode by default.
If you have any of the following applications installed on the same 64-bit server as Sage 200, you must adjust some settings to accommodate 32-bit applications:
n Microsoft Exchange 2007 with Service Pack 3. n Microsoft Exchange 2010.
n Microsoft Terminal Services Gateway.
n Windows Server Update Service (WSUS) and HTTP Compression.
To do this we have provided theSage 200 64bit Web Service config.bat (v6.4)batch file which edits theapplicationHost.configfile. This file can be found on the Sage 200 DVD or downloaded from the support area of the Sage website.
To run the Sage 200 64-bit Web Service Config.bat file
The batch file is commented and should be reviewed before being run on a live system.1. Copy the Sage 200 64bit Web Service config.bat (v6.4) file to the root c:\ drive of your Sage 200 server.
2. Right-click and chooseRun as administrator.
3. Review the content of the batch file. If you are happy to proceed, pressY. 4. Choose the option that matches your configuration:
Option Change made to the ApplicationHost.config file
A - Microsoft Exchange 2007 with Service Pack 3 Applied
1. AddsBitness64to thePassword Expiry Module. The 64-bit version of thePassword Expiry Moduleis incompatible with 32-bit application pools. To resolve this the Password Expiry Module is set to only run against 64-bit applications.
2. AddsBitness64to thePassword Expiry Notification Service. The 64-bit version of thePassword Expiry Notification Service
is incompatible with 32-bit application pools. To resolve this the
3 | Deploying Sage 200 across a network
Option Change made to the ApplicationHost.config file
B – Microsoft Exchange 2010
1. AddsBitness64to thePassword Expiry Module. The 64-bit version of thePassword Expiry Moduleis incompatible with 32-bit application pools. To resolve this the Password Expiry Module is set to only run against 64-bit applications.
2. AddsBitness64to thePassword Expiry Notification Service. The 64-bit version of thePassword Expiry Notification Service
is incompatible with 32-bit application pools. To resolve this the
Password Expiry Notification Serviceis set to only run against 64-bit applications.
3. AddsBitness64toKerboros Authentication.
The 64-bit version ofKerboros Authenticationis incompatible with 32-bit application pools. To resolve this the module is set to only run against 64-bit applications.
C - Microsoft Terminal Services Gateway:
AddsBitness64to thePassword Expiry Module.
The 64-bit version of thePassword Expiry Moduleis incompatible with 32-bit application pools. To resolve this the Password Expiry Module is set to only run against 64-bit applications.
D - Microsoft Windows Server Update Service (WSUS) and HTTP Compression:
The 64-bit version of theWindows Server Update Service (WSUS)
uses a type of compression calledxpress. This is a global setting that is not compatible with 32-bit application pools, which are required for Sage 200. The batch file disableshttp compression.
5. Each time you select an option, the following prompt appears:Do you wish to configure more services? [Y/N].
SelectYto configure each application that have installed. 6. When you have finished, select optionG.
7. The following prompt appears:Do you want to restart IIS now?
You must re-start IIS for the changes to be applied. To re-start IIS now, chooseY.
8. Press any key to close the batch file.
A copy of your originalapplicationHost.configfile is stored in the following location:
C:\Windows\System32\inetsrv\config \Backup – Pre Sage200 64bit Web Service Config.
If you revert back to the original version of theapplicationHost.configfile Sage 200 will stop working.
3 | Deploying Sage 200 across a network
Installing Sage 200 on Microsoft Small Business
Server 2011
If you intend to install Sage 200 on Microsoft Small Business Server 2011, you must consider the number of other services running on the server. Any installation of Sage 200 should be restricted to low user counts (6 – 8 users) and to small to medium size databases (~2GB). This is to make sure that performance is acceptable. To increase performance and for higher user counts and database sizes, use a separate server joined to the same domain.
Prerequisites
All editions
n Microsoft SQL Server and Analysis Services can be set up to run under the LOCAL SYSTEM account or a specified domain account.
n If you chose to run SQL Server and Analysis Services under a domain account rather than a local account, a new user should be created using the SBS ‘Create User Wizard’ and applying the users template.
n If SBS 2011 is your Domain Controller, you must create new users, including the Sage 200 Secured Services and Sage 200 Services Users, using the SBS Create User Wizard and applying the users template.
n If Analysis Services is installed as a named instance, you must open the firewall for the Analysis Services executable. Usually C:\program files\microsoft sql
server\msas10.sql2008\OLAP\bin\msmdsrv.exe.
Essentials editions
n Make sure that all SQL Server and Analysis Services services, including the SQL Server Browser, are set up to run under the LOCAL SYSTEM account or a specified domain account.
Standard and Premium editions
n You cannot install the SQL 2008 Management Tools on the SBS 2011 server. This is because SBS 2011 already has an instance of SQL Express 2008 for Sharepoint
management. You must install the SQL 2008 Management Tools on another server or a client machine attached to the SBS domain.
n Analysis Services and SQL Server must be installed as a named instance. This is because SBS 2011 already has an instance of SQL Express 2008 for Sharepoint management.
To install Sage 200 on SBS 2011
You must run theSage 200 64bit Web Service config.bat (v6.4)batch file which edits the
applicationHost.configfile to accommodate 32-bit applications.
1. Download theSage 200 64bit Web Service config.bat (v6.4)file from the support areas of the Sage website and copy it to the root C:\ drive of the SBS server.
2. Review the contents of the file to make sure you are happy with the following changes that will be made to theApplicationhost.configfile.
n AddsBitness64to thePassword Expiry Module.
The 64-bit version of thePassword Expiry Moduleis incompatible with 32-bit application pools. To resolve this the Password Expiry Module is set to only run against 64-bit applications.
n The 64-bit version of theWindows Server Update Service (WSUS)uses a type of compression calledxpress. This is a global setting that is not compatible with 32-bit application pools, which are required for Sage 200. The batch file disableshttp compression.
3. Right-click the file and chooseRun as administrator. 4. PressYto continue.
5. ChooseOption F - Microsoft Windows Small Business Server 2011.
6. Once completed the following prompt appearsDo you want to configure more services?
PressN.
7. To exit the batch file, select optionG. The following prompt appearsDo you want to restart IIS now?. To save the changes and re-start IIS, pressY.
You must re-start IIS for the changes to be applied. 8. Press any key to exit the batch file.
9. Install Sage 200. Select thesetup.exefile from Sage 200 Suite DVD. Right click the file and chooseRun as Administrator.
Note:Due the elevated security of SBS2011, you must run thesetup.exefile with elevated privileges.
10. Continue with a standard installation.
3 | Deploying Sage 200 across a network
Installation options for virtualised platforms
All elements of Sage 200 are supported in a virtualised environment if the following conditions are met:
n The underlying virtualisation platform has been accredited by the Microsoft Windows Server Virtualisation Program. Seehttp://www.windowsservercatalog.com/svvp.aspxfor further details.
n The host operating system is one of the supported operating systems outlined in the system requirements. SeeSupported operating systems on page 4.
Sage 200 is also supported when running in a Microsoft Hyper-V virtualised environment which is running one of the supported operating systems outlined in the System Requirements.
You must ensure that the server hosting the virtual machines is of sufficient specification to run both the underlying operating system, and the virtual machine that Sage 200 is running on.
Client hosted virtualisation
Sage 200 clients are supported when running in a client hosted virtualised environment, provided that the virtualised environment is running a supported version of Windows. For example:
n Sage 200 Client running in a Windows 7 VM hosted on Mac OS. n Sage 200 Client running in a Windows 8.1 VM hosted on Linux.
Due to the large number of virtualisation platforms it is not practical or feasible for Sage to test them all. If you encounter issues while running client software in a client hosted virtualised environment, you may be required to reproduce the issues in a standard (non-virtualised) environment. Refer to the Products and Services Terms and Conditions here: http://www.sage.co.uk/legal/terms-conditions/product-services-terms.html.
Hosting
Sage 200 is only supported in a hosted environment where the entire solution is hosted and users connect to the system via a Microsoft Terminal Services, Remote Desktop Services or Citrix connection.
Sites considering a hosted solution may wish to consider Sage 200 Online as an alternative method of deployment.
Installing Sage 200 using Citrix or Terminal
Services
Sage 200 is designed around a ‘rich-client’ architecture that is best suited to deployment over a local area network. Sage 200 is supported in a Microsoft Terminal Services, Microsoft Remote Desktop Services and Citrix Presentation Server environment with the following considerations:
n As Sage 200 is best suited to deployment on a local area network, where possible, we recommend that you run large batch processes, such asUpdate Waiting Postings, on a high specification client joined to the local area network.
If the entire solution is hosted remotely, large batch process tasks should be run ‘out of hours’ when more bandwidth and server resources are available. Alternatively, you may want to consider deploying Sage 200 Online.
n For Terminal servers, Remote Desktop Services or Citrix servers hosting Sage 200 client sessions, you must allow:
l At least 600MB memory per user on the Citrix/Terminal Server, for each client session
running occasional or one-off tasks, such as an account enquiry or price check.
l At least 1GB memory for each client session processing for any prolonged period of
time, for tasks such as checking in stock, or creating purchase orders.
l You should run large batch processes and updates on the local area network, or out of
hours.
l For Windows Server 2012 or 2012 R2, the application must be deployed using
'Session based desktop deployment' with a published remote desktop. 'Virtual machine based desktop deployment' is not supported.
l If you are using form launching for Sage 200 CRM, the Task Launcher Service:
o Needs to be enabled and set to start 'Automatically'. o Must be set to run in multi-user mode for each user.
o with Windows Server 2012 or 2012 R2 you must enable the .NET 3.5 and.NET
Extensibility 3.5features in IIS.
For information on setting up the Task Launcher Service, seeSet up Form Launching on page 178.
n We donotsupport running Sage 200 over Citrix or Terminal Services on Microsoft Small Business Servers.
This is because the Terminal Services Application Mode is disabled in these operating systems. See the following websites for further information:
Supported Versions of Citrix / Terminal Services/
Remote Desktop Services
W ind o w s R e mo te D e s k to p S e rv e r 2 0 0 8 R 2 p u b lis h e d v ia R e mot e D e s k to p S e rv ic e s W ind o w s S e rv e r 2012 & 2 0 1 2 R 2 P u b lis h e d v ia R e mo te D e s k to p S e rv ic e s C it rix X e n A p p S e rv e r v 6 .x (64 -b it o n ly , S ta n d a rd , A d v a n c e d , E n te rp ris e a n d P lat in u m E d it ion s )
Sage 200 Core Components
Sage 200 Server
1
Sage 200 Financials Client
1
Sage 200 CRM
Sage 200 CRM Server
1
Sage 200 CRM Client
1
Sage 200 Commercials
Sage 200 BI Server
1
Sage 200 BI Client
(including Sage 200 BI Admin Tool)
1
Sage 200 BI Excel Client
1
Sage 200 Optional Modules
Sage 200 Commercials Client
1
Sage 200 Project Accounting Client
1
Sage 200 ManufacturingSage 200 Manufacturing Server
1
Sage 200 Manufacturing Client
1
Sage 200 Migration Tool
Install Sage 200 CRM on multiple servers
If you're running a Sage 200 site with a large implementation of Sage 200 CRM, you may want to consider enabling the load balancing features of the application. This allows you to balance user sessions over multiple servers.
Sage 200 CRM can be implemented in a distributed environment, where a single Sage 200 CRM database is accessible through multiple web servers. The main benefit of the multi-server CRM solution is to provide efficient session balancing for sites with high numbers of concurrent CRM users. The balancing can be performed by Sage 200 CRM or by a dedicated load balancing application.
Prerequisites
n Decide which server you want to act as your primary Sage 200 CRM web server.
Note:If you are hosting Sage 200 CRM externally, this will need to be the web server with your externally accessible IP address. This server will act as the initial entry point for all Sage 200 CRM connections.
n A valid license that includes Multi-server Sage 200 CRM. The same Sage 200 CRM license key can be used on all of the Sage 200 CRM servers.
Installing multi server CRM
To install Sage 200 CRM across several servers:
1. Install the Sage 200 CRM Server on the server that acts as your primary Sage 200 CRM web server.
a. Follow the installation instructions as you would for a typical Sage 200 CRM installation.
b. Make a note of the installation name you specify in theInstallation namefield. This name is required when you are installing the other servers in the multi-server Sage 200 CRM environment.
c. Specify a separate SQL server to host the Sage 200 CRM database. If you are hosting Sage 200 CRM externally, this SQL server should be behind a firewall.
3 | Deploying Sage 200 across a network
2. Install the CRM server on your other web servers.
a. Follow the installation instructions as you would for a typical Sage 200 CRM installation.
b. Make sure that the name you enter in theInstallation namefield is the installation name you specified during the original Sage 200 CRM installation.
c. Change the name in theDatabase Serverfield to the server on which the Sage 200 CRM database was previously installed .
d. When you see the follwing messageThe database already exists for the Multi-Server install. If you wish setup can reinstall the database, clickNo.
Continue with the installation
e. Install all of the other web servers in the same way.
Configure your servers
Database configuration settings are set up automatically when you install multi-server Sage 200 CRM.
1. Log into Sage 200 CRM as an administration user. 2. OpenAdministration > System > Database. 3. SelectChange.
Check your settings are as follows:
CRM Server Names
The names of all the Sage 200 CRM Web Servers separated by semi colons
Do Load Balancing
Set this toTrueto make sure load balancing is performed by Sage 200 CRM.
If you want to use a specialized application to perform the load balancing, set this toFalse.
DTLS Server Name
Delayed Table Level Script server name.
This is the server that you want all Table Level Scripts performed on.
When all the servers are set up, theDefault database is the same on each of the web servers.
How changes are made
Sage 200 CRM is set up on multiple servers, any changes you make on any Sage 200 CRM server are reflected on all of the other Sage 200 CRM servers in the cluster. Metadata is refreshed on all the servers if any customization is carried out.
1. The list of all available servers is obtained from thecustom_sysparamstable. 2. The number of users logged onto each server is calculated.
3. The CRM Logon page for the server with the lowest number of logged on users is loaded. 4. Once logged on, the name of the server can be seen in the URL.
Load Balancing Example
The following example shows happens when four users log onto Sage 200 CRM.
In this example:
n Load balancing has been configured with a cluster of three web servers, with Web Server 1 acts as the primary web server and is externally accessible.
n The URL for Sage 200 CRM is the same for all four clients, and points to Web Server 1. n The Sage 200 CRM database resides on a separate SQL server, behind a firewall. n All web servers can access the SQL server.
1. User 1 logs on. None of the web servers currently have any logged in users. Because the load is equal amongst the servers, Web Server 1 processes the request and User 1 is logged onto the CRM Database.
3 | Deploying Sage 200 across a network
2. User 2 logs on. Web Server 1 already has one active connection so it balances the load by passing the request to Web Server 2. User 2 is logged onto the CRM Database with a URL pointing to Web Server 2.
3. User 3 logs. Web Servers 1 and 2 already have 1 active connection each, so Web Server 1 passes the request to Web Server 3 to balance the load. User 3 is logged onto the CRM Database with a URL pointing to Web Server 3.
4. User 4 logs on. All web servers have 1 active connection each so the request is handled by Web Server 1 and User 4 is logged onto the CRM Database.
Tip:Make sure your users close their browsers once they have logged out of Sage 200 CRM. This is to ensure accurate load balancing. When a user does not close their browser window after logging out, they will be logged back into the same server the next time they log in and this can result in unbalanced servers.
Security and firewall settings
Sage 200 uses a combination of Windows security and SSL certificates to secure communications between the Sage 200 server and client machines.
Windows File Permissions
Domain
When deployed in an Active Directory Domain environment the central Sage file share (e.g. c:\Sage) and associated files and subdirectories have their permissions set to restrict access to specific Sage 200 user and admin groups.
Workgroup
When deployed in a Windows workgroup environment the central Sage file share (e.g. c:\Sage) and associated files and subdirectories have their permissions set to Everyone | FullControl. We recommend that you set the Share and Security permissions as follows:
n Set the Share permissions toFullControl/Change/Readfor these users: n Sage 200 Administrators group
n Sage 200 Users group n Sage 200 Services user
n Sage 200 Secured Services user
n Set the Folder Security toModify/Read&Execute/ListFolderContents/Read/Writefor these users:
n Sage 200 Administrators group n Sage 200 Users group
n Sage 200 Secured Services user
Note:Once you have installed Sage 200, restart the machine before you access System Administration . This is so the security permissions are set correctly.
Required Firewall Settings
Sage 200 runs a variety of programs and services that can be affected by security firewall settings. The following table details the ports that need to be opened in order for Sage 200 to function correctly.
3 | Deploying Sage 200 across a network
Server Port Details
Instance)
Database Server (Named SQL instance)
UDP Port 1434 Named SQL Instance
Sage 200 File Server TCP Port 139 File & Print Sharing
TCP Port 445 File & Print Sharing
TCP Port 137 File & Print Sharing
TCP Port 138 File & Print Sharing
Sage 200 Application Server TCP Port 10443 HTTPS
Sage 200 Self Service TCP Port 10444 HTTPS
Sage 200 CRM Server TCP Port 80 HTTP
TCP Port 443* HTTPS
You will need to open port 443 and grant permission for https://licensing.services.sage.com and www.google.com. Google needs to be allowed as when it checks the licence, it pings Google to ensure there is an internet connection prior to going to the licensing server.
The following ports are required for Sage 200 Business Intelligence:
Server Port Details
Database Server (Default SQL Instance)
TCP Port 2383 SQL Analysis Services
Database Server (Named SQL Instance)*
TCP Port 2382 SQL Analysis Services
UDP Port 1434 SQL Browser Service
* For more information see,http://msdn.microsoft.com/en-us/library/ms174937.aspx#bkmk_ named.
Additional firewall settings if you have a named SOL instance or
dynamic ports
b. SelectSQL Server Network Configuration > Protocols for<your instance Name>.
c. Right-clickTCP/IPand selectProperties.
d. Select theIP Addressestab and scroll down to theIPAllgroup. e. The current port is theTCP Dynamic Portsnumber.
3 | Deploying Sage 200 across a network
Database configuration and maintenance
Once you have sized your system, you can start looking at optimising access to the database to ensure maximum performance. For smaller databases this is not so essential but as the database increases in size, especially once it starts getting over 4GB, real performance improvements can be obtained by having appropriate database configuration and maintenance plans in place.
Setting up a Database Maintenance Plan
We strongly recommended that you set up a database maintenance plan as part of your Sage 200 deployment. You should include both the Sage 200 and CRM databases in the maintenance plan.
For detailed information on setting up the tasks and plan for SQL Server please refer to the manufacturer’s web site but we recommend the following:
To set up a new database maintenance plan for Microsoft SQL Server
1. Open Microsoft SQL Server Management Studio and connect to the instance of SQL thathosts your Sage 200 and CRM databases.
2. Right clickManagement > Maintenance Plansand selectMaintenance Plan Wizard. 3. Follow the steps in the wizard until all the tasks have been defined. We recommend that you
include the following:
n Check Database Integrity– We recommend that you run this nightly for both the Sage 200 and CRM databases.
n Shrink Database– Avoid this option for large or rapidly growing databases. see the following section onDisk Management on page 41for further advice on database size. n Reorganize Index– We recommend that you run this nightly for both Sage 200 and
CRM databases.
n Rebuild Index– We recommend that you run this weekly for both Sage 200 and CRM databases.
n Update Statistics– We recommend that you run this nightly for both Sage 200 and CRM databases.
You should only run maintenance plans ‘out of hours’ when no users are on the system. We recommend that you run the plan once initially to ensure the plan completes successfully and then schedule the tasks to run automatically overnight.
Before any maintenance tasks can be run, check that the SQL Server Agent is running.
disabling theAuto ShrinkandAuto Growoptions within SQL Management Studio and manually padding the database to accommodate future growth.
To Disable Auto Shrink
1. Open Microsoft SQL Server Management Studio and connect to the instance of SQL that hosts your Sage 200 and CRM databases.
2. Right clickDatabases > <Sage 200 database name>, and selectProperties. 3. Choose theOptionspage.
4. SetAuto ShrinktoFalse.
5. Repeat for your Sage 200 CRM database.
To manually grow your database
1. Open Microsoft SQL Server Management Studio and connect to the instance of SQL that hosts your Sage 200 ERP and CRM databases.
2. Right clickDatabases > <Sage 200 database name>, and selectProperties. 3. Choose theFilespage.
4. For both the MDF and LDF files, click the browse button in theAutogrowthcolumn. 5. Set theFile Growth > In Megabytesvalue to an amount of disk space that will allow for
plenty of growth.
For example, if your current database is 2GB, you could set this value to 6GB. Increasing this value does not increase the size of your SQL backups.
6. Repeat for your Sage 200 CRM database.
Other Disk Management Tips
n Separating the operating system and database onto their own separate drives is best practice for large databases to make sure that Windows and system disk don't compete with SQL IO requests.
n Make sure that the space you’re allocating to the MDF/LDF files is largely contiguous and kept defragmented.
n The Sage 200 database must remain synchronisied with the Sage 200 CRM and Sage 200 BI warehouse and analysis services databases.
Make sure you backup or restore Sage 200 CRM and the Sage 200 BI warehouse and analysis services databases at the same time as your Sage 200 database.
Database management for Sage 200 Business Intelligence
For Sage 200 BI, if you need to recover to a specific point in time (i.e. just before the disaster happened) and wish to avoid hard disk space issues, we recommend that you:3 | Deploying Sage 200 across a network
This is because the data warehouse’s database log file grows when the data warehouse is upgraded and cubes refreshed using the Sage200 BI Admin tool. This growth is usually proportional to the number of record updates performed on the data warehouse.
If you have a relatively large Sage 200 database, the data warehouse’s log file will grow significantly every time the data warehouse is updated.This can lead to disk space problems on the database server fairly quickly for large datasets, and over a longer period of time for smaller datasets.
However the log file contains information about all the changes made to the data warehouse required for the database administrator to perform any required disaster recovery procedures.
Set up a disaster recovery mode
The disaster recovery mode that you choose for the data warehouse database depends on how you use BI.
When 'As At' cubes are updated, data is pulled down from Sage 200 to provide a typical snap shot of your data for a specific point in time. The recovery mode that you choose depends on whether you need to recover your data this specific point in time or whether the accuracy of the as 'as at' data is not crucial, i.e. the snap shot is out by one day.
You can choose one of the following modes:
n Full - choose this to recover data to a specific point in time.
This uses full, differential and transaction log backups to provide the best protection against failure. It allows the database to be restored to the point of failure or to a specific point in time. n Simple - choose this if the accuracy of the “as at” status of the BI reports is of low
importance.
This recovers data to the most recent successful full database or differential backup. The underlying transaction logs are truncated each time a checkpoint is issued for the database. This mode restricts the database log file growth.
To set the data recovery mode
1. Open Microsoft SQL Server Management Studio and connect to the instance of SQL that hosts the Sage 200 BI databases.
2. Right clickDatabases > (data warehouse database name), and selectProperties. 3. Choose theOptionspage.
4. Select the requiredRecovery model.
Reduce the database size
This will be blocked until users are already connected to the database have close their connection. This ensures that no transactions are rolled back unexpectedly (i.e. a user in the middle of updating the warehouse).
Follow the steps below or run the following SQL script:
[ALTER DATABASE
database-name SET RESTRICTED_USER]
.a. Open Microsoft SQL Server Management Studio and connect to the instance of SQL that hosts the Sage 200 BI databases.
b. Right clickDatabases ><data warehouse database name>), and select
Properties.
c. Choose theOptionspage.
d. In theOther options > Statesection, setRestrict AccesstoRESTRICTED_ USER.
2. Backup the transaction log and database.
a. Right -click the data warehouse database and selectTasks > Backup
b. Make aFullbackup if you don't already have one, otherwise skip to the next step. c. Run the SQL command [checkpoint] against the data warehouse database. This
flushes the contents of the transaction log into the main database files.
d. Make aTransaction Logbackup. Make sure thatTruncate the transaction logis selected on theOptionspage. This is only be available if your database is inFull RecoveryorBulk Loggedmode.
e. Make aFullbackup of the database.
3. Shrink the database.
a. Right -click the data warehouse and selectTasks > Shrink > Database. b. Verify that the database log file is now reduced in size.
4. Remove theRestricted userset in step 1 by either reversing the steps or by running the following SQL script:
[ALTER DATABASE DATABASE-NAME SET MULTI_
USER]
.Installing Sage 200
Sage 200 uses a single installer to install all modules apart from Sage 200 CRM. When you run the Sage 200 installer, you can choose which modules of Sage 200 you install.
Before beginning the installation procedure, please refer to the sectionDeploying Sage 200 across a network on page 17to make sure that you know where you want to install each component.
To install Sage 200 CRM, use the Sage 200 CRM installer. This can be found in the CRM folder on your Sage 200 DVD. For instructions on installing and setting up Sage 200 CRM, seeInstall and set up Sage 200 CRM on page 153.
Key facts about the installer
1. You use the installer to install the various components of Sage 200 on your server and on your client machines. Use the installation wizard to select the relevant server components and client components.
The following table shows which components of the additional modules are installed when the Sage 200 components are selected.
Sage 200 components selected: Additional components installed, if selected:
Sage 200 Server Sage 200 Business Intelligence Server
Sage 200 Manufacturing Server
Sage 200 Client Sage 200 Business Intelligence Client
Sage 200 Manufacturing Client
2. Where possible, the Sage 200 installer checks to make sure you have the correct pre-requisites installed. When this is not the case, an error message is displayed. You must install the required components and run the installer again.
SeePrerequisites on page 12.
3. The Financials, Commercials, Project Accounting, Bill of Materials,and Self Service modules are always installed. They are activated by your licence key.
4. The Sage 200 Client must be installed on the same machine as the Sage 200 Server. Therefore, theSage 200 Clientis selected by default when choosing to installSage 200 Server.
5. You can choose whether to install Sage 200 Business Intelligence and Sage 200
Manufacturing, however you must install both the sever and client elements on your server. You don't have to install BI and Manufacturing on all client machines.
6. If you are installing Sage 200 Manufacturing on a PC which has Sage 50 Manufacturing installed, uninstall Sage 50 Manufacturing before installing Sage 200 Manufacturing.
7. To install Sage 200 Self Service on separate external facing web server, use the Self Service standalone installer. This can be found on the Sage 200 DVD.
SeeInstall Sage 200 Self Service on an external facing web server on page 58.
