We just went to MS SQL Server 2000 and I need to create a database. I have a program that stores data in MS SQL Server 2000 on the individual computer. This program will run on about 15 computers and store the data locally. I need to take the individual databases and store them on one central database. I can't use replication, because I don't want data deleted from my central database when it's deleted from the individual ones. The central database will be a repository where all of our queries will be done against. We would like the individual databases to send data to the central one as they get data, but might end up doing it twice a day. Any pointers/help would be appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
New User: Need best method to create a 'backup' database.
- Thread starter SQLBill
- Start date
I don't quite understand why you would create this type of architecture as it would seem to be prone to data integrity problems.
I think that the recommended method would be through replication as this would provide the best data integrity.
If this is not an option, you could create a dts package that would export the data on a periodic basis. you would have to build in to this dts, a method to 'know' if data has been moved (like may be a replication column) that would be updated as part of a transaction. you would then schedule this process on either the target or source.
I think that this method would have potential to 'break' however and personally would hesitate to use it.
I would think that you might be better off writing some program to do this that might guarantee data integrity. you could run this in a dts package. Paul
I think that the recommended method would be through replication as this would provide the best data integrity.
If this is not an option, you could create a dts package that would export the data on a periodic basis. you would have to build in to this dts, a method to 'know' if data has been moved (like may be a replication column) that would be updated as part of a transaction. you would then schedule this process on either the target or source.
I think that this method would have potential to 'break' however and personally would hesitate to use it.
I would think that you might be better off writing some program to do this that might guarantee data integrity. you could run this in a dts package. Paul
- Thread starter
- #3
Let me explain a little further. The database I'm trying to create would be an archive of what is on the individual computers. The records are NEVER modified. Once a record is saved in the database on the individual computer, that record is NEVER changed. It can only be deleted and that will be done ONLY to open space in the individual database. I need an 'archive' that will store the records from about 30 individual computers. Since I can't go into the actual details, let me explain it this way. I have 30 salespersons, they each have their own computer. When they complete a sale the information goes into a database on their computer. I want all of those computers to then send the information to a central database. I will use that database to create charts/graphs of performance. The individual computers will hold their information for 30 days, but the central one will hold it for 6 months (then be backed up to tape). I need the individual databases to 'archive' to the central one frequently, since that is the one I will do my research against. So as long as I have the records on the individual computers copied onto the central one exactly as they are, there isn't a data integrity issue.
- Status
Similar threads
- Locked
- Question