{"id":9793,"date":"2017-02-16T19:13:43","date_gmt":"2017-02-16T18:13:43","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-failover-cluster-on-linux-and-synchronizing-uidsgids-across-nodes\/"},"modified":"2023-07-18T07:39:43","modified_gmt":"2023-07-18T05:39:43","slug":"sql-server-failover-cluster-on-linux-and-synchronizing-uidsgids-across-nodes","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-failover-cluster-on-linux-and-synchronizing-uidsgids-across-nodes\/","title":{"rendered":"SQL Server Failover Cluster on Linux and synchronizing uids\/gids across nodes"},"content":{"rendered":"

In my <\/span>previous blog post<\/span><\/a>, I talked about SQL Server on Linux and high availability. During my test, I used a NFS server to share disk resources between my cluster nodes as described in the Microsoft documentation. A couple of days ago, I decided to add a fourth node (LINUX04) to my cluster infrastructure and I expected to do this work easily. But no chance, I faced a problem I never had before on this infrastructure. <\/span><\/p>\n

\"blog<\/p>\n

Switching over this last node led to a failed SQL Server FCI resource. After digging into the problem, I found out the root from the SQL Server error log as shown below:<\/span><\/p>\n

[mikedavem@linux04 ~]$ sudo cat \/var\/opt\/mssql\/log\/errorlog\n2017-02-12 18:55:15.89 Server      Microsoft SQL Server vNext (CTP1.2) - 14.0.200.24 (X64)\n        Jan 10 2017 19:15:28\n        Copyright (C) 2016 Microsoft Corporation. All rights reserved.\n        on Linux (CentOS Linux 7 (Core))\n2017-02-12 18:55:15.89 Server      UTC adjustment: 0:00\n2017-02-12 18:55:15.89 Server      (c) Microsoft Corporation.\n2017-02-12 18:55:15.89 Server      All rights reserved.\n2017-02-12 18:55:15.89 Server      Server process ID is 4116.\n2017-02-12 18:55:15.89 Server      Logging SQL Server messages in file 'C:\\var\\opt\\mssql\\log\\errorlog'.\n2017-02-12 18:55:15.89 Server      Registry startup parameters:\n         -d C:\\var\\opt\\mssql\\data\\master.mdf\n         -l C:\\var\\opt\\mssql\\data\\mastlog.ldf\n         -e C:\\var\\opt\\mssql\\log\\errorlog\n2017-02-12 18:55:15.91 Server      Error: 17113, Severity: 16, State: 1.\n2017-02-12 18:55:15.91 Server      Error 2(The system cannot find the file specified.) occurred while opening file 'C:\\var\\opt\\mssql\\data\\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.\n2017-02-12 18:55:15.91 Server      SQL Server shutdown has been initiated<\/pre>\n
Well, the error speaks for itself and it seems I\u2019m concerned by a file access permission in my case. My first reflex was to check the corresponding permissions on the corresponding NFS folder.<\/span><\/p>\n
[mikedavem@linux04 ~]$ sudo ls -lu \/var\/opt\/mssql\/data\nTo.al 53320\ndrwxr-----. 2 995 993     4096 Feb 14 23:12 lost+found\n-rwxr-----. 1 995 993  4194304 Feb 14 23:19 master.mdf\n-rwxr-----. 1 995 993  2097152 Feb 14 23:19 mastlog.ldf\n-rwxr-----. 1 995 993  8388608 Feb 14 23:19 modellog.ldf\n-rwxr-----. 1 995 993  8388608 Feb 14 23:19 model.mdf\n-rwxr-----. 1 995 993 13959168 Feb 14 23:19 msdbdata.mdf\n-rwxr-----. 1 995 993   786432 Feb 14 23:19 msdblog.ldf\ndrwxr-----. 2 995 993     4096 Feb 14 23:08 sqllinuxfci\n-rwxr-----. 1 995 993  8388608 Feb 14 23:19 tempdb.mdf\n-rwxr-----. 1 995 993  8388608 Feb 14 23:19 templog.ldf<\/pre>\n
According to the output above we may claim this is a mismatch issue between uids\/guids of the mssql user across the cluster nodes. At this stage, I remembered performing some tests including creating some linux users before adding my fourth node leading to create a mismatch for the mssql user\u2019s uids\/gids. Just keep in mind that the SQL Server installation creates a mssql user by default with the next available uid\/gid. In my case uid and guid.<\/span><\/p>\n
Let\u2019s compare mssql user uid\/gid from other existing nodes LINUX01 \/ LINUX02 and LINUX03:<\/span><\/p>\n
[mikedavem@linux04 ~]$ id mssql\nuid=997(mssql) gid=995(mssql) groups=995(mssql)\n[mikedavem@linux04 ~]$ ssh linux01 id mssql\n\u2026\n[root@linux04 ~]# ssh linux01 id mssql\nuid=995(mssql) gid=993(mssql) groups=993(mssql)\n\u2026\n[root@linux04 ~]# ssh linux02 id mssql\nuid=995(mssql) gid=993(mssql) groups=993(mssql)\n\u2026\n[root@linux04 ~]# ssh linux03 id mssql\nuid=995(mssql) gid=993(mssql) groups=993(mssql)<\/pre>\n
Ok this explains why I faced this permission issue. After investing some times to figure out how to get rid of this issue without changing the mssql user\u2019s uid\/guid, I\u00a0read some\u00a0discussions about using NFS4 which is intended to fix this uids\/gids mapping issue.\u00a0It seems to be perfect in my case! But firstly let\u2019s just confirm I\u2019m using the correct NFS version<\/p>\n
[mikedavem@linux04 ~]$ mount -v | grep nfs\nnfsd on \/proc\/fs\/nfsd type nfsd (rw,relatime)\n192.168.5.14:\/mnt\/sql_data_nfs on \/var\/opt\/mssql\/data type nfs (rw,relatime,vers=3,rsize=65536,wsize=65536,namlen=255,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.5.14,mountvers=3,mountport=20048,mountproto=udp,local_lock=none,addr=192.168.5.14)<\/pre>\n
Well, my current configuration is not ready to leverage NFS4 yet and some configuration changes seem to be required to address it. <\/span><\/p>\n
Firstly, let\u2019s change fstype parameter of my FS resource to nfs4 to mount the NFS share with NFS4.<\/span><\/p>\n
[mikedavem@linux04 ~]$ sudo pcs resource show FS\n Resource: FS (class=ocf provider=heartbeat type=Filesystem)\n  Attributes: device=192.168.5.14:\/mnt\/sql_data_nfs directory=\/var\/opt\/mssql\/data fstype=nfs\n  Operations: start interval=0s timeout=60 (FS-start-interval-0s)\n              stop interval=0s timeout=60 (FS-stop-interval-0s)\n              monitor interval=20 timeout=40 (FS-monitor-interval-20)\n\n[mikedavem@linux04 ~]$ sudo pcs resource update FS fstype=nfs4\n[mikedavem@linux04 ~]$ sudo pcs resource restart FS\nFS successfully restarted<\/pre>\n
Then I had to perform some updates to my idmap configuration on both sides (NFS server and client as well) to make the mapping working correctly. The main steps were as follows:<\/p>\n