CODEWITHSUNDEEP
c#sql.netsql-serverdatabase-migration
Custos
Custos

Reputation: 13

Moving data between similar databases

I have 2 SQL Server databases with almost identical structure. I have to regularly move data and only data from one to the other. The solutions found deal mostly with recreating the databases, I only want to move slightly modified data. The tables affected need to be configurable and the two databases are on different machines.

So: get data every minute or so, insert it into another one with very light, predefined changes and delete the original records.

What is the best solution in this situation for .NET and/or SQL? Thanks.

Upvotes: 1

Views: 202

Answers (3)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

A SSIS package can be created to do the following in a restart-able manor.

1 - Mark the data you want to copy from Server A.

2 - Copy the data from Server A to Server B.

3 - Delete the marked data from Server A.

Nice thing about this solution is that the data sources can be virtually anything ODBC compliant.

I have written a Data Scoop TSQL program in the past that moved data from one database to another on the same server. It was scheduled as a job to run every 5 minutes.

It used the OUTPUT clause to make the transaction one unit. I also did it in a while loop using the TOP(N) clause so that the log file would not grow.

-- Delete a row and data to audit table
DELETE FROM [Person].[CountryByContinent]
  OUTPUT 'DELETE RECORD', DELETED.Country, DELETED.Continent
  INTO [Stage].[Audit] ([Action], [Country], [Continent])
WHERE [Country] = 'Chillis Grill & Bar'; 
GO

If it is on another server, you can always use a linked server. However, they are know to be slow sometimes given the LAN/WAN transit.

Good luck in your endeavor.

Upvotes: 1

Joe Ratzer
Joe Ratzer

Reputation: 18549

I would use SQL Server Replication. Using the in-built replication will prevent anyone reinventing the wheel.

Upvotes: 4

Daniel P
Daniel P

Reputation: 150

May be you need a mirrored database. here there are a link that could you interest: http://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx

Another solution is more rudimentary, every table in your database should have a Timestamp field, then, through a .net application you select the rows depending of the timestamp and insert to to the other database.

Upvotes: 1

Related Questions