Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 11th, 2004, 10:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default MS SQL Server Replication

Hi all,
Im not even sure if it is replication that I require or Mirroring.
I have 6 databases in different states that are required to be kept in sync.

There is currently a sync process(VB6) being run intermittantly to keep all the servers in sync.

Can Replication achieving this, Or Mirroring.

I would be very appreciative of any advise or directions to articles that could achieve this.

BTW, it is only one DB on each server that can be synced, other DB's must be untouched.

Thanks in advance for any info.


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
__________________
======================================
"They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad."
--Shakespeare
======================================
 
Old August 12th, 2004, 01:13 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Books Online has some good information on Synchronizing Data in SQL 2000.

Hope it helps

Synchronizing Data

Synchronizing data refers to the process of data being propagated between Publisher and Subscribers after the initial snapshot has been applied at the Subscriber. When a subscription is synchronized, different processes occur depending on the type of replication you are using and whether the subscription has been marked for reinitialization.

For snapshot replication, synchronize means to reapply the snapshot at the Subscriber so that schema and data at the subscription database is consistent with the publication database. For transactional replication, synchronizing data means that data updates, inserts, deletes, and other modifications are distributed between Publisher and Subscribers. For merge replication, synchronization means that data updates made at multiple sites are merged, conflicts (if any) are detected and resolved, and data eventually converges to the same values.

The Distribution Agent and the Merge Agent move changes to data that occur at the Publisher or at Subscribers. For consistency, Microsoft® SQL Server™ 2000 replication uses the term synchronize to refer to when one of these replication agent runs.

Snapshot Replication Synchronization
When a subscription to a snapshot publication is synchronized, the Distribution Agent (using distrib.exe or the Distribution ActiveX® Control) runs and the most recent snapshot will be applied at the Subscriber. If modifications to data have been made, a new snapshot will need to be generated before the new data can be applied to the Subscriber.

Transactional Replication Synchronization
When a subscription to a transactional publication is synchronized, the Distribution Agent (using distrib.exe or the Distribution ActiveX Control) runs and UPDATE, INSERT and DELETE statements that have been logged at the Distributor are propagated to the Subscriber.

If the subscription has been marked for reinitialization, the Snapshot Agent and Distribution Agent must run so that a new snapshot is generated and propagated to Subscribers.

Merge Replication Synchronization
Synchronization occurs when Publishers and Subscribers in a merge replication topology reconnect using the Merge Agent (replmerg.exe or the Merge ActiveX Control) and updates are propagated between sites, and if necessary, conflicts detected and resolved. At the time of synchronization, the Merge Agent sends all changed data to the other sites. Data flows from the originator of the change to the sites that need to be updated or synchronized.

At the destination database, updates propagated from other sites are merged with existing values according to extensible and flexible conflict detection and resolution. A Merge Agent evaluates the arriving and current data values, and any conflicts between new and old values are resolved automatically based on the default resolver (a resolver you specified when creating the publication or a custom resolver).

Changed data values are replicated to other sites and converged with changes made at those sites only when synchronization occurs. Synchronizations can occur minutes, days, or even weeks apart. Data is converged and all sites eventually end up with the same data values. However, if conflicts were detected and resolved, it means that work that was committed by some users was altered or undone to resolve the conflict according to your defined policies.

Synchronizing Schema Changes
Microsoft® SQL Server™ 2000 supports limited schema changes to an existing publication database. You can add columns to and drop columns from a published table without dropping and re-creating the publications and subscriptions referencing that table.

Replication of schema changes is supported for snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table.

For more information, see Schema Changes on Publication Databases.

On Demand Script Execution
On demand script execution allows you to post a SQL script, and then during the distribution or merge process, the script can be executed at all Subscribers to a specific publication.

On demand script execution is available for snapshot replication, transactional replication, and merge replication.

To specify a script to run for all Subscribers to a snapshot, transactional or merge publication, execute sp_addscriptexec. The next time the Distribution Agent or Merge Agent runs, the script will execute at each Subscriber.

The following parameters need to be specified when executing sp_addscriptexec.

Parameter Data Type Description
@publication sysname Specifies a valid publication. Required. No default.
@scriptfile nvarchar(8000) Specifies the UNC path where the SQL script is located. Required. No default.


On demand script execution copies the script to the replication working directory and then uses osql.exe to apply the script at the Subscriber. If there is a failure when applying the script for snapshot or transactional publications, the Distribution Agent will stop. The sp_addscriptexec system stored procedure has an additional parameter, @SkipError, to specify whether the Distribution Agent should stop if an error is encountered (@SkipError = 0) or if the error should be logged and the Distribution Agent should continue (@SkipError = 1).

To synchronize a push or pull subscription

Enterprise Manager

How to synchronize a subscription (Enterprise Manager)
To synchronize a push subscription

At the Publisher, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, expand the Publications folder, and then click the publication for which subscriptions need to be synchronized.


Right-click the subscription you want to synchronize, and then click Start Synchronizing.
To synchronize a pull or anonymous subscription

At the Subscriber, open SQL Server Enterprise Manager, expand a server group, expand the Replication folder, and then click the Subscriptions folder.


Right-click the subscription you want to synchronize, and then click Start Synchronizing.


To synchronize an anonymous subscription

Windows

How to synchronize an anonymous subscription (Windows Synchronization Manager)
To synchronize an anonymous subscription

On the Start menu, point to Programs, point to Accessories, and then click Synchronize.


Click the subscription that you want to synchronize, and then click Synchronize.


To view and resolve merge synchronization conflicts

Enterprise Manager

How to view and resolve merge synchronization conflicts (Enterprise Manager)
To view and further resolve synchronization conflicts

Expand a server group, and then expand a server.


Expand Databases, and then expand the name of the database.


Expand Publications, right-click the publication, and then click View Conflicts.


In the Publications in database list, select the publication to view.


In the Tables with conflicts list, select the table of conflicts to view.


Note Be sure to connect to the correct server to view the conflicts. The location of the conflict table varies depending upon whether replication has been configured for centralized of decentralize logging of conflicts. If centralized, the conflict table is stored at the Publisher and you must connect to the Publisher to view the conflicts. If decentralized, the conflict table is stored at either the Publisher or Subscriber, depending upon which one lost the conflicts.
 
Old August 12th, 2004, 02:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Thanks Jemacc, I'll do a little playing arround with this and see how I go.

Thanks again.


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Naming convention for Replication darrenb SQL Server 2005 3 May 3rd, 2008 02:50 PM
Cool new SQL Server Replication Monitoring product jkatsos SQL Server 2005 0 April 20th, 2006 09:46 PM
Cool new SQL Server Replication Monitoring product jkatsos SQL Server 2000 0 April 20th, 2006 09:44 PM
SQL Server Transactional Replication with Triggers RRNP SQL Language 0 October 4th, 2004 05:18 PM
SQL Server Transactional Replication with Triggers RRNP SQL Language 0 October 4th, 2004 05:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.