Mirroring SQL Database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Mirroring SQL Database

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

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
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
[email protected] When life gives you a lemon, fire the DBA.
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.
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
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.
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
[?]
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.
]]>