Sql Server mirroring between two servers

  • Dear all

    i have a server for windows 2003 and sql server database 2005 and have around 10 database

    and also i have the same server lying in canada and it has also got the same windows server 2003

    and database server 2005

    now i want to sysnc them both means what database i have in here i want the same to be in the server

    in canada and i want that to happen regularly that both server should sync in the evening so how can i

    do that can anybody help

    regards

    puneet brar

  • It depends on how you intend to use that other server in Canada. Can you provide more details as to what you are trying to accomplish?

  • Can you connect to the server in canada from your server.

    if yes, then you have many different methods to sync up your databases in 2 locations.

    for eg:

    Snapshot Replication (schedule once / twice a day)

    Other Replications

    Log shipping

    DB Mirroring!

    Pavan.

  • thanks for the prompt response

    Yep i can log on to server in canada basically i want that both the database should be same as sometimes my server in india goes down or the internet gets down so thats a problem

    so what i want is that when i leave office the database in the canada server should be the same so that if this server goes down we can use that

  • In this case you can go for

    Log shipping

    Transactional replication

    database mirroring

    explore these before you take a step!

    Pavan.

  • can you please tell me some step by step procedure i mean how can i do that and hhow can i start on that score

    i will b ereally thankfull

  • I do not believe that it is a straight forward answer.

    There is a lot of analysis that needs to be done before you can choose which high availability mechanism you would choose.

    Factors to consider are:

    1. Max time Latency between the two databases

    2. Geographical location

    3. Network bandwidth between these two servers

    4. Number of databases you need to replicate

    5. Size of databases

    6. Usage pattern of databases

    After pondering on all these things, you will find a log of articles on MSDN and other sites on how to setup each one up. You can also go through sql server books for a beginner level detailed explanation.

    Regards

    Akhil

  • Puneet Brar (1/28/2010)


    Dear all

    i have a server for windows 2003 and sql server database 2005 and have around 10 database

    and also i have the same server lying in canada and it has also got the same windows server 2003

    and database server 2005

    now i want to sysnc them both means what database i have in here i want the same to be in the server

    in canada and i want that to happen regularly that both server should sync in the evening so how can i

    do that can anybody help

    regards

    puneet brar

    How big is the database? What will be the size of it after 1 one year?

  • 1. Max time Latency between the two databases

    latency between the two databases should not be an issue as i suppose i do not want the database to be mirrored all the time i just want them to be mirrored or replicated on every evening at around 20:00 hours

    2. Geographical location

    One database server is in india and one is in canada

    3. Network bandwidth between these two servers

    bandwidth is one side we have a t100 line and one side that is in india we have a 784mbps connection

    4. Number of databases you need to replicate

    number of databases are eight to 10

    5. Size of databases

    max 1gb the size i of each database once we can manually shift the whole database but after that

    i want them to be mirrored

    6. Usage pattern of databases

    usage pattern is mainly development purpose as one database remains free the whole day

    and we just wanted to use them when one is down

    After pondering on all these things, you will find a log of articles on MSDN and other sites on how to setup each one up. You can also go through sql server books for a beginner level detailed explanation.

  • From what you've said, your connection will possibly sustain mirroring with automatic failover, if you have another SQL Server you can use as a witness (this can be a SQL Server Express installation). This will allow you High Availability, and you will be able to schedule snapshots of the database which is set to "recovering" (the mirror) for reporting purposes. In this configuration, the mirror will be entirely synchronised with the principal as changes are committed to the mirror prior to being comitted on the principal. The downside of this is that if your connection isn't sufficient to sustain the mirroring process, the speed of your system will suffer. If you notice performance issues, you should consider using mirroring in Performance mode. This doesn't require a witness server and is asynchronous, so it won't affect system performance. In this configuration, if a problem occurs on the principal database, you will need to perform a manual failover.

    Since you mention you have 8 to 10 database you wish to "replicate" you need to be careful if the databases have interdependencies since mirrored databases can fail over individually, not necessarily collectively.

    If you have the capacity, I would recommend you test both these configurations and see which is more suitable for your infrastructure.

    I hope this is of some help.

  • True HA/DR is a VERY complex yet critical topic and you really should consider getting an expert on board to advise/assist you. You are heading for trouble without that.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 11 posts - 1 through 10 (of 10 total)

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