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 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 '13 at 21:49

This question came from our site for professional system and network administrators.

add comment

3 Answers 3

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 '13 at 12:42
add comment

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 '13 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 '13 at 14:14
    
Well said Darin! That's it :) –  Sky Jan 25 '13 at 21:49
add comment

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
add comment

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.