x

Copy Production DB to another DB for OLAP processing

Hello

We have a mighty 70GB production database but we now need a copy of this database for Analytical Processing. We do have replication setup for some tables to feed in web applications but wanted to know best way to copy entire data from one DB to another.

We would like to refresh data every 24hours. I can manually backup Production database and restore a copy but is not feasible for an everyday task.

Please advise.

Many thanks Dhar

more ▼

asked May 10 '10 at 04:23 PM in Default

Dhar gravatar image

Dhar
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

The best approach would be to build an SSIS package that only moves the data that has changed for any given day. It might take a while to build this, but it will work the best for you over the long term.

Other options include running a backup & restore, which, as you note, isn't probably sustainable. You can go with snapshot replication, but that would probably work out to be as problematic, if not more so, than moving the databases.

I'd suggest the SSIS solution.

more ▼

answered May 10 '10 at 04:26 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

+1 I like the idea of using SSIS for doing this.
May 10 '10 at 04:47 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

I agree with Grant, an SSIS package that only moves essential data is probably the way to go. But in SQL there are often many options with their own trade offs here are a couple of others:

Use Red Gate SQL Backup to do your nightly backup and restore. I did this for a while from production to a test database the developers where using on database over 100GB with great success. Its compression abilities make it very good at minimizing the network traffic involved, and it is quite easy to script a job that will do it automatically so you do not need to do it manually.

Also, you may want to look at transactional replication. I have used transactional replication to keep reporting servers in synch with production with great success. Remember that you do not need to set transactional replication to run constantly, and you can schedule it to run once a day, or once an hour tends to be a good compromise for some organizations.

I should point out it is possible in some cases with lots of updates that transactional replication is less bandwidth effecient then backing up and restoring, but that should only happen on systems where the number of changes is large in comparison with the total size of the data involved, which is relatively rare.

more ▼

answered May 10 '10 at 05:04 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

I did think about transactional replication but I just wasn't sure if they were open to the possibility of updating all day long. it's actually a great way to go. I've used it in the past on systems that we wanted to replicate for reporting. Good suggestion.
May 10 '10 at 09:39 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

If it's that big a DB then, chances are, you have a decent SAN to go with it. Many SAN manufacturers have a utility to make "SAN SNAPSHOTS" which are more like "clones". They take almost nothing to setup, have very little effect on performance of the server, don't require any interuption on the prod server, and require very little "time out" on the target server.

We used such a thing at one of the companies I worked for with great success.... almost a tera-byte of data in about 2 minutes. It beats the hell out of replication and SSIS and restores.

more ▼

answered May 11 '10 at 12:35 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 8

(comments are locked)
10|1200 characters needed characters left

I would say you have a few viable options - Log Shipping, Backup/Restore, Replication, SSIS - and each has its good and bad points.

Firstly the ones I have used most:
Replication, we have merge and transactional in place here and its pretty reliable, when it goes wrong it is a pain to sort though. Thankfully its not often. The benefit here is that the OLAP database is available 24/7 - no down time while updates are applied.

Backup/Restore, We use SQL Backup but have nothing that is the same size as you are suggesting so I cannot comment on performance/time required that would compare to your situation. SQL Backup is rock solid though. I have never seen a backup or restore fail due to software issues - sometimes the network has failed, sometimes I have made a mess of settings but other than that 100% success rate.

Now ones I havent tried:
Log Shipping, this can be managed through SQL Backup or via SSMS and TSQL scripts. I have not used it in production as our needs for up-to-date data and 'always on' reports means we cant cope with the down time while the log files are applied. It is more flexible than replication and may take system updates more easily. Replication gets broken down and rebuilt approx twice a year here as there are updates brought online.

SSIS, never used it to move data like this so that would be a real voyage of discovery for me. If you are confident with SSIS then I am sure there are people that have gone that way before that will be able to advise on what is required.

Good luck

more ▼

answered May 11 '10 at 05:48 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 77 107

(comments are locked)
10|1200 characters needed characters left

Hi Dhar,

If I would, I will use Logshipping. It is a best way so far in our environment (telecom). I do every 2 hours logshipping. But you can set the time interval, in your case.. you can set @ 10PM every day so that that can avoid the data traffic. You need to make sure to set your database to FULL recovery.

Thanks. Leo

more ▼

answered May 11 '10 at 07:21 AM

Leo gravatar image

Leo
1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x83
x6

asked: May 10 '10 at 04:23 PM

Seen: 1919 times

Last Updated: May 10 '10 at 04:26 PM