ASE Quiz Question Randomizer
 
Randomly selected quiz question (want a different question? Click here):


December 2004
Suppose you're a RepServer DBA, and you want to maintain a historical overview of all changes made to any RepServer configuration parameters. Unlike ASE, RepServer config parameter changes are not logged into the RepServer errorlog file. How can you still keep track of these changes?

Answer:
The answer is actually pretty simple: triggers.
First, observe that all RepServer configuration parameters are stored in the RSSD table rs_config (actually, there's one exception, namely the disk_affinity settings -- but we'll deal with that later). A change to a configuration parameter (with RepServer commands configure replication server, create/alter connection or create/alter route) translates into either a change of a row in rs_config (if the changed parameter already existed in rs_config) or an insertion of a new row into rs_config (if it's a setting for a particular database connection or a route, which didn't exist yet).
With this in mind, it's pretty straightforward to log all RepServer config changes: just create triggers on rs_config, and log all changes into a separate table:
use your_RSSD
go

create table rs_config_log (dt datetime, 
                            action char(1), 
                            optionname varchar(30),
                            objid rs_id, 
                            charvalue_new varchar(255) null, 
                            charvalue_old varchar(255) null)
go

create trigger rs_config_trig_i on rs_config for insert
as
   insert rs_config_log 
   select getdate(), 'I', optionname, objid, charvalue, null
   from inserted
go

create trigger rs_config_trig_u on rs_config for update
as
   insert rs_config_log 
   select getdate(), 'U', i.optionname, i.objid, 
          i.charvalue, d.charvalue
   from inserted i, deleted d
   where i.optionname = d.optionname
     and i.objid = d.objid
go

create trigger rs_config_trig_d on rs_config for delete
as
   insert rs_config_log 
   select getdate(), 'D', optionname, objid, null, charvalue
   from deleted
go
Pretty simple, right? Just query rs_config_log and you have a timestamped log of all RepServer config changes. You'll notice that changing the value of an existing parameter is implemented by deleting and re-inserting the corresponding row into rs_config.
Also note that connection- or route-specific settings cannot be deleted from rs_config (so as to revert to the RepServer-wide defaults) with regular RepServer commands (not counting dropping the connection or route). If you want to achieve this, you'll have to manually delete the corresponding rows from rs_config.

As mentioned earlier, changes to the disk_affinity settings are not caught this way -- this is simply because disk_affinity is stored in the RSSD table rs_diskaffinity instead of rs_config. Obviously, nothing stops you from writing similar triggers on rs_diskaffinity, which is left as an exercise for the reader.

Although it's a good principle not to mess around in the RSSD, it's actually perfectly safe to create these triggers: the changes made to rs_config by RepServer are neither extremely performance-critical nor frequent. Also, the triggers do not change any functionality in the RSSD from the perspective of RepServer (which acts as the client application from an RSSD perspective). The only thing to keep in mind is that the triggers should never cause an SQL command to fail -- and I don't immediately see a reason why they would. The insert actions in the triggers will perhaps generate some addtional log records, but that's most likely negligable in comparison with the amount of changes to RSSD tables like rs_locater and rs_segments. Also, the amount of space occupied by the table rs_config_log is not likely to be significant.

NB: On a different but related topic: Note that rs_config.objid, and therefore also rs_config_log.objid, is either 0 (for RepServer-global config parameters) or non-zero (for settings for a specific database connection or route). Unfortunately, in the latter case, the ID value of the connection or route is byte-swapped in an undocumented, platform-specific way. To convert rs_config.objid to the corresponding value in rs_databases.dbid or rs_routes.dest_rsid, use the following expression in a 'classic' ASE RSSD: hextoint(substring(bintostr(rs_config.objid),1,8)). In an 'embedded' ASA RSSD in RS 12.6, use this expression instead: convert(int, substring(rs_config.objid,1,4)).
If you have a copy of my book "The Complete Sybase Replication Server Quick Reference Guide", you can download some handy stored procs to display all current non-default config settings for databases and routes. Those procedures can simply be adapted to work with rs_config_log instead.

How would you rate this ASE quiz question?
Great    OK    Average    Boring     


More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


 
 This document is located at www.sypron.nl/quizrand.html