Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been given a somewhat vague requirement regarding a data extract process which I need some help with. I'm fairly new to database warehousing etc. but I will try to explain everything relevant.

We are beginning a project to set up a shared services domain between our site (A) and another site (B). Currently we both have independent and unrelated systems to store the same thing. Site A has the best system and so the plan is to move our database (SQL Server) to the shared services domain and then both sites will use the single database. We will both have high-speed dedicated network bandwidth to the shared services domain.

The infrastructure is being taken care of by network guys but I am going to have to implement the business reporting side of things. The requirements around this are:

  • Database X has data 'belonging' to both site A and site B (but referring to the same nature of information)
  • Site B's data needs to be extracted and manipulated into some kind of report format (Site B's data can be logically separated from Site A's data based on some values)
  • The data needs to be sent to Site B over the network
  • The process needs to be quick

I have some basic knowledge of SSIS, which just because of what I've read on MSDN and forums seems like the best way to go, but actually I'm not aware of any alternatives to SSIS.

What are the options for this requirement?

Thanks

share|improve this question

1 Answer 1

up vote 1 down vote accepted

If I understand correctly, I wouldn't use SSIS. This is invoked on demand or scheduled to shift data whereas I read this as "automatic shift" of data

So, I'd consider replication or database mirroring and the let the DB engine move data for you:

  • Replication: use this if you need a subset of the datas (not all column, rows or tables)
  • Mirroring: use this to copy the entire database (you'd need a snapshot on the mirror to get data out though)
share|improve this answer
    
Thanks, I will read up and consider these two options some more. My only question is what if the transformation and processing involved is quite substantial? Would built-in database functionality be versatile enough? –  a12jun Dec 5 '11 at 22:28
1  
Thanks, I ended up using Transactional Replication for this with a remote distributor. It made for plenty of reading material through the (shudders) MSDN libraries. –  a12jun Feb 13 '12 at 16:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.