Best solution to provide read copy for MIS environment in SQL Server 2008R2
Hi, We require to provide read copy for MIS purpose ( reporting). The domain is Telecom BSS and our DB size is around 250-500 GB. The DB has around 800+ tables. Out of this, only 50+ tables required for reporting purpose. Here Out of 50+ tables, there are 10+ tables, which have more than 20+ million records. The version is SQL Server 2008R2 standard edition. Approach we are looking: 1. Log shipping 2. Mirroring 3. Replication 4. Incremental Load (By LastModifiedDate) For, 4th approach requires us to modify the schema and procedures. This approach makes huge impact on development. For 4th Apporach: The plan is to export all the column of these tables on every 15 minutes and provide to other team based on Last modified date. For this process, every operation on this table should update the Last modified date. For 3rd approach: Is Transactional replication incur on any IO Issue? We would like to know which is best solution to provide read copy for other teams. And which is the global practice on this process. Note: Dev team is suggesting to implement Transactional Replication. DBA team suggests to go with 4th approach. Thanks!
OK, not all the comments are showing up so posting this way now instead. >From OP >Yes, we are going to start the process with all initial required data. So, replication won't fail. And its going to update all the changes in predefined interval. (Using transaction replication which going to use Log data in back ground)..Mostly pull subscription we are preferring. >Change Tracking and CDC used mainly for audit purpose. OK, so you start the table with 20+ million rows, let replication update 1 row in the 20+ million, how are you going to find that one row that has been updated? Change Tracking, yes primarily an auditing purpose, but what is it your doing here? Your auditing what has been changed as you want delta. Again to only get delta records, you need to implement a delta routine. Change Tracking or option 4 are your only options with Standard edition