Brief about Transactional Replication with up-datable Subscriptions
Transactional Replication with up-datable Subscriptions is feasible ,when your business demands to modify data in subscriber.
I am concentrating on implementation and practical approach about this Replication in this post .
Note : Normal Transactional Replication will not allow data modifications in subscriber level.
I am using one custom keyword i.e TRUS (Transactional Replication with up-datable Subscriptions)in this post for flexibility.
Pre implementation
1.MSDTC (Microsoft Distribution Coordinator)Service
It is a windows service ,we need this service because we are going to use linked server to access publication database from subscriber.
We can verify this service status in Management Studio it self. If this service is showing red then ,we need to start it
We will get below error if we configure TRUS with out this service.
Note :We have to start this service in Subscriber server.
It is a windows service ,we can start it using services.msc
Enter in to services applet and start it.You can change start up type as automatic .
we are designing our replication topology as
Single Publisher ,Single subscriber ,Single Remote Distributor
In management studio register and connect all these three instances
Create one database in SQL_2005_One instance (Publisher Server)
Create one table for verifying our Replication using below script
USE [MYREPLTEST]
Go
CREATE TABLE [dbo].[MyTable](
[Name] [varchar](50) NOT NULL,
[Id] [int] NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Note:Primary key is mandatory for a table to involve in TRUS.
Implementation
While configuring any replication ,first step is configuring Distributor server.
I am configuring SQL_2005_Two as Distributor.
Configuring Distributor server will create Distribution Database in that instance
To run Replication agent jobs ,SQL server is require.wizard will configure it to start automatically.
Here we have to mention snapshot folder location.
by-default it is creating in distribution server MSSQL\ReplData folder.
Tip: In real time we will create a shared folder in distribution server
Next step will create Distribution Database
Remove Publisher check mark ,because we are not configuring publication in this instance
Click Add drop down tab and select Add SQL Server Publisher Option
Connect to Instance that you want to configure as Publisher server (SQL_2005_One)
You can verify that Publisher server enabled in window
This window allow us to create a password (which can be use by remote publishers while connecting this distribution server)
Below are self explanatory,click next
Final window for this distribution configuration wizard .Here we can find what are the configuration options we have chosen
Click Finish
You will get success report window
Close this window .we have configured distributor server successfully.
Note: above steps are same for snap shot and Transactional replication.
Observation :You can find Distribution Database, under system database node in SQL_2005_Two instance
Next step is to configure Publication in Publisher server
Under Replication node , right click Local publication,select New publication
New Publication Configuration Wizard will open
Select second option ,because we are using remote distributor
click Add and connect to distributor server
Click Next ,Wizard will ask for password (we have set this while configuring Distributor server)
Enter correct password
Click next , select Publication database
Next,Select Publication Type as “Transactional Publication with updatable subscriptions ”
Next Window allow us to select Articles (tables to involve in replication)
I have selected “Mytable” as article
Unique identifier column will be added to our table (this will be used for tracking changes to the replicated table )
Click next, i am not going to filter any columns
Next i am going to initialize subscriber using snapshot agent immediately
Note:Depends on your articles size ,snapshot agent will execute for hours in real time.i will use different techniques to avoid this delay
(i will cover in my future posts )
Next window, configuring security setting for replication agents
Below agents will involve in TRUS
1.Snapshot Agent
2.Log reader Agent
3.Queue Reader Agent
4.Distributor Agent
each agent responsible for their respective tasks (i am not going in-depth in this post)
We are going to provide security options each agent
If we are in domain we can mention domain account or local machine account and password.
I will try to provide identical approach to real time environment.
I have created windows user with password never expire option (if we are in domain we can create in Active directory)
In local machine we can do this in computer management >>users >>right click >>new user
Note :this user should me member of local admin for the servers those are going to to involve in replication.
Time for configuring replication agents security using newly created local windows user
click on “security settings ” tab against snapshot agent
I am running Logreader agent,Que reader agent with same user credentials as like snapshot agent.
Provide publication name as Mypublication
Click Finish ,You should get success full window
Observation:
1.You can find Publication name in publisher server
2. We can observer one linked server in Publisher server ,three linked servers in distributor server
As of now no linked servers created in subscriber server
Note :We cannot use these linked servers for data access (by default it will be disabled)
Now time to configure subscription server
Next,Select publisher server
Here i am planning to configure pull subscription to reduce burden on distribution server(this is applicable in real time)
I am selecting subscription database from available databases in subscriber server .If it is not available you can create new database
Next ,Distributor agent security configuration
click on ellipse button
This is imp ,please be aware that we are giving same windows local user as process account for distributor agent
Next window for configuring distributor agent schedule (i am giving continuously it is also same for real time )
Next,window will allow us to configure how data modifications are going to effect from subscriber to publisher
Let me explain abit here,we have two options
1.Simultaneously Commit changes
2.Queue changes and Commit when possible
option selection depends on your environment and requirement
If publisher and subscriber are communicating each other properly and business demands that updates from subscriber should effect on publisher ASAP then first option is recommended.
Other side
Where as communication line between publisher and subscriber is weak ,and disconnecting frequently then second option is recommended
Tip :I remember that one of our customer has very low speed network line form primary to secondary server and complaining that logshipping is not working properly in such environment we can use second option.
I am selecting first option now
Next, I am going to set up login credentials which are used while subscriber connects to publisher to modify the changes in publication database
Imp Tip : Before performing this ,i have created one sql login in publisher server and mapped to publication database with sufficient privilege to modify the data.
Use below code for doing same in your machine
CREATE LOGIN [Repl_test] WITH PASSWORD=N’P@ssw0rd’, DEFAULT_DATABASE=[MYREPLTEST], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [Repl_test] ENABLE
Go
USE [MYREPLTEST]
GO
EXEC sp_addrolemember N’db_datareader’, N’Repl_test’
GO
EXEC sp_addrolemember N’db_datawriter’, N’Repl_test’
go
EXEC sp_addrolemember N’db_ddladmin’, N’Repl_test’
Hmm..
Now come back to New Subscription wizard
Select first option for creating linked server with default values and provide login credentials that just now we have created in publisher server.
This linked server will be used for updating the changes to publisher from subscriber .
Click next for coming windows
In final window you can find details about our subscriber configuration
click finish ..wait for success
Oops ..Wizard failed to create subscription
Imp Note : Publication Access List is a collection of logins ,which can be used to create and synchronize subscriptions in Replication.
Repl_test login is not member of this PAL ,i.e we are getting above error.
Before adding Repl_test login to PAL, same login should be available in distributor server also because we are using remote distributor in our topology.
Action : i have created Repl_test login in Distribution server
Now we can able to add same login to PAL in publisher server.Please verify below screen shots.
Now we have to configure new subscription in subscriber server ,fallow the steps from create new subscription.
This time we are successfully completed ,configuring subscription.
Post Implementation
Now ,Let us verify our Replication performance
We can verify all replication agents performance using replication monitor
We can open Replication Monitor in any of the server involved in TRUS.
Right click on Replication node in management studio>>select replication monitor
Register your publisher server ,if it is not showing in replication monitor
we can able to see the data in subscription database
We can modify data in subscription database
Data modifications reflecting in publication database
Replication monitor is showing publication performance as excellent.
Our Configuration completed successfully.
Final notes:
1. This is almost identical to configuring TRUS in production environment.
2.I have not cover below topics in this post
Linked servers vs Replication, Jobs involved in Replication,Agents participation in Replication,Monitoring Replication ,Improving Replication performance,issues with different Replication models.
i will try to cover all above topics in more detailed manner in future posts.
Friends i hope this post will help you to get familiar with Transactional Replication With Updatable Subscriptions.
Always welcome your feedback..