How to Transfer data, data archieving, good strategy?

Discussion:

(too old to reply)

Shamshad Ali

2009-08-31 14:33:50 UTC

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.

Todd C

2009-09-01 15:16:01 UTC

Permalink

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

Post by Shamshad Ali
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.

kami

2009-09-08 10:03:34 UTC

Permalink

hmmm

Post by Todd C
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
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.
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

- Show quoted text -

Shamshad Ali

2009-09-08 10:18:14 UTC

Permalink

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

Loading Image...

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.

Post by Todd C
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
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.
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

Todd C

2009-09-08 12:53:01 UTC

Permalink

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

Post by Shamshad Ali
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/y1pY8EJTkW33tF8ZkyDcCDTkExTtqrfQF4v_m37c_MVZ_ufVVzkIyAMdCZvJhtF2zCs8-IingrzM9ijDJDG2dJwaCtSp6QYz631/Database%20Architecture.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.

Post by Todd C
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
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.
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

2009-09-09 02:42:56 UTC

Permalink

Hi Todd,

Thank you very much for your suggestions and help. I had already planned
that both Servers would be basically two different clusters. This way I am
sure that we can achieve high performance and high availability. I have not
mentioned earlier in the diagram to make it simple for everyone to
understand the basic idea but we are planned for cluster.

Loading Image...

One more help, do you know someone who can help me on this design further
and reply quickly? As you said that you are not expert in SQL Server
Replication same for me too ;). So if you suggest me some one to contact
personally who can take interest in this design and provide help quickly or
a forum where experts are providing answers in more details because I saw in
SQL Replication news group, the response to questions is just like dead
posts. So your suggestion would be a great help once again.

Shamshad Ali.

Post by Todd C
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
* 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

Post by Todd C
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
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.
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

Post by Shamshad Ali
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.