DB Warehousing

[awaywifye]

I have approx 15 databases that I run though and collect certain tables/colums into one central database. I was told that I should use master as the warehouse as it already touches all the databases. Currently the setup looks something like:

Views in each of the DB
Tables in Master
Stored procedures in Master

Batch file calls osql. Calls a stored procedure which pulls data from view and "Insert Into" master.

1) Should I be using master? I'm noticing that performance on the server is dropping at a quick rate the larger the new tables get. Would it be better to create a warehouse table?
2) Using the views/stored procedures works, but it is time consuming and eats up memory. After doing some reading it looks like DTS would be a better approach?

Any suggestions are welcome!

 

[Corran007]

The Master database?

you should not have non system information in master. The thing about master is that its useful for putting procedures that you want to access from each database, but these should only be maintenance things, certenally not user data or procedures.

What happens when you need to restore your database? you will have to restore master meaning the server will not be available for a certain amoutn of time. You have to put the system in single user mode to restore master.

I would just create a Central database. I would also look into DTS as it can be a very useful thing, especially for the type of task you are describing.

You could just use dts intead of the osql. The rest of it would proably not to be modified. DTS can call stored procedures views, just about anything you want.