How to insert data from a local MSSQL db table to a remote MSSQL db table using WCF application coded in .NET [closed]

Question

I made a WPF application using VS2013, coded in .NET. It is working fine in my local SQL Server. But I want to use it to upload data from a local database to a remote database. How can I do this?

I have three buttons (UPLOAD, DOWNLOAD, SYNC), and three stored procedures in SQL Server.

• Upload will run insert into Database123.table, select * from DATABASE456.table
• Download will run insert into database456.table2, select * from database123.table2
• sync will run delete from databse456.table1 where id in database456.table2

But i want to use a remote database in place of database123. How can I do this?

Answer 1

I can see four ways you could accomplish this:

1. Linked servers

You can establish a linked server on both the local and remote SQL Server, which will allow you to reference tables not only on the same machine, but also on the remote machine, like so:

INSERT INTO some_server.some_database.some_schema.some_table SELECT * FROM local_database.local_schema.local_table;

You might run into trouble with your database administrators - some are perfectly fine with linked servers, others not. Also, there's a bit of work to making authentication over linked servers work without introducing security risks.

2. Replace the stored procedures with application logic

Create two connections in your application, one to the source database, the other one to the target database. Then, for each row you read on the source connection, you insert/delete/perform some action on the target connection.

The downside with this approach is that you can run into performance issues if you work with large amounts of data, or if you're not careful with your database design and indexing.

3. ETL tools

There are a range of data tools out there, including SSIS and Pentaho, that allow you to create data transfer/synchronization processes in a graphical interface in Visual Studio. SSIS is included with SQL Server.

Obviously, there's a considerable learning curve to mastering these tools.

4. SQL Server replication

For bonus points, see if setting up replication between the two SQL Servers could solve your problem, or ask a database administrator for help with this.