Exclude records from a select according to a comma separate list is a string

declare @pDBNameExcludeList varchar(max) = 'master,model,msdb'

select *

from sys.databases d

where upper(','+replace(replace(coalesce(@pDBNameExcludeList,'[]'),'[',''),']','')+',') 

not like upper('%'+name+'%')

Convert from pages to KB/MB

X pages = (X * 8) KB = (X * 8 /1024.0) MB

SAP Replication Server Routing

How does it work?

In a "standard" replication - we have a replication definition and subscription that declare the replication between database A to database B (and C, D, ...).

In a routing replication there is one more step before the target replicated database - another RS.

The second RS should know when and what to replicate to it's target DB.

The route from The first RS to the second, and specific between their RSSDs - replicate the required definitions (rep. definition, etc.) from the first RSSD to the second one.

When the second RSSD has these definitions - we can create a subscription on the second RS from the source database to the target database. This is how it works.

Before declare a routing

1. Two replication servers are installed and started.
2. All target databases must have the same columns and Primary key.
3. No identity in the target table (or subscription that know to update identity column).

Importent notes:

1. The passwords to all RSs in the process should be the same.
2. The RepAgent has to use a known servers names (in the ini files).
3. Source and target tables must have a primary keys.

Routing definitions

Init servers

1. Edit the ini file in the new server with:
   
   1. Current (new) RS.
   2. ASE instance of the RSSD.
   3. ASE instance of the replicatec(=target) database.
   4. First (=source) RS.
2. Edit the ini of the first (=source) RS with:
   1. New RS.
   2. ASE instance of the source RSSD.
3. Run rs_init:
   1. Two ways to do that:
      1. run rs_init
      2. create <SAP_RS_FOLSER>/REP-16_0/init/rs/RS2.rs (copied and edited from install.rs) and run it
         1. ./rs_init -r ../init/rs/RS2.rs
   2. In rs_init we define the servers, databases, RSSDs, users, etc.
4. Check that RS is up:
   1. isql64 -S RSFOG2 -Usa -P<password>
   2. admin version
      go
   3. admin who
      go

Manage the route

#		RS1 (source RS)	RSSD of RS1 (RS1_RSSD)	RS2 (second RS)	Target replicated database
1	Create route	alter connection to SERVERNAME.RS1_RSSD set log transfer ON go create route to RS2 set username sa set password PASSWORD go
1.a	Monitor the new route until it became active		1> rs_helproute 2> go  route        route_status                                                                                                   ---------------------------------- RS1 --------> RS2 Route is being created. Destination has not yet finished creating system table subscriptions.  Systable Subs to be created …… (list of what created) * re-run to re-check untio all created.
2	add connection RS2 (second RS) → target database			Use rs_init Declare DATABASE INFORMATION 1. SQL Server name: (target ASE) 2. SA user: sa 3. SA password: ****** 4. Database name: (target database name) 5. Will the database be replicated: no 6. Maintenance user: <target database name>_maint 7. Maintenance password: ****** 8. Is this a Physical Connection for Existing Logical Connection: no Can be done also with "create connection" command.
3	Configure a  rep_agent to the RSSD		1> use RS1_RSSD 2> go 1> sp_config_rep_agent RS1_RSSD , 'enable' , 'RS1' , 'sa' ,'PASSWORD' 2> go Replication Agent enabled for database 'RS1_RSSD'. The Replication Agent thread needs to be started using sp_start_rep_agent. (return status = 0) 1> sp_start_rep_agent RS1_RSSD 2> go Replication Agent thread is started for database 'RS1_RSSD'. (return status = 0)
4	Grant permissions in the target database				1> grant all on FirstTable to  <target database name>_maint 2> go
5	create a subscription to the replicate connection in the second RS			1> create subscription subTest3 for repdefTest with primary at PRIMARYDBSERVERNAME.PRIMARYDB with replicate at TARGETDBSERVER.TARGETDBNAME without materialization 2> go Subscription 'subTest3' is in the process of being created. 1> check subscription subTest3 for repdefTest with primary at PRIMARYDBSERVERNAME.PRIMARYDB with replicate at TARGETDBSERVER.TARGETDBNAME 2> go

SAP RS - The Replication Process

Before declare a Replication

We should have:

• Databases installations (SPA ASE or others), primary and replicates.
• Instances were set in sql.ini.
• RSSD database.

The Replication Process

1. Add a table to Replication

Create connections to the primary and replicated server. – (using rs_init)

Create replication definition

Create (and validate) publication

Create (and validate) subscription

Add table to replication - sp_setreptable FirstTable, true (without true/false – just display the current status)

The replicated table must have a primary key!

• In both sides – primary and replicated
• It’s part of the replication definition, and the replication won’t work without the PK.

2. Replication Agent - Rep Agent

Transactions to be replicated are extracted from the source database transaction log.

The component that do that is the RepAgent.

REP Agent is a component that scans the transaction log of the source database and picks up the changes for replication.

3. From Rep Agent to Distributer

SQM – Stable Queue Manager

• Write the changes to the queue.

In Bound Queue

• Store changes until they will be distributed.
• There is one inbound queue per primary database.
• contains data rows and transactions that have begun at the primary database but have no yet been committed (SQT will pass to the next steps only committed transactions).
• Transactions will be stored in IBQ until a copy will be distributed to all subscriptions (out-bound queue).

SQT – Stable Queue Transaction

• Alert the Distributer that changes are available.
• Move from the IBQ to the next steps only committed transactions

Distributer

The Distributer read transaction and determine who is subscribing to it.

Thread forward the transaction to the next SQM “on the way” to the out-bound Queue.

4. Distribute to target databases

SQM

A SQM for the out-bound queue - Writes the transaction to the out bound queue if there are subscription.

Out-bound Queue

There is one outbound queue for each RDB.

DSI / RSI

DSI / RSI “translate” between the source and target servers.

• DSI – Data Server Interface – the target is database.
• RSI – Replication Server Interface – the target is replication server.

SAP Replication Server Overview

Data Replication is a process that copying data from a database to another databases.

SAP Replication Server uses to get real-time data updates.

SAP RS can replicate data not only from/to ASE databases (also ORACLE, SQL SERVER, etc.).

SAP RS can be installed and run on Windows and Linux.

Replication is

• From a primary (= source) database [one primary database].
• To one or more replicate (= target) databases [one or more replicate databases].

RS can replicate one or more tables from the primary database. That means that not all the DB has to be full replicated, and in the other hand not only one object.

RS main terms

Replication definition

Describes the structure of what we replicate.

In order to publish and subscribe data, we first need to create a replication definition to designate the scope and location of the primary data.

In the Replication definition we declared exactly what we want to replicate:

• DML/DDL
• Which table/s
• What in the table:
• Columns
• Data (where salary > 1000 )
• etc.

Publication

We “publish” data at primary sites …

Subscription

… to which Replication Servers at other sites “subscribe.”

* Creating a replication definition does not, by itself, cause Replication Server to replicate data. 

We must also create a subscription against the replication definition to instruct Replication Server to replicate the data in another database.

LTM (LTM locator)

A “locator” value that is used to identify the last point in a transaction log from which all data has been successfully received by the primary Replication Server.

Related to the RepAgent.

Connection

Connection exists between RS and the databases it manage (RS databases - One Primary database, One or more Replicate databases).

A Replication thread DSI uses this connections to send updates to the replicate database.

RSSD – Replication Server System Database

Used for RS to save some definitions and configurations.

Can be in any SAP ASE instance (not must to be on the server where the RS is installed).

System tables include:

• Descriptions of replicated data and related information.
• Security records for Replication Server users.
• Routing information for other sites.
• Access methods for the local databases.
• Other administrative information.

sql.ini

Server Entries definitions.

In Linux OS is "interfaces" file instead of sql.ini.

SAP RS - Data is not replicated, although all definitions looks OK

What happened?

Data is not replicated, although all definitions looks OK.

Solution:

Can be more than one reason, try to check:

• Check definition of databases and servers – all those definitions can be very confusing… (names, IPs, etc. [sql.ini])
• Check PKs in both sides – as already mentioned above: primary and replicated
• Permissions issues
  • RS permissions – example: grant role replication_role to sa (RSSD database)
  • Primary DB
  • Replicated DB – example: GRANT INSERT, UPDATE, DELETE ON replicated table to the maintanance user of RS (_maint)
• Network and connections issues – permissions to inbound/outbound access to/from the servers.
  • don’t forget after fixing a problem to resume connection the server.database
• LTM is not set to the correct “locator” value.
  • rs_zeroltm, dbcc settrunc(ltm, valid)

Increase the width of isql output

In order to increase the width of isql output add -w2048 in the enf of the command (2048 is the width, it can be changed to ither number)

isql64 -S RSFOG2 -Usa -Pdba4ever -w2048