Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Can replication be cascaded?

Author  Topic 

TestEngineer
Starting Member

29 Posts

Posted - 2006-01-26 : 09:59:20
Please bear with me because I'm a test engineer and not an IS professional. I'm setting up an engineering traceability database system for use by our end-of-line test systems, label generation software, and packout software. We have multiple facilities, including a couple in Mexico. I know we can set up replication on this database structure between this facility and the other facilities in the company by setting up the server here as a publisher and the others as subscribers.

We have frequent network issues in our Mexican facilities, without 24 hour IS support. Consequently, I want to set up small workgroup cells of test systems that interact with a "cell" database server for each cell. I'd like to maintain repair traceability within a cell should the facility network goes down/connection lost with the facility network server. I'd also like to maintain and continue to track information at the facility level should there be a issue with Internet connectivity for a prolonged period of time.

Finally my question: Can we setup a Facility A Publisher, with a Facility B Subscriber. Then have the same Facility B Subscriber act as a Publisher to Cell 1 Subscriber?

In outline form:
Facility A (Publisher)
-Facility B (Subscriber)
-Facility C (Subcriber)
-Facility N...

Facility B (Publisher) (same db as Facility B Subscriber above)
- Cell 1 (Subscriber)
- Cell 2 (Subscirber)
- Cell n (Subscriber)

Facility C (Publisher) (same db as Facility C Subscriber above)
- Cell 1 (Subscriber)
- Cell 2 (Subscirber)
- Cell n (Subscriber)

Facility N...

Would this be possible using MS SQL Server 2000 for the facility level databases and MS SQL Server Express 2005 on the Cell-level databases? If not, will it work if we use MSDE or full-blown Server 2000 on the cells?

Thanks in advance for any information.

mblythe
Starting Member

16 Posts

Posted - 2006-01-27 : 11:49:44
Yes you can do this. We call it republishing. Republishing data involves the following steps:

1. Create a publication at the Publisher (A).
2. Create a subscription to the publication for the republishing Subscribers (B & C).
3. Initialize the subscriptions. The subscriptions must be initialized before the publication is created at the republishing Subscriber, or replication will fail.
4. Create a publication in the subscription database at each republishing Subscriber (B & C).
5. Create subscriptions to the publication at the republishing Subscriber for the other Subscribers (Cells 1 & 2).
6. Initialize the subscriptions.

You can do this with merge or transactional replication, but merge is required if you need changes at Subscribers to flow back to the Publisher. If you use merge replication in a republishing topology, all republishing Subscribers must use global subscriptions (@subscriber_type param of sp_addmergesubscription or sp_addmergepullsubscription).

As far as versions, keep the following in mind:

* MSDE can be a Publisher for merge, but not for transactional.
* SQL Express cannot be a Publisher at all. You *can* replicate changes from Express to another version if you have a merge Subscriber configured on the Express instance.
* A Subscriber to a merge publication cannot be a more recent version than the Publisher.
* A read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version.

The consequence of all this is that you could use merge replication with MSDE at B,C,1,2, etc. or you could use transactional replication with a full version of 2000 at A,B,C and MSDE or Express at 1 & 2. The ultimate choice will depend on app and perf requirements.

Michael Blythe
Technical Writer
SQL Server Replication - Microsoft
---------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Go to Top of Page
   

- Advertisement -