The motivation for this question is with regard to staging tables. Should staging tables be in a separate database for performance reasons? Or can you place staging tables and your destination tables all in one database (using "staging" as schema) without any performance impact on reads of your final tables?
My hunch is I don't think it matters where the staging tables are located, if they are on the same server, but I have nothing to back that up. If there is a performance hit, why?
What matters is where the datafiles are located. If you put them on the same physical storage, it won't matter for performance if you have staging tables in a schema in the main database or if you put it in its own database.
I'm simplifying a bit though. You will share transaction log file between the tables if they are in the same database. You will share allocation bitmap between the tables if you put them in the same db as well. But the main performance impact will be that the datafiles are placed on the same storage device.
There are other considerations however, which you should think about. Do you need to backup the staging database? On the same schedule as the main database? Will you manage to restore the main database in time if the staging schema is also in the same database?
I wouldn't recommend mixing staging tables and production or live DW tables in the same database. When I need to restore a database, I want only the necessary data to be a part of that. Staging database I can think about later, once I get production database or DW database up and running.
Thank you @Magnus Ahlkvist for your response. I have a follow up question.
If you were doing new Data Warehouse (DW) development in Azure SQL Database, would you still separate staging and your dims and facts? It is my understanding that you cannot simply write a stored procedure across two Azure SQL Databases.
What prompted this question is that we are adding a new small data warehouse (Kimball architecture) on-premises which we will need to migrated to the cloud within the next two years. We use SSIS to transform and move data. (Please don't ask why we are not just developing in Azure to begin with, because I couldn't give you an answer, and that is not my call to make.)
We'd like to architect the new DW to make migration to Azure as easy as possible and I thought combining our staging DB with our DW DB would do that. Our DWs are not big enough for Azure Data Warehouse.
Would you still separate stage and the DW if you were developing in Azure SQL Database?
20 People are following this question.
