June 1, 2011 at 10:59 pm
What would be the best strategy for a SQL server database copy/move from a source location separated by the Internet to a destination? (preferably automated)
In this case both locations are not connected by VPN.
They are on different domains/networks.
The frequency of getting the copy from the source will be approximately twice a month.
Prefer to use a MS SQL Server solution rather than custom programmatic solution
June 2, 2011 at 5:24 am
don't have a possibility of to use a VPN? just by security!!
June 2, 2011 at 6:26 am
My first instinct on reading the title and intial requirement statement was to suggest snapshot replication.
However, for something twice a month, I'd suggest a simple SSIS package, or even a web service. Either one can bypass domain-difference issues.
You might be able to do something with a linked server, using SQL security to bypass domain authentication, but that will invoke the distributed transaction coordinator, and that's often a huge bottleneck, especially if it's a lot of data.
If it were my project, I'd use SSIS. It's a toolset I'm comfortable with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2011 at 5:45 am
Hi,
You can use either SQL transactional or Snapshot replication but i recommend transactional type and based on the size of the database you need to decide the method of initializtation.
if the database size is not that big, you can initalize using Snaphot if not use a backup file.
then schedule the distributor job to replicate (Or push )the updates to the subscriber once per week.
Or you can consider SSIS package scheduled weekly to move the data to the other location.
Regards,
Nesma Mounir
June 7, 2011 at 6:29 am
My problem here is both the systems are not on same network... How to configure Replication if Both the servers are on different network...
June 7, 2011 at 6:55 am
odanwada (6/7/2011)
My problem here is both the systems are not on same network... How to configure Replication if Both the servers are on different network...
Use SQL Server authentication, instead of Windows authentication. I set up snapshot replication between two domains, and set up an SQL login specifically for that. It works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 9:08 am
Use SQL Server authentication and grant the SQL login access to both servers and it will work perfectly fine
Regards,
Nesma Mounir
June 7, 2011 at 10:06 am
Use SQL Server authentication, instead of Windows authentication. I set up snapshot replication between two domains, and set up an SQL login specifically for that. It works.
i agree!!!
June 8, 2011 at 12:18 am
1-Take Full Backup in the shared folder of network 1 and then other network have rights to restore from this shared folder
2-You can Use FTP from 1 network to another and then restore on the second domain
3-Create the backup device on the other network shared folder and then restore on it
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 8, 2011 at 12:03 pm
GSquared (6/2/2011)
but that will invoke the distributed transaction coordinator
not to mention all the NetBIOS port coms it requires
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply