locked
Other use of Mirroring ?? RRS feed

  • Question

  • Hi,

    I am in need to replace all the (50+) databases to a remote server on a daily basis??

    Both servers need to be active during normal use.

    So the Question is can I use Mirroring to replicate data onto the other server by mirroring and then disable mirroring to use both the servers.

    can this approach be used, as I am using SQL SERVER for the first time.

    If not what are best possible options i could use??

    Thanks in Advance. !!

    Monday, May 8, 2017 1:57 PM

Answers

  • I've worked on topologies like this.

    Differential backups might help you here. Do a full on Sundays and then differentials for the rest of the week.

    Transactional replication can work - it will replicate from s1 to s2 (source to destination) only and you can have it run somewhat current.

    You may run into desktop heap limitations with large numbers of databases. Using pull replication will help you here. Using replication will meet all of your stated goals, and all that will go over the wire are changes.

    • Proposed as answer by Teige Gao Tuesday, May 9, 2017 2:27 AM
    • Marked as answer by Saurabh055 Tuesday, May 9, 2017 5:45 AM
    Monday, May 8, 2017 3:07 PM

All replies

  • From what you describe you need to make a copy of your databases at a point in time to another server. As you are using mirroring, I suspect you are breaking the mirror on the secondary to allow uses to access the databases.

    Mirroring is best used to protect a copy of a database and keep a synchronized copy on the secondary. You can break the mirror so that users can access it on the secondary, but at that time, your mirror'd databases becomes progressively more and more out of sync.

    Are you looking to keep the databases accessible on your remote server and somewhat in sync on the remote server?

    If you want them readable all the time - you need to use an Always on Availability Group with SQL 2012 Enterprise Edition and above.

    If you want them readable and writeable but not quite up to sync transactional replication is the best choice.

    If you want them up to date, but accessible at some point - look at log shipping or mirroring. With both of these technologies you will need to break the mirror or recover the databases. Log Shipping does allow you to run in standby mode where the databases are somewhat up to date, but you would need to kick your users off line each time you want to apply the log.

    Monday, May 8, 2017 2:13 PM
  • If I read you correctly you want changes made to a database be made available at other location at certain time and this other location need not always be in sync with other, if this is the scenario and simple backup of database and then restore should do. I am going correct ?

    For mirroring you would have to break it and reconfigure every day which would be tedious task.

    AOAG can be option but the other replica/database would always be read only so you cannot run insert update delete on it.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, May 8, 2017 2:42 PM
  • If I read you correctly you want changes made to a database be made available at other location at certain time and this other location need not always be in sync with other, if this is the scenario and simple backup of database and then restore should do. I am going correct ?

    For mirroring you would have to break it and reconfigure every day which would be tedious task.

    AOAG can be option but the other replica/database would always be read only so you cannot run insert update delete on it.

    Yes @Shanky_621 you got the requirement correct.

    Simple backup and restore is my last option as the number of databases on the server is very large (May even cause errors during manual activity).

    Both the databases need to be available for all the operations during normal times.

    Monday, May 8, 2017 3:00 PM
  • From what you describe you need to make a copy of your databases at a point in time to another server. As you are using mirroring, I suspect you are breaking the mirror on the secondary to allow uses to access the databases.

    Mirroring is best used to protect a copy of a database and keep a synchronized copy on the secondary. You can break the mirror so that users can access it on the secondary, but at that time, your mirror'd databases becomes progressively more and more out of sync.

    Are you looking to keep the databases accessible on your remote server and somewhat in sync on the remote server?

    If you want them readable all the time - you need to use an Always on Availability Group with SQL 2012 Enterprise Edition and above.

    If you want them readable and writeable but not quite up to sync transactional replication is the best choice.

    If you want them up to date, but accessible at some point - look at log shipping or mirroring. With both of these technologies you will need to break the mirror or recover the databases. Log Shipping does allow you to run in standby mode where the databases are somewhat up to date, but you would need to kick your users off line each time you want to apply the log.

    The databases need to be at sync only at the start of the day (S1 to s2). i.e. S2 will maintain a copy of S1 at every start of each day. If S2 is out of sync throughout the day it's is not an Issue (S2 will be accessible internally).

    We are using Standard Edition on both the servers.

    Transactional replication will also snyc changes from S2 to S1 which is not desired.

    As the number of Databases on the Server is very high, Backup and Recover this Data from one server to other is very time consuming (manually may be even error prone).

    Also considering at different Data Integration TPT to achieve this.


    Monday, May 8, 2017 3:01 PM
  • I've worked on topologies like this.

    Differential backups might help you here. Do a full on Sundays and then differentials for the rest of the week.

    Transactional replication can work - it will replicate from s1 to s2 (source to destination) only and you can have it run somewhat current.

    You may run into desktop heap limitations with large numbers of databases. Using pull replication will help you here. Using replication will meet all of your stated goals, and all that will go over the wire are changes.

    • Proposed as answer by Teige Gao Tuesday, May 9, 2017 2:27 AM
    • Marked as answer by Saurabh055 Tuesday, May 9, 2017 5:45 AM
    Monday, May 8, 2017 3:07 PM
  • Yes @Shanky_621 you got the requirement correct.

    Simple backup and restore is my last option as the number of databases on the server is very large (May even cause errors during manual activity).

    Both the databases need to be available for all the operations during normal times.


    No that would be easy please see how to automate backup and restore

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Teige Gao Tuesday, May 9, 2017 2:27 AM
    Monday, May 8, 2017 3:09 PM
  • For large numbers of databases or large databases you may struggle with this given time constraints and available network bandwidth.

    If this is a case you may not be able to ship full databases on a daily basis. You will need to select other options which are more efficient.

    Monday, May 8, 2017 3:18 PM