Tell me more ×
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.
up vote 3 down vote favorite

I have a database in a server in Africa, and need a daily sync\restore to a server in Europe.

My first idea was to sync the data between the 2 databases, but the database has more than 1000 tables, and is not well built, so the software I've tested didn't work great.

For what I've read the best option is to backup the database in one server and restore it to the remote server.

Is this the best solution?

Is Redgate Backup Pro the best tool for the task?

I've been playing with it and it seems to have everything I need: backup\restore scheduling, compression, etc.

Need some advice on this. Thanks in advance :)

share|improve this question

migrated from serverfault.com Jan 23 at 21:49

3 Answers

up vote 1 down vote

Scheduling backups and restores via the SQL Agent isn't very hard. It is only one or two lines of TSQL. Just make sure that the backup finishes before the restore starts.

You do not mention which version or edition of SQL Server you are using. Some versions/editions of SQL Server support Backup compression natively. (Here is the page that talks about the capabilities of the different editions of SQL Server 2012.) If you are not using a version/edition of SQL Server that supports compression, RedGate's backup compression should be a reasonable choice. (I've not used that particular compressor. I have used a competitor, SQL Lightspeed. I believe that the products are fundamentally the same.)

In the past, I've found the larger problem is getting the backup files from one location to the other, as it is often assumed that both servers are on the same LAN and domain and that you can simply use copy.exe or robocopy.exe. I was forced to cobble together my own FTP transfer schemes back then, which is more work and testing.

Another problem is getting the user IDs synced up with the login IDs after restoring the database to the target server. If you have already done this a few times, you know what I mean. If not, here is a link to a relevant SE thread.

share|improve this answer
In Europe we have SQL Server 2008 (not R2). I don't know if the client will use this edition in Africa or will use an Express Edition.. – CSeven Jan 24 at 12:42
up vote 0 down vote

Have you thought about Log shipping? It might be worthwhile to consider it as another option. It can be faster than restoring whole database (of course it depends how many transactions you usually have per day, it can differ case by case). To do that you need follow the steps below:

Back up the transaction log at the primary server instance.

Copy the transaction log file to the secondary server instance.

Restore the log backup on the secondary server instance.

http://msdn.microsoft.com/en-au/library/ms187103.aspx

share|improve this answer
Log shipping is not working if the servers are not in the same domain. And I'd think they are separate domains, at least by thinking of the locations :-). – Marian Jan 24 at 23:28
1  
With a few hours of coding, you can write your own log shipping with TSQL. You just follow the Backup->Copy->Restore workflow. You can copy via ftp (if you don't have a vpn, encrypt the file first, ftp is not secure) or you could use scp or something along those lines. Set up one Agent job to copy files to a local folder. Set up another job to check for and restore tlog backups. Once you have the file local to you, check to see if the tlog backup has already been restored. If not, restore it and then flag the file as restored. Repeat at intervals. You still must sync the logins and users. – darin strait Jan 25 at 14:14
Well said Darin! That's it :) – Nazila Beikpour Jan 25 at 21:49
up vote 0 down vote

If you are able to create a Site to Site VPN between servers in Africa and Europe, you should be able to setup Log Shipping pretty easily. With Site to site you can just copy/paste the files i.e. eliminating FTP completely. With log shipping, you can even keep the database in Read Only/Stand By mode to query from if needed.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.