locked
How can I sync SQl Servers between two locations? RRS feed

  • Question

  • Hello Experts, I'm an MCSE on server 2012 side. Well one of my colleague is managing SQL Database and apps. Previously we were in single location. Now we have expand our educational campuses in different location. We are using SQL Server 2008 R2 for testing, attendance etc etc. Now we want to deploy sql server on different campuses and sync them with our main campus, How could this possible? We don't have high bandwidth internet facility to use only main sql database online, that's why we're thinking to deploy SQL Server separately in each campus and then these servers can be synced late night. Please guide me how can I sync/mirroring these databases and what settings are required.?
    Friday, October 16, 2015 6:20 PM

Answers

  • The process is relatively straightforward:

    1. Create Mirroring endpoints
    2. Backup primary and logs and restore to secondary with logs (with NORECOVERY)
    3. Grant rights on your endpoints
    4. Establish Mirror

    I as suspecting hat your campuses are within different Active directory domains so if that is the case you will need to use Certificate based authentication for your endpoint security (which is a little more complex). See "Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)" article for the full process.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Sunday, October 18, 2015 6:40 AM
  • You can have Database mirroring.. Log shipping..As suggested already you can automate the nightly backup and the restore it in another site daily to keep both the sites DBs in sync...

    But you have to think about RPO & RTO in case of any Disaster ...

    Peer-to-peer replication for HA and DR (and reporting).

    Synchronous database mirroring for HA/DR and log shipping for additional DR.

    https://technet.microsoft.com/en-us/library/hh393561%28v=sql.110%29.aspx

    RTO & RPO :-

    http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/

    http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives


    Raju Rasagounder Sr MSSQL DBA


    Sunday, October 18, 2015 10:05 PM
  • Hi There,

    as per your requirement mirroring is not the best solution for you.

    you wanted to sync the databases in more than one location. with mirroring you can establish between two databases only.

    And you if you wanted to leave the data bases operational so that they can synch each other with main server !!!your solution is merge replication.

    using Merge replication you can get the data changes between all your campuses and leaving your databases on each campus is operational.

    Good luck

    kumar

    Monday, October 26, 2015 4:02 AM

All replies

  • The process is relatively straightforward:

    1. Create Mirroring endpoints
    2. Backup primary and logs and restore to secondary with logs (with NORECOVERY)
    3. Grant rights on your endpoints
    4. Establish Mirror

    I as suspecting hat your campuses are within different Active directory domains so if that is the case you will need to use Certificate based authentication for your endpoint security (which is a little more complex). See "Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)" article for the full process.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Sunday, October 18, 2015 6:40 AM
  • Generally you can copy backups over each campus and perform restore database , BUT the question is what if main's campus server is gone down, what happens then?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 18, 2015 6:45 AM
  • You can have Database mirroring.. Log shipping..As suggested already you can automate the nightly backup and the restore it in another site daily to keep both the sites DBs in sync...

    But you have to think about RPO & RTO in case of any Disaster ...

    Peer-to-peer replication for HA and DR (and reporting).

    Synchronous database mirroring for HA/DR and log shipping for additional DR.

    https://technet.microsoft.com/en-us/library/hh393561%28v=sql.110%29.aspx

    RTO & RPO :-

    http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/

    http://sqlmag.com/blog/sql-server-recovery-time-objectives-and-recovery-point-objectives


    Raju Rasagounder Sr MSSQL DBA


    Sunday, October 18, 2015 10:05 PM
  • Hi There,

    as per your requirement mirroring is not the best solution for you.

    you wanted to sync the databases in more than one location. with mirroring you can establish between two databases only.

    And you if you wanted to leave the data bases operational so that they can synch each other with main server !!!your solution is merge replication.

    using Merge replication you can get the data changes between all your campuses and leaving your databases on each campus is operational.

    Good luck

    kumar

    Monday, October 26, 2015 4:02 AM