5

Just wondering what kind of processes you guys have for transferring production data offsite for analysis purposes?

I have read some of the literature but I am not sure of the overheads involved and the setup processes.

  • Replication: With the production acting as both the distributor and publisher. I will employ a push system that updates my database on my analysis server weekly
  • SSIS: No experience but seems to be the 'standard' way?
  • Manual: Use windows scheduler to zip back my backup file and open up an FTP connection to my analysis server
  • Mirroring: No experience but used more for high availability?

FYI, I am using SQL Server 2008 with Windows Server 2008 R2. MY DB is about 36 gigs. I am only interested in the data.

Other methods welcome!

*EDIT Additional information: I don't mind the database being read only and I am looking to run this process once a week.

3
  • what's the change rate of the database in question?
    – Jim B
    Feb 11, 2010 at 2:27
  • not very much at the moment. you can assume the database is constantly aroud 36 gigs
    – Nai
    Feb 12, 2010 at 10:53
  • How close in terms of bandwidth is your 'analysis server' to your database server? How much down-time is permissible for your 'weekly load'? Feb 12, 2010 at 15:09

6 Answers 6

7

You could look at log shipping, very similar to your 'manual' approach but uses built-in SQL components to do the work and works with transaction log backups.

A great explanation and comparision to db mirroring can be see at http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx

1

As a mere suggestion, you say your db is 36 gigs. Depending on the compression + speed of transfer you might find it easier to dump it on a USB drive and snail mail it. Furthermore, some ftp will never allow files of > 4GB. Incremental pushes might be your way, but again consider the worst case: what happens if your data changes faster than your db can push it to the replicator?

1

I have a client who needed this exact thing. They couldn't accept the db being inaccessible when the logs restore so log shipping was out. Replication was not possible because their schema would be too much of a hastle. Mirroring wouldn't work because it's read-only, and they needed a writable database... but those writes did not have to go back to the main server.

I ended up writing a maintenance plan and a few scripts that does a full backup of the db in question (120 GB), then uses xp_cmdshell to copy the file to a network share on the other SQL server, and then lastly executes a job remotely (you can set what server to execute on in the plan step) to do a restore of the database.

You may want to check out Tara Kizer's isp_Restore script which can help you with this. http://weblogs.sqlteam.com/tarad/archive/2005/11/08/8262.aspx I ended up writing my own script because hers didn't do exactly what I needed, but it should at least get you started.

4
  • Keep in mind, this job I created only runs once a night, however it can easily accommodate more frequent updates with diff/trn backups, but again the client couldn't accept the database being unavailable during the restores. If you don't mind the DB being inaccessible, log shipping might be perfect for you depending on how heavy your writes are cause you will get big TRN's that will take longer to restore. Just set the restore frequency to once an hour. I have also done this for clients who can take the inaccessible DB during restores and didn't need writable databases.
    – tcnolan
    Feb 10, 2010 at 23:42
  • Shouldn't there be somesort of 'RECOVERY' option? I mean afterall log shipping is also meant to provide a backup of your database? How else would you restore it anyway? It sounds like log shipping might be for me considering I don't mind it being read only. However, there probably will be a need to create bespoke indices which is not available unless the DB can be written to.
    – Nai
    Feb 12, 2010 at 11:15
  • If the database restores nightly, the database will be offline, which is the same effect as log shipping. Why not just use Log Shipping but set it to only run once a day at whatever time the script runs. This way it's just one restoration a night, and you're not copying the entire database, and would most likely execute quicker than a full backup/restore... Feb 13, 2010 at 23:27
  • For me anyway, the client needed the full backup/restore because of how they were working with the data. I did it this way because I wanted to have complete control of what was happening, also log shipping would require the LSN to be maintained at all times which there was no guarantee of which would break log shipping. With my script I can configure it to do the full backup/restores and get diffs or trns worked into the mix as well if they wanted more up to date information throughout the day. Log shipping would have to be reconfigured every time they wanted to change things around.
    – tcnolan
    Feb 16, 2010 at 23:02
0

Log shipping is great for analysis, but be prepared for high overhead with the initial backup and transfer of the data to another server/instance. Additionally, log shipping implies a delay between live data and analysis data. After that, you're only moving transaction log backups, which are theoretically smaller.

In our environment, we log ship nightly, because management runs reports when the come in to the office in the morning, and those reports typically require a significant amount of pivots.

0

We had a 4GB database, where we infrequently needed a backup for analyzing issues. The database was too big to take a backup during the day while production was running. So our solution was to run a backup at night when our production system was down and keep the most recent backups (I think we kept one week).

One of our requirements was that the database needed to be shipped to different departments depending on the issue. Another reason was, that we could make the backup files available to Project Managers, who did not have direct access to any SQL server and they were in charge of sending the backups to the appropriate recipients. They didn't needed to involve Admins to get the backups.

The daily backups were scheduled using the standard SQL server tools.

2
  • How did you actually go about 'sending' the DBs to the respective departments? It sounds like you used log shipping too?
    – Nai
    Feb 12, 2010 at 11:17
  • No we just took a full offline backup. Than we send the backup to the person that needed it (which is sometimes tricky, since the zipped backup file was around 80 MB). The person receiving the Backup had his/her own SQL server and imported (or whatever that function was called) the database. You need to change the paths of the database files when importing. Feb 12, 2010 at 14:56
-1

Assuming your target analytic database is the same platform as your production database, the easiest solution is to do a weekly incremental backup and then apply it to your analytic database. If your database isn't growing very quickly, an incremental file should be transmittable in a reasonable period of time.

If you are transmitting to some other database type than what you are currently running in production, an ETL tool like Pentaho Data Integration might be a better fit. An ETL tool is an even better solution if you only care about some of the tables and not all of them.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .