0

SQL Server 2005

I have two databases (Source1 and Source2) with the same data model and I plan to merge the data into a third database (Target) with an "abstracted" data model. In the future, it's possible to have additional Source databases that will need to be merged into Target.

Originally, I was hoping to build views and stored procedures that would reside in the Target database that I could dynamically run again Source1 to load Target then switch to Source2. That doesn't seem to be possible without dynamic SQL.

So plan B is to build the views and procedures in the Target database then deploy them to Source1 and Source2. What is the best way to replicate code to multiple databases?

I'm open to any new ideas that address this situation.

1 Answer 1

0

SQL Server has replication built in. Have you looked into that?

If it's just a few views or procedures, I'll use a Central Management Server to deploy them to multiple servers. You just define the group of servers and then execute the script. Pretty simple.

For 3rd party options, Idera has Multi Query and Red Gate has Multi Script as well.

2
  • Thanks for the tips! I'd seen MultiScript but not the other two tools. We'll have ~100 views/procedures. Replication seemed a little overkill, but if it's easy to setup, I'm all for it.
    – Jason Baek
    Commented Jul 12, 2013 at 17:20
  • Because I'm in a bit of a hurry, I think I'm going to go with something like this for now: link. Long term I'll probably switch to Central Management Server.
    – Jason Baek
    Commented Jul 18, 2013 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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