Refreshing Development Databases

SQL Server Best Practices
SQL Server Best Practices
Been a busy week at work and I’ve had to perform multiple dumps of production databases back to a development server. This is something that would normally be pretty quick and easy however a couple of things impacted that and led to having to make some choices about how to perform the task.

Here are the relevant details that I need to use to decide how to move the backup files:

  1. The servers are in two different datacenters and the WAN link between them is not very fast
  2. The backups are stored on a CIFS share that is non-routable for anything other than machines on that VLAN (for security reasons)
  3. There is only a single machine in the datacenter that has the ability to reach through the firewall to the development server
  4. I had to get the data across the WAN as fast as possible, however did not want to have to babysit what would be a several hour process (it should restarted in the case of any issues)
  5. All the hosts involved are running Windows 2008R2 SP1
  6. I did not want to install any extra software on any of the servers
  7. This is not an ongoing process and so I wanted something quick. No need to try and over-engineer the situation

How to get at the backup

My first thought was to use the improved TCP stack in Windows 2008R2, log on to the development server and pull the data from the CIFS share. Unfortunately this was out as the development machine does not have access to the subnet in order to pull the data.

Next was to perform a backup directly across the WAN to the development machine. This would not work due to firewall restrictions preventing communication between the machines (and this would also not be network optimized)

The only option left was to use the single machine in the datacenter (let’s call this the jump server), on the relevant VLAN, which had access through the firewall to push the backup to the development box.

Continue reading on SirSQL.net.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *