SQL Server Performance

Need Advice on methods of data replicating to other database...

Discussion in 'ALL SQL SERVER QUESTIONS' started by meenxip, Oct 18, 2013.

  1. meenxip New Member

    Dear all,
    My client has requirement as below.
    They are having SQL server 2008 database which got too bulky effecting the performance.
    So they decided to copy the same database to other database (which would be used for reporting)
    and the from the original database they deleted all the data prior to 3 yrs. So the original database is trimmed and this becomes principal database and is used for transactions and used by the application to insert new data.

    Now the requirement is, copied (reporting database) database should be replicated from the principal database for newly inserted data. It should be done real time, and its not viable, at least it should be done with 5-10 mins.
    At anytime the reporting database should be having full database of all the years but the transaction database will have only latest three years data only.

    Now I am in question which method I should use to do data replication and what are the challenges. considering their budget is very limited and near-time data reporting and full transaction safety..

    also last note, I heard that replication is good for load balancing, you can distribute your database to multiple servers. can you let me know if this can be used in my case?
    Thanks you all.
  2. AJITH123 Member

    As per your note, the secondary database will be used for only reporting purpose, then you can go for a LogShipping method rather than mirroring. As per my experience mirroring is quite complex than logshipping. DBAs need to focus more on mirrorning compared to logshipping since the transaction is taken care by sql server engine and a lot more... If your database is in full recovery mode then i would say, Logshipping is the best fit for you.
    meenxip likes this.
  3. meenxip New Member

    Thank you Ajith for the advice.
    I do understand mirroring is not an option for me. What I am trying to understand is should I choose log shipping or Replication.
    What are the server/licensing requirement for both? and if I prefer real time data replication to the secondary database then which is the better option? and why?

    Thanks once again..
  4. AJITH123 Member

    My bad...instead of mirroring I should type replication :(

    Since you need to implement it in the production, sqlserver licences needed for both the servers, and the server configuration is depends like, how frequent the database hit, performance, hardware cost etc.

    If you implement Replication, need more focus but log shipping is quite simple and have much control.
  5. Gopi Krishnan New Member

    Replication will work here, but transactional replication has dependencies like the tables should have a primary key. Logshipping can't be used here as a reporting solution , if you configure standby logshipping with 15 min latency the restore process need an exclusive lock to restore database, so the users need to force disconnect. If the report run for 20 min, user will be disconnected. So you check the database design and opt replication / mirroring as appropriate solution.
    meenxip likes this.
  6. meenxip New Member

    Thank you Ajith and Gopi Krishnan,

    Since there are multiple users constantly working on the primary database, the exclusive locking of the database cant be possible In my case.
    If in any case, if we do replication in the same database server, i.e. the publisher and subscriber database resides on the same database server, then what would it be issue? I mean space and performance issue?
    Can you guide me more on replication related issues and requirement, please note this is the first time I am working on this side of database management. so your guidance will be helpful.
  7. Gopi Krishnan New Member

    Yes, eventhough its not recommended (Since replication is a high availability solution, recommended to configure in a different instance.) , you can configure publisher and subscriber on same server , provided server has capacity (CPU / Memory / IO) to handle the application requests. Transactional replication need a primary key on the tables , so you need to ensure the required table have primary key.

    For configuring replication , you can follow this article.
    http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

Share This Page