March 6, 2008 at 12:55 pm
This may be typical, so I need somewhere to read more in detail.
Currently:
I have a web server which interacts with clients. It has a SQL 2000 database which I can upgrade to 2005 if necessary.
I have an Access database with some archived data that belongs together with the live data.
Target:
I want the web server to continue interacting with the clients, but to keep only the more recent clients' data. Say, the last three months. There are some reference tables that rarely change as well.
I want the Office server to contain all the data, forever, for research purposes. It needs to receive the live data updates periodically, but it is not necessary to have it done more than say, weekly. For reasons of making this a backup, it might be a good idea to do it often, however.
I know how to move the Access data into SQL Server, and it has to reside together with the other archived data.
What kind of replication setup do I need, or is there a better way?
What strategy? Move all the Archived Access data into the live web server database then publish everything to the Office and set up a subscription in the office to receive the new client data? How do I keep the data in the web server database cleaned up so that older data is only in the archive at the Office and the live database can respond quickly to current clients?
Send me off to do some reading... thanks:)
March 6, 2008 at 1:39 pm
PhilM99 (3/6/2008)
This may be typical, so I need somewhere to read more in detail.Currently:
I have a web server which interacts with clients. It has a SQL 2000 database which I can upgrade to 2005 if necessary.
I have an Access database with some archived data that belongs together with the live data.
Target:
I want the web server to continue interacting with the clients, but to keep only the more recent clients' data. Say, the last three months. There are some reference tables that rarely change as well.
I want the Office server to contain all the data, forever, for research purposes. It needs to receive the live data updates periodically, but it is not necessary to have it done more than say, weekly. For reasons of making this a backup, it might be a good idea to do it often, however.
I know how to move the Access data into SQL Server, and it has to reside together with the other archived data.
What kind of replication setup do I need, or is there a better way?
What strategy? Move all the Archived Access data into the live web server database then publish everything to the Office and set up a subscription in the office to receive the new client data? How do I keep the data in the web server database cleaned up so that older data is only in the archive at the Office and the live database can respond quickly to current clients?
Send me off to do some reading... thanks:)
Considering you're talking about an archive currently stored in Access... I wouldn't go nearly so "fancy" as a replication scenario. Why not just start with a "history table" (same structure as the "current data" table, just holding the older data)?
That way - it's a simple scheduled job to move data from one to the other, and it's a simple matter of using a UNION query in case you need to report on current AND old data.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 6, 2008 at 5:15 pm
That doesn't seem like a bad idea, since I'm not keen on becoming a replication 'guru' for the sake of one project.
I have to force fit the old 'Access' data together with the live data anyway (unfortunately there's some duplication).
So I could put them together in a new SQL Server db in the office (there are actually maybe 10 fairly active tables involved).
I know what union queries are, so I see what you mean as to how to run it manually. How would I schedule it? From the office end, I would pull it in? Is this sort of a DTS-like setup?
Hmmm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply