I am going to upgrade the shared hosting of my web site to a VPS mainly because my database is getting too big, > 200mb. The basic VPS plan on CrystalTech where I am hosting has 512mb ram, disk space shouldn't be an issue. If I install SQL Server 2008 Web Edition (which I have license) on here do you think it will slow the server down too much considering the amount of memory available? Has anyone experienced similar setup.
512 Megs of RAM isn't a lot any more. I'd recommend upgrading that to atleast 2 Gigs if you are going to run SQL Server on the server. The general rule with SQL Server is more RAM is always better (as long as the SQL Server can use that much RAM).
SQL Server will very quickly take all the memory available in the machine, leaving nothing or next to nothing for the web server and the OS. SQL Server will in order to improve performance cache as much information as possible in memory so that it has to go to the disk as little as possible (no matter what kind of database design you use). With so little memory available (you'll probably only end up with 100 Megs of RAM if that available for the buffer cache) you won't be able to store much information in RAM, and you'll be hitting the disk all the time looking for data.
The answer is ... It depends.
SQL Server performance is directly related to disk speed and memory. If you are using SQL Server for a key->value database without complex joins -- 512mb will probably be ok for now (it is the minimum microsoft recommends however). Its impossible to guess how heavily your database will be used, what your app does, how many users you have, etc. The memory will almost certainly be a weak point as you try to scale out.
Make sure you have addiquate swap space, and monitor your memory usage. Good monitoring will give you good metrics on how you need to scale your system, and where, EXACTLY the bottlenecks are as they develop.
Best thing to do is try it with all the defaults & see how it goes.
Obviously this isn't recommended & you should be aware of the issues & risks you can run into, but it is possible. Squeezing sql server into this much RAM isn't going to be fun and will affect performance.
You can tweak the following settings to reduce the memory footprint of sql server:
Restrict the max server memory to stop sql server using all the memory on the server for buffer cache. I'd start with something like 200 MB & play from there.
Reduce the number of max worker threads. Assuming your running x32 your default is 256. This will affect the number of threads sql server has available to service requests.