Where do you put your staging Tables? DWH DB or as a separate DB

View previous topic View next topic Go down

Where do you put your staging Tables? DWH DB or as a separate DB

Post  meb97me on Mon Apr 08, 2013 7:36 am

I'm interested to hear where people are putting the their staging areas.

The options as I see it are (using SQL Server)
1) In the DWH DB but under a separate schema & file group (to logically & physically separate the data)
2) In a separate database on the same server
3) Separate DB on a separate Server (can't really see any benefit to this)

The benefit of 1) is that MERGE statements for loading can be used without the need for 3 part naming conventions so transporting your
scripts between dev, test and prod don't need changing. (Granted you could do it with parameters in the TFS build process or the like but that adds a bit more complexity to the project)

Benefits of option 2 are that you completely separate the staging area from the warehouse. So your backups don't need to include any staging data which may be present (granted staging should be cleared down after a load but I guess due to timings etc. there could be the possibility that at the time backups are taken some staging data could be present)

Thanks


Last edited by meb97me on Mon Apr 08, 2013 7:37 am; edited 1 time in total (Reason for editing : changed title)

meb97me

Posts : 34
Join date : 2010-07-28

View user profile

Back to top Go down

Re: Where do you put your staging Tables? DWH DB or as a separate DB

Post  Jeff Smith on Mon Apr 08, 2013 3:16 pm

I do option 2.

Different databases makes it a little easier for backups. There are probably some benefits from a temp and logging perspective as well. Also, if you decide to put the Staging on a seperate server at a later time, having them on seperate Databases will simply the change.

Different servers can be useful. It would allow you to do some of the staging work without impacting the performance of the production database.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Where do you put your staging Tables? DWH DB or as a separate DB

Post  ngalemmo on Mon Apr 08, 2013 3:40 pm

It is also typical to have QA and Prod on different servers with the same database arrangement, so you do not need to alter scripts when promoting tested and approved processes to production. Most sites also do the same with DEV.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Where do you put your staging Tables? DWH DB or as a separate DB

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum