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:
- Constantly (but asynchronously) updated copies of dev/test/prod databases on our monitoring VM.
- 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:
- Implement database mirroring between our dev/test/prod database servers and our always-on monitoring VM in the host's network.
- 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:
- Is this the best and/or simplest way to accomplish what we are trying to do? If not, what is better and why?
- Is it even possible to do log shipping off a mirrored database?
- 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.