up vote 0 down vote favorite
share [g+] share [fb]

How can I synchronize MS SQL Server databases? Is there any tool, preferably open source?

link|improve this question
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
feedback

4 Answers

up vote 2 down vote

Have you looked at the built in mirroring functions? Technet has a brief description here. The primary server sends its transaction log records to a secondary server, which then replays the transaction log.

Mirroring can be set up synchronously or asynchronously.

link|improve this answer
feedback
up vote 2 down vote

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.

link|improve this answer
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've added more details to my answer above – Branko Feb 23 '10 at 15:17
feedback
up vote 2 down vote

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:

  • Export/import data using Integration Services.
  • Backup/restore.
  • Detach, make a physical copy, attach.
link|improve this answer
feedback
up vote 0 down vote

http://danmacs.posterous.com/configuration-of-database-mirroring-for-non-d is a link to my blog 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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.