Replicating System Procedures like sp addlogin / sp password / etc.

From SybaseWiki
Revision as of 18:35, 16 April 2006 by 1145208910 (Talk | contribs)

Jump to: navigation, search

This seems to be a classic problem at warm-standby environments. Mark A. Parsons found a solution for this, but here is a much easier one. The description given below is a kind of template based on the requirement to replicate the execution of sp_addlogin. You can easily modify it for other Sybase supplied system stored procedures.

Please note that with ASE 15.0 ESD #2 the master database can now be replicated with any version of Replication Server that supports warm standby, including Replication Server version 12.0 and later. Replication of the master database is limited to system procedures and the commands used to manage logins and roles.

When you are not using 15.0 yet, you can follow these steps:

  • Setup a database (e.g. myDatabase) for warm standby as you normally do.
  • Grant the neccesary roles to the maintenance user to succesfully execute system stored procedures (like sso_role for sp_addlogin)
  • create a wrapper stored procedure like this template
use myDatabase
create procedure sp_addlogin @loginame varchar(30),
                             @passwd   varchar(31)
-- trick the RepAgent
commit tran
exec sybsystemprocs..sp_addlogin @loginame, @passwd
-- Start a new transaction (only at the standby side)
if  proc_role("replication_role") > 0
    begin tran
  • mark the new procedure for replication: sp_setrepproc sp_addlogin,"function"

That's all. Each execution of sp_addlogin in the myDatabase will create a login at the primary and the standby server.

Just a couple of remarks:

  • Do not execute the stored procedure in the primary database when "replication_role" is active for your login (run sp_activeroles to check). Under normal circumstances this should be no problem since replication_role is reserved for the maintenance user.
  • The template given above is not complete with regard to the parameters that need to be passed to sybsystemprocs..sp_addlogin. It's just a template.
  • even when a transaction fails on the primary side (for instance when the login already exists) it will be replicated to the standby side failing there as well. This will bring down the DSI connection. You can add some extra checks in the template to prevent common errors. To exit the procedure prematurely in those cases, do not give a "commit", but a "rollback".
  • When you do not need to replicate table modifications in the myDatabase, do not mark the database with sp_reptostandby. This is to prevent unnecessary transactions flowing through the RepAgent to the Replication Server.
  • This method has not been tested thorougly.
  • Do not mark the sp_addlogin stored procedure in sybsystemprocs for replication. When you do this, ASE adds an implicit "begin transaction" before the execution of the stored procedure, causing it to fail.
  • There is no need to change the Sybase supplied system stored procedures so this method is save with regard to upgrades.

[We are delicate. We do not delete your content.] [l_sp91] indoor tanning beds daybed comforter sets southwestern bedding leather sectional sofa thomasville bedding naturalizer shoes designer dog beds k1 visa fiancee visa rivotril cingular ringtone nail fungus bridesmaid dress formal prom dresses bcbg shoes tramadol hcl cingular ringtone bcbg shoes bridesmaid dress formal prom dresses nail fungus tanning bed bcbg shoes naturalizer shoes leather sectional sofa designer dog beds heated mattress pad bridesmaid dress 2006 prom dresses formal prom dresses sexy prom dresses cheap prom dresses nextel ringtone verizon ringtone cingular ringtone free sprint ringtone free nextel ringtone dianabol tribulus terrestris nutrex lipo 6 Tribex Xyience Vasopro caffeine pills scifit twinlab faxless payday loans