SQL Server Performance Forum – Threads Archive

Running an FTP server over an SQL Server..

I need input on this… There is pressure to have an FTP Server Installed over my SQL servers
in order to have the backup files (mind you … 45GB plus in size) copied
back and forth between them… I ‘ve got 2 servers, call them Production,
and Backup. I don’t really enjoy the idea. What do you think?

---

Is the concern realated to security? If so install ssh and do a secure file transfer.
That seems easier that monting sharers and what not. Are you backup up the backup files to tape? If not you have to get a copy of the
backup off of that production server – seems like transfering the files to another server will
give you a little more protection in case of a disaster. Again if you have money in the budget, a copy of SQL Litespeed will greatly reduce (80% reduction) the size of the backup file. Makes file transfer a lot easier…

---

True, you can use third party tools to reduce the overhead in copying files. But if the budget doesn’t support then you can adopt using differential backup solution and full BACKUP once in a week to reduce the network overhead. Basically you may have issues in using FTP on SQL Server, but you can test the pattern and schedule the timings in such a way that will not affect the SQL performance during the FTP process. _________
Satya SKJ

---

My only concern in this scenario would be the impact on resources i.e. CPU usage, Network bandwidth, Disk I/O and physical memory. So the first suggestion will bw to concur with the suggestions of the rest of the members of the forum – reduce the backup size / go for differenial backup and also schedule the ftp upload / download during non peak hours. Secondly I would suggest that you conduct a test run of this configuration on production. And do the test during average load (and not idle load) condition. This is because, ultimately the backup times for the database is going to increase. By testing under avg load you are just exploring the possibility of backup + upload time to spill over the idle time window. After this test, if you don;t see any impact, i think there should not be any problem to make SQL server an FTP Server. P.S. If you are considering the possibilty of reducing the backup size, try Zipping the database backup. In certain cases, I have seem the size of the backup to reduce by over 35%. HTH. Gaurav

---

I don’t see is why the production server(mainly in an uploading role) should be a fulltime FTP server.The service can be enabled only when you need to FTP back the backups.

---

My concern has to do with the cpu utilization and network utilization as well. Also, yes, already the full backup is twice per day (actually for reasons that have to do with business case). From there on, differencials are per 30 mins, and are about 2 – 15 gig, depending on the transaction load, this is quite a busy SQL server mind you. So this is why I don’t fancy the FTP server idea, I am afraid that FTP server running, and being utilized, will hog the CPU and the network, when the differencials will be sent over the other server. I am posting this cause I just ask for your opinions, kinda like a feedback from people with experience. Thank you in advance.

---

If the hardware is resource enough with memory, CPU, RAID usage in terms of high-spec then definetly you can deploy fulltime FTP server on SQL SErver. BTW have you tested your database with stress-testing, what is the predicted size and usage of the database? _________
Satya SKJ

---

When we move backup files between servers, we just copy them over shares. Using FTP, seems to me, to be more work that it needs to me. I have not tested performance between these two options, but my gut feeling is that a copy will be faster than an FTP. If you do run into time problems when coping files, consider using a third-party program, such as SQL LiteSpeed, to reduce the size of the backups. I often do this in order to significantly reduce copy times, especially when copying over a WAN. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

Brad, I think FTP will be a great option as compared to file copy over network when the machines are physically distant and also parts of different WAN’s. Gaurav

---

No,
Machines are on the SAME hub, same net, the guy that administers the net,
is just someone that hates Netbios and somehow things that ftp is the answer to all his problems. I witnessed today raise to latches, a VERY great one. Also the whole copy process over FTP, was very much slower, compared to NetBios. I think that netbios copy is much faster compared to it, when we talk same lan at least.

---

If both the machines on same network then why do you require FTP, you can use normal COPY command to transfer the files. _________
Satya SKJ

---

Gaurav — I should be a little clearer. I use COPY over both LANs and WANs, when they belong to us. But when I copy large backups to other LANs or WANs (not controlled by us) we use FTP. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

Your net admin (who hates ‘netbios’) should first of all think about replacing the HUB (you said you have one on the network) with a SWITCH…If that is right, the COPYing of the DB might just realize a boost. And maybe improve all other network hardware components on your LAN.

---

Thank you very much, they actually listened to reason,
They don’t use the FTP service anymore. And of course it’s much better, faster, no latches increment, as
I anticipated it. Thank you so much for your Input.

---

You are welcome. I hope this is the beginning of better co-operation with your net admin. Like a Chinese DBA once said: "May your database live in interesting times".
NHO

---

]]>