SQL Server Performance Forum – Threads Archive
Replication Questions.
Here is what has been asked of me. I need to replicate a database overseas once daily – so, please help w/following: 1. Use Snapshot?2. What is best method – we’d rather push, should we use VPN or FTP?
3. Right now I can connect via IP and port – is that sufficient.
4. Can you replicate from 2000-2005? Or only 2005 to 2000? -OR- is there a better alternative than replication? Thx!
(1) How about T-log shipping?
(2) You can replicate from 2000- 2005. ***********************
Dinakar Nethi
SQL Server MVP
***********************
Ok, thank you. If I set the replication up ON the 2000 server as PUBLISHER, I can still replicate TO 2005? Log Shipping is too frequent, we only need it once daily.
Yes You can replicate from SQL server 2000 to SQL server 2005. The replication concepts are same. Satya
Cool. Via IP (vpn or ftp?) How would I need to set the schedules?
VPN is better than FTP and make sure the link is available always for replication consistency. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
What is the db size? Once a day you can do backup restore…
Backup the db to multiple files…
Zip the backup files…
copy backup files to destination…
Restore as new database…
swap the dbs once the restore completed… This is offline operation at the destination … If you do snapshot replication, it will also block the users but if it fails or lost connection in the middle of the process you end up with in accurate incorrect schema/data…
Mohammed U.
Thank Mohammed – that’s an idea – how can I automate it?
Size is less than 100mb (actually after I shrink the log its about 50mb)
How would I copy the db? I could use FTP for that right? Give me some sample logic/code Many THX!
Create a SSIS package with the following tasks and schedule as a sql job… 1. Backup the database…
2. Zip the backup file using COMPRESS widows command
3. UPload ftp task.. Destination Server…
Create the SSIS package with the following tasks and schedule it…
1. DowLoad ftp task…
2. Check the file date and Backup date time using RESTORE HEADERONLY command…
3. If it is new backup file… Restore as Database_new
4. Kill all the users from OriginalDB rename it to Database_Old and Rename Database_new to Original…
5. Sync the user… This way you don’t need to maintain the connectivity except during the file copy… Mohammed U.
]]>