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

Using Group Managed Service Accounts with SQL Server

Configure the SQL Server instance to use the gMSA

Managed Service Accounts with SQL Server

Using a gMSA with SQL Server

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

Microsoft: Configure Windows Service Accounts and Permissions (--> Managed Service Accounts, Group Managed Service Accounts, and Virtual Accounts)

Microsoft: Group Managed Service Accounts Overview

Getting Started with Group Managed Service Accounts (for gMSA requirements).

Creating and Associating A Group Managed Service Account

Step-by-Step Guide to work with gMSA - PowerShell Guide

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	Asynchronous	synchronous
Automatic failover	No (a slave has to be promoted to master)	Yes
Downtime when a master fails	Yes	No

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