Как правильно настроить транзакционную репликацию?
Изучаю 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 как-то донастроить нужно?