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.
