Move data from one SQL server to another
I need to have data from multiple tables in one SQL Server database moved to another SQL Server database on a daily basis. I am looking to find the best approach to this. The SQL Servers exist on different networks. Linked servers may not be possible. I'd like to automate this as much as I can, as it will be a daily dump of info from one server to the other. Any ideas on the best approach to this? Both are SQL Server 2012 databases.
Jose Torres
When you say different networks do mean different domains
ASKER
Yes, different domains.
Are you able to use SSIS?
other wise you need to dump the data to a point where bother severs can access the data. the best way is with SSIS.
other wise you need to dump the data to a point where bother severs can access the data. the best way is with SSIS.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I know now that I can use linked servers. However, the tables I have on the receiving server can't be updated while the primary key is set to increment. Can that be programmatically turned on and off? My attempts so far have failed, as doing that with a linked server does not seem to work. Another problem is that I want to keep the tables in sync. The first SQL Server will get updates daily, and I want to send that data to the linked server. But that 2nd server may end up with new records of its own I need to not overwrite and send back to the original database.