Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 9 December 2015

Transactional Replication with Updatable Subscriptions

Transactional Replication with Updatable Subscriptions






Types of Replication:
1)Snapshot -------------------------------------------------------------Very Rarely Data changes
2)Transactional---------------------------------------------------------Data Changes Frequently
3)Transactional with updatable Subscriptions----------------both pub and sub can modify
4)Merge-------------------------------------------------Publishers and Subscripers can modify when connected

It is similar to the Transactional Replication that works with every transaction. It has some extra features. Here, both Publisher and Subscribers can update the existing data. It uses MSDTC to maintain the changes. It is so fast and its latency is low. This article explains how to configure Transactional Replication with Updatable Subscriptions in SQL Server 2008 R2?
Before start we need to learn some prerequisite knowledge about SQL server replication and transactional replication with updatable subscriptions. Some key points of transactional replication with updatable subscriptions are given bellow:
  • Primary key is mandatory for every table.
  • It is mandatory to active and running MSDTC.
  • Need to create a link server for every subscriber to publisher.
  • An auto generated unique identifier column is added to subscribers table.
Configuration of Transactional Replication with Updatable Subscriptions
To configure transactional replication with updatable subscriptions three steps are required:
  1. Configuring Distribution
  2. Creating Publisher
  3. Creating Subscriber
Configuring Distribution
At first we need to configure a distributor database which manages the total replication process.
Creating Publisher
The steps to create publisher are given bellow:
Step1:
Connect to the server with actual server name.
Step2:
Expand the Replication folder and right click on Local Publications. Select New Publication.
Snapshot replicaion
Step3:
New Publication Wizard window will be open. Click next
New-Publication-Wizard
Step4:
Select the database that contains database objects for publications. For example select “TestDB” and click next.
Publication database
Step5:
Choose the appropriate publication type according to requirements. For now select Transactional publication with updatable subscriptions.
publication-type
Step6:
Select the tables or others object to publish. We can select tables, stored procedures, views, and functions according to our choice. Here we will select only tables. Click next.
Remember that Primary key columns are mandatory for all tables in transactional replications. We can’t select tables which does not have a primary key column. If we try to select, it will provides warning message.
publication-article
Step7:
A unique identifier column will be added to all the tables of publisher. It is used to track the changes. It is recommended and don’t delete this column from the table. Unless replication will not works.
Article-Issues
Step8:
We can set filters to exclude unwanted rows. If required please add. Otherwise click next.
Filter-table-rows
Step9:
Specify the time when Snapshot Agent will run. Select “Create a snapshot immediately and keep the snapshot available to initialize subscriptions” and click next.
Also we can select a schedule to run the snapshot agent. We can modify it any time from the jobs.
Snapshot agent
Step10:
Need to provide agent security. Specify the account under which the agent will run. Click Security Settings tab.
Agent-Security
Stept11:
Under the Queue Reader Agent section, Click Security Settings tab.
Queue-Reader
Stept12:
Select “Run under the SQL Server Agent service account”. Click Ok.
Stept13:
Set the domain/machine account under which the snapshot agent process will run.
Select “Run under the SQL Server Agent service account”
Select “Using the following SQL Server login” and provides the credentials.
Click Ok.
snapshot-agent-security
Step14:
Click next.
Step15:
Click next.
Step16:
Give a suitable name to the publisher and click finish.
Complete-the-wizard
Step17:
If every thing is ok publication creation will be completed.
Creating-Publication
Creating Subscriber
After the creation of publisher the next step is to create the subscriber for it. The steps to create subscriber are given bellow:
Step1:
Right click on created publisher and select New Subscriptions.
Subscriptions-transactional
Step2:
New Subscription Wizard window will be open. Click next
New-Subscription-Wizard
Step3:
Click next.
New-Subscription-Wizard-transactional
Step4:
Choose distribution agent location. Select the first one and click next.
Distribution-Agent-Locatio
Step5:
Choose subscribers and specify each subscriptions database. We can use same or remote SQL server as subscriber. We can set multiple subscribers for one publisher. In the subscriber list the default server is local. If we want to use different server, we need to click Add Subscriber and provides the credentials. Remember that for connection, the actual server name is mandatory. We need to create subscriber database. We can do that either restoring the publisher database or create new database. If we restore the database, then the database name will appear in the dropdown. Here we will use remote server (SDEV). After that, click next.
Subscribers
Step6:
Provide the process account and connection options for each distribution agent. Click the button highlighted in the bellow image.
distribution-agent-security
Step7:
Provide distribution agent security. Specify the domain/machine account under which distribution agent will run.
Select “Run under the SQL Server Agent service account”
Provides the credential of SQL server
Click ok
distribution-agent-security
Step8:
Click next.
Step9:
Specify synchronization agent schedule as Run continuously and click next.
synchronization-schedule
Step10:
Specify initialize subscriptions “Simultaneously commit changes” and click next.
Updatable-subscriptions
Step11:
Specify the login that will be used to connect to the Publisher when the replicated data is change. That mean need to create a link server for the Publisher. We can create a link server before or we can create it now.
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.
Login-For-Updatable-Subscriptions
Step12:
Choose “Create the subscription” and click next.
Wizard-Actions
Step13:
Click finish. If everything is ok subscription creation will be completed. Expand local publications node and subscriber will be displayed. If we log on to subscriber database we will find the publisher.
Transactional-Replication
Creating-Subscriptions-finished
In this way we can create transactional replication with updatable subscriptions in SQL Server 2008 R2.
MSDTC Distributed Coordinator:
  • Go to Administrative Tools -> Component Services
  • Expand Component Services -> Computers ->
  • Right-click -> Properties -> MSDTC tab
  • Hit the Security Configuration button and choose Start Services

Server ‘SERVERNAME1′ is not configured for RPC for a Linked Server:

    Once in while I am asked to troubleshoot a SQL Server database where my only connection is though a linked server.  Because this database server is on protected network, I don’t have port 1433 open to connect the instance with SQL Server Management Studio.

There are a couple of commands I like to run to check the health of the database.
First, already knowing that the database is running, I like to look at the error log with the xp_readerrorlog extended stored procedure.
When I run a simple command to view the error log from my linked server:
exec SERVERNAME1.master.sys.xp_readerrorlog
Note: replace SERVERNAME1 with the name of your linked server
I get the following error:
Msg 7411, Level 16, State 1, Line 1
Server ‘SERVERNAME1′ is not configured for RPC.
This means that when I set up my linked server, I disable RPC commands to and from running for security reasons.
To re-enable the RCP commands for the linked server:
exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc’, @optvalue=’true’
exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc out’, @optvalue=’true’
Note: again replace SERVERNAME1 with the name of your linked server
————————————————————————————–
Once RPC is enabled, there are a couple of other command I like to run to find out the health of the SQL instance & its databases.
– This extended stored procedure displays operating system information regarding local attached disks:
exec SERVERNAME1.master.dbo.xp_fixeddrives
– This stored procedure displays the size of the database and how that space is currently allocated.
exec SERVERNAME1.mydb.dbo.sp_spaceused
– You can run DBCC CHECKDB and other DBCC commands over a linked server connection:
EXEC SERVERNAME1.mydb.dbo.sp_executesql N’DBCC CHECKDB’
Note: Again replace SERVERNAME1 with the name of your linked server & mydb with the name of your database
————————————————————————————–
On a final note, remember to disable RPC when your done as a security precaution:
exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc’, @optvalue=’false’
exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc out’, @optvalue=’false’
Note: again replace SERVERNAME1 with the name of your linked server
LinkedServer:
EXEC sp_addlinkedserver   
   @server=N'WELCOME-PC\MSSQL05',             -- Remote Computer Name
   @srvproduct=N'',                 -- Not Needed 
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'WELCOME-PC\MSSQL05';  -- Server Name And Instance
EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'true'

sp_link_publication is used by immediate updating subscriptions in transactional replication.
Must be run at Subscriber 
EXEC sp_link_publication 
    @publisher = @publisher, 
    @publication = @publication,
    @publisher_db = @publicationDB, 
    @security_mode = 0,
    @login = @login,--------------------login name must be available subscriber
    @password = @password;