SQL Server Performance

Transactional Replication

Discussion in 'Performance Tuning for SQL Server Replication' started by iambhushan, Mar 28, 2006.

  1. iambhushan New Member

    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.


    Bhushan Kalla

Share This Page