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.

I'm reading the many other posts here about mirroring vs. replication, but I'd still appreciate it if someone with some experience would weigh in.

My small development team needs to have local automatic synchronization of databases which are hosted remotely.

The network situation is a little complicated:

  • The actual databases (dev, test, prod) are being hosted remotely on VMs in a DMZ. Our team does not have direct access to the DMZ.
  • Instead we have VPN access to the host's internal network, and the firewall between the internal network and the DMZ currently only allows RDP to pass through.
  • Locally we have a development database server, and individual developers have local SQL Servers on their development laptops.

We have requested and received an always-on VM in the remote host internal network that we plan to use to monitor the health of our dev/test/prod servers as well as to maintain hot backups of our databases.

So we are looking at two levels of data synchronization:

  1. Constantly (but asynchronously) updated copies of dev/test/prod databases on our monitoring VM.
  2. Occasionally-updated copies of the databases on our local development database server and/or developer laptops.

We aren't looking to implement fail-over at this point, because even the "prod" environment is not critical (it's more of a pre-prod environment).

We also need this to be as simple as possible, and SQL Server 2012 is not an option. We are stuck with 2008.

Here's what I'm thinking:

  1. Implement database mirroring between our dev/test/prod database servers and our always-on monitoring VM in the host's network.
  2. Implement log shipping to occasionally (when on the VPN) synchronize our development database server and laptops with the DB copies on the always-on monitoring VM.

My questions are:

  1. Is this the best and/or simplest way to accomplish what we are trying to do? If not, what is better and why?
  2. Is it even possible to do log shipping off a mirrored database?
  3. What ports are required to be open in order to implement database mirroring (or log shipping if that's better) between dev/test/prod in the DMZ and our monitor VM in the internal network?

Thank you for your time.

share|improve this question

migrated from stackoverflow.com Mar 6 '13 at 21:57

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

1  
Are you stuck with ONLY having the RDP port open or can you also open up the ports required for mirroring (default 5022), replication (it's own port list + windows file and print sharing if you want to send snapshots over, otherwise a FTP solution to send it). Are you stuck with just RDP? –  Ali Razeghi Mar 6 '13 at 22:45
    
No, actually, the reason this is critical is because I need to request the port exception ASAP, so I need to know which ports to request, so I need to know which technology to choose. I might be able to just request both. Which ports are associated with file & print sharing? –  JimBurnell Mar 7 '13 at 15:12
    
According to support.microsoft.com/kb/298804, the ports for file-sharing are either TCP/UDP 135-139 or TCP/UDP 445? Any idea which of these is preferable? –  JimBurnell Mar 7 '13 at 15:25
    
Hi Jim, Sorry for the delay, I hadn't logged in these last 2 days working on some high priority items. It depends on which version I believe, it's changed a few times, I would recommend testing both. Did you get the exceptions you needed? –  Ali Razeghi Mar 9 '13 at 17:23

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.