SQL Server Performance

ODS - Operation Data Source with small data comapring to Production to gain performance

Discussion in 'ALL SQL SERVER QUESTIONS' started by sachinonnet, Dec 27, 2012.

  1. sachinonnet New Member

    Hi,
    we are in the process of building ODS - Operational Data source, ODS will contain only current 3yrs data, whereas Production server contain 10yrs old data.
    What I need is a smaller sized database which contain data for last 3yrs than the production database which contain data for 10yrs.
    I need a way to have the ODS server storing only the most recent data. Any ideas or recommendations on this?
    is it possible to delete data from a read only standby database that imports shipped log files?
    I know this shouldn't be possible because the deletion would change the LSN and the log shipping should break.
    We try replication but require more maintanance.
    we can create ETLs but they may kill performance of the prod server.
    Regards
    Sachin
  2. sachinonnet New Member

    How about snapshot ?

    we can take DB snapshots on PROD and execute ETL on snapshot. let me know your view / recommendation on this.
  3. Shehap MVP, MCTS, MCITP SQL Server

    For such cases , we usually need to implement a DWH solution for online ETL or Replication Solution …How..?

    For online ETL DWH You have basically 2 ways :

    ·Either to use third party tools such as Microsoft Fast Track ,Sybase Data Warehousing or IBM infosphere warehouse…etc

    ·Either to use Merge Commands to build efficiently an online ETL DWH between the production DB and the other ODS

    You can read more about how to build easily Online ETL DWH using Merge commands at my blog : http://www.sqlserver-performance-tuning.com/apps/blog/show/12927173-data-warehousing-workshop-3-4- ,but keep in your mind that Merge is applied only for SQL Server 2008 versions and upwards

    For Replication , you have also basically 2 ways :

    Either to use third party tools such as Sybase Replication server , IBM infosphere CDC (Change Data Capture) or Oracle GG (Golden gate)..etc

    Or to use Microsoft Replication technique but to build one-way replication (Transactional replication ) but you should filter the data needed 3 million out of 10 million

    Please let me know if any further help is needed

Share This Page