SQL Server Performance

Which to choose

Discussion in 'Performance Tuning for SQL Server Replication' started by jbsog, Oct 23, 2007.

  1. jbsog New Member

    I am looking for some additional expert opinions on a replication environment that needs some issues resolved.
    Current Setup - 1 prod server is being replicated to a secondary server using Log Shipping, every 30 minutes. The secondary server is both for reporting and as a warm standby.
    Issue: the log shipping is starting to take up too much time to load the secondary server for any reports to be ran (sometimes up to 15 minutes). The reports are sometimes down to only running within a 15 minute timeframe. Also, if a report is incorrect and data needs to be fixed, the user may have to wait 30 minutes for the data to be replicated back to the reporting server to see the change (Primarily for Monthly and Quarterly reports).
    Requirements: - Need a warm Standby Server for Disaster Recovery, Users need a larger window in order to run reports, most reports don't need to be up to the minute, a few do. AND Users need to be able to fix data for the Monthly & Quarterly reports without waiting so long before they can verify if the change in the production data has fixed the report.
    Open to ANY recommendation for the resolution, new server, change replication to something other than Log Shipping....
    There is a 1gb fiber connection between the boxes, box 1 has Raid 10 for log files & Raid 5 for data file, Reporting server is all Raid 5. Production has 12 gb RAM, Report has 6
    My thought is to potentially mix some of the replication methods, but unsure of if that is a good idea. Also thought about adding an additional box, the updating of data leads me towards the merge replication, but need to be able to mitigate any risk involved with that.
    Any direction would be helpful.
  2. ndinakar Member

    Have you tried/looked into Transactional Replication?
  3. satya Moderator

    What is the problem with Log shipping in this case?
    Reporting server - do you mean SQL Server reporting services or your own data server that is used for reporting purpose?
    I don't recommend replication for pure warm standby in this case, they are other technologies available in this case.
  4. jbsog New Member

    It's a server for data used in reporting. There isn't a problem with Log Shipping it works, however the available time that the data is available for reporting on the secondary server is too small of a window at times, only 15 minutes for the whole company to do reports. The other reason is when quarterly reports have to be reviewed and corrected within a few weeks for several external clients, this doesn't give the report analysts much working time.
    What would be a better way to have a warm standby?
    We are also considering Transactional Replication, but are researching if all the tables have a unique key, I believe that's a requirement in order to use Transactional Replication?
    Thanks for the help.
  5. jbsog New Member

    So I guess my question is what is the best solution to provide a warm standby server AND real-time to near real-time replication for reporting data.
  6. ndinakar Member

    [quote user="jbsog"] What would be a better way to have a warm standby? [/quote]
    When you say warm standby do you want your application to automatically connect to your stand by or you expect to take some downtime and re-point your application to the stand by? Replication is not "warm stand by" as in its not automatic failover. You have to manually repoint your application. Database Mirroring can provide the "automatic failover" solution but there are few limitations with the technology too.
    [quote user="jbsog"] We are also considering Transactional Replication, but are researching if all the tables have a unique key, I believe that's a requirement in order to use Transactional Replication? [/quote]
  7. jbsog New Member

    We could go either way with the "automatic" failover, I have also been looking at clustering, and found a software from XLink that clusters just the data for pretty inexpensive. What are some of the limitations to Database Mirroring?
  8. SQL2000DBA New Member

    In my opinion better approach to resolve this issue is to create snapshot database (interval of 5min during peak time ) for reporting requirement and keep existing standby server setup as it.
  9. satya Moderator

    Database snapshots and Database Mirroring are features in SQL 2005, so the originator has to confirm whether he needs this for SQL 2005 or not.
    For SQL2000 there is a limited option when used log shipping, only replication can help to some extent. But in any case you shoudl control the reporting queries by optimizing them.
  10. jbsog New Member

    If upgrading to 2005 to use Data Mirroring would resolve the issue, I am good with that. SQL 2000 does also have snapshot as a replication option.
    Reporting queries should be optimized, I agree, however, that's not part of the original issue, when running the reports, the users are basically having to try to run reports, if the DB is down due to log shipping, they have to wait and keep trying until they can, until the next log shipping runs. That is extremely frustrating to the reporting users, they shouldn't have to worry about when they can and can't run reports during business hours.
    What are some of the catches or restrictions with Data Mirroring in 2005, if any?
  11. jbsog New Member

    If snapshot is done every 5 minutes, what is the latency, would it be able to keep up before the next one kicked off, the main time I am worried about is, there are external loads into the DB's with a week or a months worth of data that takes a few days to run, The log shipping files during this timeframe are around 300 to 400 MB, I did see an unusual one spike to 1 gb, so that would be about 50 to 150 MB every 5 minutes? Would Snapshot keep up?
  12. satya Moderator

    Yes, why not and before that I would like to know what is the transactions/sec rate on the database.

Share This Page