principlesqlMinor
Mirroring vs. Log Shipping
Viewed 0 times
logshippingmirroring
Problem
The software company I work for is planning to migrating our infrastructure into the cloud. Our current focus is to find the best database replication method.
Current Servers:
SQL Server 2008 SE - Located in NY (In house - Master)
SQL Server 2012 SE - Located in Chicago (Rackspace - Slave)
We have been considering log shipping, but we are also aware of mirroring and replication.
Can someone please provide some insight on what our best approach for this task may be? We simply want to keep both databases in sync with each other. Real time does not matter yet, but it will in the near future.
Note: Our long term goal is to make Rackspace our primary, and NY our secondary.
Current Servers:
SQL Server 2008 SE - Located in NY (In house - Master)
SQL Server 2012 SE - Located in Chicago (Rackspace - Slave)
We have been considering log shipping, but we are also aware of mirroring and replication.
Can someone please provide some insight on what our best approach for this task may be? We simply want to keep both databases in sync with each other. Real time does not matter yet, but it will in the near future.
Note: Our long term goal is to make Rackspace our primary, and NY our secondary.
Solution
Log Shipping essentially involves three jobs. The first job backs up the log on the primary server and stores those backups in a local folder. The second job copies those files across the network to the secondary server. The third jobs restores those backups using the WITH STANDBY option. Log Shipping is set up on an entire database. The database on the secondary server is accessible but is read-only.
Requires: A shared folder on the primary server. Firewall configuration to allow the secondary server to access the file share. Security set up so the proxy on the second server has permissions to access the file share. This may be an issue for you in a hosted situation.
Caveat: Log shipping is not real-time. The common interval for the first job is 15 minutes. An unplanned failover will lose data.
Caveat: Log Shipping uses transaction log backups, so its design must be done in conjunction with your backup design.
Database Mirroring involves the primary server sending individual transactions to the secondary server, in either a synchronous fashion or an asynchronous fashion (asynchronous is enterprise edition only). Like Log Shipping, Mirroring It is set up on an entire database. Unlike Log Shipping, the secondary database is inaccessible.
Requires: An endpoint to be created on each sever for the mirroring traffic. Firewall allowing traffic from and to that one port.
In a synchronous mirroring setup, an unplanned failover will not lose data.
Both Mirroring and Log Shipping are creating a copy of an entire database so there is no object requirements. Heaps, Clustered Tables, tables with and without keys - all get copied.
Note: Given the requirement for file sharing, I disagree with Szymon's comment about log shipping being easier to set up and maintain and requiring less resource. Installing the File Server role on a Windows Server is increasing resource requirements as well as increasing the surface area of attack. Additionally, in a log shipping unplanned failover, bringing the secondary online is a pain. Lots of steps, most of which involve running stored procedures in a query window.
Requires: A shared folder on the primary server. Firewall configuration to allow the secondary server to access the file share. Security set up so the proxy on the second server has permissions to access the file share. This may be an issue for you in a hosted situation.
Caveat: Log shipping is not real-time. The common interval for the first job is 15 minutes. An unplanned failover will lose data.
Caveat: Log Shipping uses transaction log backups, so its design must be done in conjunction with your backup design.
Database Mirroring involves the primary server sending individual transactions to the secondary server, in either a synchronous fashion or an asynchronous fashion (asynchronous is enterprise edition only). Like Log Shipping, Mirroring It is set up on an entire database. Unlike Log Shipping, the secondary database is inaccessible.
Requires: An endpoint to be created on each sever for the mirroring traffic. Firewall allowing traffic from and to that one port.
In a synchronous mirroring setup, an unplanned failover will not lose data.
Both Mirroring and Log Shipping are creating a copy of an entire database so there is no object requirements. Heaps, Clustered Tables, tables with and without keys - all get copied.
Note: Given the requirement for file sharing, I disagree with Szymon's comment about log shipping being easier to set up and maintain and requiring less resource. Installing the File Server role on a Windows Server is increasing resource requirements as well as increasing the surface area of attack. Additionally, in a log shipping unplanned failover, bringing the secondary online is a pain. Lots of steps, most of which involve running stored procedures in a query window.
Context
StackExchange Database Administrators Q#51729, answer score: 7
Revisions (0)
No revisions yet.