Transfer data from one location to another having the same database and table schema

Question

We have 2 branches B-1 and B-2 within the distance of 100kms. Both these Branches have their own SQL server setup in their respective locations ;with same database name, tables and structure... Means both these servers has same Database and table Schema.

B-1 is functional since 2017. And hence we consider it as Master Server wherein we have designed hundreds of reports and provides the complete statistics and operational reports.

B-2 is recently setup and the problem is that here, we have very limited source of electricity infact no electricity supply at all. And hence we run the server + user PC's using Generators (for 8 hours continiously). The nature of the work in both these branches are the same.

My problem is that, It is highly impossible for me to merge these two servers due to Non VPN Availibily and I am having a very difficult time to retireive the reports from both these locations together.I want to transfer the newly inserted/edited data from the B-2 to the database of B-1 (Master Server). Due to security issues, we cannot go for Public IP either. The resource i have is only internet connectivity.

What I am planning is like uploading the newly added/edited data file from the B-2 server (2 tables) to FTP server and then I want to insert the contents of the file from the FTP Server into the B-1 Server and delete that file from FTP.

Hence I need:

1. Sql Script to write table result (newly added/edited) to a file (ex: abcFileName)
2. Sql script to insert data into a table from the above file(Ex: abcFileName).

Note: If i get the script for my above needs, then i will automate them via batch file or via .NET/C# application to Send the file (from B-2) and receive the file(at B-1).

Please note: The Source and Destination Table structures are 100% same and without any differences.

Please do not provide SSMS solution (as I am planning to automate my queries and without user assistance)

Thanks in Advance.

Since Arabic fonts weren't siaplying in the .txt file, I switched to .csv file. and this is my workaround query:

    DECLARE @ExportSQL nvarchar(max);
SET @ExportSQL = 'EXEC ..xp_cmdshell ''bcp "SELECT TOP 5 * from Transport.dbo.Test_Table " queryout "D:\results4.csv" -T -c -t -S ABDULLAH-PCNEW'''

Exec(@ExportSQL)

I get the below CSV file but with all the SQL table fields under one Excel column.

Appreciate all your comments/suggestions. Actually I would not prefer to call it as a Synchronize request.... Instead I can say like it is a Push and Read the effected data from one location to another (not vice versa).. Ex: Lets assume, B-1 Server has 100,000 rows already. B-2 Server has 5 rows in it.

Work Operation on Date: 02-10-2020 B-1 Server = Saves 10 new rows. B-2 Server = Saves 200 New Rows.

End of the Day I need as follows: B-1 Server = 100,210 rows B-2 Server = 205 Rows

Answer 1

Simple idea: use SQL SELECT to generate INSERT script, automate that with OSQL and other FTP tools and batch scripts.

Example SELECT:

select 'INSERT INTO TABLE_A(A, B) VALUES (' + CAST(A AS VARCHAR) + ', ' + CAST(B AS VARCHAR) + ')' from Table_A

it will give you results like:

INSERT INTO TABLE_A(A, B) VALUES (2, 3)
INSERT INTO TABLE_A(A, B) VALUES (5, 6)

execute that with OSQL and save results to file:

osql -S yourserver -d yourDB -Q "SET NOCOUNT ON ; select 'INSERT INTO TABLE_A(A, B) VALUES (' + CAST(A AS VARCHAR) + ', ' + CAST(A AS VARCHAR) + ')' from Table_A" -o d:\test.sql

After that transfer your file and execute it on your co-location.

Answer 2

The question isn't easy to answer in just a single SO answer. This functionality is more-or-less available out of the box, through replication. There are several types of replication some of which require a direct connection to apply changes directly from one server to another (eg Transactional replication). Others, like Merge and Snapshot replication collect and publish changes that can be applied to subscribers. Those changes can be stored in a folder or even an FTP site periodically.

Replication is a big subject. The Replication section in the documentation is almost an entire book. You could chechek the Tutorial: Configure replication between a server and mobile clients (merge) to see a step-by-step, screen-by-screen guide on how to configure a Merge replication that publishes only specific tables to a storage location that subscribers can read from.

Publishing through FTP was anticipated, in fact it's a common way for branch offices and mobile clients to get data without exposing the database server to the public Internet. The article Deliver a Snapshot Through FTP explains how to configure publishers and subscribers to work with snapshot and changes published to an FTP site.

The blog post SQL Server Merge Replication FTP: the ultimate guide to remote database synchronization describes both activities in one article.