Data Archiving / Warehousing best practices

[guptakaps]

Hi,

We are planning to develop a data archiving / warehousing solution for our product and looking for some white papers / industry standards that we can refer to rather than re-inventing the wheel

To give you some back ground info., we have a Head Office -> Multiple outlets kind of a system where an estate can have hundreds of outlets connected to one Head office. Each outlet PC has MSDE installed and has its own database. Head Office has an Enterprise SQL Server and a database, which has exactly the same schema as outlets. The Head Office polls the data from the outlets every night to consolidate all the information for reporting purposes.

The problem is that after a few years, some of the Head office databases are getting to a size which is very difficult to maintain, and outlet databases are reaching their 2GB limit on MSDE. We would like to implement a solution where data is archived on a periodic basis, both at the Head Office and the outlets, and could be made available for historical reporting.

I have read the other posts in this group, but can not find anyone with a similar problem. May be we are a bit out of date with technology

Any advice that can point us in the right direction to take this project forward will be greatly appreciated

Thanks
Kaps

 

[johnherman]

You could consider using SQL Server replication to get the data from the production server(s) to an arhive server(s) and then delete from the production servers. You would need a rule for replication to "Not Forward Deletes" to the subscriber from the publisher.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[guptakaps]

Thanks for you reply John,

Yes, this is something we can consider at the HO, but on the outlet where only an MSDE is installed, we might have to use the same server, but create a new database.

Also, I am mainly looking for advice on some industry standards / white papers so that we can do a bit of research before finalising the best way forward. Do you know of any?

Thanks
Kaps

 

[johnherman]

I'm also thinking about maybe using a Linked Server from the outlet to the HO with the "archived" data residing at HO, but accessible at the outlet, albeit at reduced speeds due to network issues. Might want to check into Linked Servers with respect to remote databases.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright