SQL Server 2019 on Ubuntu Linux

-> SQL Server 2019 installation will be performed on a Ubuntu Azure Virtual Machine.

-> Provisioning an Ubuntu Server 18.04 LTS Azure Virtual machine.

Blog91_1

Blog91_2

Blog91_3

Blog91_4
-> NIC network security group will be set to None as this is a test instance. Appropriate configuration will be required for production server

Blog91_5

Blog91_6

Blog91_7

Blog91_8

Blog91_9

-> Install Putty from https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html to login to the provisioned Virtual Machine and start the SQL Server 2019 Install.

-> From Azure portal open the virtual machine provisioned and click on connect. Make sure SSH tab is selected. Public IP address in the SSH tab will be used in the putty session to connect to the provisioned Virtual Machine,

Blog91_10

Blog91_11

-> Below command can be used to import the public repository GPG keys

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

 

Blog91_12

-> Below command will be used to register the Microsoft SQL Server Ubuntu repository:

sudo add-apt-repository "$(wget -qO- "https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)"

Blog91_13

-> Below commands will be used to initiate the SQL Server 2019 install,

sudo apt-get update

Blog91_14

sudo apt-get install -y mssql-server

Blog91_15

sudo /opt/mssql/bin/mssql-conf setup

Blog91_16

In Putty, Developer Edition is selected.

Blog91_17

“License terms” should be accepted. SQL Server system administrator password should be provided. This will be the sa password.

Blog91_18

Blog91_19

-> Below command can be used to check the status of installed SQL Server,

systemctl status mssql-server --no-pager

Blog91_20

-> Open SQL Server Management Studio and connect to the Installed SQL Server using Public IP address,

Blog91_21

Blog91_22

-> All SQL Server configuration can be performed from SQL Server Management Studio.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

-> Client advised that they are trying to connect to a SQL Server named instance and getting below error,

Blog90_1

TITLE: Connect to Server
——————————
Cannot connect to JBAG1\IN01.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> Client advised that they were able to connect to a default instance that is installed on the same server.

-> Client also advised that there were no recent network changes in the database server.

-> I wanted to make sure if SQL Services was online. In my case it was online.

-> Then I checked if Remote connection was enabled from SQL Server Instance Properties -> Connection -> “Allow remote connections to this server”. It was checked.

-> Verified if “SQL Server Browser Services” was started. It was started.

-> Checked what protocols were enabled. In my case TCP\IP and shared memory was enabled. Checked further if TCP\IP is configured to use static or dynamic port.

TCP\IP was configured with a static port of 51233.

-> Verified if there was an “inbound Rule” to allow TCP port 51233 in the firewall. It was already created.

-> Checked if there were any bad alias created in SQL Server Configuration manager or cliconfg.exe. There were none.

-> I tried executing Telnet and understood that port 51233 is open. I also got the port scan output from the client and it showed that the port was open.

-> I tried to connect to the SQL Server named instance using Servername,port instead of Servername\Instancename. The connection was successful.

-> The connection doesn’t work only if I use Servername\Instancename. I checked further and then realized that someone have changed Hide Instance to Yes in configuration manager.

Blog90_2

Blog90_3

Hide Instance : SQL Server uses the SQL Server Browser service to enumerate instances of the Database Engine installed on the computer. This enables client applications to browse for a server, and helps clients distinguish between multiple instances of the Database Engine on the same computer. Setting Hide Instance to YES will prevent the SQL Server Browser service from exposing an instance of the Database Engine to client computers that try to locate the instance by using the Browse button. Setting Hide Instance to Yes is a security best practice, but can cause issue as detailed in this post.

-> I advised this to the client and changed “Hide Instance” Setting to No and the connection went through fine. Please note that this change doesn’t require a SQL Server restart.

-> Client advised that one of their DBA’s changed this recently after a security assessment of their SQL Server instance using SQL Server vulnerability assessment report from SQL Server Management studio. Advised them that they can perform below to connect to SQL Server named instance using “Hide Instance” set to Yes,

1) Change the application connection string from Servername\Hostname to Servername,Port.

2) Create a ALIAS in SQL Server configuration manager or cliconfg.exe.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Designing High Availability and Disaster Recovery using SQL Server Standard Edition

-> Option 1

Option1

Option 1 utilizes standalone database server JBSERVER1 and JBSERVER2 participating in a basic availability group using synchronous commit with automatic failover for high availability in primary datacentre. Logshipping is configured between Primary Replica in Primary datacentre to JBSERVER3 in secondary datacentre for disaster recovery. DNS alias should be configured and used in application connection string and should be modified to JBSERVER3 in case of a disaster recovery, there will be no changes required for failover in primary datacentre as Listener will be utilized.

It should be noted that in basic availability group each availability group can contain only one database. In an event of some databases failing over to secondary replica and other databases residing on primary replica can result in issues. You can workaround this by failing over all availability group in an event of single availability group failover using this article.

-> Option2

Option2

Option 2 utilizes SQL Server installed on a failover cluster with shared storage. JBSERVER1 and JBSERVER2 will be the physical nodes with a virtual SQL Server installed and configured for high availability. Basic availability group using Asynchronous commit with manual failover will be configured between the virtual SQL Server in Primary datacentre to JBSERVER3 in secondary datacentre. DNS alias should be configured and used in application connection string and should be modified to JBSERVER3 in case of a disaster recovery, there will be no changes required for failover in primary datacentre as virtual SQL Server name will be utilized.

-> Option 3

Option3

Option 2 utilizes SQL Server installed on a failover cluster with shared storage. JBSERVER1 and JBSERVER2 will be the physical nodes with a virtual SQL Server installed and configured for high availability. Logshipping is configured between the virtual SQL Server in Primary datacentre to JBSERVER3 in secondary datacentre for disaster recovery. DNS alias should be configured and used in application connection string and should be modified to JBSERVER3 in case of a disaster recovery, there will be no changes required for failover in primary datacentre as virtual SQL Server name will be utilized.

It is prudent to test high availability and disaster recovery to make sure how long does it take and if it is acceptable to your business. It is mandatory that the design can achieve the required RTO and RPO.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.