Is there any advantage to having a separate SQL server for a Moodle installation? Our Moodle covers about 2,000 end users and is fairly straightforward. What would be the disadvantages of having the databases Moodle uses on the same production server?
The advantage of separate servers is that you have more total hardware (memory, processor, hard drives) running the server.
The advantage is bigger if you have serveral web servers all talking to the one database. This is necessary for large sites.
The disadvantage is that each connection from the web server to the database has to travel across the network, rather than being in the memory of one computer. That makes it slighly slower for each single user, even though the multi-server system can cope with more simultaneous users.
The fact that you are planning to buy a new server means either you have already reached the limits of the present setup or expecting that to happen soon. How do you know that the added hardware will take over the additional load? I mean, have you identified the database server as the bottleneck? If not, how to go ahead is explained in the documentation linked in to the introduction of this forum.
Otherwise the additional energy dissipation will only help to melt the arctic ice cap.
I know, this is not going to help you. Still I want to note that by running more efficient software (Linux against Windows Server, MySQL/PostgreSQL against MSSQL) you can keep the hardware requirements low.