May 18, 2009 at 9:32 am
Hi,
I am trying to figure a way to improve the promotion of our reporting tables from our staging to our production server. I would like to minimize the time where the tables are not available in the production (reporting) server. As of now, we are using copy objects provided by SSIS. This is OK, but can take a while.
Is there a better way to achieve this?
I am think of moving the tables using temporaries names in the production (reporting) server. After all tables are move from staging to production, I will drop the production (reporting) tables and rename the temporary tables to their good names. If I choose this method, I will need to double the size of my production (reporting) database to accommodate two copies of every tables. Is someone has already done something similar?
Thanks for your help.
May 18, 2009 at 1:26 pm
Switch the server -actually, switch the access path to the server.
Do not call them production and staging, call them Reporting-1 and Reporting-2.
Today Reporting-1 is exposed as production while Reporting-2 is exposed as staging.
Tomorrow -after switching access path - Reporting-1 will be your staging platform while Reporting-2 will be your production one.
Nice and clean, no data movement.
😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 19, 2009 at 4:28 am
Switching Servers like that is fine in theory but I've not come across many companies who have an infrastructure system in place where this is feasible.
Production servers are usually subject to different backup routines, offsite backup storage etc and quite often in a different subnet on the network with a much tighter access policy.
Treating both development and production servers the same to allow switching like you suggest will result in the infrastructure team having some stern words!
If it's taking a long time to move the data due to the volume then schedule out of hours down time and run the updates overnight.
If 24 hours access is required then copying and renaming as you suggest is a possibility at the cost of temporarily using extra disk space.
How big is the database?
May 19, 2009 at 5:18 am
Switching the databases can be a good idea, but unfortunately this might not an option for us. The cost of modifying our connections to the reporting server might be too high,but will investigate.
As of now, our the size of our database is +/- 60GB. It took roughly 30 minutes to move the data using Copy Objects task from SSIS and another 40 minutes to rebuild all the indexes. This not be long for now, but we are expecting a big growth in the next year and we were asked to minimize the time where our reporting database is not available.
I am a bit new in the world of data warehouse. What are the best practice when you have huge databases? Like Walmart for example?
All ideas are welcome.
Thanks much for your help
May 19, 2009 at 8:05 am
Rem (5/19/2009)What are the best practice when you have huge databases? Like Walmart for example?
Real world DWH environments do not include a "staging" database that somehow would be later exposed as "production"; basically you have three sets of tables: FACT and DIMension tables -your actual DWH - plus STGing tables used during ETL processes.
Having said that and taking into consideration we are talking about a smal 60 Gig database let me go back to an alternative "switching" solution... rename the database 😉
You have your DB1 database on the box.
Force restore your staging database into the same box as DB2
Rename DB1 as DBold
Rename DB2 as DB1... you are done
Check sp_renamedb system storedproc.
Cheap, fast and clean 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 19, 2009 at 10:09 am
Thanks all for your answers
Maybe I didn't explained properly, sorry for the confusion.
We actually have the following setup : Facts - Dimension tables, etl processes on a staging env. (database) also use to create aggregated tables and create cubes. After all done, we are moving a set of tables from this database to another one use by end users to get some information that is not included in the cubes.
Renaming database, renaming tables, modifying connections sting are great ideas. I am sure I am not the first one using reporting tables. I am just curious to see how other are managing this process.
Thanks for your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply