How to identify Primary DataServer only knowing Replicate DataServer
Sivaraj
replicated site (StandBy ASE Server) without the help of
Replication Server. Or how can I know what is the
replication server for a replicate server?
Jeff Tallman [Sybase]
help of Replication Server"...
If in RS, you can use admin show_connections, admin who, or other
commands to see which connections/threads are active - if the actual ASE
servername was used (vs. an alias name in the interfaces file) then your
replicate database connection should show up fairly clearly....of
course, the problem is that this requires the help of RS....
In the replicate database, itself, the repagent should be configured -
just not running and the secondary truncation point not set. However,
if you do sp_config_rep_agent <dbname>, it should list the DS.DB name
that it will use as the connect source as well as which RS it is using.
A third way is if you have access to an RSSD, any RSSD within the
replication domain for the system in question - the rs_databases table
lists all the databases within the domain along with the id for the RS
that controls it (in rs_sites).
A fourth way is that if replication is currently working, simply do a
sp_who in the replicate server, find the spid for the maintenance user
and then do a select * from master..sysprocesses for that spid. It will
list the ip address (later revs of ASE) and/or hostname that the RS is
on that is replicating into that database. From there, you can go to
that machine and narrow it down to one of possibly several RS's (some
sites run more than one RS on a box) and use one of the three techniques
above to eliminate the others to find the one in question.
...may be other ways.
Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.t...@sybase.com
http://blogs.sybase.com/database
Sivaraj
Sorry if my question is confusing you. My problem is:
I have lot of servers in my environment. For a particular
database in a server, I want to confirm whether that is a
replicated dataserver or not (I hope, we can find using
rs_lastcommit). If that is a replicated one then how can I
come to know which Replication Server is replicating the
data to this server from some other Primary Server. Even the
maintenance user is also not named as per the standard
(assume I have many more users on this replicated server,
how to find that which is a maintenance user). Here I do not
have proper document, also we have lot of replication
servers in our environment. On which Replication Server to
check. Thanks for your patience.
Sivaraj
Jeff Tallman [Sybase]
....Ahhhh.....you do have some fun - but it is doable, just tedious.
What I would be tempted to do is:
1) find all the RS's first and then get into one of their RSSD's
2) use rs_sites and rs_routes to figure out routes, etc. and which RS's
are in that domain vs. another. For example, if an RS is not listed in
rs_sites, it is in another domain - find that RS and get into it's RSSD.
3) for each domain, use one of the RS's RSSD rs_databases to get a list
of servers.databases (make sure to lookup in interfaces file which
hosts/ports they are pointing to as server.database could be aliased).
The problem with looking for rs_lastcommit is that it could be there
just due to a dump/load. For example, someone might have done a dump of
prod and loaded into dev - but dev doesn't have replication set
up....still would have rs_lastcommit. You could check the
rs_lastcommit's datetime column to see if used recently as a secondary
check.
You can try using sp_who from any dataserver as well. RS identifies
itself pretty clearly (app name column will say "RepServer") and the
user's database context will be the database it is in (so if spid is
showing loginname 'myusername' is in 'finance_db' and the program name
is "RepServer", then it is the maintenance user. One way to find all
the maintenance users in a server is to look for all the logins with
'replication_role' (syslogins -> sysloginroles -> syssvrroles (I think -
don't have master systables in front of me)).
Mark A. Parsons
==============================================
rs_lastcommit contains info about the last time a transaction was applied to the current database. The 'origin' column
tells us which database was the source of the transaction (ie, origin=PDB; more on this later).
Ignore the rs_lastcommit record with origin=0.
It's not uncommon to have records in rs_lastcommit with old datetime stamps in the dest_commit_time column.
For example, a change in PDBs would leave the old PDB/rs_lastcommit record in place in the RDB.
Another example, a dump-n-load of a replicated database (eg, from production) into another database (eg, into
development) will drag along the PDB/rs_lastcommit records from the original RDB.
So, if you know data is being actively replicated, and there are no down repservers (or repserver threads), then the
existence of rs_lastcommit records with 'recent' dest_commit_time datetime values would tend to indicate the current
database is a RDB.
-------------
How to find the replication server(s) (aka RRS) that is applying transactions into a RDB?
=========================================================================================
One potential method (which Jeff has mentioned) ... if you have an active DSI connection into the RDB then you can pull
the repserver's host (or ip address) and OS process id from sysprocesses. [NOTE: You're looking for
sysprocesses.program_name = 'RepServer' and sysprocesses.dbid = db_id(<RDB>).]
NOTE: A DSI connection may timeout (ie, disconnect from the RDS) after a period of inactivity; in this case you won't
find a record in sysprocesses.
With the info from sysprocesses you can go to the host machine and see what's running under the given OS process id.
Depending on the OS and the tool you're using to view process info, you may be able to see the '-S<RRS>' flag for the
repserver binary process.
-------------
How to identify the source database (PDB) of data replicated into a RDB?
========================================================================
In the RDB, rs_lastcommit.origin contains a unique number of the PDB(s).
NOTE: Ignore the rs_lastcommit record with origin=0.
NOTE: There may be more than one source database (ie, multiple PDBs) replicating into a given RDB.
NOTE: There may be old/invalid records in rs_lastcommit. You'll want to take into consideration those rs_lastcommit
records with recent values in the dest_commit_time column. (we touched on this earlier)
This PDB origin number can be used to look up the PDS/PDB in the a repserver's RSSD..rs_databases table. The problem
with this method is that you have to know which RSSD to look in.
NOTE: Unless all repservers are connected to all other repservers via routes, there's no guarantee that all RSSDs will
have the info you're looking for in the local rs_databases table.
The better option is to log into the ID repserver's RSSD and look for the PDB origin number in the rs_idnames table (ie,
rs_idnames.id = PDB/origin#).
So now the problem is finding out which repserver is the ID repserver.
All repservers have to connect to a single repserver known as the ID repserver. (This is typically the first repserver
that was setup in a replication domain.) Every repserver has a configuration file which contains the name of the ID
repserver.
The ID repserver is primarily responsible for generating unique numbers for every dataserver/database pair, as well as
every repserver, in a replication domain. The complete list of these unique numbers is located in the ID repserver's
RSSD..rs_idnames table.
NOTE: The ID repserver can be, and usually is, used like any other repserver to also perform data replication.
-------------
While it's possible to *sometimes* answer your questions with the info Jeff and I have provided, in order to obtain the
info you typically need special permissions in the RDB, access to the various machines in your network, access to the
various repserver config files, and on and on and on ... and then you have to jump through these same hoops each time
you wish to answer your question ... and you'll probably have to answer these questions on an ongoing basis if databases
are constantly being added/dropped/moved within your replication domain.
Because replication domains can be rather complicated to traverse, it's not uncommon for Repserver admins to have a
series of queries they run (against all RSSDs) on a regular basis which provides them with the details necessary to find
the various components (eg, PDB/RDB pairs, WS pairs, repdef names and host repservers, repdef/subscription pairs, etc)
in a replication domain.
So, your best bet is to get your Repserver admin to regularly run a query against all RSSDs. The objective being to
generate a list of all PDB/PRS/RRS/RDB tuples in your replication environment, and then place a copy of the information
in a location that you can access (eg, OS text file, database table). [Chances are your Repserver admin already has
this info ... or wishes s/he had this info.]
I've attached a script that I believe should give you a good starting point for generating all PDB/PRS/RRS/RDB tuples
known by a given RSSD. (I pulled this code from a larger set of scripts so I'm hoping I didn't cut out any important
parts ...)
The general method for collecting the data:
1 - log into the ID repserver
2 - get the ID repserver's RSSD location (admin rssd_name)
3 - log into the ID repserver's RSSD
4 - run the attached query
5 - while in the ID repserver's RSSD, pull a list of all known repservers (rs_idnames.type=8); exclude the ID repserver
6 - for each repserver:
6a - log into the repserver
6b - get the repserver's RSSD location (admin rssd_name)
6c - log into the repserver's RSSD and run the attached query
7 - consolidate all results into a unique listing; place results into a file/database that is readily accessible by all
interested parties
-------------
NOTE: PDS/PDB = primary dataserver/database
RDS/RDB = replicate dataserver/database
PRS/RRS = primary/replicate repserver
RS = repserver
Mark A. Parsons
A couple more methods of finding the ID repserver for a replication domain:
While logged into a repserver:
admin config, id_server
While logged into a repserver's RSSD:
rs_configure 'id_server'
Mark A. Parsons wrote:
... snip ...
> So now the problem is finding out which repserver is the ID repserver.
>
> All repservers have to connect to a single repserver known as the ID
> repserver. (This is typically the first repserver that was setup in a
> replication domain.) Every repserver has a configuration file which
> contains the name of the ID repserver.
>
> The ID repserver is primarily responsible for generating unique numbers
> for every dataserver/database pair, as well as every repserver, in a
> replication domain. The complete list of these unique numbers is
> located in the ID repserver's RSSD..rs_idnames table.
... snip ...
Jeff Tallman [Sybase]
...and when you get done getting all the information - draw the
topology!!!! Use PowerPoint, Sybase Control Center, Visio, Sybase
PowerDesigner, old fashion pen & paper - whatever, but do yourself a
HUGE favor and draw that thing out....and then frame it on the wall as
you will be using it constantly.
Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.t...@sybase.com
http://blogs.sybase.com/database