Transactional Replication

Question

Transactional Replication

Bhushan Kalla

Default port

Points: 1475
More actions
March 28, 2006 at 9:48 pm

#90730
Hi Everyone,
I have a serious issue on replication in my production environment. I just set-up replication and it failed When a record is added to a table(A) in the database. I did capture the SQL profile the replication activity on the database then I have seen a weird thing. When a record was added to the table that replciation is passing sp_MSins_table(A) and also passing sp_MSdel_Table(B) where table(A) has a foreign key relation with Table(B). I don't know why the replication is passing delete command when a record is added to the Table(A) that makes replication fail. I copied the procedure that I followed in my replicaiton if any one can help me you are greately appreciated and it is also a great help.

Thanks,
BK

Replication Notes:

The current replication set up is a transactional replication with minimum latency and it is for both Production database. The database should be replicated from server(A) and server(B). The source database server is server(A) and destination database server is server(B). Both servers have cluster set-up and server(A) is an ES7000 mainframe box.

Note: The services start set-up account is used other than system account that is a domain account and has system admin permissions.
- Change the database access from Multi_User to Restricted_User/ Single_User. That would kick off all users from the database and the database is isolated from the application. It helps us not to corrupt the database.
ALTER DATABASE [DBNAME] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
- Take full backup of the database on Source database server and move the back-up file to destination database server.
- Restore the back-up copy on the destination server and change the access to Restricted_User/Single_User.
- Configure Publisher, Distributor and Subscriber servers.
- Environment: server(A) is Publisher and Distributor; server(B) is Subscriber.
- Configure transactional replication with minimum latency.
- Create publication with all tables in the source database excluding some security tables and filtered some fields which are text/image data types.
- Create push subscription to replicate data
- Generate custom stored procedures script by executing the system stored procedure on source database; sp_scriptpublicationcustomprocs [publication name].
- Run the generated script in the above step on destination database.
- Run the Autofix script on destination database to fix domain account user on SQL Server.
- Change the database access from restricted/single user to multiuser access.
March 31, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

aria-231041 SSC Veteran Points: 285 More actions · Answer 1

I am having this issue with my replication as well - did anyone have any suggestions? Replication is setup from Server A to Server B (server A is publisher with a push subscription to server B). Server B is set up as subscriber and distribution server.

Setup this weekend, did some testing with data changes and worked okay. This morning one record added, another changed and receiving error:

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_REFERRALTEXT_REFERRALID'. The conflict occurred in database 'A', table 'referraltext', column 'referralid'.

Last Command: {CALL sp_MSdel_referral ('referral# ')}

Transaction sequence number and command ID of last execution batch are 0x0003EA2C00000CBE000100000000 and 1.

Thanks,

Aria

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

AFAIK, you need to drop all foreign keys on subscriber.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Bhushan Kalla Default port Points: 1475 More actions · Answer 3

Dinakar is right.. you should drop all FKs on Subscriber database.

Bhushan

Dove-265763 SSC Veteran Points: 264 More actions · Answer 4

WHen i tried to connect to a remote SQL Server I encountered the following issues. Please see the error logs and suggest the possible solution if you hvae time,

Please find the following details:-

Scenario 1

Logged in to Enterprise manager
Clicked on “New SQL registration”
Add server IP “10.193.16.177”
Selected Authentication mode as “SQL Server”
Keyed in Login name as “N800\harihan_lavanya” and the password

When they click on finish they are getting an error stating “Login failed for User N800\Harihan_Lavanya”

Scenario 2

Log into Enterprise Manager using “Run as” option and login using “N800\Harihan_lavanya”.
Clicked on “New SQL registration”
Add server IP “10.193.16.177”
Selected Authentication mode as “NT Authentication”

When they click on finish they are getting an error stating “Login failed for User N800\Harihan_Lavanya”

Again, we also tried using Mixed Mode Authentication but it’s failing also…

1. Regarding the Connectivity Issue the SQL Server Registration Property is showing the same error during registering to the IP: 10.193.16.177. The Error screen is as following:

2.And if I try with the RunAs Current User and for the New registration to the same IP, it gives the following Error Screen:

Regards,
Dove