CODEWITHSUNDEEP
sql-server-2008
Khizar
Khizar

Reputation: 329

Data sync between two SQL servers

I have same database schema on two different SQL servers 2008. I have one server installed locally and other is company,s live server. I have an application installed on local machine, My problem is that whenever I run my application data on both SQL servers (local and live) should sync to keep same database on both servers. My database contains 5 tables.

Thanks

Upvotes: 9

Views: 62000

Answers (3)

David Steele
David Steele

Reputation: 3461

There are a couple of ways to do this and the best choice will depend on

  1. Whether or not both SQL Servers are available all the time
  2. Whether 1 is master and one is just a copy
  3. Other things I can't think of at the moment as your question is a little vague

However, things to look at include

  1. SQL Server Replication - See Here
  2. Microsoft Sync Framework - See Here
  3. Log Shipping - See Here

Hopefully, that will give you some place to start looking.

Upvotes: 18

Vali Alexandrescu
Vali Alexandrescu

Reputation: 176

Late answer but can be useful to the visitors of the thread

There is no need to compare and synchronize data manually. For this purpose, you may want to use SQL data comparison tools such as SQL Server’s TableDiff (located in the COM folder of the SQL Server installation) or some 3rd party tool, to configure options for synchronization, map the tables and columns with different names, and create your own comparison keys in the GUI.

In this article, you will find out how to schedule the unattended process using a batch file, while only having to check SQL Server job history: http://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/

Upvotes: 5

Wolf
Wolf

Reputation: 6499

Because I'm use SQL Express so I must choose Microsoft Sync This example using Microsoft Sync Technology https://code.msdn.microsoft.com/Database-SyncSQL-Server-e97d1208

Example above include:

  • Synchronizing a server database scope (hosted in a SQL Server or SQL Server Express instance) with multiple client-side instances of SQL Server or SQL Express
  • Enabling synchronization over an n-tier model by using WCF as an endpoint

  • The new multi-scope change-tracking model on the server

  • Full initialization of the client side databases Configuration of SqlSyncProvider for both the server and client Enabling batched synchronization

  • Enabling batched synchronization

Upvotes: -2

Related Questions