Customizing replication
Let's walk
through the steps to establish the replication property and the constraints on
the subscription database so that both the publishing database and the
subscription database are identical, so that when there is a failure in the
publisher, we can switch to the subscriber.
Step 1
Stop
distribution as shown below. [Refer Fig 1.5 and 1.6]
Step 2
Enable
identity property on the subscription database on both the Emp and Dept tables using
Enterprise manager as shown below [Refer Fig
1.7 and 1.8]
Fig 1.7
Fig 1.8
Step 3
Drop the
index and add the necessary constraints as shown below.
Use DB1
go
Drop index DEPT.Dept_PK
go
ALTER TABLE [Dept] ADD CONSTRAINT [Dept_PK] PRIMARY KEY CLUSTERED
(
[id]
)
go
drop index Emp.Emp_Pk
go
ALTER TABLE EMP ADD
CONSTRAINT [Emp_PK] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [Dept_FK] FOREIGN KEY
(
[Dept_id]
) REFERENCES [Dept] (
[id]
)
Step 4
Update the stored procedure created by the replication
setup. When setting up replication, SQL server creates three stored procedures.
One for insert, one for update and one for delete. Basically we are updating
the insert and the update procedure with "Set identity_insert on" and
"Set identity_insert off"
Insert procedure for the table "Dept" created by SQL Server
create procedure "sp_MSins_Dept" @c1 int,@c2 varchar(50)
AS
BEGIN
insert into "Dept"( "id", "Name" )
values ( @c1, @c2 )
END
Update
the procedure using the code below
Alter procedure "sp_MSins_Dept" @c1 int,@c2 varchar(50)
AS
BEGIN
set identity_insert Dept on
insert into "Dept"( "id", "Name" )
values ( @c1, @c2 )
set identity_insert Dept off
END
Insert procedure for the table "Emp" created by SQL Server
create procedure "sp_MSins_Emp" @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50)
AS
BEGIN
insert into "Emp"( "Id", "Dept_id", "Empname", "Zipcode", "Country" )
values ( @c1, @c2, @c3, @c4, @c5 )
END
Update the procedure using the code below
Alter procedure "sp_MSins_Emp" @c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50)
AS
BEGIN
set identity_insert Emp on
insert into "Emp"( "Id", "Dept_id", "Empname", "Zipcode", "Country" )
values ( @c1, @c2, @c3, @c4, @c5 )
set identity_insert Emp off
END
Update procedure for the table "Dept" created by SQL Server
create procedure "sp_MSupd_Dept"
@c1 int,@c2 varchar(50),@pkc1 int
,@bitmap binary(1)
as
if substring(@bitmap,1,1) & 1 = 1
begin
update "Dept" set
"id" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "id" end
,"Name" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Name" end
where "id" = @pkc1
if @@rowcount = 0
if @@microsoftversionj>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Dept" set
"Name" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Name" end
where "id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
Update
the procedure using the code below
Alter procedure "sp_MSupd_Dept"
@c1 int,@c2 varchar(50),@pkc1 int
,@bitmap binary(1)
as
begin
update "Dept" set
"Name" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Name" end
where "id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
Update procedure for the table "Emp" created by SQL Server
create procedure "sp_MSupd_Emp"
@c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50),@pkc1 int
,@bitmap binary(1)
as
if substring(@bitmap,1,1) & 1 = 1
begin
update "Emp" set
"Id" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "Id" end
,"Dept_id" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Dept_id" end
,"Empname" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "Empname" end
,"Zipcode" = case substring(@bitmap,1,1) & 8 when 8 then @c4 else "Zipcode" end
,"Country" = case substring(@bitmap,1,1) & 16 when 16 then @c5 else "Country" end
where "Id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "Emp" set
"Dept_id" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Dept_id" end
,"Empname" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "Empname" end
,"Zipcode" = case substring(@bitmap,1,1) & 8 when 8 then @c4 else "Zipcode" end
,"Country" = case substring(@bitmap,1,1) & 16 when 16 then @c5 else "Country" end
where "Id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
Update
the procedure using the code below
Alter procedure "sp_MSupd_Emp"
@c1 int,@c2 int,@c3 varchar(50),@c4 int,@c5 varchar(50),@pkc1 int
,@bitmap binary(1)
as
begin
update "Emp" set
"Dept_id" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else "Dept_id" end
,"Empname" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else "Empname" end
,"Zipcode" = case substring(@bitmap,1,1) & 8 when 8 then @c4 else "Zipcode" end
,"Country" = case substring(@bitmap,1,1) & 16 when 16 then @c5 else "Country" end
where "Id" = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
Step 5
Start
Distributor to synchronize the publisher and the subscriber. [Refer Fig 1.9]
Fig 1.9
Do some
deletes and updates to the publisher to make sure that it is replicated to the
subscriber. [Refer Fig 2.0]
update Emp set empname = 'Sophia Marque' where empname='Sofie Marque'
update Emp set empname = 'Zhang Ziyi' where id=4
delete from emp where id =2
Fig 2.0
Conclusion
By this customized replication, when the publisher fails,
you can point all of the applications
accessing the publishing database to the subscriber's subscription database,
since Publishers
publishing database and the subscriber's subscribing database look alike,
including the identity
property. Since identity property is enabled in the subscription database and
all the constraints are re-created in the subscription database, applications
work seamless when pointing to subscription database.
»
See All Articles by Columnist MAK