http://petersap.nl/SybaseWiki/index.php?title=Master_Database_Replication&feed=atom&action=historyMaster Database Replication - Revision history2024-03-29T04:47:38ZRevision history for this page on the wikiMediaWiki 1.24.2http://petersap.nl/SybaseWiki/index.php?title=Master_Database_Replication&diff=1831&oldid=prevPsap: /* Install the replication server stored procedures and tables */2009-01-20T13:50:44Z<p><span dir="auto"><span class="autocomment">Install the replication server stored procedures and tables</span></span></p>
<table class='diff diff-contentalign-left'>
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr style='vertical-align: top;'>
<td colspan='2' style="background-color: white; color:black; text-align: center;">← Older revision</td>
<td colspan='2' style="background-color: white; color:black; text-align: center;">Revision as of 13:50, 20 January 2009</td>
</tr><tr><td colspan="2" class="diff-lineno">Line 43:</td>
<td colspan="2" class="diff-lineno">Line 43:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_update_threads to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_update_threads to <maintenance_user></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_check_repl_stat to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_check_repl_stat to <maintenance_user></div></td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_update_lastcommit to <del class="diffchange diffchange-inline"><maintenance_user></del></div></td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_update_lastcommit to <ins class="diffchange diffchange-inline">public</ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_initialize_threads to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant execute on rs_initialize_threads to <maintenance_user></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_threads to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_threads to <maintenance_user></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_lastcommit to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_lastcommit to <maintenance_user></div></td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"></del></div></td><td colspan="2"> </td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">Grant the following permissions as well, on both servers.</del></div></td><td colspan="2"> </td></tr>
<tr><td class='diff-marker'>−</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"> grant execute on rs_update_lastcommit to sso_role</del></div></td><td colspan="2"> </td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>===Configure both servers for replication===</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>===Configure both servers for replication===</div></td></tr>
<!-- diff cache key website-mw_:diff:version:1.11a:oldid:1824:newid:1831 -->
</table>Psaphttp://petersap.nl/SybaseWiki/index.php?title=Master_Database_Replication&diff=1824&oldid=prevPsap: /* Install the replication server stored procedures and tables */2008-12-10T13:58:18Z<p><span dir="auto"><span class="autocomment">Install the replication server stored procedures and tables</span></span></p>
<table class='diff diff-contentalign-left'>
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr style='vertical-align: top;'>
<td colspan='2' style="background-color: white; color:black; text-align: center;">← Older revision</td>
<td colspan='2' style="background-color: white; color:black; text-align: center;">Revision as of 13:58, 10 December 2008</td>
</tr><tr><td colspan="2" class="diff-lineno">Line 47:</td>
<td colspan="2" class="diff-lineno">Line 47:</td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_threads to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_threads to <maintenance_user></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_lastcommit to <maintenance_user></div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>  grant all on rs_lastcommit to <maintenance_user></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Grant the following permissions as well, on both servers.</ins></div></td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="color:black; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> grant execute on rs_update_lastcommit to sso_role</ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"></td></tr>
<tr><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>===Configure both servers for replication===</div></td><td class='diff-marker'> </td><td style="background-color: #f9f9f9; color: #333333; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #e6e6e6; vertical-align: top; white-space: pre-wrap;"><div>===Configure both servers for replication===</div></td></tr>
<!-- diff cache key website-mw_:diff:version:1.11a:oldid:1819:newid:1824 -->
</table>Psaphttp://petersap.nl/SybaseWiki/index.php?title=Master_Database_Replication&diff=1819&oldid=prevPsap at 12:09, 11 September 20082008-09-11T12:09:40Z<p></p>
<p><b>New page</b></p><div>With a warm standby setup of 2 Sybase ASE's and a Replication Server it is often needed to keep the login information between those 2 database servers in sync. Various methods can be used to achieve this, but with Replication Server 15 in combination with ASE 15 you can now use "master database replication". Master database replication is specifically designed to keep roles, logins and password characteristics in sync between ASE's. <br />
<br />
This page describes the steps to be taken to create a warm standby environment for the the master database.<br />
<br />
==Assumptions of the environment==<br />
The setup makes the following assumptions: <br />
* The Replication Server is already configured and running. <br />
* Replication is done between ASE servers <br />
* The master database between the two ASE servers are in sync, in other words: logins, passwords and roles are the same across the two master databases.<br />
<br />
The setup is divided into a number of steps, where each step is a separate unit of work.<br />
<br />
==Preparation of the Sybase ASE-servers==<br />
===Create a maintenance user on both ASE's===<br />
<br />
The maintenance user is used by the replication server to apply transactions. Make sure that the name, password and suid of the maintenance user on both servers are identical. For security reasons, do not alias the account to dbo.<br />
<br />
use master<br />
go<br />
sp_addlogin "<maintenance_user>","<maintenance_user_password>"<br />
go<br />
grant role replication_role to "<maintenance_user>"<br />
go<br />
sp_adduser <maintenance_user><br />
go<br />
<br />
-- check the value of the suid<br />
select suser_id("<maintenance_user>")<br />
go<br />
<br />
===Install the replication server stored procedures and tables===<br />
<br />
To do this, make a copy of the script rs_install_primary.sql located in the $SYBASE/$SYBASE_REP/scripts directory. Remove the last two commands of the script (the dbcc settrunc and the sp_setreplicate commands). The commands that are skipped will be executed at a later stage, when the replication agent is configured. Apply the script on both servers.<br />
<br />
isql -Usa -P<password> -S<active_server> -Dmaster -i changed_rs_install_primary.sql <br />
<br />
Grant the maintenance user permissions to the new objects, run the following statements on both servers.<br />
<br />
grant execute on rs_marker to <maintenance_user><br />
grant execute on rs_ticket to <maintenance_user><br />
grant execute on rs_ticket_report to <maintenance_user><br />
grant execute on rs_get_lastcommit to <maintenance_user><br />
grant execute on rs_update_threads to <maintenance_user><br />
grant execute on rs_check_repl_stat to <maintenance_user><br />
grant execute on rs_update_lastcommit to <maintenance_user><br />
grant execute on rs_initialize_threads to <maintenance_user><br />
grant all on rs_threads to <maintenance_user><br />
grant all on rs_lastcommit to <maintenance_user><br />
<br />
===Configure both servers for replication===<br />
<br />
sp_configure "enable rep agent threads",1<br />
<br />
==Preparation of the Replication Server==<br />
'''Create a logical connection on the replication server'''<br />
<br />
The name of it does not have to match with the name of the primary database server and database, but this convention is widely used. <br />
<br />
create logical connection to <active_server>.master<br />
<br />
===Create a connection from the replication server to the active database===<br />
<br />
create connection to <active_server>.master<br />
set error class to rs_sqlserver_error_class<br />
set function string class to rs_sqlserver_function_class<br />
set username to <maintenance_user><br />
set password to <maintenance_user_password><br />
with log transfer on<br />
as active for <active_server>.master<br />
<br />
===Create a login in the replication server===<br />
<br />
This login is used by the rep-agent running in the ASE to connect to the replication server. <br />
<br />
create user <rep-agent_user> set password <rep-agent_password><br />
go<br />
grant connect source to <rep-agent_user><br />
go<br />
<br />
==Configuration of the ASE servers==<br />
===Configure the Rep Agent===<br />
The configuration of the Replication Agent can now be done. Apply the commands on both ASE's.<br />
<br />
use master<br />
go<br />
sp_config_rep_agent "master", "enable", "<repserver>", "<rep-agent_user>", "<rep-agent_password>"<br />
go<br />
sp_config_rep_agent "master", "send warm standby xacts", true<br />
go<br />
sp_setreplicate rs_marker,"true"<br />
go<br />
sp_setreplicate rs_update_lastcommit,"true"<br />
go<br />
<br />
===Mark the database for replication===<br />
Execute the sp_reptostandby stored procedure in the active ASE to activate replication from the active database to the replication server. Again, apply the commands on both ASE's.<br />
<br />
use master<br />
go<br />
sp_reptostandby "master","all"<br />
go<br />
<br />
===Start the RepAgent===<br />
Now you can start the RepAgent on the active ASE.<br />
sp_start_rep_agent "master"<br />
go<br />
<br />
==Configure the replication server for the standby database==<br />
<br />
===Create a connection from the replication server to the standby server===<br />
<br />
create connection to <standby-server>.master<br />
set error class to rs_sqlserver_error_class<br />
set function string class to rs_sqlserver_function_class<br />
set username to <maintenance_user><br />
set password to <maintenance_user_password><br />
with log transfer on<br />
as standby for <active_server>.master<br />
<br />
===Start the connection from the replication server to the standby database===<br />
<br />
resume connection to <standby-server>.master<br />
<br />
===Restart the connection from the replication server to the active database===<br />
<br />
When the connection to the standby server is created, the connection to the active database is stopped. Just restart it.<br />
<br />
resume connection to <active-server>.master<br />
<br />
Congratulations, you now have a working Warm Standby configuration for the master database!<br />
<br />
<br />
[[Category:RepServer]]</div>Psap