August 31, 2009 at 6:05 am
I have two databases Frontend and backend. All data of years since the db started working is kept in Backend (you can say it as historical data or achieving and for reporting purpose data.)
The frontend database is used for users live insertions/transactions in this database. We only have to keep last 7 days data into this (frontend) database and rest of the data will be moved to Backend database on daily bases. This way we will have only last 7 days data into Frontend db.
There are master and detail tables in Frontend and its structure would be same as in Backend database. We want data should be consistent. I would like to know how do I do this job?
Either DTS or Logshipping or write a custom stored procedure to take a copy of whole live data of last 7 days and update in backend.
Is there any logic we can build to solve this problem or does anyone has done such kid of job before? Remember that the last 7 days data could be updated because of the business logic that if a session remains open for few days the session end time would be going to update in last few days. That’s why we are keeping atleast last 7 days data in frontend db.
August 31, 2009 at 10:23 am
Some of that depends on how you will be using your backend database. Is the application using it, is it kept around for compliance, is it used for reporting, trending, analytics? How much data are you talking about? Do you move data from front end to back end daily, weekly, monthly?
In general, an SSIS solution would work well for moving the data and purging the front end database. This will allow you to, at some point if it is not already, move the backend database to a different DB server and not have to worry about linked servers and such.
Also, if you are using the backend DB for reporting, trending, or analytics, I would suggest transforming the data into a star-schema design to optimize your reporting. I'm sure that your front end database is optimized for OLTP whereas your back end database should be set up for OLAP. That is, of course, if you are going to be using the back end database for anything.
August 31, 2009 at 10:54 am
First of all thanks for your reply and help. I am giving you reply to your questions in steps below:
[Some of that depends on how you will be using your backend database. Is the application using it, is it kept around for compliance, is it used for reporting, trending, analytics? How much data are you talking about? Do you move data from front end to back end daily, weekly, monthly?]
Yes, application will use it only for reporting purpose. Currently data is arround 10 GB. Yes we will be moving data on daily bases but last 7th day's only.
[In general, an SSIS solution would work well for moving the data and purging the front end database. This will allow you to, at some point if it is not already, move the backend database to a different DB server and not have to worry about linked servers and such.]
Please provide a sample so that i can check if it solves my problem and cover/fit with my existing situation/scenario/database requirements.
[Also, if you are using the backend DB for reporting, trending, or analytics, I would suggest transforming the data into a star-schema design to optimize your reporting. I'm sure that your front end database is optimized for OLTP whereas your back end database should be set up for OLAP. That is, of course, if you are going to be using the back end database for anything.]
Yes ofcourse as I mentioned that the backend db would be exactly copy frontend database. How does start-schema help in this regards. Please write in details and have links, references so that i can start working on it.
Thanks.
Shamshad Ali.
August 31, 2009 at 3:59 pm
Shamshad Ali (8/31/2009)
First of all thanks for your reply and help. I am giving you reply to your questions in steps below:[Some of that depends on how you will be using your backend database. Is the application using it, is it kept around for compliance, is it used for reporting, trending, analytics? How much data are you talking about? Do you move data from front end to back end daily, weekly, monthly?]
Yes, application will use it only for reporting purpose. Currently data is arround 10 GB. Yes we will be moving data on daily bases but last 7th day's only.
[In general, an SSIS solution would work well for moving the data and purging the front end database. This will allow you to, at some point if it is not already, move the backend database to a different DB server and not have to worry about linked servers and such.]
Please provide a sample so that i can check if it solves my problem and cover/fit with my existing situation/scenario/database requirements.
[Also, if you are using the backend DB for reporting, trending, or analytics, I would suggest transforming the data into a star-schema design to optimize your reporting. I'm sure that your front end database is optimized for OLTP whereas your back end database should be set up for OLAP. That is, of course, if you are going to be using the back end database for anything.]
Yes ofcourse as I mentioned that the backend db would be exactly copy frontend database. How does start-schema help in this regards. Please write in details and have links, references so that i can start working on it.
Thanks.
Shamshad Ali.
Please provide a sample so that i can check if it solves my problem and cover/fit with my existing situation/scenario/database requirements.
An SSIS solution is something that you'd need to build out so there really is not a 'sample' that I could provide. At a high level, you'd use SSIS to move the rows from your front end DB into the back end DB and then purge the data from the front end database. The SSIS package would be scheduled daily, weekly, monthly or at whatever interval you desired.
Yes ofcourse as I mentioned that the backend db would be exactly copy frontend database. How does start-schema help in this regards. Please write in details and have links, references so that i can start working on it.
If you plan to use the back end database for reporting, trending, analytics, it would be best to transform your data into a schema that more easily supports that kind of demand. Your front end database is an OLTP database. Depending on how heavily used the back end database may be, you may consider redesigning the schema to be more optimized towards an OLAP type environment.
Much of this depends on how you want to use the database and how much resources you have available for making changes. If all you really want to do is archive off old data, you could use backup and restore along with a purge SP to remove the old data from your front end. If you want to create an environment for reporitng and anlaytics, use SSIS to transform the data into your backend DB so that it is optimized for reporting.
September 1, 2009 at 1:16 am
Thanks again for your support.
[Much of this depends on how you want to use the database and how much resources you have available for making changes. If all you really want to do is archive off old data, you could use backup and restore along with a purge SP to remove the old data from your front end. If you want to create an environment for reporitng and anlaytics, use SSIS to transform the data into your backend DB so that it is optimized for reporting.]
I need Backend db to be used for reporting purpose for both (end-users and regional Admins.). There would be small load on backend db by admin users for looking montly, yearly statistics for their individual regional/location wise reports. The frontend db would be highly used for transactions and weekly report by end users. As it keep only 7 days of data, if a client/end-user want a montly report then we will be using UNION on Frontend DB and Backend db for their date ranges. but the default option to end-users reporting is for a range of one week only, which we will be able to run from Frontend DB directly, or we can compromise on it to run all reports from Backend. but recent changes will not be looked in reports if a user want to see This Week's report.
The basic purpose to do this is to have good user experience and site don't get slow down. so we are designing this for good/better performance at database level. I have following 5 options for moving/synchronizing frontend data to backend db.
1- SSIS Package
2- Mirrorning - one friend suggested this as an option.
3- Snapshot replication.
4- Backup frontend db, move on Backend Server location and after restore, synchronise it manually, Insert if not exists else update.
5- Manual process i.e. BCP each Transaction table move files on frontend server and then read data and update Backend transaction tables one by one.
I would like to know which option would be fastest and with low maintenance cost, with minimum load on Frontend database so that the Frontend server would be mostly free for end-users activities.
Each user hit on web page is a part of our reporting. We have a target of 100,000 users hits at a time.
Shamshad Ali.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply