Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

SAP (sybase) RS: Permission error while using rs_init

Error message:

Permission denided. You must either be the database/object owner or process any of the following roles in order to perform this operation: sa_role, replication_role
failed to execute 'dbcc is_replication_enabled' as user RS1_RSSD_prim

Solution:
Grant required roles to the relevant users:

use RS1_RSSD
go
grant role sa_role to RS1_RSSD_prim
go
grant role replication_role to RS1_RSSD_prim
go

SAP (sybase) RS: RSI USER become Inactive

Problem:
Replications via the routing don't work and RSI  is inactive (checking with"admin who_is_down") 

1> admin who_is_down
2> go
Spid     Name   State    Info
------- -------   ------- -------
            RSI       Down   RS2

Solution:
Resume route:
1> resume route to RS2
2> go
Route to 'RS2' is resumed.

Check that nothing is down:
1> admin who_is_down
2> go
Spid     Name   State    Info
------- -------   ------- -------

1>

Enabling Password Encryption for SAP (Sybase) RS

Encrypted Password RS can be set in 2 ways:
  1. RS that it's RSSD is set with encrypted password.
  2. RS that is itself set with encrypted password (Encrypted password RS).

RS that its RSSD is set with encrypted password

This configuration means that:
  1. The RS that itself is not set with encrypted password.
  2. The RSSD of the RS is set with encrypted password (more correct: the ASE instance where the RSSD is stored is set with encrypted password).

On the RS, we need to set "send_enc_password” to 'on' - from RS or in the configuration file.

From RS:
  1. login RS.
  2. configure “replication server set send_enc_password"
  3. Restart RS
1> configure replication server set send_enc_password to 'on'
2> go

From the configuration file:
  1. Set the configuration parameter "RS_send_enc_pw" to “on” in the rs_name.cfg file.
  2. Restart RS.

In order to make routing works, It has to be done in the ‘source’ RS of the routing and in any other RS that connects to the “encrypted password” ASE instance.


Encrypted password RS

By default, RS is passwords encrypted.
When installing a new RS - it uses password encryption.

Verify that password encryption is enabled in RS

There are few checks in order to verify that password encryption is enabled or not for an exists RS system.

1. Check the password_encryption configuration

select objid, optionname, charvalue=substring(charvalue,1,25) from rs_config where optionname = 'password_encryption'
go

2. Check these parameters in the configuration file and see that they are encrypted: RSSD_primary_pw_enc, RSSD_maint_pw_enc, ID_pw_enc, RS_random.


Manage password encryption in RS

If your RS environment is not set with password encryption (probably old installation), you can enable it using rs_init.
The process is described in the following chart and in SAP site (Enabling password encryption for a Replication Server).

Altering an encrypted password in RS can be done using rs_init, as described in SAP site: Altering an encrypted password for a Replication Server.


More queries:

1> use YOUR_RSSD
2> go
1> select username, uid, use_enc_password, enc_password from rs_users
2> go

1> use YOUR_RSSD
2> go
1> select * from rs_encryptionkeys
2> go



Can I disable password encryption for RS?
Password encryption can be disabled for RS, but then the passwords would be exposed in the RSSD and SRS cfg file.

It looks like there is no good reason to do that.

Disabling Password Encryption:
  1. Set password_encryption to '0'
  2. Change existing passwords in the RSSD tables to clear text
    1. Change in rs_users and rs_maintusers tables.
    2. There is no way to decrypt the passwords listed in the rs_users and rs_maintusers.
    3. Changing passwords with the alter user SRS command for SRS users, or the alter connection command for the maintenance users.
    4. Once you change the password on a connection for a maintenance user, you will need to change it at the replicate dataserver and resume the connection.
  3. Change existing passwords in the Replication Server configuration file: manually reenter, in clear text, passwords that are currently encrypted.
  4. Restart RS.

configure replication server set password_encryption to '0'
go



Enabling Password Encryption for SAP ASE (Sybase)

Enabling Password Encryption for SAP ASE is done by the enabling “net password encryption reqd” on the ASE server.
  1. Set 'net password encryption reqd' = 1 (or 2, check the values on SAP site)
  2. Restart ASE.
sp_configure 'net password encryption reqd', 1
go


In order to connect this ASE, you need an encryption of the login password.

For example, in isql you need to add the "-X", that Initiates the login connection to the server with client-side password encryption.



Also for connections from RSs, Replications to this server can’t be replicated until we set the connection to it from the other RS (“send_enc_password” parameter, see here).


CMD - Displays and uses all commands you did

Maybe better than copy-paste for CMD :)

In Windows Command Prompt, F7 displays all commands you did before, so you can use it again without typing again - just go up and down with the arrays and press Enter when you are on the command you need.


SAP RS - RSI USER become Inactive

Error:
RSI USER become Inactive .
Replications via the routing don't work and RSI  is inactive (checking with"admin who_is_down") 

1> admin who_is_down
2> go
Spid     Name    State    Info
-------   -------      -------    -------
            RSI        Down   RS2

Solution:
Resume route:

1> resume route to RS2
2> go
Route to 'RS2' is resumed.

Check:
1> admin who_is_down
2> go
Spid     Name    State    Info
-------   -------      -------    -------
1> 

SQL Server - delete backup and restore information

Deletion of backup and restore information from the backup and restore history tables can be done in 2 ways:

1. Deletes backup and restore information about a specified database.
All the information regarding the specific database will be deleted.
No choice to delete between dates.

USE msdb;
GO
exec sp_delete_database_backuphistory @database_name = 'DATABASENAME';
GO

2. Deletes backup and restore information older than a specified date.
Can't be run for one or more specific databases.
Will affect all databases.

USE msdb;
GO  
EXEC sp_delete_backuphistory @oldest_date = '2015-01-01'; 
GO


Remarks:
1. The SPS must be run from msdb database.
2. The SPS affects the following tables:
  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory
Check the deletion by execute a select on one of them:
SELECT * FROM msdb.dbo.backupset;

3. The physical backup files are preserved.