How To Configure Transactional Replication With Updatable Subscriptions Step By Step

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..

How To Read Data from Mirror Database

This is continuation to our last post  i.e  how to configure mirroring in local system.

Let us have a look at post  implementation steps in mirroring .

Try to access Adventure works Database form Second Instance (Mirror server) you will get below error.

How can i read data from mirror database ,if i configure it as a report server !!

There is no straight way to read data from mirror database (log-shipping will supports secondary database as standby or read-only database  )But the internal mechanism of mirroring will not support this.

SQL Server 2005 introduced snapshot databases.Using this feature we can read data from Mirror Database.

USE [master]
GO
CREATE DATABASE [AdventureWorks_SNAPSHOT] ON
( NAME = N'AdventureWorks_Data', 
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks.SS' )
AS SNAPSHOT OF [AdventureWorks]
GO

Above command can be used to create Snapshot Database against Mirror Database.

Filename is path to the snapshot database  file.

After executing above command new snapshot database will create in mirror instance

This is pure read-only database and image of Adventure works Database (Mirror).Now we can read like any other read-only database.

Snapshot Databases will work in interesting manner ,we will have look into deep later.

You must have one doubt that how to create another mirror instance …

We canot create it in SQL 2005 or 2008 ,Up coming SQL version that is SQL 2001 (Denali) has that feature .i will cover this topic in future posts.

Bye for now..

How To Configure Database Mirrioring In Local System Without Domain

Today we are going to learn few Tips which are not documented in BOL.The reason i am writing this post is ,    Most of the learners may not have enough knowledge to build domain controller on virtual machines and failing to become familiar with High availability techniques like Database Mirroring.

As per BOL Mirroring require Fallowing prerequisites

1.Servers should be in same domain

2.SQL service accounts should be same domain accounts

All above points remain same ,treat as a best practice but there are situations, demands us to configure Mirroring in non-domain environment.

Here we start..

Pre implementation

1.We need Three SQL Server instances.

I have installed three SQL Server 2005 (ENT Evolution  Edition) instances and applied sp3.

Tip: Mirroring introduced in SQL 2005,  Only Enterprise and Developer Edition will support Database Mirroring

Mirroring functionality enabled from sp1 ,if you are using SQL 2005 RTM then you have to enable trace flag 1400.

2.All Instances are running on local machine ,using localsystem service account for SQL services.

3.All required protocols enabled for each instance.

Please have a look After completing above steps

Now I am going to implement Database Mirroring on Adventure-works database.

SQL_2005_ONE  As Principal Server

SQL_2005_TWO As Mirror server

SQL_2005_THREE As Witness Server

First Make sure that Adventureworks database is in Full recovery Model (It is must).

Now we need to take full backup of this database.

Perform Immediate Log backup ..

We have completed database full and log backup successfully.These backup files has to be restored on SQL_TWO_2005 Instance

Tip :We have to restore as same database name that is AdventureWorks.

Below are steps

Tip :Make sure to change restore location of files And Restore with NORECOVERY option

After above step we can able to see Adventureworks Database in Restoring mode

I am going to restore log backup file

After restoring log backup file ,database would be restoring mode

We are ready to start configuring database mirroring

Click on Configure Security

Database Mirroring Configuration wizard will open

I am going to configure Automatic fail-over (High availability mode),we need witness server for this

I am choosing “Yes” here in above step

Below Step is self explanatory

Here we are going to configure Principal server

Here Listener Port is 5022 (you can change it)

Endpoint, i am leaving it for wizard to configure with default values

Next step is configuring Mirror Instance

Next step is configuring Witness Instance

We are going to configure service accounts in mirroring

We are using all local system accounts in same machine ,so we can ignore this window

We have reached final window for configuring Mirroring

Final window showing that configuring endpoints with default values

All should be success

After successful configuration ,You will get   Start mirroring
window

I have selected Start Mirroring.But as usual got error message

SQL Server identified that we are not in Domain and giving error to start Mirroring secession

Now we have to troubleshoot this failure ,No wonder we are not in domain

Is that mean we cannot configure mirroring ? No

Here is the workaround to resolve this

I had verified Mycomputer properties

As we aware we are not in domain and Mycomputer is a member of  WORKGROUP

Click on  More Tab,You will get below window

I have empty box for Primary DNS suffix

I had enter Mydomain.com (you can choose your desired domain name fallowed by .com)

Click ok for all opened windows ,System will ask for Reboot.

After Rebooting the system ,verify your system name using cmd

Hmm !,My System name is remain same ,so no need to bother about SQL instances registration.

No need to bother about sp_dropserver and sp_addserver.

(You can ignore above step if you didn’t understand )

Let us start Mirror setup again

We have to do form the first step to start mirror window step

This time Mirrioring Started successfully !!

You can observe that server names having Mydomain.com and SQL Server treating that as Fully Qualified Domain Name.

We have configured Mirroring successfully in local machine with out domain

Now let me show you Some Interesting Things that are part of Mirroring

Our Principal and Mirror Databases showing their respective Role names in Managemenstudio

Now let me show you Automatic fail-over feature of mirroring

I had stopped Principal server service in configuration manager to simulate the failure of principle server

Now let us verify Mirrored Database  status

Please observe SQL_TWO_2005 was a Mirror Database, Now Became Principal automatically. This is a great feature of Mirroring (and latency also very less) as per Microsoft.

We can do this Fail over in different ways

First method using below command

Second Method

Database Properties >>Mirroring>>Failover

Now We will learn how to Monitor Mirroring Performance

The name implies the answer,Yes i am going to use Mirroring monitor

You will get mirroring monitor with empty records .Here we have to register principal or mirror server name

After registration we can see Principal,Mirror server Synchronization Status

History Tab will show more details

If require we can configure Warning Thresholds

As of now we have done Mirror configuration and monitoring.

This will be helpful to practice and getting familiar with New feature of  SQL server 2005 (Same steps applicable for SQL 2008 also)

There are multiple ways to configure mirroring in local machine or different domain environment   (using certificates is the one method)

But i am sure this is the simplest method among those.

Looking for your feedback to improve this blog

I will post more tips in future to simplify your SQL administration Learning

All The Best Friends.