Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
you might get a more complete answer in the DBA side of StackExchange just due to experience. – Top__Hat Dec 13 '11 at 15:54
Thanks for the suggestion, Isn't it against the rules if I did that? – Mihalis Bagos Dec 14 '11 at 10:17

Yes, you can have Analysis and Reporting just target one environment because you just point them at that environment. Best Practice is to run the SSRS (Reporting Services) and SSAS (Analysis Services) on boxes other than the production database for your application. This is because the core database engine, SSRS, and SSAS all use I/O and memory differently. They also cluster differently if you plan to do that at some point.

CDC, Change Data Capture, may allow you to do the archiving strategy you want, but you need to see if you can effectively subclass a delete statement. Basically you want two types of deletes from that removes it from production (smaller, faster transaction database) but does not remove it from arching and a second type of delete that removes the data from all environments. In theory I believe this is possible but I have never done it in production.

share|improve this answer
Thanks for the reply! For the SSRS and SSAS you suggest that they are calculated on a different machine, but their input data continue to lay on the production machine? I was thinking data duplication on a separate environment completely different from the production. I am also now looking for more info on CDC – Mihalis Bagos Dec 14 '11 at 10:20
Think of SSRS as an application server. It's a best practice to run an app server seperate from a database server so they don't contend for the same CPU and I/O resources. – Anon246 Dec 19 '11 at 20:25
I think he means the database part of SSRS; if you have the option to separate that from the production databases, by all means do so. – adaptr Dec 22 '11 at 11:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.