SQL Server Performance Forum – Threads Archive
Replication or Log Shipping ?
Hi, here is my problem. I got 2 server SQL 200 having for the Server 1 : database1, database2, database3 ; and database4, database5, database6 for the Server 2. I got 2 type of customers, those who connect on Server 1 and Those who connect on Server 2. If the Server 1 or 2 crash, i would like that the remaining server handle all the customers for a moment. And when the crashed server come back online, i need he restore its databases from the other server. P.S: I use citrix, so customer work on those databases directly on the servers. My questions :1. is it possible via the Replication or the Log shipping procedure ?
2. If i can do it via the replication way, it seems to me that i ll have to set up both server as Publisher/Subscriber and Distributor, Right ? Hope i am clear . BTW thanks for the great info on this site.
I still have a lot to learn.
I would recommend log shipping since it would be easier to create objects in a published database than replication. For example, with replication, you will have to drop the subscription before you can add an article (table or view) to a publication. Keep in mind that with log shipping restoration, the database is unavailable for general use.
Replication allows for 24/7 availability although it is (slightly) more difficult to implement. You’d likely want to go with simple transactional replication, in the latter case When you use log shipping the unit of redundancy is the whole database, so any changes you make inside your database will be transferred to the warm standby server when you apply transaction logs to it. Replication, on the other hand, has articles as the unit of redundancy, which is basically a set of tables (and maybe procs), so any changes to data in these tables will be replicated to the subscriber, but anything outside like users, indexes, etc has to be transferred manually. As you can see replication may have less latency but it’s more work for the dba than log shipping. 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 satya
I would recommend log shipping since it would be easier to create objects in a published database than replication. For example, with replication, you will have to drop the subscription before you can add an article (table or view) to a publication.
Keep in mind that with log shipping restoration, the database is unavailable for general use.
Replication allows for 24/7 availability although it is (slightly) more difficult to implement. You’d likely want to go with simple transactional replication, in the latter case
Replication allows for 24/7 availability although it is (slightly) more difficult to implement. You’d likely want to go with simple transactional replication, in the latter case
So if i well understood : 1. I cant have Server 1 that will be a Publisher and Distributor (sending db 1,2,3) for the Server 2 that will be a Subscriber (receiving db 1,2,3) and in the same time Server 2 that will be a Publisher and Distributor (sending db 4,5,6) for the Server 1 that will be a Subscriber (receiving db 4,5,6) ?? Or did i miss-translate ? 2. Via the replication process, is the change (IP, NAME) of the standby server to become the primary server is automatic ? 3. For the log shipping process, once the primary serv is down, and the stand by server has been configured to handle the work ; is there a specific process to restore the ‘old’ primary server or should i have to set up a new log shipping process or just the usual backup ? same for the replication process. hope i am clear. Thanks.
1. Yes, but this pattern itself seems to be confusing.
2. The common benefit of SQL Server replication is the availability of data when and where it is needed. And for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency. 3. Yes you can easily transform or simulate the failover server and make standby as primary server in case it fails.
(but different approach from Replication)
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.
Why this pattern seems confusing to you ? *scratch head* Thanks
May be the I should use term MIXUP instead of CONFUSING, being the same server is involved in publishing and subscribing in replication and also in terms of log shipping participation. Is there any chance of adding 2 more server to act as STANDBY server instead of using server 2 as standby for server 1 and vice-versa. 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.
I see ur point. and Indeed i had already thought at his solution.
But its not possible. in fact i was thinking about :
– for the log shipping option, having the server 1 as primary server with all the database and having the server 2 as a standby server. => seems ok for me
– for the replication option, having this "special" configuration or both publish/subcrib for the both server. => this option seems harder to me but slighty more "powerful". What ya think about ?
From my experience Log shipping is easy and with an extra configuration you can make it powerful and use in case of troubled situation. Had bitter expereinces with Replication, though could able to resolve there and then. So it purely depends on your capability and managebility between these 2 options. 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.
Well its clear now , ty a lot Satya for advices and tips <img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ /><br /><br />I will go for the log shipping process i think.<br /><br />*bows*
]]>