Sql-server – Data Extract and submit across the network

etlsql serverssis

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

Best Answer

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)
Related Question