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)

SQL - select dates list between dates

DECLARE
@StartDate DATETIME, -- date and time
@Duration INT,
@ToDate DATETIME;

SELECT
        @StartDate = '2021-07-28 08:00:00.000',
@Duration = 60,
@ToDate = '2021-08-03 08:00:00.000';

DECLARE @l_Date DATETIME;
SET @l_Date = CAST (@StartDate AS DATE);


; WITH GetDates AS
(  
    SELECT @StartDate AS OccupiedDate, @l_Date AS CalDate
    UNION ALL  
    SELECT DATEADD(HOUR ,24, OccupiedDate) AS OccupiedDate, DATEADD(HOUR ,24, CalDate) AS CalDate
FROM GetDates  
    WHERE OccupiedDate < @ToDate  
)  
SELECT FROM GetDates;


; WITH GetDates AS
(  
SELECT @l_Date AS CalDate
UNION ALL  
SELECT DATEADD(HOUR ,24, CalDate) AS CalDate
FROM GetDates  
WHERE DATEADD(HOUR ,24, CalDate) < @ToDate  
)
SELECT FROM GetDates;

SSIS - get packages versions and data

SELECT prog.project_id, f.[name], *
FROM SSISDB.catalog.packages pkg
JOIN SSISDB.catalog.Projects prog ON pkg.project_id = prog.project_id
JOIN SSISDB.catalog.Folders f ON proj.folder_id = f.folder_id
WHERE prog.[name] = N'PROJECTNAME'

SQL Server: Check if Service Broker is enabled

SELECT name, database_id, is_broker_enabled
FROM sys.databases 
--WHERE name = 'DATABASENAME'
--WHERE database_id = 14

SSMS: sql server Script failed for StoredProcedure 'PROCEDURENAME' (Microsoft.SqlServer.Smo) ... Invalid version: ...

What I tried to do:
Alter Stored Procedure in SSMS.

Error:
sql server Script failed for StoredProcedure 'PROCEDURENAME' (Microsoft.SqlServer.Smo) ... Invalid version: ...

Solution: 
Update SSMS to a newer release.

gMSA and SQL Server

 Overview for gMSA - see in this post.


gMSA and SQL Server
 A single gMSA can be shared across multiple SQL Server hosts.
It helps to manage the service accounts in environments with a large number of SQL Server instances, without compromise on security issues.
Any update to an MSA password does not require a restart of SQL Server.
gMSA can be very interesting for an availability group environment
  • The main point of gMSA for AG is the setup of one managed service account for all replicas.
  • All instances in AG should be in the gMSA group.
  • gMSA for AG is only supported from SQL Server 2016.

Connecting gMSA with SQL Server
A connection between SQL Server to gMSA is created by assigning the gMSA account to the SQL Server service.
The connection is done:
  • For a new installation: During the SQL Server installation you specify the gMSA account.
  • For an existing SQL Server instance: changing the existing SQL Server instance to use a gMSA is done with the SQL Server Configuration Manager (SSCM) tool.

gMSA requirements
gMSA requirements - see here.
Additional requirements for gMSA for SQL Server:
SQL Server 2014 and above
SQL Server support for gMSA when running on Windows Server 2012 R2 and above.
Domain Functional Level of 2012 or higher.
Active Directory PowerShell module installed.

Useful links

gMSA - Group Managed Service Account

Overview

Standalone MSA (Managed Service Account) is a type of managed domain account created and managed by the domain controller:
  • It’s assigned to a single member computer for use running a service.
  • The password is managed automatically by the domain controller.
  • You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service.
  • including delegation of management to other administrators.

gMSA (Group Managed Service Account) is the same as MSA but for a group:
  • A group = multiple servers.
  • Windows manages a service account for services running on a group of servers.
  • gMSA provide a single identity solution for services running on a server farm, or on systems behind Network Load Balance.
  • Active Directory automatically updates the group managed service account password without restarting services.
  • The service accounts can be used for scheduled tasks, IIS application pools, SQL Server and Microsoft Exchange.
  • Because gMSA can be used with multiple machines, it allows you the flexibility to be able to implement Network Load Balancing (NLB). NLB allows you to group together servers and operate as one single system.

Windows PowerShell commands are used to administer gMSA.

gMSA requirements
Windows Server 2012 and above.
A 64-bit architecture (required to run the PowerShell commands).
Domain Functional Level of 2012 or higher.
A Key Distribution Service root key (KDS root key) has to be created and 10 hours are required for it to be replicated on all domain controllers.

Useful links

MySQL Cluster Overview

 MySQL Cluster Overview

In MySQL Cluster, there is typically no replication of data. It has only data node synchronization

MySQL Cluster is a shared-nothing architecture

  • As a result, no two components of the cluster will share the same hardware.
  • The cluster will be fully operational when at least one node is upon each data node group. As a result, the MySQL cluster avoids single point failure and ensures 99.99% availability.


Replication <--> Cluster

Replication

  • Master-Slave, one server is designated to act as the master.
  • (there is also master-master configuration).
  • Asynchronous - not all nodes have the freshest data at all time
  • No automatic failover

Cluster

  • Internally, the MySQL Cluster also uses synchronous replication.



Replication

Cluster

Asynchronous / synchronous

Asynchronoussynchronous
Automatic failover

No

(a slave has to be promoted to master)

Yes

Downtime when a master fails

YesNo




MySQL InnoDB Cluster

MySQL InnoDB Cluster provides high-availability and scaling features.

MySQL InnoDB Cluster consists of at least three MySQL Server instances.

MySQL InnoDB Cluster usually runs in a single-primary mode, with one primary instance (read-write) and multiple secondary instances (read-only).

  • Table creation and data insertion can be done only on the primary instance.
  • (Advanced users can also take advantage of a multi-primary mode, where all instances are primaries. This is not the common case).

MySQL InnoDB Cluster data nodes are actually a Group Replication.



InnoDB Cluster does not provide support for MySQL NDB Cluster.


MySQL NDB Cluster

A standard MySQL server does not support the MySQL Cluster engine NDB.

  • MySQL Cluster is tightly linked to the MySQL server, yet it’s a separate product.
  • This means we need to install the custom SQL server packaged.


MySQL Galera Cluster

  • Galera Cluster for MySQL is a true Multi-Master Cluster based on synchronous replication.
  • It is a multi-master database cluster that supports synchronous replication
  • https://galeracluster.com/

Different MySQL versions on the same Windows server

Linux

We can only have one version of MySQL setup on the machine using the default installation procedure with apt-get.

If we try to install one version over another, it will replace the first version and will retain the second version.


Windows

In Windows, we can install different MySQL versions on the same server using the installer:

  1. Install the first instance.
    1. Download MySQL Installer for windows and run it.

    2. Check and install requirements for MySQL products, Install MySQL, Configure MySQL Server, Set password for root account, (create another user), Create a service for MySQL Server, Apply configuration (= execution of all the above), Finish.
  2. Run MySQL Installer again, Press “Add” button and you will get all MySQL products list.
  3. Choose the MySQL version you need and move it to “Products to be installed” list.
  4. “Next” and move to other steps that similar to the regular installation: Check and install requirements, Download, Installation.
  5. After installation is finished, “Next” to configuration or if there is no “Next”, Select this version in the installation list and press “Reconfigure”.
  6. Configure MySQL Server: Set password for root account, (create another user), Create a service for MySQL Server, Apply configuration.
  7. Finish.
  8. Checks:
    1. The "C:\Program Files\MySQL" folder will have two “MySQL Server x.x” folders - one for each version.
    2. The " C:\ProgramData\MySQL " folder will have two data folders - one for each version.
    3. MySQL service should be created for each version.
  9. Enable Remote connection.



Multiple MySQL instances on one server

 Notes:

  • The process described here is for Linux OS.
  • We can only have one version of MySQL setup on the machine using default installation procedure with apt-get.
    • If we try to install one version over other, it will replace the first version and will retain the second version.

1. Create Data directory

1.1 Create a directory

mkdir -p /opt/second_server_data/


1.2 Initializing the directory

Initializing the directory

chmod --reference /var/lib/mysql /opt/second_server_data/
chown --reference /var/lib/mysql /opt/second_server_data/

Add rules to the policy file to stop the OS from restricting MySQL to access our newly created directoriesy, etc.

Edit “/etc/apparmor.d/usr.sbin.mysqld”, add these permissions (as the default data dir has).

sudo nano /etc/apparmor.d/usr.sbin.mysqld
 
Under “# Allow data dir access”:
/opt/second_server_data/ r,
/opt/second_server_data/** rwk,
 
Under “# Allow pid, socket, socket lock file access”:
/var/run/mysqld/second_server.pid rw,
/var/run/mysqld/second_server.sock rw,
/var/run/mysqld/second_server.sock.lock rw,
/run/mysqld/second_server.pid rw,
/run/mysqld/second_server.sock rw,
/run/mysqld/second_server.sock.lock rw,

Restart apparmor service

(AppArmor is a linux kernel security module that allows you to restrict program capabilities on a per-program basis)

sudo service apparmor restart


1.3 Initialize the created data dir as a MySQL data directory

mysqld --initialize --user=mysql --datadir=/opt/second_server_data/

Check the contents of the dir: “sudo ls -l /opt/second_server_data/”.

These files should be there (drwxr-x--- 2 mysql mysql …..) : mysql, performance_schema, sys

A default root password was generated and stored in “/var/log/mysql/error.log”

2020-12-17T08:45:06.170673Z 1 [Note] A temporary password is generated for root@localhost: 1hfBQ5G66d_V


2. Create a socket (and a TCP port) for a client

Given correct permissions MySQL is able to create these on its own.


3. MySQL configurations

* Steps 3-5 can be done also using mysqld or mysqld_safe.


start mysqld / mysqld_safe

sudo mysqld --no-defaults --datadir=/opt/second_server_data/ --port=3307 --socket=/var/run/mysqld/second_server.sock
  1. no-defaults: Makes sure no options are loaded from option files.
  2. data-dir: Path to the data directory that we created.
  3. port: TCP port.
  4. mysqlx: X plugin is an interface to allow MySQL function as a document store. Since we don’t need it, we will disable it.
  5. socket: Specify location of socket file

sudo mysqld_safe --no-defaults --datadir=/opt/second_server_data/ --port=3307 --socket=/var/run/mysqld/second_server.sock


4. Testing

mysql -h 127.0.0.1 -P 3307 -u root –p
--Or
mysql --socket=/var/run/mysqld/second_server.sock -u root –p
 
--Stop mysqld_safe using:
sudo mysqladmin -h 127.0.0.1 -P 3307 -u root -p shutdown

In order to perform SQL operations, the auto generated password should be changed:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'second_server';


5. Create cnf file for the second instance

5.1 Copy the cnf file and add to it:

[mysqld]
server-id = 2
port = 3307
socket = /var/run/mysqld/second_server.sock
datadir = /opt/second_server_data/
#mysqlx = 0

or

[mysqld_safe]
server-id = 2
port = 3307
socket = /var/run/mysqld/second_server.sock
datadir = /opt/second_server_data/
#mysqlx = 0


5.2 start mysqld/mysqld_safe with the new cnf

sudo mysqld --defaults-file=/etc/mysql/second_server.cnf

or

sudo mysqld_safe --defaults-file=/etc/mysql/second_server.cnf

5.3 Testing as before


6. Create a service for the new instance

Create a service using system

6.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


6.2 Start the service

Sudo systemctl start mysql_3307


6.3 Checking

systemctl status mysql_3307
 
journalctl -xe


6.* Stop the service (if required)

systemctl stop mysql_3307