0

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.

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

1 Answer 1

1

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 production...one 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.

3
  • 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 Dec 14, 2011 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, 2011 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, 2011 at 11:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .