Post reply

Sync Databases

  • November 29, 2010 at 2:54 am

    #241753

    Hi,

    I need help in this scenario. We have one SQl server, I need to maintain two databases of this server in another physically far location.

    Just the exact copy not like failover. Just the same data so that if some thing happens on one, other should also be up but not like automatic failover or anything.

    Just to sync databases that whatever happened in primary should be synced with other. I am very confused that what should I use, replication, mirroring, log shipping.

    Can anyone advice?

    Thanks for help!

  • November 30, 2010 at 2:50 am

    #1256657

    You can go for replication

    If you plan for transactional replication then all the tables must have primary key.

  • November 30, 2010 at 3:10 am

    #1256662

    Thanks for reply!

    After reading a lot about everything I am thinking to go with Log Shipping? Can you give me suggestion on that?

    Regards

  • November 30, 2010 at 3:35 am

    #1256669

    Log shipping sends a backup of the log over and restores it. It's only as up-to-date as the most recent log that was shipped over....

    if you want to a copy of the database so more people can use the data from multiple locations use replication..

    if you want it for high availability use log shipping.

  • November 30, 2010 at 3:47 am

    #1256673

    Thanks..thats what I am looking for , we need to create a passive backup solution so I think log shipping will work.

    Thanks again for help

    Regards

  • November 30, 2010 at 12:02 pm

    #1256946

    You can configure the log shipping to be stand by(Read only) to use as reporting environment. Of course during the log restore the database will be in restore/recovery mode.

  • November 30, 2010 at 2:49 pm

    #1257062

    Bsql_Dev,

    The needs described are what we call Disaster Recovery or DR. The first task in architecting DR is establishing Recovery Point Objectives RPO and Recovery Time Objectives RTO. RPO is the amount of acceptable data loss expressed in time, RTO is the maximum time required to perform a system failover, also expressed in time. These values will dictate the available options and how they are implemented.

    Note RTO is the amount of time for complete system failover, not just the backend, this needs to include application layer, DNS, load balancing systems... everything required to restore services to end users. Don’t forget to include outage notification, waking up and logging in!

    Given the distance concern you will be limited to asynchronous transactions, the maximum distance between data centers needs to be no greater than 100 km to support synchronous or two phase commit. The 100 km limit is an industry standard it's not an absolute being as it's dependant on latency, bandwidth and application resiliency.

    So what are the options? Currently Microsoft is recommending database mirroring as it’s DR solution for SQL Server 2008 installations. The other not so evident option is SAN based mirroring, contact your SAN vendor for implementation considerations and options.

    Database Mirroring has a slight learning curve, nothing extraordinary don’t worry. Once setup manually failing over and failing back with Database Mirroring is simple. The challenges you will face are explaining to management why synchronous isn’t going to happen and managing system databases.

    Good luck!

    Dan

  • December 1, 2010 at 12:45 am

    #1257179

    Thanks Ki Chiang & Drawlings,

    Drawlings,

    Thanks for information and explaining, in my case I am gonna put complete live solution including DSN, Application setup everything will be up but just not live so if some thing happen at one site, you can use the complete system on other server.

    The thing is if database goes wrong its different, but we have big systems connected with databases.. so I need to create two fully operated server environments , only problem is to sync DATABASE changes from current to backup environment

    Regards

  • December 3, 2010 at 12:56 am

    #1258231

    Hi again,

    I have some problems in settings now.

    I have tested log shipping on same domain and it did work.

    Bur for Live I need to setup in different domains and I don't know how to do that I need to create share but it's not working spent so many hour but still have problems. I have three problems.

    1. How I create this share folder for backup in different domains.

    2. When you add secondary in log shipping how I connect to SQL Server which is in different domain.

    3. I need to make current live Server as Seconday and new Server as primary by restoring a full backup of current Live. How I setup current live as secondary.

    Can you give me some advices on that. Thanks much .

    Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply