Wednesday, September 20, 2017

Recommendation for separate staging database

​Following are the reasons to recommend separate staging database:

1) The database log file does not contain the staging ETL operation resulting in a smaller sized log file

The size of the database & backup files will be smaller

2) If log/data files keep growing, we can see massive performance hits as page splits and non-sequential writes may occur

3) For high availability, we can avoid pushing stage data across the network

4) The recovery model for a separate staging database can be set to SIMPLE to avoid generating a huge log

5) The separate staging database can be easily moved to another instance or server for heavy data processing without affecting the reporting environment