Enable availability groups

[PSchicht]

Hi!

I'm evaluating the SQL Server 2017 Windows Docker image and wondering how to enable availability groups without the SQL server configuration tool? I want to try this: SQL Server 2017 Read-Scale Availability Groups (https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/11/17/sql-server-2017-read-scale-availability-groups/)

[twright-msft]

Always On Availability Groups for SQL Server (Windows and Linux) is not supported yet, but we are working on it.

We have AG support for Linux containers in private preview right now.

[olljanat]

@twright-msft will AG for Linux container be published for SQL 2017 or 2018 when it is ready?

@PSchicht note that you can find test version of AG support from here: https://github.com/Microsoft/mssql-server-ha

[twright-msft]

AGs for Linux containers is planned for SQL Server "vNext" (in private preview now with some customers). Undecided about whether or not it will be backported to SQL Server 2017 yet. We'll get some testing done on vNext first and then decide.

[olljanat]

FYI. I found that @rgward is also shared his demo scripts on: https://github.com/Microsoft/bobsql/tree/master/demos/sqlserver/sqllinux/demo_cross_platform_ags

But thing which I don't fully understand yet is that what is purpose of Pacemaker or these tools on mssql-server-ha repository if AG can work also without them?

[twright-msft]

Bob's demo shows a "clusterless" AG across Windows and Linux. Because there is no cross-OS platform cluster manager, there is no automated failure detection, leader election, and failover in that scenario. When you set up a clusterless AG the only thing that is happening is the replication of the data. It's really just an option for use cases when you want to do a manual failover. Some scenarios - migration with practically zero down time from Windows to Linux or vice versa, "OS level redundancy" (i.e. if there is an issue such as a zero day vulnerability on Windows you could fail over to Linux or vice versa). Another scenario could be where you want the read scalability of an AG but dont really care about the automated HA.

The purpose of Pacemaker on Linux and Windows Server Cluster Services on Windows is to provide the monitoring, leader election, and automated failover.

[aharpervc]

Another scenario could be where you want the read scalability of an AG but dont really care about the automated HA.

Is there a demo anywhere of this setup, using only containers? Like for setting up a read replica in a reproducible lab/demo context, and don't care about automatic fail over or cross platform.

IE, something we could use to test rails-sqlserver/tiny_tds#407

[twright-msft]

There isnt one specifically using containers, but I think you can follow this guide and adapt it to containers. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-create-availability-group?view=sql-server-2017
Adaptations: put the .cer file in the container image before you deploy it or have it be mounted somehow so that it is easy to access. Use the clustertype=None option in the example. Don't do all the pacemaker stuff.
Let us know if you need any help on this. We'd be willing to help especially if we can get this documented as a tutorial or example here on mssql-docker.

[gotiya]

I have successfully managed to enable SQL Server 2017 Availability groups without using SQL Server Configuration Tools....... for a read-scale 2 nos. SQL server docker container instances on the same docker host. I am happy to get this documented - if I could be shown how to go about and some assistance.

Update : this is on SQL Server 2017 Dev Edition with docker for Windows.

[twright-msft]

Hi @gotiya - That's great. For now it might be best to document this as an example here on the mssql-docker project. Do you want to fork this project, create a directory under /windows called 'examples' and then put your stuff there - .md files, scripts, etc? Then, please submit a pull request and we'll take a look at it. Thanks!

[keeyu2]

Anyone able to join the replica? I am not able to use the endpoint using the sql script provided by microsoft. I think I might need to edit the bridge on the docker network.
CREATE ENDPOINT AGEP
STATE = STARTED
AS TCP (
LISTENER_PORT = 5122,
LISTENER_IP = ALL )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQL1AGN1_Cert,
ROLE = ALL);

[keeyu2]

I was able to get the two containers to work by creating a network bridge and join the containers to the bridge. Then i was able to ping the host so i can use the endpoints.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Secr@tP@ssword!" --network sqlserver-bridge -p 1401:1433 -p5023:5022 --name sql1 -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-CTP2.3-ubuntu

Then I used the following documentation to create a read scale AAG
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-rs?view=sql-server-2017