Post reply

Backup Database Across the Internet

  • June 16, 2004 at 4:39 pm

    #82995

    I’m trying to come up with a solution to provide a periodic secure backup service for our clients’ MSDE databases that we would house on SQL Server.  What’s the best way to backup an MSDE database over the internet?  Would you recommend replication, log shipping, something else?  Can you also explain pros and cons of these strategies, as I’m a newbie? 

  • June 17, 2004 at 5:21 am

    #510790

    I guess it depends on a variety of factors:

    1. What's the size of the client db?
    2. What are the security requirements (authenticated access, logging, encryption, etc).
    3. What is the availability timeframe on the SQL Server side (when does the backup data have to be available)?
    4. What security devices are in place between the client environment and the SQL Server?

    There are many ways to address this.  A simple solution may be creating daily backups of the client db to a FTP accessible directory on their end, then creating periodic jobs to retrieve that backup file.  FTP is simple, and provides authentication and even encryption.

    Adam

  • June 17, 2004 at 2:17 pm

    #510929

    Thanks Adam!  Here are some more details…

     

    1. MSDE has a max of 2 GB.  I’m guessing the average would be about 1 GB.
    2. It has to be very secure, I’m guessing all of the above.  My ignorance prevents me from being more specific.
    3. We need to be able to do a full recovery within 12 hours.
    4. Firewall, perhaps on both sides, not sure what else.

     

    Is your FTP suggestion something we can do with SQL Server or would this require some kind of script?  We would also like the ability to query the backup databases for research purposes.

  • June 17, 2004 at 2:53 pm

    #510933

    Seems like you should be able to back the db up locally and set up an ftp job (perhaps using the xp_cmdshell procedure) to send it over to you.  You would still have a local copy so it could be used if needed.  That would allow a recovery to be completed in much less than 12 hours.

  • June 18, 2004 at 5:14 am

    #510984

    Joe,

    If you need encryption for the transfer (which may be excessive, but only you know your security requirements), then you have to use Secure http://FTP.   I'm familiar with the CuteFTP.com suite of products.  You can specify directories and permissions for each customer, and all access is authenticated and logged.

    You may run into issues with the size of the backup.  In my experience, file transfers greater than 500mb have a tendency to be unstable.  You may want to considering using WinRAR or some other archiving and chunking tool. 

    Both WinRAR and SecureFTP have scripting capability, so you can build a batch or script to execute each task automatically, and call the job from within SQL Agent or Windows Scheduler.

    Of course, this is just one method.  I'd be glad to see other solutions to this problem.

    As Matthew stated, once you have the backup, you can have a functional database up and running within minutes, both for backup and reporting.

    AT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply