1

We are looking at collecting data from partners' Microsoft SQL Servers and importing it into our own SQL Server. Part of what we want to do is to take all of their data separately and then combine it all together so that we can create baselines on how they are performing against one another comparatively. I am curious to learn what best practices or recommendations there might be to achieve this?

The easiest approach that I can think of, is to set them up as linked servers on our SQL Server and then write stored procedures (and automate a schedule using SQL Server Agent) to import the data from each to local tables. I've also started looking at 3rd party systems to do this (e.g. stitchdata) but am not seeing ones that will import data back locally, most of them appear to import data to a cloud DB solution.

Has anyone done something similar before and can help steer us in the right direction?

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MattC
  • 119
  • 2
  • 13
  • Do they have someone who manages the data that you need to obtain? – Ross Bush Sep 20 '17 at 12:56
  • One suggestion from me is that to not use sql for that. I did similar things many times and after some time there was a little logic in this. For example to write selected data (by same condition) in other tables. After that there was more and more logic and it always become hard to maintenance this sql code. Good way which works for me is to write some wndows service in C#. It will be very simple for the beginning. Good abstraction will cause that there will be no problem with future development. – Arkadiusz Sep 20 '17 at 12:56
  • Going over a remotely link server will be very slow compared to restoring the data into a local instance and migrating from there. Would the client be wiling to send you a current .BAK? – Ross Bush Sep 20 '17 at 13:00
  • @Ross, technically they do not. However, all of our partners use the same system, so all of the data is structured the same way. – MattC Sep 20 '17 at 13:13
  • 1
    Yes .BAK is a good start - just restore their BAK's locally into multiple databases on the same server then run your queries across them. THis is technically the most straightforward solution. There's a multitude of more complicated solutions (replication, mirroring etc.) but I don't see any need for those – Nick.Mc Sep 20 '17 at 13:29

1 Answers1

0

To Solve this problem using SQL Tools an approach is you create a staging database to load all external information.

To gather the data you can use SSIS packages to connect directly to the sources. and schedule the packages on SQL ServerAgent

I avoid using the linked server to ETL proposes for many reasons, but the most important to me are:

  • If the remote server is unavailable, all ETL process can be broken.
  • The process would have been strongly linked to the origin and if the source changes you will need to reconstruct many things.

Tou can use or not SP to load and compare the tables between the final database and the stage. It will depend if the database is on the same server, performance, etc.

Maurício Pontalti Neri
  • 1,060
  • 1
  • 10
  • 17