We are now moving from a MySQL to a MSSQL 2008R2 database. As the project is a web application that we need to be able to scale well, we need to rework the way the DB handles data.
We have 3 separate environments: production, staging, and archive/backup.
First of all, is it possible to have Analytics and Reporting services set up only on the archive environment? Or is it critical that they operate on the environment that the original transaction happens? (Maybe the services need all exact logs, etc?) The point of this is to be able to "lighten" the critical system that is production, and move the burden to the archive where the services can consume more resources freely.
Second, is there any good way to insure that the archive/backup environment will be updated and intact? Any tools to suggest? We are really looking into Redgate but we don't have any experience in this matter either. We need to sync the database with the backup archive with the new stuff
Thirdly, we would like to have some kind of Grandfather-Father-Son grouping. For example we would like to have the following precalculated at all times, and nothing else precalculated:
- Daily totals of last week
- Weekly totals of previous 5-6 weeks
- Monthly totals of all previous months
Will indexed views be good enough for this purpose? We want all the fields to be precalculated and stored.
Lastly, we would like to have some features like StackExchanges (and generally Wikis) versioning. Are there ways to archieve the older versions somehow, but still on the production environment, and keep the newer versions of stuff more readily available? We have been looking into Partitioning but it doesn't seem to handle such intricate scenario (we don't want ALL posts prior to X date to be partitioned, rather than we need all versions that are older than the newest version).
If you don't have a direct answer to all of the sub-questions please still leave an answer, so that we can gather a full reply on archiving best practices/scalability.
If some features/tools you suggest come from the newer 2012 version, it's still ok as we will upgrade once it is released.