How can I synchronize MS SQL Server databases? Is there any tool, preferably open source?
-
Which database server ? MSSQL Server ? What do you want to accomplish ? Do both database servers need to be in sync, or can one lag behind the other ? How far apart are they ? – Dave Cheney Feb 23 '10 at 12:55
-
Lag behind sync would not problem. If it do both database server sync will be great. – Rajesh Feb 23 '10 at 13:07
5 Answers
For scheduled and ad hoc database synchronization you can use ApexSQL Diff (for object schema synchronization) and ApexSQL Data Diff (for record synchronization)
They cannot be used for real time synchronization. IMO, it's best to use database mirroring for real-time
![](../../users/profiles/134678.webp)
- 169
- 1
- 1
As others have said, there are lots of built-in ways in SQL Server 2008 to make two servers synchronize, real-time or on a schedule.
Some points you should take into account:
- How "far" are the two servers (i.e. how much bandwidth and latency between them)?
- How big are the two databases?
- How often and how much do data change?
- Do data actually change, or are only new data added to the existing?
Depending on the answers, you can choose the method most suited for your needs; other than the ones which were already described (replication and mirroring), remember you can also do some other things:
- Log Shipping
- Export/import data using Integration Services.
- Backup/restore.
- Detach, make a physical copy, attach.
If I understand you right you want to have a copy of the database from the web server on your local machine. The changes in the database on the web server should be periodically copied to your local server.
I believe you can do that with replication but you need at least a Workgroup edition of SQL server on your web server.
If your database is not large or doesn't change frequently you can use Snapshot replication. SQL Server will periodically take snapshot of the database and replicate it to your local server.
For bigger or frequently changed databases I would suggest you to use merge replication. It is less time and resource-intensive.
You can get more information about replication in Books Online. Take a look also at Data Movement Patterns.
![](../../users/profiles/3124.webp)
- 123
- 4
-
I have a remote server on which running webistes using Db server MSSQL2008 , I want to mirror database in my local machine also has MSSQL2008. I want to sync both MSSQL server in an interval, also I would like give Database's name as a choice to sync. – Rajesh Feb 23 '10 at 13:35
-
I have a blog post describing how to set up database mirroring for non-domain servers. That does not however allow you to have both SQL Servers active at the same time, to do that you would need to do some form of replication. Depending on how the data is accessed, if it's all read then it's not too bad, if it's read on one server and read/write on the other, again not too bad, if it's write/write then you are going to be in for some "interesting" times.
![](../../users/profiles/274176.webp)
- 43,252
- 2
- 75
- 122
![](../../users/profiles/4718.webp)
- 852
- 1
- 8
- 27