?
Solved

MS SQL Sync Two DBs

Posted on 2010-01-10
15
Medium Priority
?
451 Views
Last Modified: 2012-05-08
Hi Experts,

I need affordable 3rd party software that can sync two MS SQL dbs.
I can do a search in google but would rather like to hear from people that have 1st hand experience and can suggest good affordable tools.

Thanks.
0
Comment
Question by:Marius0188
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26280675
If you need to sync schema's(DDL's), then go for these tools

1. Apex SQL Diff (http://www.apexsql.com/sql_tools_diff.asp)
2. Red Gate Compare(http://www.red-gate.com/products/SQL_Compare/index.htm)

If you need to sync data across two databases, then go for

1. Apex SQL Data Diff (http://www.apexsql.com/sql_tools_datadiff.asp)
2. Red Gate Data Compare(http://www.red-gate.com/products/SQL_Data_Compare/index.htm)
0
 
LVL 11

Expert Comment

by:ashraf882
ID: 26280726
You can try VS2008's native schema and data comparator.
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 26290665
I suggest SQL Delta...

http://www.sqldelta.com/

I using this tool from past 2 years... very good one...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Marius0188
ID: 26308327
I need to actually synchronize two dbs data.
Data will be captured on a remote pc with no connectivity but in the meantime data is also been captured at the office.

When the remote computer is connected, every no and then, to the main office then the two dbs should synchronize their data.
Both ways...

Thanks for comments so far. Checking those out.
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 1000 total points
ID: 26311321
I think you need to setup merge replication

SQL Server 2005 - Merge Replication Step by Step Procedure
http://www.codeproject.com/KB/database/sql2005-replication.aspx
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26313658
>> When the remote computer is connected, every no and then, to the main office then the two dbs should synchronize their data.

Then you need to go for Merge Replication only and the link provided by Rimvis should help you in configuring that..
0
 

Author Comment

by:Marius0188
ID: 26338738
Mmmh, I dont see the link for merge replication.
Please repost it.

thanks all
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 26338806
http://www.codeproject.com/KB/database/sql2005-replication.aspx

Or just  google "SQL Server 2005 Merge Replication"
0
 
LVL 14

Assisted Solution

by:Jagdish Devaku
Jagdish Devaku earned 1000 total points
ID: 26339166
0
 

Author Comment

by:Marius0188
ID: 26406572
To what extreme can one automate the SQL Merge Replication?
And those of you who have set this up, how complicated is it and what is a reasonable time to quote on?

Thanks to all so far.
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 26406655
Once setup, replication should work sutamatically. At least if you have stable database structure. If you ann new tables to database, you will have to manually add them to replication. SQL Server replicates only data. You cannot transfer other database objects (stored procedures, UDFs etc.).
We have  a few merge publications in our network and there are no problems with them. If you have a skill to manage SQL server, you shouold be able to setup replication.
What do you mean by "reasonable time"? Data transfer latency? Of course it depends on you network and data amount. But usually updates are transferred in about 1-2 minutes.
0
 

Author Comment

by:Marius0188
ID: 26415415
OK, just explain how does the replication and migration happens automatically?

And then how long should it take me (hours) to set up replication migration on SQL 2005?
Thanks!
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 26415559
Once replication is setup, Merge Agent is created. It could be either on data publisher (push subscription) or data subscriber (poll subscription). Agent periodically (every minute by default) detects data changes and replicates them to other servers.

How long this would take, depends on many things. Do you have much expierience with SQL Server? How big is your database? How many tables? Will your network configuration permit remote servers to connect to your main office?

0
 

Author Comment

by:Marius0188
ID: 26425138
Thanks.

Ok in regards to the second question.
I've 2 years solid MS SQL experience, but T-SQL for ages...
Though I've never worked with replication and migration...
The database is really not that big. Maybe few 100MB's.

Mmmh, your questions about the remote server:
I am not going to connect to the main db via remotely.
But instead the remote users is going to work on a local version of the db and once they get back to the office and connects to the office network, their local version of the db should be synchronized with the main db hosted on site and office. Obviously this should be a two way process, main db should be updated and the local version should be updated. Will this still work for me? I mean the replication and migration of MS SQL? I need to make 100% sure. :)

Thank for all the help so far.
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 26425481
YOU don't have to connect to main DB. I mean that you should have an instance of SQL Server Express Edition at each computer. As Express Edition doesn't have SQL Server Agent, Merge Agent should run on main server (you will have push subscription). In order to merge data, Merge Agent must connect to the instance of SQL Server Express, hosting you local database. This solution is easiest to setup, but it has a drawback. Main instance will have to periodically attempt to connect to local database, as it doesn't know, when exactly database will be available. You should be able to setup publication ina an hour + 15 minutes for every local database (assuming SQL Server Express is allready installed).

Alternativelly, you can implement pull subscription from local database with Windows Synchronization Manager
http://msdn.microsoft.com/en-us/library/ms151863%28SQL.90%29.aspx
User will have to manually launch synchronization process, when computer is conneccted to main office. But I haven't tried this myself, so I can't tell will there be any problems with it.

You might want to read this regardinng merge replication on SQL Server Express:
http://www.databasejournal.com/features/mssql/article.php/3725816/SQL-Server-2005-Express-Edition---Part-16---Transactional-and-Merge-Replication.htm

0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question