database export

Question

database export

sqlfriend

SSC Guru

Points: 52588
More actions
March 6, 2015 at 9:57 am

#316057

We have a in house developed application and database. It is online transaction database for student school assignment.
Size is about 6 GB.
We have another department needs our data for planning of their work.
Currently we have an SSIS package to export data to another database on their server nightly. Most of tables they need are exported.
The SSIS basically is truncate their existing tables are then import all the data from our database. It takes over one hour.
But since there are millions of records in a couple of tables, the SSIS nightly use a lot resources. And I start to doubt if this is the best way to do it.
One option I think maybe I can restore a copy of our database to their server nightly and make read only, restore process may save some time and resources than the long run SSIS.
Any suggestions or any other way for better meet their need for replicating the data?
They don't need necessarily real time, overnight data is fine.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

EMarkM Ten Centuries Points: 1114 More actions · Answer 1

I really like Backup > Copy > Restore.

It's probably my most used method as it is simple, reliable, easy to set up, fully controllable, and has the added advantage of testing your backups - always a good thing!

Alternatives might be log-shipping or replication, but these are a little more complex to set up, particularly if there is not a huge amount of DBA skill at the organisation. They offer some more flexibility, but I'm not convinced that you require that in your case.

To conclude - your original proposal is a good one.

seshurtz SSC Veteran Points: 281 More actions · Answer 2

Hi sqlfriends!

I have used backup/restore operations to transfer data before and found it to work well in certain circumstances. A 6GB backup/restore should be relatively quick however, I think it's important to get a little more information as there are a lot of different options out there. You said that the SSIS operation is using a lot of resources? Could you provide more detail on that? Have you identified a problem associated with it or just looking to improve the performance overall?

Thanks! 🙂

udaynov17 SSC Eights! Points: 832 More actions · Answer 3

Since there is no much high availability needed in the environment I suggest set a backup and restore job every night specifying the paths of the database. That would be easy and certain.:-)

sqlfriend SSC Guru Points: 52588 More actions · Answer 4

Thanks it looks like backup and restore is the better option for us now.

Since the database size is not big and it is a simpler process.

It should only run less than 5 minutes.

For seshurtz's question, the SSIS package export and import 60 tables, some of the table has millions of records.

I do find some performance issue using our database performance analyzer tool, the most expensive query on our server is one data flow task in the SSIS to import and export a big table, the monitoring shows

the query execute 4 times, not sure why it is 4 times, it is a single data flow task from one table to another table, and

Query accounted for 16% of instance execution time, 97% in PREEMPTIVE_OS_WAITFORSINGLEOBJEC, Spent a significant amount of time on these wait activities

•PREEMPTIVE_OS_WAITFORSINGLEOBJEC (8.52 minutes)

Thanks,

CooLDBA Hall of Fame Points: 3301 More actions · Answer 5

sqlfriends (3/6/2015)
We have a in house developed application and database. It is online transaction database for student school assignment.
Size is about 6 GB.
We have another department needs our data for planning of their work.
Currently we have an SSIS package to export data to another database on their server nightly. Most of tables they need are exported.
The SSIS basically is truncate their existing tables are then import all the data from our database. It takes over one hour.
But since there are millions of records in a couple of tables, the SSIS nightly use a lot resources. And I start to doubt if this is the best way to do it.
One option I think maybe I can restore a copy of our database to their server nightly and make read only, restore process may save some time and resources than the long run SSIS.
Any suggestions or any other way for better meet their need for replicating the data?
They don't need necessarily real time, overnight data is fine.
Thanks

Use Log shipping

sqlfriend SSC Guru Points: 52588 More actions · Answer 6

Thanks, I know log shipping is another way to replicate database, but not sure if it is the best for our situation.

We don't need high availability, overnight is fine.

By using log shipping, I think it will use my production resource at day time.

seshurtz SSC Veteran Points: 281 More actions · Answer 7

Hey sqlfriends,

yeah, a backup and restore process may prove to be significantly faster than the SSIS package. I find myself itching to delve into that process and figure out why it might be so slow. :pinch:

Anyways, I have always found a backup/restore operations to offer even more use. Once I restore the database to the target location, I will execute a DBCC CheckDB command against it to ensure nothing was corrupt and I know that my backup has been tested and verified!

Good luck and let us know how it turns out. 🙂

~Steve

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

sqlfriends (3/6/2015)
We have a in house developed application and database. It is online transaction database for student school assignment.
Size is about 6 GB.
We have another department needs our data for planning of their work.
Currently we have an SSIS package to export data to another database on their server nightly. Most of tables they need are exported.
The SSIS basically is truncate their existing tables are then import all the data from our database. It takes over one hour.
But since there are millions of records in a couple of tables, the SSIS nightly use a lot resources. And I start to doubt if this is the best way to do it.
One option I think maybe I can restore a copy of our database to their server nightly and make read only, restore process may save some time and resources than the long run SSIS.
Any suggestions or any other way for better meet their need for replicating the data?
They don't need necessarily real time, overnight data is fine.
Thanks

Why not just give that other department read-only privs to the original database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sqlfriend SSC Guru Points: 52588 More actions · Answer 9

Thanks, that is one option I thought too.

But Since our database has online transactions daily, I prefer not giving additional people to query our database directly.

They may have complicated queries and reports that use the database for their purpose of their department work.