 | |
How to Transfer data, data archieving, good strategy?
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
How to Transfer data, data archieving, good strategy? -
08-31-2009
, 09:33 AM
#2
| |||
| |||
|
|
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. |
#3
| |||
| |||
|
|
Hello: This sounds like a not too easy task. Replication might work for you, but you would need to investigate and TEST fully. You could set up your front-end machine as the 'master/publisher' and your back-end machine as the subscriber. The trick would be to: 1) set the subscriber to NEVER delete data, if in fact replication options allow that 2) set up routines on the front-end to purge data older than X days. If you can get all the above working, I suggest the following as well: Store the "7" (days) as a record in a 'Control' table that can be queriedor retrieved by the delete routines so that should you later decide to retain 10 or 14 days, it is a simple changing of a value, not re-writing code, possibly missing some routines! Second, do everything through stored procedures, and have another 'control' table that decides the order in which the procedures are run. Based on table structure changing, you may need to add/remove/change the order of the deleting procedures. Best of luck to you. I don't envy the job ahead of you! ===== Todd C "Shamshad Ali" wrote: 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 inthis 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 wouldlike 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.- Hide quoted text - - Show quoted text - |
#4
| |||
| |||
|
|
Hello: This sounds like a not too easy task. Replication might work for you, but you would need to investigate and TEST fully. You could set up your front-end machine as the 'master/publisher' and your back-end machine as the subscriber. The trick would be to: 1) set the subscriber to NEVER delete data, if in fact replication options allow that 2) set up routines on the front-end to purge data older than X days. If you can get all the above working, I suggest the following as well: Store the "7" (days) as a record in a 'Control' table that can be queried or retrieved by the delete routines so that should you later decide to retain 10 or 14 days, it is a simple changing of a value, not re-writing code, possibly missing some routines! Second, do everything through stored procedures, and have another 'control' table that decides the order in which the procedures are run. Based on table structure changing, you may need to add/remove/change the order of the deleting procedures. Best of luck to you. I don't envy the job ahead of you! ===== Todd C "Shamshad Ali" wrote: 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. |
#5
| |||
| |||
|
|
Hi Todd, Thank you very much for your suggestion. Based on your suggestions and our business requirements to meet, I have created a diagram and wanted to share with you. Please feel free to ask any questions to understand it first. Based on our proposed architecture (as depicted in the diagram), Following are my questions for which your answers will be very helpful http://w8qm4a.blu.livefilestore.com/...chitecture.gif 1. Does any aspect of this design lead us to a disaster if we implement it? If yes please point it out and let us know why it could be a disaster and if there is any better solution exist. 2. Do you think if this architecture will involve heavy maintenance cost which could be a nightmare for whole project? Just to let you know our target is to meet high Performance and high availability with minimum maintenances cost as the system we are developing will have 100,000 online users transactions at a time. Shamshad Ali. "Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote in message news:AE77ACB9-286F-425D-949C-B88DDEE85B6B (AT) microsoft (DOT) com... Hello: This sounds like a not too easy task. Replication might work for you, but you would need to investigate and TEST fully. You could set up your front-end machine as the 'master/publisher' and your back-end machine as the subscriber. The trick would be to: 1) set the subscriber to NEVER delete data, if in fact replication options allow that 2) set up routines on the front-end to purge data older than X days. If you can get all the above working, I suggest the following as well: Store the "7" (days) as a record in a 'Control' table that can be queried or retrieved by the delete routines so that should you later decide to retain 10 or 14 days, it is a simple changing of a value, not re-writing code, possibly missing some routines! Second, do everything through stored procedures, and have another 'control' table that decides the order in which the procedures are run. Based on table structure changing, you may need to add/remove/change the order of the deleting procedures. Best of luck to you. I don't envy the job ahead of you! ===== Todd C "Shamshad Ali" wrote: 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. |
#6
| |||
| |||
|
. So if you suggest me some one to contact|
Shamshed: I reviewed your drawing and associated notes. I don't see any major issues with it. I think your planned layout of the Pub/Sub is correct. It has the *system* manage the 7 day rule, not user-developed code. With that single difference from my suggestion I believe you will have a system that will be easier to maintain. I am by no means an expert on SQL Server Replication, but I will offer these suggestions: * Read all you can on replication. BOL, MSDN, Microsoft Press books, on-line video tutorials. * Test EVERYTHING fully: every situation you can think of, such as power failure on your PUB, or a record being edited by both Pub and Sub, etc. * If you are also looking for high performance and high availability, read up on log shipping, database mirrors, and clusters, especially how the interact with replication. Best of luck to you and your project. ===== Todd C "Shamshad Ali" wrote: Hi Todd, Thank you very much for your suggestion. Based on your suggestions and our business requirements to meet, I have created a diagram and wanted to share with you. Please feel free to ask any questions to understand it first. Based on our proposed architecture (as depicted in the diagram), Following are my questions for which your answers will be very helpful http://w8qm4a.blu.livefilestore.com/...chitecture.gif 1. Does any aspect of this design lead us to a disaster if we implement it? If yes please point it out and let us know why it could be a disaster and if there is any better solution exist. 2. Do you think if this architecture will involve heavy maintenance cost which could be a nightmare for whole project? Just to let you know our target is to meet high Performance and high availability with minimum maintenances cost as the system we are developing will have 100,000 online users transactions at a time. Shamshad Ali. "Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote in message news:AE77ACB9-286F-425D-949C-B88DDEE85B6B (AT) microsoft (DOT) com... Hello: This sounds like a not too easy task. Replication might work for you, but you would need to investigate and TEST fully. You could set up your front-end machine as the 'master/publisher' and your back-end machine as the subscriber. The trick would be to: 1) set the subscriber to NEVER delete data, if in fact replication options allow that 2) set up routines on the front-end to purge data older than X days. If you can get all the above working, I suggest the following as well: Store the "7" (days) as a record in a 'Control' table that can be queried or retrieved by the delete routines so that should you later decide to retain 10 or 14 days, it is a simple changing of a value, not re-writing code, possibly missing some routines! Second, do everything through stored procedures, and have another 'control' table that decides the order in which the procedures are run. Based on table structure changing, you may need to add/remove/change the order of the deleting procedures. Best of luck to you. I don't envy the job ahead of you! ===== Todd C "Shamshad Ali" wrote: 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. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.