October 7, 2011 at 2:45 pm
We have recently moved some data into the SaaS model with a vendor. We have legacy applications that need to continue hitting the local SQL Server as if it never migrated off site. The vendor has allowed us access to the SQL server with our data via Citrix. What we'd like to do is keep the local SQL Server in synch with changes that are made to the database behind the Citrix connection.
My original thought was that I could create a job on the remote server to perform a differential backup, use SFTP to pull the backup file down to our network, and restore the differential on the local SQL server.
After doing some research, the differential approach won't work because the database needs to be in a non-operational state and have the full backup applied right before the differential is restored. Log shipping won't work because the 2 sql servers cannot connect directly with each other.
Our users would like this data refreshed every hour, so the process of completing a full backup, transfer to our network, and restore would take too much time.
Is there a better option for keeping 2 sql 2005 servers in synch that cannot talk directly to each other?
Thanks for your time
Ryan
October 10, 2011 at 3:24 pm
Just so we are on the same page
Server A (remote behind citrix) is online doing stuff.
Server B (your SQL server on a local system)
They cannot see each other on the network.
On you local instance you could maunally apply transaction log from the remote instance copied via ftp to a stand by readonly database
you could then create another database on the same instance and update the tables manually from the standby database.
Its clunky but it could work.
If the majority of work is reading tables you could create views to reference the the standby database also.
October 10, 2011 at 3:31 pm
You understand the situation correctly.
We will give the transaction log suggestion a try. That should work pretty well.
Thanks for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply