Push FTP with SQL Server[1]

Push Those Files Away!!!!

Introduction

When DTS was released, I thought this was a useful, but overhyped tool. After all, I'd spent seven years developing all types of SQL applications that made do with xp_cmdshell for anything that I couldn't do in T-SQL. Then last year, I started working for a company that used a "Managed" service for it's website. Bascially this means that we paid some company a rental fee for servers and they kept the servers running, backed up, patched, etc. in a data center. The data center I used happened to be in Dallas, which is fine. Except, I live in Denver. Kind of a hassle for hands-on work. Fortunately we were running on Windows 2000 and DTS can in very handy for building some data transfer applications.

The Problem

Recently we moved to another data center in Denver and upgraded to SQL Server 2000. Tada!!!!! Finally, DTS included an FTP task. I eagerly dug into DTS (with the aid of Professional SQL Server 2000 DTS) and got ready to develop an automated task to send the database backups to my development server each night.

I perform local backups from SQL Server and then need to copy these to a remote server. In the current setup, I wanted to copy them to a utility server on the local network, but a server that was not part of the domain (for security reasons). This makes transfers using standard Windows file copy utilities problematic. The solution that I wanted to use was ftp since this is a standard, open solution. At the present time, all my servers are Windows, but the possibility exists that we may implement some Linux or Unix servers.

One problem: The FTP task only "Gets" files.

How absolutely, completely, totally annoying! How much effort is there to "push" files when you have already written an object to "Get" them!!!!!!! (SQL Server Team, hint, hint).

The Solution