October 10, 2013 at 9:07 am
I am not sure exactly the best way to accomplish this task so am looking for some advice
We use Dynamics GP. At the moment this is in an OLTP environment. It also contains about 10 years of historical data. We also need to run a number of reports but this impacts the OLTP environment.
My intent is to create a duplicate copy of the GP database on a separate SQL server. I can use this db for reporting requirements. This DB can contain all the data and then I can purge everything older than three years from the primarly OLTP db.
Want I then need to do is, after the nightly GP post, take a copy of todays updated information and merge it into the db on the report server. This way the report DB is always, at worst , one day behind the OLTP db which is just fine.
And this (sorry for the long winded explanation) is where my question comes in. I can see that I can use the merge command to do what I want to do but if any of you are familiar with GP , then you will know how many tables will need to be merged. Is Merge still the best way to accomplosh this task , simply iterating through each table, or is there are better method to perform this data merge for the entire DB ?
Thanks
October 10, 2013 at 10:46 am
Can you restore a backup to your reporting server?
October 10, 2013 at 10:57 am
Sorry I responded too quickly and my suggestion won't help since you need to purge the primary.
I have seen commercial applications that archive data into a separate but identically structured database and then purges that data from the primary. Reporting can then be done by unioning the two databases.
So maybe you can use this process as a model. Restore a copy of you primary into an archive database. Purge your primary. Restore a copy of you purged primary on a daily basis. You then could write views to retrieve data from your primary after the purge date and from your archive before the purge date.
October 10, 2013 at 12:25 pm
Thats a really good ideal. Unfortunately in our case it will not work. Users who use GP will be able to log into either DB, one to perform quick simple transactions (order creation etc.) and the other to run lengthy reports. GP itself does not have views that allow searching a across databases. So essentially the OLTP db will be a stripped down version of the report db.
October 10, 2013 at 1:47 pm
The first thing that comes to mind is to create an SSIS ETL package that loads your reporting database. You can then have greater granularity of what gets loaded into the reporting database.
If the ETL puts too much pressure on your OLTP DB then I think you have the right idea, backup/restore the database to a "staging" database on the reporting server, then push the data into the reporting database.
The one thing you didn't point out here is what is the scope & scale of the databases & tables? Are there 100's of tables that are needed? How many transactions are you dealing with on a daily basis.
What you have described is something resembling a data warehouse instead of a OLTP database. But that is another can of worms.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 10, 2013 at 6:04 pm
Last fleeting thought. I might explore replication. My initial and brief research indicates that filtered replication may be possible. If this is correct it may be easier than writing the merge statements yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply