Hi Mr_Mayor if you connect two Sage 300 application server "sites" to the same SQL server, and users attempt to concurrently access a company database, the best thing that can happen is a database lock preventing users from one or eventually both sites from logging into that company database.  The worst thing that can happen is horrific database corruption.  With pre enhanced security sites you're likely to get locks first. At a post-enhanced site heaven knows what will happen - could be a complete mess.  Its also a violation of site licensing rules.  There should never be a need to do this, what is the business case?  If you're operating an RDS environment, on the second server simply deploy the Workstation Setup components, NOT the main server installer components.  This will spread out the load without risking all the problems of having two server sites.  Please go ahead and provide detail on your server infrastructure layout, the problems you're experiencing, and what you want to improve i.e. migrate to a more powerful server and we'll do our best to assist in putting a safe technical procedure together.  Also, please try and get in touch with your Sage 300 Business Partner as they're generally your #1 contact for issues like these.  Good luck!...Tim.

Wait... am I reading this correctly? OP wants to separate the current single box db+sage300 to dedicated boxes. 

You 100% can have multiple Sage300 application servers hitting the same db. It's one of the three configuration options in the installation guide. "Client-Server Network: In a client-server configuration, Sage 300 programs and databases are on one or more dedicated servers." - page 9.

Database must be on a single server. Shared data folder must be on a single server. Sage 300 Program Files can be on multiple servers.

Install Sage300 program files and system manager on the other box, during install set the SharedData path as a UNC path to the current SharedData, press install.

See Administration / Installation and Admin guide here: https://cdn.na.sage.com/docs/en/customer/300erp/Documentation.htm

The biggest gotchya is to make sure both servers have the same Sage300 program file versions and patches. Also, location of reports if you're not using customised directories to point to a single path.

Wait... am I reading this correctly? OP wants to separate the current single box db+sage300 to dedicated boxes. 

You 100% can have multiple Sage300 application servers hitting the same db. It's one of the three configuration options in the installation guide. "Client-Server Network: In a client-server configuration, Sage 300 programs and databases are on one or more dedicated servers." - page 9.

Database must be on a single server. Shared data folder must be on a single server. Sage 300 Program Files can be on multiple servers.

Install Sage300 program files and system manager on the other box, during install set the SharedData path as a UNC path to the current SharedData, press install.

See Administration / Installation and Admin guide here: https://cdn.na.sage.com/docs/en/customer/300erp/Documentation.htm

The biggest gotchya is to make sure both servers have the same Sage300 program file versions and patches. Also, location of reports if you're not using customised directories to point to a single path.

Hi SergeB nope OP wants to run two SITE folders hence his comment "it's a transitional process of a 24/hr client. the backup server isn't licensed but will allow more of a handover takeover unlike shutdown and migrate. they don't have the downtime." where his all-in-one SQL/App server becomes separate App server + SQL Server, but to avoid downtime he wants to have both in production briefly so he can try to move users from one to the other without downtime. Well at least that was my interpretation hence I asked for more info.  Instead with additional info I was going to suggest he does a workstation setup on the second/new machine, make sure it works, then help him get the programs over - basically end up at the same place exactly what you said and referenced in the doco as I didn't expect him to have a win with the doco without extensive Sage 300 admin experience.

However I then realised the problem is more likely to be database related then database server related as the Sage 300 programs themselves on the app server really don't create much overhead or resource utilisation because all the execution take places on the end users' RDS or individual workstation.  Therefore I thought "he will go to all the effort of introducing a second application server and then find performance is still slow with performance problems in the company databases".  So before OP went to all the trouble, I thought I would give him some SQL optimisation tips as OP may find he can fix up SQL performance and eliminate the initial cause without significant server changes.  Many times I've had clients with slow bank recs and the like believe they need to provision additional resources only for some indexing and index rebuilds to make the bank rec 5 to 10x faster.  Worse still is when a lot of money is spent on additional hardware/cloud capacity only for it to not help and end up an optimisation issue in the first place. Of course I could be totally wrong and was hoping additional site information would help clarify.  So that's the background behind where I was heading with this case

hi 

thank you Sergeb. you are both right to some extend. 

the scenario is migration from one server to the other but without downtime

so my thought process was to install a separate server, start migrating people from one server to the other without downtime. in the end the current Sage box becomes only the database server whilst I have a new server altogether. I was thinking of having both programs and data on the new box then link the workstations via odbc to the current box

In the end I will have an application server and a database server on different boxes as I have a huge memory consumption thats making the server freeze and hang. I will then look into the script by Tim on how to handle the database, slowness and indexing as i have other 3rd party application who i feel are the biggest contributor to the memory spiking issue as sage wouldn't be that resource hungry for basic Sage modules