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)

Linux - Create a service for the new instance - using systemd

How to create a service for the new instance - using systems?

In this example: MySQL service.

1. Create “.service” file
Save in “/lib/systemd/system/” or “/etc/systemd/system”

touch mysql_3307.service
vi mysql_3307.service

 
[Unit]
Description=MySQL 3307 service
After=network.target
 
[Service]
Type=simple
Restart=always
User=mysql
Group=mysql
ExecStart=/etc/mysql/sudo mysqld --defaults-file=/etc/mysql/second_server.cnf
TimeoutSec=600
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
LimitNOFILE=5000
 
[Install]
WantedBy=multi-user.target

2. Start the service
Sudo systemctl start mysql_3307


3. Checking
systemctl status mysql_3307
 journalctl -xe


* Stop the service (if required)
systemctl stop mysql_3307

MySQL - Can not connect to the database

Error message:

ERROR 2003 (HY000): Can't connect to MySQL server on '....' (111).


Solution:

  1. Check connection details:
    1. Verify that the IP is correct.
    2. Verify that the Port number is correct.
    3. Check username and password.
  2. Network issues:
    1. Check that connection to VPN or any other connection types.
    2. Check Firewall definition.
    3. For cloud instances: check that your IP is allowed.
  3. Check that MySQL is up:
    1. Check that the MySQL service is up:
      1. Windows: in the services.
      2. Linux: sudo systemctl start <SERVICENAME>
  4. MySQL configurations:
    1. Max connections - see here: http://copypastenet.blogspot.com/2021/02/mysql-see-orand-change-how-many.html 
    2. cnf file configurations.

MySQL - see or/and change how many connections can connect to a MySQL instance

 # see the value:
show global variables like 'max_connections';

# Change the value:
SET GLOBAL max_connections = 250;
or in the cnf file property "max_connections".

SQL Server Availability Group – simulate a failover

  1. Set one of the secondaries as an aSynchronic.
  2. Suspend data movement in this secondary.
  3. Insert some data in the primary.
  4. Resume data movement.
  5. Set the secondary as a Synchronic.
















SELECT 
    ar.replica_server_name, 
    adc.database_name, 
    ag.name AS ag_name, 
    dhdrs.synchronization_state_desc, 
    dhdrs.is_commit_participant, 
    dhdrs.last_sent_lsn, 
    dhdrs.last_sent_time, 
    dhdrs.last_received_lsn, 
    dhdrs.last_hardened_lsn, 
    dhdrs.last_redone_time
FROM sys.dm_hadr_database_replica_states AS dhdrs
INNER JOIN sys.availability_databases_cluster AS adc 
    ON dhdrs.group_id = adc.group_id AND 
    dhdrs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = dhdrs.group_id
INNER JOIN sys.availability_replicas AS ar 
    ON dhdrs.group_id = ar.group_id AND 
    dhdrs.replica_id = ar.replica_id