September 13, 2017 at 11:57 pm
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!
�SE��
September 14, 2017 at 7:33 am
muthuveerappan2007 - Wednesday, September 13, 2017 11:57 PMHi,
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!
�SE��
Without knowing the reasoning behind the suggestions you have in hand, it would be impossible to provide much more than some guidance around what the various questions are that you want to be asking. What is the total data size of the tables that need to be copied for reporting? Is the reporting server on the same network as the production server? What's the lowest network speed in the path between the two servers? How often is the data in those large tables updated / inserted ? What volume of growth in the relevant tables exists? If the overall data volume is rather large, but the number of updates is small, it's a very different scenario than having a large volume of data AND a large number of updates. Transaction replication usually works well, but again, without details on just how much updating is taking place, there's no way to know what's best. Of course, the DBA's will probably need to approve of the methodology, so there may be some amount of office politics that goes into the decision as well.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 14, 2017 at 11:52 pm
As mentioned, out of 50 tables, 10+ tables have 20+ millions records or so. So obviously, the data volume is high. Since more indexes exists too. Other tables have around 100k - 500k records. Except few tables (10 or so) , the data growth is in moderate rate. I am not sure on discrete TPS value.
Expecting larger number of update in near future. The network bandwidth is not an issue. If so, there is the possibility to keep the read environment in the same the LAN. The purpose of this read copy is for analyse purpose, what i means that Already MIS is using the production database for the same. So, there is no issue as we create database in our LAN.
Is there any other issue we have to expect in transcriptional replication before implement? Or in other words Would like to know practical discrepancies in replication process? (expect primary key, truncate command)
Thanks for your reply -;)
September 15, 2017 at 12:55 am
moving over here as this is more of the discussion forum rather than ASK SSC.
So if all you want is a readable copy then yes replication will give you what you want, you will also be able to create indexes that support your reports on the subscriber which would potentially cause issues in your OLTP system. Will there be an overhead, yes like anything you adding additional load. You have the distributor database more IO, you also have the reader agents which need to read the TX log and send data to the distributor and subscribers. How much IO, it depends on your configuration, hardware and how frequent tables are updated.
Log Shipping again will give you what you need, but you will need to be aware that you can get really old data or be kicked out the database while logs are restoring depending on the options set. But you cant make any modifications to the destination.
Also saying your running SQL 2008 R2 which went out of mainstream support 3 years ago and only has 2 years of extended support left (that is if you have extended support agreements with Microsoft), I would push for an upgrade to 2014/2016 and use Always On Availability Groups with readable secondary's to offload your reporting to other servers.
Just remember all these OLTP setups, not OLAP so might not be the best solution for you.
If you want OLAP you will need to do ETL processes where you can really fine tune reporting and analytical processing.
September 15, 2017 at 1:53 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply