How to push data from local DB to SqlServer online

Rameez Javed

5y837

Hi everyone, I want to push some data from my local machine to online database, Keep it in mind I've to push daily based data online for customers view. It may vary according to daily entries.

One thing to make sure there must not be any loss of row while insertion (want to use transaction (roll-back) for this system.)

Thanks in advance :)

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi5y ago
Accepted answer
While your question isn't that clear but you can use :

Git :
As a starting point, all database objects are scripted from within SSMS and saved to a local folder that will be used as a local repository. As soon as a database object change is verified locally (tested on a local copy of a database), it can be pushed to a remote repository, so the rest of the team can see the change and apply it against a working copy of a database.

SSIS :
This method assumes there is communication between an app-server (that has Visual Studio with SSDT tools installed) to server-A and the same app-server to server-B. You also need to create the destination database and the destination table in server-B as described earlier.

Linked Server :
If you need direct communication between SQL Servers then you can use the linked server feature to read/write data directly, but this would not allow SSIS's other processing features. Also this will not work on Azure SQL. There is a per table/view option that will work on Azure SQL (and newer on-prem SQL instances) in external data sources. Both these options require at least one of the SQL instances to have credentials for accessing the other, which may be undesirable depending on your situation and security model.

PowerShell and dbatools-io to Copy a SQL Server Table to Another Server :

This method assumes there is communication between an app-server to server-A and the same app-server to server-B, and also requires you to have installed dbatools.io on the app-server, here is a link that will help you with the installation. You also need to create the destination database and the destination table on server-B as described earlier.
Rameez Javed5y ago
Thanks everyone for your kind concern.
Sachin Singh5y ago
Linked server is a good option , apart from that.
in my case i have created an Insert Audit which gives me all the rows which are inserted from the last time and then i use my web api to insert those newly added rows to live database.
step 1.Enable Audit
USE master
GO
ALTER DATABASE [DBName]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

USE myDb
GO
ALTER TABLE tableName
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
step 2. Get Inserted rows
SELECT CT.SYS_CHANGE_VERSION,
  CT.SYS_CHANGE_OPERATION, a.*
  FROM CHANGETABLE
(CHANGES [TableName],0) as CT
JOIN [dbo].[TableName] EM
ON CT.ID = a.Emp_ID
ORDER BY SYS_CHANGE_VERSION
step 3. Push all these rows to live database using web api/ web service anything there are number of ways.
Kiran Mohanty5y ago
Hi,
You can develop windows service, which will look at you database in 5 minutes interval or so. In database table keep a flag to identify whether the data is copied to the customer DB or not.

Use windows service to copy data from local DB to customer DB and to update local database flag.