SQL Server Performance

Mirroring SQL Database

Discussion in 'Performance Tuning for SQL Server Replication' started by Kats123, Aug 16, 2004.

  1. Kats123 New Member

    HI,

    I am relatively new to SQL 2000. Have an SQL 2000 database running on Server 1. I would like to mirror this database onto another SQL 2000 server so that I can have a realtime copy of my database on Server 2. Also, please advise on how this can be done using software.

    regards
    George
  2. chopeen Member

    quote:Originally posted by Kats123

    I am relatively new to SQL 2000. Have an SQL 2000 database running on Server 1. I would like to mirror this database onto another SQL 2000 server so that I can have a realtime copy of my database on Server 2. Also, please advise on how this can be done using software.

    MS SQL Server 2000 has two features that can find useful:
    1. replication (there are 3 kinds of replication, but I think that you will be interested in transactional replication in the first place),
    2. log shipping (Enterprise Edition only).

    You will find a lot of information about both this topics on BOL.

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  3. derrickleggett New Member

    If you use log shipping, the databases on the second server will be unavailable while it's restoring the logs. Transactional replication would allow the data to be available.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. satya Moderator

    Few articles about replication concept for your reference:
    http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000--Part-1/
    http://www.databasejournal.com/features/mssql/article.php/3383221
    http://www.sql-server-performance.com/replication_tuning.asp


    If you would like to have only warm standby then Log shipping will do the work, refer to SQL books online for more information in this regard.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. chopeen Member

    quote:Originally posted by derrickleggett

    If you use log shipping, the databases on the second server will be unavailable while it's restoring the logs.

    I did not know that. Although now when I think of it, it seems pretty self-explanatory.

    How does it exactly work?
    Suppose, that there are some users connected to the database, when a new log comes and needs to be restored. Will those connections be automatically terminated or will the log restore wait?

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  6. satya Moderator

    http://www.sql-server-performance.com/q&a37.asp to answer your question.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Kats123 New Member

    Thank you everyone,

    I have looked at information on both transactional replication and log shipping. On sql forums many people seem to have problems with SQL replication. Log Shipping looks the easier option. From your experience can I use log shipping to replicate the database and logs. Lets say every 15 min automatically as an SQL JOB? Or is it better to set up a main sql database and sql subscriber?

    PS: I looked at DTS as an option the only problem is if DTS cuts out during a transfer I potentially loos all my transaction logs.

    regards
    George
    [?]
  8. satya Moderator

    Log Shipping is available to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database.

    DTS can be used to export table data and views but not all the objects of the database, so in your case Log shipping is the ideal solution.

    For more information on setting up Log shipping refer to the books online.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page