So my main windows server is being bogged down by MSSQL. I have been looking into various options including windows azure and have come to the conclusion a dedicated server running MSSQL will be the best option.
My question is - is it really as simple as installing MSSQL and blocking all ports bar 1433 and RDP on the new server ?
Obviously security and a backup system will also need to be deployed.
Second question, should I go for a server within the same network as the main server, or will an external server within the same geographic location suffice ?
It is mostly that easy. There are a few things that will have to be done. I have listed them mostly out here:
1. It is as simple as blocking all ports except 1433(TCP) and 1434(UDP) for MSSQL. Don't forget the secondary port. It is often times missed, and can cause some issues. I also suggest scoping these ports down to just accepting connections from your web server. This will greatly increase security to this machine. You can open RDP if you like, but I would also scope that down through the firewall as well, to your home/officeIP address.
2. The lower the latency between the two servers, the better. So, if you are looking in the same geographical location, but not on the same network, you will most likely be fine. If you can do the same network, that would be even better, but by no means required.
3. You will also have to update connection strings on your site, or settings in your application that uses MSSQL. Currently right now, it is probably referencing the IP of 127.0.0.1 (or localhost). This will have to be updated to the new database server's IP address.
4. You will need to bring your site/application down for a brief time while you backup/restore the database from your old server to your new server.
Feel free to ask if you have any additional questions regarding this.