SQL Server Hub Subscription model (multi-release single subscription)

Original: SQL Server Hub Subscription model (multi-release single subscription)

Most SQL Server replication topologies are based on the central publishing model, which is replicated from one publication to one or more subscriptions. Another replication model is the central subscription model, which uses transactional replication to replicate from multiple publications to a single subscription.

The hub subscription model is used to merge data from multiple data sources, such as:

A central server that aggregates inventory from multiple data warehouses to corporate headquarters;

"The same company from the continuous transmission of remote office data to the central Office system;

The consolidated order information is centrally processed in the same place.

By default, a subscription is initialized by the Snapshot Agent to generate a snapshot and is applied by the Distribution Agent. When a snapshot is applied, the default item (article) property "The action if name is already being used (action if name is on use)"Set to "Delete first object and recreate (drop existing object and create a new one)", meaning that when a target table object exists in the subscription, it is deleted and the object is recreated. In the hub subscription model, this setting is problematic when you apply a snapshot from multiple publications. The first snapshot application is normal, and subsequent snapshot applications will erase the data from the previous snapshot.

The workaround for this problem is to have a horizontal partition, set a static row filter, set the property " when the name is already in use " as " delete data if the target has a row filter, delete only the data that matches the filter (delete data,if article has a Row filter, delete only data, matches the filter)"

Now, I'm merging into another database using 2 of the library data:

"Set horizontal partition Flag"

By default, the table structure is as follows:

CREATE TABLE [dbo]. [Demotab] ([ID] [int] IDENTITY (max) not null,[name] [varchar] (TEN) not null,[value] [decimal] (4) null,constraint [Pk_demotab] PRI MARY KEY CLUSTERED ([id] ASC)) GO

If so, the ID self increment must have duplicate keys, so add a flag column [LocationID] and as the ID column as the key combination. As below, create tables in two published data tables, respectively.

--Database: [Testdbsuba]use [Testdbsuba]gocreate TABLE [dbo]. [Demotab] ([ID] [int] IDENTITY (max) not null,[name] [varchar] (TEN) not null,[value] [decimal] (4) Null,[locationid] Int. not NULL D Efault (1) CONSTRAINT [Pk_demotab] PRIMARY KEY CLUSTERED ([id] asc,[locationid] ASC)) go--database: [Testdbsubb]use] [Testdbsubb ]gocreate TABLE [dbo]. [Demotab] ([ID] [int] IDENTITY (max) not null,[name] [varchar] (TEN) not null,[value] [decimal] (4) Null,[locationid] Int. not NULL D Efault (2) CONSTRAINT [Pk_demotab] PRIMARY KEY CLUSTERED ([id] asc,[locationid] ASC)) GO

At this point, you can use the field [LocationID] to determine the data of different databases, there will be no primary key duplication problem in the same subscription table. But how do you differentiate data from different publication databases when one of the publications is initialized, or when you manipulate the data? Next, it's important to set up a static row filter!

"Set Static row Filters"

You can now create a publication in a database [Testdbsuba] or [Testdbsubb] server, using transactional replication , choosing which tables you want to publish, such as:

Next, set the row filter to [Locationid]=1 (this is the publication of the database [Testdbsuba]), and set the release of [Testdbsubb] in the same vein!

The next step is complete. There is one more important setting that publishes the properties of the Table object. In fact, in the previous step in the operation can also be set.

"Set application properties for table articles"

Right-click just create a completed publication, select Properties, select an item, and optionally set a table or all tables.

Select Set all tables, set property " action when name is already in use " to "delete data, if Target has row filter, delete only data matching that filter (delete data,if article has a row filter, delete Only data that matches the filter)"

After Setup is complete, make sure to launch!

Similarly set up [Testdbsubb] release! (Note [LocationID] = 2)

Subscribe to normal settings, two publications are the same database.

Successively initialize the application snapshot separately, normal!

Now test to insert the data in the two publication databases, respectively.

INSERT into [Testdbsuba]. [dbo]. [Demotab] ([Name],[value]) SELECT ' Testdbsuba ', 0INSERT into [Testdbsubb]. [dbo]. [Demotab] ([Name],[value]) SELECT ' Testdbsubb ', 0SELECT * from [TestDB]. [dbo]. [Demotab]

The results are merged!! All DML operations are synchronized properly! The data can be synchronized with normal operation, but the fields and additions Gerze have a problem!!

SQL Server Hub Subscription model (multi-release single subscription)