none
Want to create Duplicate database servers which both servers are usable.

    Question

  • Hi,

        I currently have one SQL server 2012 Enterprise R2 running on Windows Server 2012 R2 Enterprise. I would like to create a duplicate database server which is the exact mirror of my current one and also be usable by client, can it be possible? I read about Database Mirroring and AlwaysOn Availability Groups and i found out that the secondary replica of both mirroring and AAG are not be usable as the primary. THank you

    Friday, July 05, 2013 7:28 AM

Answers

  • Hi Tibor,

    Thanks for your advice. I forgot to mention that we are planning to use one as primary Read-write and one as secondary Read-only but both must be readable and sync. Can I use database mirroring? If I use Database mirroring can both of the principal and mirror are readable? Please leave a msg if my question is not too clear, because I'm quite new to MS SQL server. Thanks again....

    Hi ,

    In mirroring you Mirror database is always in restoring state so you cannot run any query nor access it in read only format.How ever using snapshot you can do it( i dont like it that way)

    If you want your other DB to be read only why not use Transaction log shipping ..other databse will be in read only mode and you can run select queries on secondary instance.

    it can work both in full rec and bulk recovery,can tolerate network fluctuation(this might cause issue with mirroring as it requires continuous connectivity)

    Please read this article

    Please revert if u have any other query

    thanks


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 08, 2013 3:25 PM
  • Hi Tibor,

    Thanks for your advice. I forgot to mention that we are planning to use one as primary Read-write and one as secondary Read-only but both must be readable and sync. Can I use database mirroring? If I use Database mirroring can both of the principal and mirror are readable? Please leave a msg if my question is not too clear, because I'm quite new to MS SQL server. Thanks again....

    In Mirroring the mirror is not readable.  Take a look at AlwaysOn Availability Groups which support readable replicas.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, July 08, 2013 3:28 PM
  • I see. So this is for load balancing, not disaster recovery/high availability, right?

    I recon you have 4 options, more or less usable for you:

    Log shipping, secondary can be read if you restore using STANDBY, but you will have old data (since last log restore) and you have to kick out all users for each log restore.

    Db mirroring: You can create a snapshot on the mirror, but it will always be out-of-date (since creation) and you have to devise some scheme on how to update (re-create) the snapshot.

    Availability Groups. You can read a replica. Requires a Win cluster for the setup (not necessarily shared disk, depends on setup etc).

    Transactional Replication. Data on subscriber will be "near real-time". Not really designed for HA/DR but if you really know what you do it can be used for such stuff.

    There's no substitute for understanding the architecture for each HA/DR and scalability technology. Only then can you pick the one(s) that matches your requirements.


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 08, 2013 4:55 PM
  •  In which time gap they sync ?? Or can we define the gap of time to sync two servers in log shipping. Thank  for you all's time and help.

    They dont exactly sync in time gap..they sync when logs from primary are restored on seconday.If you have defined 15 min delay for backup,backup will happen every 15 mins,same for copy every 15 mins and same for restore every 15 mins.Suppose you did update on primary and after update commits immediately backup is happeing ..so total time for restore will be time taken to copy backup from primary to sec and then 15mins for restore to start...so they will sync in 15mins plust some more time.

    You can set time for each operation backup copy and restore and this will define how fast ur primary will be in sync with Sec generally there is some amount of delay.

    For more details please read transaction log shipping


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 11, 2013 10:06 AM

All replies

  • You need to be more precise in your description.  What exactly are you trying to accomplish?  How will the 2 instances be used?  Do you really mean "servers"?  Is it sufficient to have 2 separate databases containing the same information?

    Friday, July 05, 2013 1:23 PM
  • Are you looking for a solution where you have the same database on two SQL Server instances and you want to be able to both read and modify on both SQL Server instances?

    If so, I strongly encourage you to re-think. Just imagine the concurrency issues if two persons modify the same data at the same time. But if you feel up to the task to deal and analyze the potential concurreny issues, then you want to look at Merge replication. It has its own aspect, so you want to spend some time reading up on the subject in SQL Server Documentation. It is, however the only "update anywhere" technology.

    The fist thing you want to do, though, is to take a step back and define what the basic reason for this is? Scalability or availability. Based on the answer to that question you want to investigate what possible technologies are available and decide which one suits you needs the best.


    Tibor Karaszi, SQL Server MVP | web | blog

    Saturday, July 06, 2013 3:13 PM
  • Hi Tibor,

    Thanks for your advice. I forgot to mention that we are planning to use one as primary Read-write and one as secondary Read-only but both must be readable and sync. Can I use database mirroring? If I use Database mirroring can both of the principal and mirror are readable? Please leave a msg if my question is not too clear, because I'm quite new to MS SQL server. Thanks again....

    Monday, July 08, 2013 4:10 AM
  • Hi Tibor,

    Thanks for your advice. I forgot to mention that we are planning to use one as primary Read-write and one as secondary Read-only but both must be readable and sync. Can I use database mirroring? If I use Database mirroring can both of the principal and mirror are readable? Please leave a msg if my question is not too clear, because I'm quite new to MS SQL server. Thanks again....

    Hi ,

    In mirroring you Mirror database is always in restoring state so you cannot run any query nor access it in read only format.How ever using snapshot you can do it( i dont like it that way)

    If you want your other DB to be read only why not use Transaction log shipping ..other databse will be in read only mode and you can run select queries on secondary instance.

    it can work both in full rec and bulk recovery,can tolerate network fluctuation(this might cause issue with mirroring as it requires continuous connectivity)

    Please read this article

    Please revert if u have any other query

    thanks


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 08, 2013 3:25 PM
  • Hi Tibor,

    Thanks for your advice. I forgot to mention that we are planning to use one as primary Read-write and one as secondary Read-only but both must be readable and sync. Can I use database mirroring? If I use Database mirroring can both of the principal and mirror are readable? Please leave a msg if my question is not too clear, because I'm quite new to MS SQL server. Thanks again....

    In Mirroring the mirror is not readable.  Take a look at AlwaysOn Availability Groups which support readable replicas.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, July 08, 2013 3:28 PM
  • I see. So this is for load balancing, not disaster recovery/high availability, right?

    I recon you have 4 options, more or less usable for you:

    Log shipping, secondary can be read if you restore using STANDBY, but you will have old data (since last log restore) and you have to kick out all users for each log restore.

    Db mirroring: You can create a snapshot on the mirror, but it will always be out-of-date (since creation) and you have to devise some scheme on how to update (re-create) the snapshot.

    Availability Groups. You can read a replica. Requires a Win cluster for the setup (not necessarily shared disk, depends on setup etc).

    Transactional Replication. Data on subscriber will be "near real-time". Not really designed for HA/DR but if you really know what you do it can be used for such stuff.

    There's no substitute for understanding the architecture for each HA/DR and scalability technology. Only then can you pick the one(s) that matches your requirements.


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 08, 2013 4:55 PM
  • You want replication.

    If you want the changes to propagate to all servers in the topology you want merge replication.

    If you want the changes made to one to propagate to the other members (subscribers) but not back to the source (publisher) you want transactional replication.


    If you're happy and you know it vote and mark.

    Monday, July 08, 2013 8:17 PM
  • I'm interested in log shipping and now reading about it. Think AAG also will be a good choice, but so costly cause need Enterprise Edition. Currently I reading about Log shipping and not too clear about sync between two server. In which time gap they sync ?? Or can we define the gap of time to sync two servers in log shipping. Thank  for you all's time and help.
    Thursday, July 11, 2013 9:24 AM
  •  In which time gap they sync ?? Or can we define the gap of time to sync two servers in log shipping. Thank  for you all's time and help.

    They dont exactly sync in time gap..they sync when logs from primary are restored on seconday.If you have defined 15 min delay for backup,backup will happen every 15 mins,same for copy every 15 mins and same for restore every 15 mins.Suppose you did update on primary and after update commits immediately backup is happeing ..so total time for restore will be time taken to copy backup from primary to sec and then 15mins for restore to start...so they will sync in 15mins plust some more time.

    You can set time for each operation backup copy and restore and this will define how fast ur primary will be in sync with Sec generally there is some amount of delay.

    For more details please read transaction log shipping


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 11, 2013 10:06 AM
  • I wouldn't use Transaction Replication for HA/DR. I would use it in the case Jerry has.

    If you're happy and you know it vote and mark.

    Thursday, July 11, 2013 8:23 PM