Difference between revisions of "Replicating System Procedures like sp addlogin / sp password / etc."

From SybaseWiki
Jump to: navigation, search
m
 
(5 intermediate revisions by 5 users not shown)
Line 1: Line 1:
 
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.
 
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.
+
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. See here how to configure this: [[Master Database Replication]]
  
 
When you are not using 15.0 yet, you can follow these steps:
 
When you are not using 15.0 yet, you can follow these steps:
Line 37: Line 37:
  
 
[[Category:RepServer]]
 
[[Category:RepServer]]
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
<div  style="display:none">
 
[We are delicate. We do not delete your content.]
 
[l_sp91]
 
[http://sitepalace.com/indoortanningbeds/ indoor tanning beds]
 
[http://sitepalace.com/cleo/daybedcomfortersets/ daybed comforter sets]
 
[http://sitepalace.com/cleo/southwesternbedding/ southwestern bedding]
 
[http://sitepalace.com/cleo/leathersectionalsofa/ leather sectional sofa]
 
[http://home.graffiti.net/thomasville_bedding/ thomasville bedding]
 
[http://home.graffiti.net/naturalizer_shoes/ naturalizer shoes]
 
[http://home.graffiti.net/designer_dog_beds/ designer dog beds]
 
[http://mujweb.cz/www/k1visa/ k1 visa]
 
[http://mujweb.cz/www/k1visa/fiancee-visa/ fiancee visa]
 
[http://rivotril.google.if.ua rivotril]
 
[http://republika.pl/cleo06/cingular-ringtone.htm cingular ringtone]
 
[http://republika.pl/cleo06/nail-fungus.htm nail fungus]
 
[http://republika.pl/cleo06/bridesmaid-dress.htm bridesmaid dress]
 
[http://republika.pl/cleo06/formal-prom-dresses.htm formal prom dresses]
 
[http://republika.pl/cleo06/bcbg-shoes.htm bcbg shoes]
 
[http://mysite.com.ua/xdem8200/pagesxdem8200/1_1.html tramadol hcl]
 
[http://mywebpage.netscape.com/burochka/cingular-ringtone.htm cingular ringtone]
 
[http://mywebpage.netscape.com/burochka/bcbg-shoes.htm bcbg shoes]
 
[http://mywebpage.netscape.com/burochka/bridesmaid-dress.htm bridesmaid dress]
 
[http://mywebpage.netscape.com/burochka/formal-prom-dresses.htm formal prom dresses]
 
[http://mywebpage.netscape.com/burochka/nail-fungus.htm nail fungus]
 
[http://www.buddyprofile.com/viewprofile.php?username=tanningbed tanning bed]
 
[http://www.buddyprofile.com/viewprofile.php?username=bcbgshoes bcbg shoes]
 
[http://www.buddyprofile.com/viewprofile.php?username=naturalizershoes naturalizer shoes]
 
[http://www.buddyprofile.com/viewprofile.php?username=leathersectionalsofa leather sectional sofa]
 
[http://www.buddyprofile.com/viewprofile.php?username=designerdogbeds designer dog beds]
 
[http://www.buddyprofile.com/viewprofile.php?username=heatedmattresspad heated mattress pad]
 
[http://www.buddyprofile.com/viewprofile.php?username=bridesmaiddress bridesmaid dress]
 
[http://www.buddyprofile.com/viewprofile.php?username=2006promdresses 2006 prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=formalpromdresses formal prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=sexypromdresses sexy prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=cheappromdresses cheap prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=nextelringtone nextel ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=verizonringtones verizon ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=cingularringtone cingular ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=freesprintringtone free sprint ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=freenextelringtone free nextel ringtone]
 
[http://dianabol.google.if.ua dianabol]
 
[http://tribulus-terrestris.blogs.eurosport.com tribulus terrestris]
 
[http://nutrex-lipo-6.blogs.eurosport.com nutrex lipo 6]
 
[http://tribex.blogs.eurosport.com Tribex]
 
[http://xyience.blogs.eurosport.com Xyience]
 
[http://vasopro.blogs.eurosport.com Vasopro]
 
[http://caffeine.blogs.eurosport.com caffeine pills]
 
[http://scifit.blogs.eurosport.com scifit]
 
[http://twinlab.blogs.eurosport.com twinlab]
 
[http://imean.com/blog/faxlesspaydayloans/ faxless payday loans]
 
 
</div>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
<div  style="display:none">
 
[We are delicate. We do not delete your content.]
 
[l_sp894]
 
[http://sitepalace.com/indoortanningbeds/ indoor tanning beds]
 
[http://sitepalace.com/cleo/daybedcomfortersets/ daybed comforter sets]
 
[http://sitepalace.com/cleo/southwesternbedding/ southwestern bedding]
 
[http://sitepalace.com/cleo/leathersectionalsofa/ leather sectional sofa]
 
[http://home.graffiti.net/thomasville_bedding/ thomasville bedding]
 
[http://home.graffiti.net/naturalizer_shoes/ naturalizer shoes]
 
[http://home.graffiti.net/designer_dog_beds/ designer dog beds]
 
[http://mujweb.cz/www/k1visa/ k1 visa]
 
[http://mujweb.cz/www/k1visa/fiancee-visa/ fiancee visa]
 
[http://rivotril.google.if.ua rivotril]
 
[http://republika.pl/cleo06/cingular-ringtone.htm cingular ringtone]
 
[http://republika.pl/cleo06/nail-fungus.htm nail fungus]
 
[http://republika.pl/cleo06/bridesmaid-dress.htm bridesmaid dress]
 
[http://republika.pl/cleo06/formal-prom-dresses.htm formal prom dresses]
 
[http://republika.pl/cleo06/bcbg-shoes.htm bcbg shoes]
 
[http://mysite.com.ua/xdem8200/pagesxdem8200/1_1.html tramadol hcl]
 
[http://mywebpage.netscape.com/burochka/cingular-ringtone.htm cingular ringtone]
 
[http://mywebpage.netscape.com/burochka/bcbg-shoes.htm bcbg shoes]
 
[http://mywebpage.netscape.com/burochka/bridesmaid-dress.htm bridesmaid dress]
 
[http://mywebpage.netscape.com/burochka/formal-prom-dresses.htm formal prom dresses]
 
[http://mywebpage.netscape.com/burochka/nail-fungus.htm nail fungus]
 
[http://www.buddyprofile.com/viewprofile.php?username=tanningbed tanning bed]
 
[http://www.buddyprofile.com/viewprofile.php?username=bcbgshoes bcbg shoes]
 
[http://www.buddyprofile.com/viewprofile.php?username=naturalizershoes naturalizer shoes]
 
[http://www.buddyprofile.com/viewprofile.php?username=leathersectionalsofa leather sectional sofa]
 
[http://www.buddyprofile.com/viewprofile.php?username=designerdogbeds designer dog beds]
 
[http://www.buddyprofile.com/viewprofile.php?username=heatedmattresspad heated mattress pad]
 
[http://www.buddyprofile.com/viewprofile.php?username=bridesmaiddress bridesmaid dress]
 
[http://www.buddyprofile.com/viewprofile.php?username=2006promdresses 2006 prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=formalpromdresses formal prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=sexypromdresses sexy prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=cheappromdresses cheap prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=nextelringtone nextel ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=verizonringtones verizon ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=cingularringtone cingular ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=freesprintringtone free sprint ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=freenextelringtone free nextel ringtone]
 
[http://www.buddyprofile.com/viewprofile.php?username=sprintringtones sprint ringtones]
 
[http://www.buddyprofile.com/viewprofile.php?username=verizonringtone verizon ringtone]
 
[http://dianabol.google.if.ua dianabol]
 
[http://tribulus-terrestris.blogs.eurosport.com tribulus terrestris]
 
[http://nutrex-lipo-6.blogs.eurosport.com nutrex lipo 6]
 
[http://tribex.blogs.eurosport.com Tribex]
 
[http://xyience.blogs.eurosport.com Xyience]
 
[http://vasopro.blogs.eurosport.com Vasopro]
 
[http://caffeine.blogs.eurosport.com caffeine pills]
 
[http://scifit.blogs.eurosport.com scifit]
 
[http://twinlab.blogs.eurosport.com twinlab]
 
[http://imean.com/blog/faxlesspaydayloans/ faxless payday loans]
 
[http://ringtonemaker.blogs.eurosport.com/ ringtone maker]
 
[http://www.buddyprofile.com/viewprofile.php?username=cashuntilpaydayloan cash until pay day loan]
 
[http://www.buddyprofile.com/viewprofile.php?username=faxlesspaydayloans faxless payday loans]
 
[http://www.buddyprofile.com/viewprofile.php?username=nofaxpaydayloans no fax payday loans]
 
[http://www.buddyprofile.com/viewprofile.php?username=cashpersonalloans fash cash personal loans]
 
 
</div>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
<div  style="display:none">
 
[We are delicate. We do not delete your content.]
 
[l_sp2006]
 
http://top20man.in.ua/black-eyed-peas-mp3 black eyed peas mp3]
 
[http://top20man.in.ua/madonna-mp3 madonna mp3]
 
[http://top20man.in.ua/eminem-mp3 eminem mp3]
 
[http://ringtonemaker.blogs.eurosport.com/ ringtone maker]
 
[http://top20man.in.ua/godsmack-awake godsmack awake]
 
[http://top20man.in.ua/godsmack-voodoo godsmack voodoo]
 
[http://top20man.in.ua/sean-paul-temperature sean paul temperature]
 
[http://top20man.in.ua/sean-paul-we-be-burnin sean paul we be burnin]
 
[http://top20man.in.ua/bad-day-daniel-powter bad day daniel powter]
 
[http://top20man.in.ua/system-of-a-down-mp3 system of a down mp3]
 
[http://top20man.in.ua/sean-paul-mp3 sean paul mp3]
 
[http://top20man.in.ua/metallica-mp3 metallica mp3]
 
[http://top20man.in.ua/shakira-mp3 shakira mp3]
 
[http://top20man.in.ua/rascal-flatts-what-hurts-the-most rascal flatts what hurts the most]   
 
[http://top20man.in.ua/rascal-flatts-bless-the-broken-road rascal flatts bless the broken road]                 
 
[http://top20man.in.ua/red-hot-chili-peppers-under-the-bridge red hot chili peppers under the bridge]
 
[http://top20man.in.ua/james-blunt-wisemen james blunt wisemen]
 
[http://top20man.in.ua/bad-day-daniel-powter bad day daniel powter]
 
[http://top20man.in.ua/godsmack-mp3 godsmack mp3]
 
[http://blog.yukonho.com/index.php?blog=44 Godsmack Awake]
 
[http://blog.yukonho.com/index.php?blog=45 godsmack voodoo]
 
[http://blog.yukonho.com/index.php?blog=46 sean paul temperature]
 
[http://blog.yukonho.com/index.php?blog=47 Sean Paul We Be Burnin]
 
[http://blog.yukonho.com/index.php?blog=48 natasha bedingfield unwritten]
 
[http://blog.yukonho.com/index.php?blog=49 50 cent mp3]
 
[http://blog.yukonho.com/index.php?blog=50 Bad Day Daniel Powter]
 
[http://blog.yukonho.com/index.php?blog=51 Daniel Powter mp3]
 
[http://blog.yukonho.com/index.php?blog=52 Goodbye My Lover James Blunt]
 
[http://blog.yukonho.com/index.php?blog=53 System Of A Down mp3]
 
[http://blog.yukonho.com/index.php?blog=54 Sean Paul mp3]
 
[http://blog.yukonho.com/index.php?blog=55 Metallica mp3]
 
[http://blog.yukonho.com/index.php?blog=56 Shakira mp3]
 
[http://blog.yukonho.com/index.php?blog=57 Black Eyed Peas mp3]
 
[http://blog.yukonho.com/index.php?blog=58 Madonna mp3]
 
[http://blog.yukonho.com/index.php?blog=59 eminem mp3]
 
[http://blog.yukonho.com/index.php?blog=60 Fall Out Boy Grand Theft Autumn]
 
[http://blog.yukonho.com/index.php?blog=61 Jack Johnson mp3]
 
[http://blog.yukonho.com/index.php?blog=62 oscar dresses]
 
[http://blog.yukonho.com/index.php?blog=63 mother of the bride dresses]
 
[http://blog.yukonho.com/index.php?blog=64 cocktail dresses]
 
[http://blog.yukonho.com/index.php?blog=65 Flower Girl Dresses]
 
[http://blog.yukonho.com/index.php?blog=66 Formal prom Dresses]
 
[http://blog.yukonho.com/index.php?blog=67 Plus Size Prom Dresses]
 
[http://blog.yukonho.com/index.php?blog=68 Free Verizon Ringtone]
 
[http://top20man.in.ua/godsmack-i-stand-alone godsmack i stand alone]
 
[http://top20man.in.ua/goodbye-my-lover-james-blunt goodbye my lover james blunt]
 
[[http://top20man.in.ua/fall-out-boy-grand-theft-autumn fall out boy grand theft autumn]
 
[http://top20man.in.ua/jack-johnson-mp3 jack johnson mp3]
 
[http://top20man.in.ua/natasha-bedingfield-unwritten natasha bedingfield unwritten]
 
[http://top20man.in.ua/50-cent-mp3 50 cent mp3]
 
[http://blogs.wwwcoder.com/cleo/ nextel ringtone]
 
[http://top20man.in.ua/bad-day-daniel-powter bad day daniel powter]
 
[http://top20man.in.ua/daniel-powter-mp3 daniel powter mp3]
 
[http://verizonringtone.forumco.com/ verizon ringtone]
 
[http://uscellularringtone.forumco.com US Cellular Ringtone]
 
[http://novogate.com/board/5907/222695-1.html free sprint ringtone]
 
[http://4898.rapidforum.com verizon ringtone]
 
[http://blogs.heraldextra.com/verizonringtone/ verizon ringtone]
 
[http://blog.investing.com/bcbgshoes/ bcbg shoes]
 
[http://blog.yukonho.com/index.php?blog=40 free sprint ringtones]
 
[http://blog.yukonho.com/index.php?blog=41 cheap prom dresses]
 
[http://blog.yukonho.com/index.php?blog=42 sexy prom dresses]
 
[http://www.buddyprofile.com/viewprofile.php?username=waterfordcrystal waterford crystal]
 
[http://www.buddyprofile.com/viewprofile.php?username=swarovskicrystal swarovski crystal bead]
 
[http://www.buddyprofile.com/viewprofile.php?username=mesotheliomalawsuits mesothelioma lawsuits]
 
[http://www.buddyprofile.com/viewprofile.php?username=mesotheliomasymptoms mesothelioma symptoms]
 
[http://www.buddyprofile.com/viewprofile.php?username=mesotheliomadiag mesothelioma diagnosis]
 
[http://blog.yukonho.com/index.php?blog=69 Cingular Ringtone]
 
[http://blog.yukonho.com/index.php?blog=70 free kyocera ringtones]
 
[http://blog.yukonho.com/index.php?blog=71 Free Nextel Ringtone]
 
[http://blog.yukonho.com/index.php?blog=73 informal wedding dresses]
 
[http://blog.yukonho.com/index.php?blog=74 bridal gowns]
 
[http://blog.yukonho.com/index.php?blog=75 Discount Bridal Gowns]
 
[http://blog.yukonho.com/index.php?blog=76 Plus Size Swimwear]
 
[http://blog.yukonho.com/index.php?blog=77 no fax payday loans]
 
[http://blog.yukonho.com/index.php?blog=78 countrywide home loans]
 
[http://blog.yukonho.com/index.php?blog=79 fast cash loan guaranteed]
 
[http://www.totalvideogames.com/blog/naturalizershoes/ Naturalizer Shoes]
 
[http://www.totalvideogames.com/blog/freekyocerarington/ Free Kyocera Ringtone]
 
[http://www.surfbirds.com/blog/formalpromdresses/ formal prom dresses]
 
[http://www.missoula.com/blog/sexypromdresses/ Sexy Prom Dresses]
 
[http://www.justachat.com/blog/?w=naturalizershoes Naturalizer Shoes][http://www.westwoodbapt.org/blog/towelwarmer/ towel warmer]
 
[http://www.toutelapoesie.com/blog/aerobed/ Aero Bed]
 
 
 
</div>
 

Latest revision as of 13:20, 11 September 2008

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. See here how to configure this: Master Database Replication

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
go
create procedure sp_addlogin @loginame varchar(30),
                             @passwd   varchar(31)
as
-- 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
go
  • 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.