x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 14 subscribers
  • Views 1055 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
Luke Crooks

Sage 200c with remote SQL

SUGGESTED
Posted By

Hi.

So I am not a sage engineer, but a Linux system admin, I work for a company that also has an external sage IT provider that handles a local on-site server with Sage/SQL etc.

The current setup is a traditional DC and separate sage/sql combined server.

The sage server is very old (Windows 2012 R2) and is in (overdue) need of replacing.

Instead of replacing the existing setup like-for-like I suggested getting a new server, and then configuring SQL Server in Azure, this would mean we would have a much more "robust" data storage solution. Currently we have a SQL server on the sage server, and another SQL server in replication on our DC. This works fine, but the backup options offered by Azure offer many more fail-safes. 

Initial discussions with our Sage provider were ended with "you can't use remote SQL for sage as it's too slow". This surprised me, as all of our other applications use remote SQL and actually work out faster than "on premise". So we took a full backup of our existing sage database, loaded it into a small spec SQL server in Azure (only 2gb of ram) and performed several large queries, such as selecting and returning 300,000 rows of data from various tables. In every test, the remote SQL was faster than querying the on premise SQL, so I went back to the provider with this and they said they would look further.

About a day later I got a messages saying "it's not possible" but with no technical reasoning behind it. They told me they had configured the database in Azure the same as the local one, yet it just "would not work" I connected to this DB to take a look, and it had no tables such as: Sage200Configuration, which our current local DB does.

So my question is, is there a way that we can have sage 200 installed on a local server, and then use a remove SQL database (such as Azure). And if not, what is the technical reasoning behind this?

Our local sage server also handles analogue connections to various manufacturing machines we have on site, making moving the entire server to Azure or SPC (sage partner cloud) not really a viable option. I have looked through the Sage200 system requirements and no-where does it state that the SQL server must reside on the same server as the sage install, only that it cannot be on the DC.

  • 0

    Luke,

    The below information is taken directly from the Sage 200 Installation guide (exact page would depend on the version, but around page 9):

    Wide Area Network (WAN) and Virtual Private Network (VPN) connections We do not recommend running Sage 200 over a Wide Area Network (WAN) or a Virtual Private Network (VPN). These connection types usually increase latency in communications between the Sage 200 server, the Sage 200 client and the SQL server. High latency between these components can cause unacceptable performance and database timeouts.

    From our own experience we do have sites running over a WAN, but only where there is a Gigabit 'pipe' between the sites and the latency is low.

  • 0 in reply to Steve Brown_1

    Hi Steve, thanks. We actually have two leased lines on site with automatic failover at 1000/1000 specification, so latency is not an issue. Direct SQL testing between the local SQL server and remote SQL server also showed an improvement, as opposed to a draw back. It was more of the technical possibility of using remote SQL that I wanted clarification on.

  • 0 in reply to Luke Crooks

    HI,

    Sage are very strict about this, and do not sites that use any form of Wide area connection regardless of speed and technology.

    If you do, you are risk of your sage BP not being able to provide you with support if there issues.

    Have a look at the sage partner portal product which an azure hosted offering of Sage 200 via Business partners who have signed up to the service....we have not.

  • 0
    SUGGESTED

    Hi Luke, 

    As per Toby's response, WAN is not tested by Sage so will therefore not be supported.

    This can be confirmed by looking at the system installation guides:

    desktophelp.sage.co.uk/.../Guide-section.htm

    I appreciate you have a gigabit connection which has worked fine when you have been testing but we would not be able to provide support if you experience issues whilst set up on a WAN connection.

    Thanks
    Kirk

*Community Hub is the new name for Sage City