It is similar to the Transactional Replication that works with every transaction. Here, both Publisher and Subscribers can update the existing data. Creating Publisher The steps to create publisher are given bellow: Step1: Connect to the server with actual server name. Click next Step4: Select the database that contains database objects for publications. Step5: Choose the appropriate publication type according to requirements.
Some key points of transactional replication with updatable subscriptions are given bellow: Configuring Distribution At first we need to configure a distributor database which manages the total replication process.
@publisher_security_mode = 1; -- Create a transactional publication that supports immediate updating, -- queued updating, and pull subscriptions.
EXEC sp_addpublication @publication = @publication, @status = N'active', @allow_sync_tran = N'true', @allow_queued_tran = N'true', @allow_pull = N'true', @independent_agent = N'true', -- Explicitly declare the related default properties @conflict_policy = N'pub wins'; -- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_replicationdboption @[email protected] DB, @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.
That mean need to create a link server for the Publisher.
We can create a link server before or we can create it now.
Expand local publications node and subscriber will be displayed.In order to create new link server select “Create a linked server that connects using SQL Server Authentication” and provides the credentials.For existing link server select “Use as linked server or remote server that you have already defined” Click next.This feature will be removed in a future version of Microsoft SQL Server.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.