Как правильно настроить транзакционную репликацию?

Изучаю sql-server. Поставил две ноды. WINSQL1 (distributor/publisher) и WINSQL1/SUBSCRIBER Хочу с pulisher на subscriber среплицировать базу данных testdb. Первый сервер настраивал так:


-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = @@SERVERNAME;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = @@SERVERNAME;
-- Specify the replication working directory.
SET @directory = N'\\' + @distributor + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'dbo.testtable'; 

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB, 
    @security_mode = 1;
GO

DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = @@ServerName;

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher, 
    @distribution_db=@distributionDB, 
    @security_mode = 1;
GO 

-- chack if pub push allowed

use testdb
EXEC sp_helppublication
GO

-- Allow db for publication

exec sp_replicationdboption 
@dbname = N'testdb', 
@optname = N'publish', 
@value = N'true';


-- Create a publication

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'testdb'; 
SET @publication = N'testdb_pub'; 
-- Windows account used to run the Log Reader and Snapshot Agents.

-- Enable transactional or snapshot replication on the publication database.
EXEC sp_replicationdboption 
    @dbname=@publicationDB, 
    @optname=N'publish',
    @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job. 
EXEC sp_addlogreader_agent 
    @job_login = @login, 
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;

-- Create a new transactional publication with the required properties. 
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_push = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true';

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @job_login = @login, 
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;
GO


-- There should be at least one 'article' in publication. Articles can be only tables with primary keys or objects

Use testdb

DROP Table testtable
CREATE TABLE testtable
   (
      TransactionID int IDENTITY (1,1) NOT NULL
      , CONSTRAINT PK_TransactionHistoryArchive1_TransactionID PRIMARY KEY CLUSTERED (TransactionID),
      testcolumn int 
   )






INSERT into testtable([testcolumn]) values(1)
INSERT into testtable ([testcolumn])values(2)
INSERT into testtable ([testcolumn]) values(3)

select * from testtable

-- Add an article to publication

exec sp_addarticle
@publication = N'testdb_pub',
@article = N'testtable',
@source_object = N'testtable'

--Add a push subscription to a transactional publication.

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'testdb_pub';
SET @subscriber = 'WINSQL1\SUBSRIBER';
SET @subscriptionDB = N'testdb';




USE [testdb]



EXEC sp_addsubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @destination_db = @subscriptionDB, 
  @subscription_type = N'push';
 

--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB
GO

Insert into testtable ([testcolumn]) values (4)

exec sp_helppublication 

@publication = N'testdb_pub' 

Во втором просто создал пустую базу данных testdb чтобы было куда слать данные. Но после выполнения этого запроса в базе данных на втором сервере таблицы не появилось. Что я сделал не так? Может быть subscriber как-то донастроить нужно?


Ответы (0 шт):