Synchronize SQL databases

Question

Synchronize SQL databases

TanaiSin

SSC Enthusiast

Points: 134
More actions
April 16, 2008 at 10:19 pm

#186562

Please can somebody help me with the following:
Our company has an enterprize site (a very large one). From time to time we need to make changes in it. In other words, we had to synchronize one database (local where we make all changes) and database on hosting. So, we did it manually (wrote scripts and inserted them in database on hosting). But as the databases grew it became impossible to synchronize everything manually. So we need some tool which could cope with this task. The problem is that, as I know, there are many tools which can do it, but we are tight in time and can't evaluate them all. Please share your ideas on what tool is worth evaluating?
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]I havn't work at such high level. My scope ends at programming. However I think, but not sure, this can be done through Replication. Search through google on Replication for more details. Correct me if I am wrong.

Mahesh[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

REDGATE SQL COMPARE

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

And now a word from our sponsors ...

😉

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

TanaiSin SSC Enthusiast Points: 134 More actions · Answer 4

Thank you for your suggestions.

We've evaluated Red Gate and Apex SQL Diff, but they represent visual tools. What we wanted is to embed such component in the site logic. In order, for example, to press one button and the synchronization is executed automatically.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

xSQLCompare has a command line utility that you could use to automate this process.

I think Red-Gate's software package does as well, but I cannot remember.

vivekanand-246309 SSC Veteran Points: 264 More actions · Answer 6

I work with VS DB PRO(Data Dude) for Schema Changes. That does the job..but it is not flexible for my environment(like validating the objects before you create or alter the schema)..If you are good at SMO(Sql Management Objects) use the scripter class to check through the objects and generate scripts.

Vivek

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

rbarryyoung (4/16/2008)
And now a word from our sponsors ...;)

Heh... didn't mean too push the sponsor... and I wouldn't have if I never used it. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TanaiSin SSC Enthusiast Points: 134 More actions · Answer 8

Thank you for suggestions.

What about Command line - it is good, but what make me upset is the fact that you should install a programm everywhere where you want to use Command Line. We want (if it possible) not to be dependable from the third party software. I.e. to create our own solution.

What about Red Gate - we've heard that we can use it as a component (to emplement it into our solution), but we are not sure if we can distribute this component inside our solution.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Well, if you really want to roll your own - how about Powershell and SMO. Yes, you still need to install both Powershell and SMO - but they are free.

Personally, I would not trust an automated tool that modifies my production system with just a click of a button (that anybody can click). After testing and validating all code changes and schema changes in DEV, the changes are applied to QA and vetted by the users. Once approved - they are then moved to live.

The final step (for me) is to use SQL Compare to validate that QA and Live have the same code and schema, just in case someone missed a step.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 10

Using a third party component or a third party command line utility will still make you dependant on installing some third party software.

I also agree that creating a button-click schema synchronization solution is pretty risky. Perhaps that is why all of the tools indicated come with somewhat complicated user interfaces that tell you exactly what will be synchronized and give lots of error reporting.

I think it is an important step to see what changes are being applied.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (4/17/2008)
rbarryyoung (4/16/2008)
And now a word from our sponsors ...;)
Heh... didn't mean too push the sponsor... and I wouldn't have if I never used it. :hehe:

Oh, I think it's completely fair. They are our sponsors, after all, I think that they deserve a plug just for that once in a while. And if folks can give sincere recommendations for their products, so much the better.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

KerryH SSChasing Mays Points: 632 More actions · Answer 12

We built a database re-sync between 2 servers (utilizing linked server) using a stored procedure. The procedure uses a cursor to walk through a list of tables and uses the SQL Server TableDiff utility to identify differences and then applies the differences the TableDiff utility identified. The TableDiff utility also identifies table structure differences. For this to work the tables need to have a primary key; the keys that use the identity property can cause issues in that one table must become the slave and not have an identity property. A similar process can be built using checksum.

TanaiSin SSC Enthusiast Points: 134 More actions · Answer 13

Thank you folks,

Well, we evaluated the products you suggested. Some of them are really cool.

We also found the following product: Database Restyle. This is a .net component which can be integrated into the application. And it seems as if it suits us.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

Glad you found a workable solution and didn't have to roll your own from scratch. For completeness, IIRC ApexSQL Diff does have a commandline interfact so you can automate it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service