Troubleshoot SQL Server on Linux

This defines how to troubleshoot Microsoft SQL Server running on Linux or in a Docker container. When troubleshooting SQL Server on Linux, remember to review the supported features and known limitations.

Troubleshoot connection failures

If you are having difficulty connecting to your Linux SQL Server, there are a few things to check.

  • If you are unable to connect locally using localhost, try using the IP address 127.0.0.1 instead. It is possible that localhost is not properly mapped to this address.
  • Verify that the server name or IP address is reachable from your client machine.

Bash

sudo ifconfig eth0 | grep ‘inet addr’

For Red Hat, you can use the ip addr as in the following example:

Bash

sudo ip addr show eth0 | grep “inet”

One exception to this technique relates to Azure VMs. For Azure VMs, find the public IP for the VM in the Azure portal.

  • If applicable, check that you have opened the SQL Server port (default 1433) on the firewall.
  • For Azure VMs, check that you have a network security group rule for the default SQL Server port.
  • Verify that the user name and password do not contain any typos or extra spaces or incorrect casing.
  • Try to explicitly set the protocol and port number with the server name like the following example: tcp:servername,1433.
  • Network connectivity issues can also cause connection errors and timeouts. After verifying your connection information and network connectivity, try the connection again.

Manage the SQL Server service

The following sections show how to start, stop, restart, and check the status of the SQL Server service.

Manage the mssql-server service in Red Hat Enterprise Linux (RHEL) and Ubuntu

Check the status of the SQL Server service using this command:

Bash

sudo systemctl status mssql-server

You can stop, start, or restart the SQL Server service as needed using the following commands:

Bash

sudo systemctl stop mssql-server

sudo systemctl start mssql-server

sudo systemctl restart mssql-server

Manage the execution of the mssql Docker container

You can get the status and container ID of the latest created SQL Server Docker container by running the following command (The ID is under the CONTAINER ID column):

Bash

sudo docker ps -l

You can stop or restart the SQL Server service as needed using the following commands:

Bash

sudo docker stop <container ID>

sudo docker restart <container ID>

Access the log files

The SQL Server engine logs to the /var/opt/mssql/log/errorlog file in both the Linux and Docker installations. You need to be in ‘superuser’ mode to browse this directory.

The installer logs here: /var/opt/mssql/setup-< time stamp representing time of install> You can browse the errorlog files with any UTF-16 compatible tool like ‘vim’ or ‘cat’ like this:

Bash

sudo cat errorlog

If you prefer, you can also convert the files to UTF-8 to read them with ‘more’ or ‘less’ with the following command:

Bash

sudo iconv -f UTF-16LE -t UTF-8 <errorlog> -o <output errorlog file>

Crash dumps

Look for dumps in the log directory in Linux. Check under the /var/opt/mssql/log directory for Linux Core dumps (.tar.gz2 extension) or SQL minidumps (.mdmp extension)

For Core dumps

Bash

sudo ls /var/opt/mssql/log | grep .tar.gz2

For SQL dumps

Bash

sudo ls /var/opt/mssql/log | grep .mdmp

Start SQL Server in Minimal Configuration or in Single User Mode

Start SQL Server in Minimal Configuration Mode

This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting.

Bash

sudo -u mssql /opt/mssql/bin/sqlservr -f

Start SQL Server in Single User Mode

Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. For example, you may want to change server configuration options or recover a damaged master database or other system database

Start SQL Server in Single User Mode

Bash

sudo -u mssql /opt/mssql/bin/sqlservr -m

Start SQL Server in Single User Mode with SQLCMD

Bash

sudo -u mssql /opt/mssql/bin/sqlservr -m SQLCMD

If you have accidentally started SQL Server with another user, you must change ownership of SQL Server database files back to the ‘mssql’ user prior to starting SQL Server with systemd. For example, to change ownership of all database files under /var/opt/mssql to the ‘mssql’ user, run the following command

Bash

chown -R mssql:mssql /var/opt/mssql/

Rebuild system databases

As a last resort, you can choose to rebuild the master and model databases back to default versions.

  1. Stop SQL Server.

Bash

sudo systemctl stop mssql-server

  • Run sqlservr with the force-setup parameter.

Bash

sudo -u mssql /opt/mssql/bin/sqlservr –force-setup

 Warning

See the previous warning! Also, you must run this as the mssql user as shown here.

  • After you see the message “Recovery is complete”, press CTRL+C. This will shut down SQL Server
  • Reconfigure the SA password.

Bash

sudo /opt/mssql/bin/mssql-conf set-sa-password

  • Start SQL Server and reconfigure the server. This includes restoring or re-attaching any user databases.

Bash

sudo systemctl start mssql-server

Improve performance

There are many factors that affect performance, including database design, hardware, and workload demands. Then explore some of the available tools for troubleshooting performance problems.

Common issues

  1. You cannot connect to your remote SQL Server instance.
  2. ERROR: Hostname must be 15 characters or less.

This is a known-issue that happens whenever the name of the machine that is trying to install the SQL Server Debian package is longer than 15 characters. There are currently no workarounds other than changing the name of the machine. One way to achieve this is by editing the hostname file and rebooting the machine.

  • Resetting the system administration (SA) password.

If you have forgotten the system administrator (SA) password or need to reset it for some other reason, follow these steps.

Bash

sudo systemctl stop mssql-server

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

  • Using special characters in password.

If you use some characters in the SQL Server login password, you might need to escape them with a backslash when you use them in a Linux command in the terminal. For example, you must escape the dollar sign ($) anytime you use it in a terminal command/shell script:

Does not work:

Bash

sudo sqlcmd -S myserver -U sa -P Test$$

Works:

Bash

sqlcmd -S myserver -U sa -P Test\$\$

Configure a SQL Server Availability Group for read-scale on Linux

You can configure a SQL Server Always On Availability Group (AG) for read-scale workloads on Linux. There are two types of architectures for AGs. A architecture for high availability uses a cluster manager to provide improved business continuity. This architecture also can include read-scale replicas. The other architecture supports only read-scale workloads. This article explains how to create an AG without a cluster manager for read-scale workloads. This architecture provides read-scale only. It doesn’t provide high availability.

Before you create the availability group, you need to:

  • Set your environment so that all the servers that will host availability replicas can communicate.
  • Install SQL Server.

On Linux, you must create an availability group before you add it as a cluster resource to be managed by the cluster. This document provides an example that creates the availability group. For distribution-specific instructions to create the cluster and add the availability group as a cluster resource, see the links under “Next steps.”

  1. Update the computer name for each host.

Each SQL Server name must be:

  • 15 characters or less.
    • Unique within the network.

To set the computer name, edit /etc/hostname. The following script lets you edit /etc/hostname with vi:

Bash

sudo vi /etc/hostname

  • Configure the hosts file.

The hosts file on every server contains the IP addresses and names of all servers that will participate in the availability group.

The following command returns the IP address of the current server:

Bash

sudo ip addr show

Update /etc/hosts. The following script lets you edit /etc/hosts with vi:

Bash

sudo vi /etc/hosts

The following example shows /etc/hosts on node1 with additions for node1node2, and node3. In this document, node1 refers to the server that hosts the primary replica. And node2 and node3 refer to servers that host the secondary replicas.

127.0.0.1   localhost localhost4 localhost4.localdomain4

::1       localhost localhost6 localhost6.localdomain6

10.128.18.12 node1

10.128.16.77 node2

10.128.15.33 node3

Enable AlwaysOn availability groups and restart mssql-server

Enable AlwaysOn availability groups on each node that hosts a SQL Server instance. Then restart mssql-server. Run the following script:

Bash

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1

sudo systemctl restart mssql-server

Enable an AlwaysOn_health event session

You can optionally enable AlwaysOn availability groups extended events to help with root-cause diagnosis when you troubleshoot an availability group. Run the following command on each instance of SQL Server:

SQL

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);

GO

For more information about this XE session, see Always On extended events.

Create a certificate

The SQL Server service on Linux uses certificates to authenticate communication between the mirroring endpoints.

The following Transact-SQL script creates a master key and a certificate. It then backs up the certificate and secures the file with a private key. Update the script with strong passwords. Connect to the primary SQL Server instance. To create the certificate, run the following Transact-SQL script:

SQL

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘**<Master_Key_Password>**’;

CREATE CERTIFICATE dbm_certificate WITH SUBJECT = ‘dbm’;

BACKUP CERTIFICATE dbm_certificate

   TO FILE = ‘/var/opt/mssql/data/dbm_certificate.cer’

   WITH PRIVATE KEY (

           FILE = ‘/var/opt/mssql/data/dbm_certificate.pvk’,

           ENCRYPTION BY PASSWORD = ‘**<Private_Key_Password>**’

       );

At this point, your primary SQL Server replica has a certificate at /var/opt/mssql/data/dbm_certificate.cer and a private key at var/opt/mssql/data/dbm_certificate.pvk. Copy these two files to the same location on all servers that will host availability replicas. Use the mssql user, or give permission to the mssql user to access these files.

For example, on the source server, the following command copies the files to the target machine. Replace the **<node2>** values with the names of the SQL Server instances that will host the replicas.

Bash

cd /var/opt/mssql/data

scp dbm_certificate.* [email protected]**<node2>**:/var/opt/mssql/data/

On each target server, give permission to the mssql user to access the certificate.

Bash

cd /var/opt/mssql/data

chown mssql:mssql dbm_certificate.*

Create the certificate on secondary servers

The following Transact-SQL script creates a master key and a certificate from the backup that you created on the primary SQL Server replica. Update the script with strong passwords. The decryption password is the same password that you used to create the .pvk file in a previous step. To create the certificate, run the following script on all secondary servers:

SQL

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘**<Master_Key_Password>**’;

CREATE CERTIFICATE dbm_certificate

    FROM FILE = ‘/var/opt/mssql/data/dbm_certificate.cer’

    WITH PRIVATE KEY (

    FILE = ‘/var/opt/mssql/data/dbm_certificate.pvk’,

    DECRYPTION BY PASSWORD = ‘**<Private_Key_Password>**’

            );

Create the database mirroring endpoints on all replicas

Database mirroring endpoints use the Transmission Control Protocol (TCP) to send and receive messages between the server instances that participate in database mirroring sessions or host availability replicas. The database mirroring endpoint listens on a unique TCP port number.

The following Transact-SQL script creates a listening endpoint named Hadr_endpoint for the availability group. It starts the endpoint and gives connection permission to the certificate that you created. Before you run the script, replace the values between **< … >**. Optionally you can include an IP address LISTENER_IP = (0.0.0.0). The listener IP address must be an IPv4 address. You can also use 0.0.0.0.

Update the following Transact-SQL script for your environment on all SQL Server instances:

SQL

CREATE ENDPOINT [Hadr_endpoint]

    AS TCP (LISTENER_PORT = **<5022>**)

    FOR DATABASE_MIRRORING (

                ROLE = ALL,

                AUTHENTICATION = CERTIFICATE dbm_certificate,

                        ENCRYPTION = REQUIRED ALGORITHM AES

                        );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

SQL

CREATE ENDPOINT [Hadr_endpoint]

    AS TCP (LISTENER_PORT = **<5022>**)

    FOR DATABASE_MIRRORING (

                ROLE = WITNESS,

                AUTHENTICATION = CERTIFICATE dbm_certificate,

                        ENCRYPTION = REQUIRED ALGORITHM AES

                        );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

The TCP port on the firewall must be open for the listener port.

Create the AG

Create the AG. Set CLUSTER_TYPE = NONE. In addition, set each replica with FAILOVER_MODE = MANUAL. Client applications running analytics or reporting workloads can directly connect to the secondary databases. You also can create a read-only routing list. Connections to the primary replica forward read connection requests to each of the secondary replicas from the routing list in a round-robin fashion.

The following Transact-SQL script creates an AG named ag1. The script configures the AG replicas with SEEDING_MODE = AUTOMATIC. This setting causes SQL Server to automatically create the database on each secondary server after it is added to the AG. Update the following script for your environment. Replace the <node1> and <node2> values with the names of the SQL Server instances that host the replicas. Replace the <5022> value with the port you set for the endpoint. Run the following Transact-SQL script on the primary SQL Server replica:

SQL

CREATE AVAILABILITY GROUP [ag1]

    WITH (CLUSTER_TYPE = NONE)

    FOR REPLICA ON

        N'<node1>’ WITH (

            ENDPOINT_URL = N’tcp://<node1>:<5022>’,

                            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

                            FAILOVER_MODE = MANUAL,

                            SEEDING_MODE = AUTOMATIC,

                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

                            ),

        N'<node2>’ WITH (

                            ENDPOINT_URL = N’tcp://<node2>:<5022>’,

                            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

                            FAILOVER_MODE = MANUAL,

                            SEEDING_MODE = AUTOMATIC,

                            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

                            );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Join secondary SQL Servers to the AG

The following Transact-SQL script joins a server to an AG named ag1. Update the script for your environment. On each secondary SQL Server replica, run the following Transact-SQL script to join the AG:

SQL

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Add a database to the availability group

Ensure that the database you add to the availability group is in full recovery mode and has a valid log backup. If this is a test database or a newly created database, take a database backup. On the primary SQL Server, run the following Transact-SQL script to create and back up a database called db1:

SQL

CREATE DATABASE [db1];

ALTER DATABASE [db1] SET RECOVERY FULL;

BACKUP DATABASE [db1]

   TO DISK = N’/var/opt/mssql/data/db1.bak’;

On the primary SQL Server replica, run the following Transact-SQL script to add a database called db1 to an availability group called ag1:

SQL

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Verify that the database is created on the secondary servers

On each secondary SQL Server replica, run the following query to see if the db1 database was created and is synchronized:

SQL

SELECT * FROM sys.databases WHERE name = ‘db1’;

GO

SELECT DB_NAME(database_id) AS ‘database’, synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

This AG isn’t a high-availability configuration. If you need high availability, follow the instructions at Configure an Always On Availability Group for SQL Server on Linux. Specifically, create the AG with CLUSTER_TYPE=WSFC (in Windows) or CLUSTER_TYPE=EXTERNAL (in Linux). Then integrate with a cluster manager by using either Windows Server failover clustering on Windows or Pacemaker on Linux.

Fail over the primary replica on a read-scale Availability Group

Each availability group has only one primary replica. The primary replica allows reads and writes. To change which replica is primary, you can fail over. In an availability group for high availability, the cluster manager automates the failover process. In an availability group with cluster type NONE, the failover process is manual.

There are two ways to fail over the primary replica in an availability group with cluster type NONE:

  • Forced manual failover with data loss
  • Manual failover without data loss

Forced manual failover with data loss

Use this method when the primary replica isn’t available and can’t be recovered.

To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and then run the following command:

SQL

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

When the previous primary replica recovers, it will also assume the primary role. To ensure that the previous primary replica transitions into a secondary role run the following command on the previous primary replica.

SQL

ALTER AVAILABILITY GROUP [ag1]  SET (ROLE = SECONDARY);

Manual failover without data loss

Use this method when the primary replica is available, but you need to temporarily or permanently change the configuration and change the SQL Server instance that hosts the primary replica. To avoid potential data loss, before you issue the manual failover, ensure that the target secondary replica is up to date.

To manually fail over without data loss:

  1. Make the current primary and target secondary replica SYNCHRONOUS_COMMIT.

SQL

ALTER AVAILABILITY GROUP [ag1]

     MODIFY REPLICA ON N'<node2>’

     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

  • To identify that active transactions are committed to the primary replica and at least one synchronous secondary replica, run the following query:

SQL

SELECT ag.name,

   drs.database_id,

   drs.group_id,

   drs.replica_id,

   drs.synchronization_state_desc,

   ag.sequence_number

FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag

WHERE drs.group_id = ag.group_id;

The secondary replica is synchronized when synchronization_state_desc is SYNCHRONIZED.

  • Update REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.

The following script sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1 on an availability group named ag1. Before you run the following script, replace ag1 with the name of your availability group:

SQL

ALTER AVAILABILITY GROUP [ag1]

     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

This setting ensures that every active transaction is committed to the primary replica and at least one synchronous secondary replica.

  • Offline the primary replica in preparation for role changes.

SQL

ALTER AVAILABILITY GROUP [ag1] OFFLINE

  • Promote the target secondary replica to primary.

SQL

ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS;

  • Update the role of the old primary to SECONDARY, run the following command on the SQL Server instance that hosts the old primary replica:

SQL

ALTER AVAILABILITY GROUP [ag1]

     SET (ROLE = SECONDARY);

  • Resume data movement, run the following command for every database in the availability group on the SQL Server instance that hosts the primary replica:

SQL

ALTER DATABASE [db1]

     SET HADR RESUME

  • Re-create any listener you created for read-scale purposes and that isn’t managed by a cluster manager. If the original listener points to the old primary, drop it and re-create it to point to the new primary.

Configure SQL Server Always On Availability Group on Windows and Linux (cross-platform)

This explains the steps to create an Always On Availability Group (AG) with one replica on a Windows server and the other replica on a Linux server. This configuration is cross-platform because the replicas are on different operating systems. Use this configuration for migration from one platform to the other or disaster recovery (DR). This configuration does not support high-availability because there is no cluster solution to manage a cross-platform configuration.

Before proceeding, you should be familiar with installation and configuration for SQL Server instances on Windows and Linux.

Scenario

In this scenario, two servers are on different operating systems. A Windows Server 2016 named WinSQLInstance hosts the primary replica. A Linux server named LinuxSQLInstance host the secondary replica.

Configure the AG

The steps to create the AG are the same as the steps to create an AG for read-scale workloads. The AG cluster type is NONE, because there is no cluster manager.

For the scripts in this article, angle brackets < and > identify values that you need to replace for your environment. The angle brackets themselves are not required for the scripts.

  1. Install SQL Server 2017 on Windows Server 2016, enable Always On Availability Groups from SQL Server Configuration Manager, and set mixed mode authentication.

 Enable Availability Groups

SQL Server Configuration Manager notes that the computer is not a node in a failover cluster.

After you enable Availability Groups, restart SQL Server.

Set mixed mode authentication

  • Install SQL Server 2017 on Linux. For instructions.

To enable hadr via mssql-conf from a shell prompt, issue the following command:

Bash

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1

After you enable hadr, restart the SQL Server instance.

The following image shows this complete step.

  • Configure hosts file on both servers or register the server names with DNS.
  • Open up firewall ports for TPC 1433 and 5022 on both Windows and Linux.
  • On the primary replica, create a database login and password.

SQL

CREATE LOGIN dbm_login WITH PASSWORD = ‘<[email protected]!>’;

CREATE USER dbm_user FOR LOGIN dbm_login;

GO

  • On the primary replica, create a master key and certificate, then back up the certificate with a private key.

SQL

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<[email protected]!>’;

CREATE CERTIFICATE dbm_certificate WITH SUBJECT = ‘dbm’;

BACKUP CERTIFICATE dbm_certificate

TO FILE = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer’

WITH PRIVATE KEY (

        FILE = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk’,

        ENCRYPTION BY PASSWORD = ‘<[email protected]!>’

    );

GO

  • Copy the certificate and private key to the Linux server (secondary replica) at /var/opt/mssql/data. You can use pscp to copy the files to the Linux server.
  • Set the group and ownership of the private key and the certificate to mssql:mssql.

The following script sets the group and ownership of the files.

Bash

sudo chown mssql:mssql /var/opt/mssql/data/dbm_certificate.pvk

sudo chown mssql:mssql /var/opt/mssql/data/dbm_certificate.cer

In the following diagram, ownership and group are set correctly for the certificate and key.

  • On the secondary replica, create a database login and password and create a master key.

SQL

CREATE LOGIN dbm_login WITH PASSWORD = ‘<[email protected]!>’;

CREATE USER dbm_user FOR LOGIN dbm_login;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<[email protected]@55w0rD!>’

GO

  1. On the secondary replica, restore the certificate you copied to /var/opt/mssql/data.

SQL

CREATE CERTIFICATE dbm_certificate  

    AUTHORIZATION dbm_user

    FROM FILE = ‘/var/opt/mssql/data/dbm_certificate.cer’

    WITH PRIVATE KEY (

    FILE = ‘/var/opt/mssql/data/dbm_certificate.pvk’,

    DECRYPTION BY PASSWORD = ‘<[email protected]!>’

)

GO

  1. On the primary replica, create an endpoint.

SQL

CREATE ENDPOINT [Hadr_endpoint]

    AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)

    FOR DATA_MIRRORING (

        ROLE = ALL,

        AUTHENTICATION = CERTIFICATE dbm_certificate,

        ENCRYPTION = REQUIRED ALGORITHM AES

        );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login]

GO

 Important

The firewall must be open for the listener TCP port. In the preceding script, the port is 5022. Use any available TCP port.

  1. On the secondary replica, create the endpoint. Repeat the preceding script on the secondary replica to create the endpoint.
  2. On the primary replica, create the AG with CLUSTER_TYPE = NONE. The example script uses SEEDING_MODE = AUTOMATIC to create the AG.

Before you run the script, update the values for your AGs.

  • Replace <WinSQLInstance> with the server name of the primary replica SQL Server instance.
    • Replace <LinuxSQLInstance> with the server name of the secondary replica SQL Server instance.

To create the AG, update the values and run the script on the primary replica.

SQL

CREATE AVAILABILITY GROUP [ag1]

    WITH (CLUSTER_TYPE = NONE)

    FOR REPLICA ON

        N'<WinSQLInstance>’

               WITH (

        ENDPOINT_URL = N’tcp://<WinSQLInstance>:5022′,

        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

                   SEEDING_MODE = AUTOMATIC,

               FAILOVER_MODE = MANUAL,

      SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

                           ),

        N'<LinuxSQLInstance>’

    WITH (

                   ENDPOINT_URL = N’tcp://<LinuxSQLInstance>:5022′,

        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

        SEEDING_MODE = AUTOMATIC,

      FAILOVER_MODE = MANUAL,

      SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)

        )

GO

  1. On the secondary replica, join the AG.

SQL

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE)

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE

GO

  1. Create a database for the AG. The example steps use a database named <TestDB>. If you are using automatic seeding, set the same path for both the data and the log files.

Before you run the script, update the values for your database.

  • Replace <TestDB> with the name of your database.
    • Replace <F:\Path> with the path for your database and log files. Use the same path for the database and log files.

You can also use the default paths.

To create your database, run the script.

SQL

CREATE DATABASE [<TestDB>]

   CONTAINMENT = NONE

  ON  PRIMARY ( NAME = N'<TestDB>’, FILENAME = N'<F:\Path>\<TestDB>.mdf’)

  LOG ON ( NAME = N'<TestDB>_log’, FILENAME = N'<F:\Path>\<TestDB>_log.ldf’)

GO

  1. Take a full backup of the database.
  2. If you are not using automatic seeding, restore the database on the secondary replica (Linux) server. Migrate a SQL Server database from Windows to Linux using backup and restore. Restore the database WITH NORECOVERY on the secondary replica.
  3. Add the database to the AG. Update the example script. Replace <TestDB> with the name of your database. On the primary replica, run the SQL query to add the database to the AG.

SQL

ALTER AG [ag1] ADD DATABASE <TestDB>

GO

  1. Verify that the database is getting populated on the secondary replica.

Fail over the primary replica

Each availability group has only one primary replica. The primary replica allows reads and writes. To change which replica is primary, you can fail over. In an availability group for high availability, the cluster manager automates the failover process. In an availability group with cluster type NONE, the failover process is manual.

There are two ways to fail over the primary replica in an availability group with cluster type NONE:

  • Forced manual failover with data loss
  • Manual failover without data loss

Forced manual failover with data loss

Use this method when the primary replica isn’t available and can’t be recovered.

To force failover with data loss, connect to the SQL Server instance that hosts the target secondary replica and then run the following command:

SQL

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

When the previous primary replica recovers, it will also assume the primary role. To ensure that the previous primary replica transitions into a secondary role run the following command on the previous primary replica.

SQL

ALTER AVAILABILITY GROUP [ag1]  SET (ROLE = SECONDARY);

Manual failover without data loss

Use this method when the primary replica is available, but you need to temporarily or permanently change the configuration and change the SQL Server instance that hosts the primary replica. To avoid potential data loss, before you issue the manual failover, ensure that the target secondary replica is up to date.

To manually fail over without data loss:

  1. Make the current primary and target secondary replica SYNCHRONOUS_COMMIT.

SQL

ALTER AVAILABILITY GROUP [ag1]

     MODIFY REPLICA ON N'<node2>’

     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

  • To identify that active transactions are committed to the primary replica and at least one synchronous secondary replica, run the following query:

SQL

SELECT ag.name,

   drs.database_id,

   drs.group_id,

   drs.replica_id,

   drs.synchronization_state_desc,

   ag.sequence_number

FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag

WHERE drs.group_id = ag.group_id;

The secondary replica is synchronized when synchronization_state_desc is SYNCHRONIZED.

  • Update REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1.

The following script sets REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to 1 on an availability group named ag1. Before you run the following script, replace ag1 with the name of your availability group:

SQL

ALTER AVAILABILITY GROUP [ag1]

     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

This setting ensures that every active transaction is committed to the primary replica and at least one synchronous secondary replica.

  • Offline the primary replica in preparation for role changes.

SQL

ALTER AVAILABILITY GROUP [ag1] OFFLINE

  • Promote the target secondary replica to primary.

SQL

ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS;

  • Update the role of the old primary to SECONDARY, run the following command on the SQL Server instance that hosts the old primary replica:

SQL

ALTER AVAILABILITY GROUP [ag1]

     SET (ROLE = SECONDARY);

  • Resume data movement, run the following command for every database in the availability group on the SQL Server instance that hosts the primary replica:

SQL

ALTER DATABASE [db1]

     SET HADR RESUME

  • Re-create any listener you created for read-scale purposes and that isn’t managed by a cluster manager. If the original listener points to the old primary, drop it and re-create it to point to the new primary.

This article reviewed the steps to create a cross-platform AG to support migration or read-scale workloads. It can be used for manual disaster recovery. It also explained how to fail over the AG. A cross-platform AG uses cluster type NONE and does not support high availability because there is no cluster tool across-platforms.