none
SQL 2012 STD: Database Mirroring or Replication?

    Question

  • I'm looking for a way to offload our reporting activity from our main SQL 2012 database.

    We have two licenses of SQL 2012 STD. I'm looking for a near real time copy of an entire database that

    can be hit with resource intensive reporting queries without affecting our main production database.

    I was thinking log shipping but that requires the copy to be offline.

    The servers will be in the same site with 1Gbps links and a database about 150GB in size and about 500MB change daily.

    Any help would be appreciated!


    Network Administrator

    Wednesday, July 24, 2013 7:09 PM

Answers

  • After doing research it looks like transactional replication would be the best fit.


    Network Administrator

    Wednesday, July 24, 2013 9:05 PM
  • Hi David,

    Transaction replication is used in server-to-server environment. When the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber. This change happens almost in real time. 
     
    Usually, database mirroring provides a single copy of the mirrored database to reside on a different server instance. Under asynchronous operation, transactions commit without waiting for the mirror server to write the log to disk, it is possible to occur data loss. Under synchronous operation, it will increase the cost of transaction latency.

    There is a similar issue about creating a real time copy of database, you can review this link.
    http://stackoverflow.com/questions/4208029/how-do-i-create-a-realtime-copy-of-my-sql-server-2005-database

    Thanks,
    Sofiya Li

    Sofiya Li
    TechNet Community Support

    Thursday, July 25, 2013 6:46 AM

All replies

  • After doing research it looks like transactional replication would be the best fit.


    Network Administrator

    Wednesday, July 24, 2013 9:05 PM
  • Hi David,

    Transaction replication is used in server-to-server environment. When the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber. This change happens almost in real time. 
     
    Usually, database mirroring provides a single copy of the mirrored database to reside on a different server instance. Under asynchronous operation, transactions commit without waiting for the mirror server to write the log to disk, it is possible to occur data loss. Under synchronous operation, it will increase the cost of transaction latency.

    There is a similar issue about creating a real time copy of database, you can review this link.
    http://stackoverflow.com/questions/4208029/how-do-i-create-a-realtime-copy-of-my-sql-server-2005-database

    Thanks,
    Sofiya Li

    Sofiya Li
    TechNet Community Support

    Thursday, July 25, 2013 6:46 AM
  • Hi

    Transactional logshipping can be done to almost near time, but depending on how ofter you Query the secondary database it can tricky to get it to work if you have the transactions set to restore to often. You can have the database online (read-only) and choose not to drop users during restoring phases on the secondary server, yes, but then the restore job will wait until there are no user Connections Before it can do a restore.

    http://msdn.microsoft.com/en-us/library/ms189572(v=sql.105).aspx

    //Alex

    Thursday, July 25, 2013 11:55 AM