CODEWITHSUNDEEP
sql-serverdatabase-designdata-warehousesql-viewtransactional-replication
James Anderson
James Anderson

Reputation: 460

SQL Server Cross Database View

I am attempting to build a reporting platform for our CRM. The design of the system has a separate DB (with the same structure) for each client. I am replicating (transactional) these 50 DBs to the report server but I now want to bring all the data together into one 'reporting' DB. Each table will have a new client id column which will keep the records unique and link to a new client table.

I want the data to be as close to live as possible (hence transactional). I thought of using a big cross database view but something tells me this is not the best way to do this.

I guess I could have triggers on each client DB that updated the reporting DB whenever there is an update, delete or insert. Not sure if SSIS could be used for this. I have only used it for ETL in the past.

So I would like to know what the best practice would be for bringing multiple databases together and keeping the data in synch with the production environment.

Upvotes: 0

Views: 1398

Answers (1)

ryan1234
ryan1234

Reputation: 7275

I definitely wouldn't make a view. The joins will be insane and it won't scale as you add new clients.

I'm not sure I would use triggers either. It could work, but really aren't you just duplicating replication at that point?

I would use replication and just replicate those tables to the reporting server. Then you can mold the replicated tables to be whatever you need them to be. Replication can be a bit of a pain to get working right, but when it works it's really amazing.

Upvotes: 1

Related Questions