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.
dodgerfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose TorresSenior SQL Server DBACommented:
When you say different networks do mean different domains
0
dodgerfanAuthor Commented:
Yes, different domains.
0
EmesCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
If possible, look at replication, as SQL will maintain that process for you after it's set up.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want to transfer the tables once a day then I'll recommend Snapshot Replication. You can configure it using a wizard and schedule the job to run daily at the hour you want to.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dodgerfanAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.