I am new to SQL and am linking tables from an Access GUI. Once my application is up and running do I need to create my SQL server on a machine that only houses the SQL, or can it be used as a general server with other applications on it that are not SQL server based? Did I make any sense at all with this question?
There is nothing to prevent you from running anything you like on a machine with SQL Server installed on it. However you'll have to use your own judgement on whether that's a good idea or not.
The only way to know for sure if it will work in your system is to profile (using perfmon or the like) and make sure the load on the cpu and I/O is acceptable.
In our shop we generally let SQL have the whole box to itself, but we put very heavy loads on it.
I would recommend that you don't put IIS and SQL Server on the same box, but only because I dislike the idea of putting SQL Server on a box that's exposed to the internet. Better to limit your surface area of attack.
We're a small shop. Total database size about 2GB, SQL Server 2000. Scope; about 500K orders averaging about 5 line items each, with all the associated payment, etc. tables.
I see little performance difference (on a large query - like a 1 year detailed sales report) between running the database on my workstation (that nearly always has about 15 memory hog windows open) and our dedicated SQL Server.
However; our other servers have to be rebooted with software updates or problems, they require other firewall ports be open, take much longer to reboot and sometimes require calling users (if you have to reboot your SQL Server server, you want minimal hassle), etc.