September 7, 2010 at 12:23 pm
Hello Everyone,
Can anyone please help me with below challenge.
We have a transactional database and planning to layout a reporting database on a different server with the up to minute data from the transaction db, and not to be impacted by any purge processes that may be implemented on the transactional db.(real time)
How can we achieve this?
September 8, 2010 at 6:08 am
You could use database mirroring to basically create a read only copy of your production database.
That mirrored database is what you would report off of, so that your report queries would not slow down production.
http://technet.microsoft.com/en-us/library/ms175511.aspx
Depending on the kinds of reports you may need to look at moving to a data warehouse solution:
http://technet.microsoft.com/en-us/library/ms151784.aspx
Also, and maybe someone here can answer this, I want to say that mirroring doesn't require any additional SQL Server licenses....
Although I think that is only if you are using the mirroring as a short term failover, I would think that if you intend to report off it for production purposes they would want you to license that second server.... but something to look into.
September 8, 2010 at 10:39 am
I guess database mirroring is not possible, my report db is on differrent server. I need my report database to be always in synch with up to time data with my primary db.
I am thinking of implementing transactinal replication so that the both db can be in sync everytime. But if any purge process takes place in primary db i dont want those changes to be affected on destination db or report db. because report db need to have all the data for reporting.
Here I have an idea, before purging the specific time period or year based data on primary
I will copy that data into a different archive table on report db so that when purge process is implemented on primary the changes are affected to report db. but the data purged will be there in different table and we can direct the report to get data from archive table when ever they cant find data in the primary tables.
example: PRIMARY DB AND REPORT DB HAS 100 ROWS IN CUSTOMER TABLE
IF WE PLAN TO PURGE 50 ROWS ON PRIMARY DB. FIRST WE GO TO REPORT DB AND COPY THOSE 50 ROWS TO A DIFF TABLE OR ARCHIVE TABLE AND PERFORM THE PURGE OF 50 ROWS ON PRIMARY, NOW THE CHANGES ARE AFFECTED TO REPORT DB AND 50 ROWS HAVE BEEN PURGED BUT WE DO HAVE THOSE 50 ROWS IN ARCHIVE TABLES.
September 8, 2010 at 1:08 pm
CANCER (9/8/2010)
I guess database mirroring is not possible, my report db is on differrent server. I need my report database to be always in synch with up to time data with my primary db.I am thinking of implementing transactinal replication so that the both db can be in sync everytime. But if any purge process takes place in primary db i dont want those changes to be affected on destination db or report db. because report db need to have all the data for reporting.
Here I have an idea, before purging the specific time period or year based data on primary
I will copy that data into a different archive table on report db so that when purge process is implemented on primary the changes are affected to report db. but the data purged will be there in different table and we can direct the report to get data from archive table when ever they cant find data in the primary tables.
example: PRIMARY DB AND REPORT DB HAS 100 ROWS IN CUSTOMER TABLE
IF WE PLAN TO PURGE 50 ROWS ON PRIMARY DB. FIRST WE GO TO REPORT DB AND COPY THOSE 50 ROWS TO A DIFF TABLE OR ARCHIVE TABLE AND PERFORM THE PURGE OF 50 ROWS ON PRIMARY, NOW THE CHANGES ARE AFFECTED TO REPORT DB AND 50 ROWS HAVE BEEN PURGED BUT WE DO HAVE THOSE 50 ROWS IN ARCHIVE TABLES.
Couple of thoughts in reverse order:
If you are purging records (and I have no idea the setup of your tables of course) you might want to look at a trigger of somekind???
My concern would be that someone will go to DELETE FROM someTable WHERE blah=SomeCondition and then all your data is gone unless you do a restore because they forgot to copy it over to your (basically) data warehouse server.
So perhaps a trigger that when rows are deleted they are first moved/copied/whatever to some staging area. Again, no idea who would be doing the purging, if you are doing it yourself then one hopes you can trust yourself and you would just built a "sproc_MoveAndPurge" that you execute and pass it whatever selection criteria it needs. Then the sproc handles the move and delete.
Another thought: If you are looking at archiving off data and then purging it, you really are talking about a datawarehouse. It might be good to read up on that a little bit, setup an SSIS package to do your ETL. It can extract the data it wants from production, transform it into some aggregate values (if needed or desired) and finally just load it into your separate server so it will be there for your reports. It could run every hour, day, year, whatever... and just pull the most recent records since the last time it ran.
It could also have a package in there / step that says "Is it the first of the month? If yes, then after I complete this ETL and verify that the records go where they are going... purge the last month of data from production" (or whatever makes sense).
Lastly:
You can do database mirroring to another server.
So you can setup your production server, (which you already have) and it has all your databases on it. Then you setup your "read only" server. This server exists to act as the mirror. You don't have a 3rd witness server because you do NOT want automatic failover, that isn't your goal (though perhaps it should be, would be a cool bit of redundancy to let queries hit the report server if the primary fails... but anyway...)
The databases on the production server just sit there and do their thing, sending "mirroring" data to the reporting server.
and http://technet.microsoft.com/en-us/library/ms188712.aspx
Though I may have mis-understood your post, so if I am missing something please let me know.
September 9, 2010 at 12:46 pm
THANKS FOR THE POST..YA IT LOOKS FINE AND NEED TO LOOK AT YOUR IDEAS TOO
ABOUT THE TRIGGER AND DB MIRR.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply