Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Oct 18 '13 at 3:37

This question came from our site for professional and enthusiast programmers.

5 Answers 5

up vote 2 down vote accepted

In my opinion, log shipping has those advantages:

  1. I understand the direction is only one-way (master to slave). Replication is a complex solution better suited to two-way data replication or replication to multiple sources.

  2. Log shipping doesn't need a special design of the database (e.g. primary keys as GUID's and not as autoincrement integers).

  3. Log shipping is a less drain on resources, is easier to set up and maintain.

As the question is quite broad and opinion based, I can't give you an ultimate solution. This is just a bit of advice that you may hopefully find helpful.

share|improve this answer
1  
Szymon, thanks for your insight –  ilikemypizza Oct 17 '13 at 20:25
1  
Expand on point 2. Specifically mirroring, AFAIK, doesn't require any special database design requirements as you imply. –  Ben Thul Oct 17 '13 at 20:56
    
I think the same but don't know much about mirroring. I think it has lower performance than log shipping but I may be wrong. –  Szymon Oct 17 '13 at 21:05
    
I think mirroring is slower because it uses 2 phase commit (server one doesn't do a final commit until server 2 has done it). Log shipping is a "fire and forget" model - the primary server just tells the slave what to do and doesn't check that it has done it. Less redundancy but better performance –  Greg Oct 18 '13 at 5:37

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.

share|improve this answer

You should take some time and check out the Database Mirroring Best Practices and Performance Considerations online article. This can save you a lot of time and help you make the right choice

share|improve this answer

If you need real time synchronization of the data, I don't think log shipping is the right approach.

If you do set up log shipping, when you cut over to making the Chicago server your production database, you need to schedule an outage for applications that write transactions to the New York databases, insure that all logs are shipped and restored into the Chicago databases, point the applications at Chicago, and then reverse the direction of the log shipping, and finally end the outage and bring the applications back online.

share|improve this answer
    
I'm pretty sure there will be an outage anyway when you switch your primary data source. Or at least, it would be risky to not have an outage. –  Szymon Oct 17 '13 at 21:07
    
If you have a mirror then you should be able to cut over with 0 data loss (still have downtime as applications try to contact NY when it is offline). If you have a witness set up (google it) then it can fail over without any downtime. You can't do this with log shipping because you have no way of knowing when the log has been processed by the secondary (Chicago) –  Greg Oct 18 '13 at 5:39

Just a small note - you cannot setup mirroring session between databases of different SQL Server version

share|improve this answer
1  
Certainly you can. That is one of the best ways to do an upgrade with very little downtime. You can mirror to a newer version, then cutover to that server. Having said that you could not then failover to the original server until you upgrade it as well. –  Max Vernon Oct 30 '13 at 22:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.