none
How to mount read-only databases on SQL Server for Linux?

    Question

  • I've recently installed the latest SQL Server for Linux to test it with our existing databases and archives.  My machine is running Ubunutu 16.10, and the SQL Server package is version 14.0.100.187-1.  As part of my tests I tried to attach a database in read-only mode, but this fails.  The database files are owned by the 'mysql' user, and have read privileges set.  But when I try to attach the database I get the following error:

    Property MemoryAllocatedToMemoryOptimizedObjectsInKB is not available for Database '[SSA]'. This property may not exist for this object, or may
    not be retrievable due to insufficient access rights.  (SqlManagerUI)

    The database attaches correctly if I set the files with read-write permssions, so the problem seems to strictly relate to read-only mode.  Is it possible to mount databases read-only on thie Linux version of SQL Server?  If so, how?

    Friday, January 20, 2017 12:55 PM

All replies

  • Hi astroDB,

    According to your description, my understanding is that you have set the read-only permission on the system level, based on my test, I also met error message.

    Read-only database also required read/write permission on the file system, when it comes to Windows Operation System, if we set read-only permission on system level, we will meet the error message 'Access is denied'. For read-only database, we just need to use the query 'ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT'.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 23, 2017 6:30 AM
    Moderator
  • Is this a change in the way the latest SQL Server version (2016) handles read-only databases? Or is it specific for the Linux version?

    We currently operate a "cluster" of database servers which share databases with each other over the network by sharing sets of read-only files.  This allows us to cross-mount and query a database on multiple servers simultaneously.  We've used this method (setting read-only permissions at OS level) for about 8 years now without a problem, and it is essential to how our services operate. 

    Do you know offhand if using the "ALTER" command to mount a database read-only will allow it to be cross-mounted on other servers simultaneously?  This didn't work for us previously, but we haven't tested it since SQL Server 2008. 

    Tuesday, January 24, 2017 11:35 AM
  • Hi Astro DB,

    Could you please provide us with a repro steps so that we can look at this in more detail.

    Following are the options I have tried so far on CTP 1.2 build and I am not noticing the error that you are experiencing.

    1. Scenario where DB got created -> Detached -> O/S level gave Read-only permissions to everyone -> attached it back :    Attached worked fine and the DB is in read-only mode

     CREATE DATABASE [mstest]
     CONTAINMENT = NONE
     ON  PRIMARY
     ( NAME = N'mstest', FILENAME = N'C:\var\opt\mssql\data\mstest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
     LOG ON
     ( NAME = N'mstest_log', FILENAME = N'C:\var\opt\mssql\data\mstest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
     GO
     USE [master]
     GO
     ALTER DATABASE [mstest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
     GO
     USE [master]
     GO
     EXEC master.dbo.sp_detach_db @dbname = N'mstest'
     GO

     [root@ctp1cento ~]# lsattr /var/opt/mssql/data/mstest*
     ---------------- /var/opt/mssql/data/mstest_log.ldf
     ---------------- /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:22 /var/opt/mssql/data/mstest_log.ldf
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:22 /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# chmod 444 /var/opt/mssql/data/mstest*

     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -r--r--r-- 1 mssql mssql 8388608 Jan 27 10:22 /var/opt/mssql/data/mstest_log.ldf
     -r--r--r-- 1 mssql mssql 8388608 Jan 27 10:22 /var/opt/mssql/data/mstest.mdf

     USE [master]
     GO
     CREATE DATABASE [mstest] ON
     ( FILENAME = N'C:\var\opt\mssql\data\mstest.mdf' ),
     ( FILENAME = N'C:\var\opt\mssql\data\mstest_log.ldf' )
     FOR ATTACH
     GO

     select name,database_id,is_read_only from sys.databases where name='mstest'
     name   database_id   is_read_only
     mstest 5      1

     select DATABASEPROPERTYEX('mstest','Updateability ') as Updateability
     Updateability
     READ_ONLY

    2. Scenario where DB got created -> Set the readonly property for the db explicitly -> Detached -> O/S level gave Read-only permissions to everyone -> attached it back :  Attached worked fine and the DB is in read-only mode

     CREATE DATABASE [mstest]
     CONTAINMENT = NONE
     ON  PRIMARY
     ( NAME = N'mstest', FILENAME = N'C:\var\opt\mssql\data\mstest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
     LOG ON
     ( NAME = N'mstest_log', FILENAME = N'C:\var\opt\mssql\data\mstest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
     GO
     ALTER DATABASE [mstest] SET  READ_ONLY
     GO
     USE [master]
     GO
     ALTER DATABASE [mstest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
     GO
     USE [master]
     GO
     EXEC master.dbo.sp_detach_db @dbname = N'mstest'
     GO

     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:10 /var/opt/mssql/data/mstest_log.ldf
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:10 /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# lsattr /var/opt/mssql/data/mstest*
     ---------------- /var/opt/mssql/data/mstest_log.ldf
     ---------------- /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# chmod 444 /var/opt/mssql/data/mstest*

     [root@ctp1cento ~]# lsattr /var/opt/mssql/data/mstest*
     ---------------- /var/opt/mssql/data/mstest_log.ldf
     ---------------- /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -r--r--r-- 1 mssql mssql 8388608 Jan 27 10:10 /var/opt/mssql/data/mstest_log.ldf
     -r--r--r-- 1 mssql mssql 8388608 Jan 27 10:10 /var/opt/mssql/data/mstest.mdf

     USE [master]
     GO
     CREATE DATABASE [mstest] ON
     ( FILENAME = N'C:\var\opt\mssql\data\mstest.mdf' ),
     ( FILENAME = N'C:\var\opt\mssql\data\mstest_log.ldf' )
     FOR ATTACH
     GO

     select name,database_id,is_read_only from sys.databases where name='mstest'
     name   database_id   is_read_only
     mstest 5      1

     select DATABASEPROPERTYEX('mstest','Updateability ') as Updateability
     Updateability
     READ_ONLY


    3. Scenario where DB files present in data folder  ->  O/S level gave Read-only permissions to everyone -> Added immutable flag to the files -> attached it back :   Attached worked fine and the DB is in read-only mode

     [root@ctp1cento ~]# chattr +i  /var/opt/mssql/data/mstest*

     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -r--r--r-- 1 mssql mssql 8388608 Jan 27 10:22 /var/opt/mssql/data/mstest_log.ldf
     -r--r--r-- 1 mssql mssql 8388608 Jan 27 10:22 /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# lsattr /var/opt/mssql/data/mstest*
     ----i----------- /var/opt/mssql/data/mstest_log.ldf
     ----i----------- /var/opt/mssql/data/mstest.mdf

     USE [master]
     GO
     CREATE DATABASE [mstest] ON
     ( FILENAME = N'C:\var\opt\mssql\data\mstest.mdf' ),
     ( FILENAME = N'C:\var\opt\mssql\data\mstest_log.ldf' )
     FOR ATTACH
     GO

     select name,database_id,is_read_only from sys.databases where name='mstest'
     name   database_id   is_read_only
     mstest 5                    1

     select DATABASEPROPERTYEX('mstest','Updateability ') as Updateability
     Updateability
     READ_ONLY


    4. Scenario where DB files present in data folder ->  O/S level permissions were not changed -> Added immutable flag to the files -> attached it back          :           Attached worked fine and the DB is in read-only mode


     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:31 /var/opt/mssql/data/mstest_log.ldf
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:31 /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# lsattr /var/opt/mssql/data/mstest*
     ---------------- /var/opt/mssql/data/mstest_log.ldf
     ---------------- /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# chattr +i  /var/opt/mssql/data/mstest*

     [root@ctp1cento ~]# lsattr /var/opt/mssql/data/mstest*
     ----i----------- /var/opt/mssql/data/mstest_log.ldf
     ----i----------- /var/opt/mssql/data/mstest.mdf

     [root@ctp1cento ~]# ls -l /var/opt/mssql/data/mstest*
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:31 /var/opt/mssql/data/mstest_log.ldf
     -rw-rw---- 1 mssql mssql 8388608 Jan 27 10:31 /var/opt/mssql/data/mstest.mdf

     USE [master]
     GO
     CREATE DATABASE [mstest] ON
     ( FILENAME = N'C:\var\opt\mssql\data\mstest.mdf' ),
     ( FILENAME = N'C:\var\opt\mssql\data\mstest_log.ldf' )
     FOR ATTACH
     GO

     select name,database_id,is_read_only from sys.databases where name='mstest'
     name   database_id   is_read_only
     mstest 5                    1

     select DATABASEPROPERTYEX('mstest','Updateability ') as Updateability
     Updateability
     READ_ONLY

    --Pradeep

    Friday, January 27, 2017 6:39 PM
  • Apologies for the long delay, been busy elsewhere and now I'm just getting back to this.  And as a result I seem to have gone backwards a step, in that now my attach script isn't working...  I'm using the SQL below to attempt to attach the database: 

    USE [master]
    GO

    /****** Attach SSA ******/
    create database [SSA] on
    (name='pfg_1', filename='\data1\ReadOnlyDatabases\SSA\PrimaryFileGroup1.mdf'),
    (name='pfg_3', filename='\data2\ReadOnlyDatabases\SSA\PrimaryFileGroup3.mdf'),
    (name='pfg_4', filename='\data3\ReadOnlyDatabases\SSA\PrimaryFileGroup4.mdf'),
    (name='cfg_1', filename='\data1\ReadOnlyDatabases\SSA\CurationFileGroup1.ndf'),
    (name='cfg_3', filename='\data2\ReadOnlyDatabases\SSA\CurationFileGroup3.ndf'),
    (name='cfg_4', filename='\data3\ReadOnlyDatabases\SSA\CurationFileGroup4.ndf'),
    (name='dfg_1', filename='\data1\ReadOnlyDatabases\SSA\DetectionFileGroup1.ndf'),
    (name='dfg_3', filename='\data2\ReadOnlyDatabases\SSA\DetectionFileGroup3.ndf'),
    (name='dfg_4', filename='\data3\ReadOnlyDatabases\SSA\DetectionFileGroup4.ndf'),
    (name='ifg_1', filename='\data1\ReadOnlyDatabases\SSA\IndicesFileGroup1.ndf'),
    (name='ifg_3', filename='\data2\ReadOnlyDatabases\SSA\IndicesFileGroup3.ndf'),
    (name='ifg_4', filename='\data3\ReadOnlyDatabases\SSA\IndicesFileGroup4.ndf'),
    (name='nsfg_1', filename='\data1\ReadOnlyDatabases\SSA\NewSourceFileGroup1.ndf'),
    (name='nsfg_3', filename='\data2\ReadOnlyDatabases\SSA\NewSourceFileGroup3.ndf'),
    (name='nsfg_4', filename='\data3\ReadOnlyDatabases\SSA\NewSourceFileGroup4.ndf'),
    (name='sfg_1', filename='\data1\ReadOnlyDatabases\SSA\SourceFileGroup1.ndf'),
    (name='sfg_3', filename='\data2\ReadOnlyDatabases\SSA\SourceFileGroup3.ndf'),
    (name='sfg_4', filename='\data3\ReadOnlyDatabases\SSA\SourceFileGroup4.ndf')
    log on
    (name='lfg_1', filename='\data1\ReadOnlyDatabases\SSA\LogFileGroup1_data.LDF'),
    (name='lfg_3', filename='\data2\ReadOnlyDatabases\SSA\LogFileGroup3_data.LDF'),
    (name='lfg_4', filename='\data3\ReadOnlyDatabases\SSA\LogFileGroup4_data.LDF')
    for attach
    GO

    But it produces the following error:

    Msg 5105, Level 16, State 2, Line 5
    A file activation error occurred. The physical file name '\data1\ReadOnlyDatabases\SSA\PrimaryFileGroup1.mdf' may be incorrect. Diagnose and cor
    rect additional errors, and retry the operation.

    Changing the Windows slashes '\' to Linux slashes '/' produces a "System cannot find the file specified error"

    Using the 'N'C:\' notation also produces a "System cannot find the file specified error"

    The files are where they are specified, and the mssql user has rw permissions on all the directories and files:

    root@ramses7:/# ls -l /data1/ReadOnlyDatabases/SSA/
    total 1301434688
    -rw-rw-rw- 1 mssql users  16574578688 Feb 27  2013 CurationFileGroup1.ndf
    -rw-rw-rw- 1 mssql users 580800217088 Feb 27  2013 DetectionFileGroup1.ndf
    -rw-rw-rw- 1 mssql users 367859269632 Feb 27  2013 IndicesFileGroup1.ndf
    -rw-rw-rw- 1 mssql users    134160384 Mar  1 17:22 LogFileGroup1_data.LDF
    -rw-rw-rw- 1 mssql users 181836054528 Feb 27  2013 NewSourceFileGroup1.ndf
    -rw-rw-rw- 1 mssql users 185463537664 Mar  1 17:22 PrimaryFileGroup1.mdf
    -rw-rw-rw- 1 mssql users      1048576 Feb 27  2013 SourceFileGroup1.ndf
    -rw-rw-rw- 1 mssql users         3263 Jun 11  2009 SSA_CreateDatabase.sql
    -rw-rw-rw- 1 mssql users        18843 Jun 11  2009 SSA_LoadData.sql
    -rw-rw-rw- 1 mssql users        83291 Jun 11  2009 SSA_Tables.sql
    root@ramses7:/# ls -l /data2/ReadOnlyDatabases/SSA/
    total 1273788616
    -rw-rw-rw- 1 mssql users  14145290240 Feb 27  2013 CurationFileGroup3.ndf
    -rw-rw-rw- 1 mssql users 580800151552 Feb 27  2013 DetectionFileGroup3.ndf
    -rw-rw-rw- 1 mssql users 361217064960 Feb 27  2013 IndicesFileGroup3.ndf
    -rw-rw-rw- 1 mssql users    134160384 Jan 20 14:42 LogFileGroup3_data.LDF
    -rw-rw-rw- 1 mssql users 179613007872 Feb 27  2013 NewSourceFileGroup3.ndf
    -rw-rw-rw- 1 mssql users 168448688128 Jan 20 12:17 PrimaryFileGroup3.mdf
    -rw-rw-rw- 1 mssql users      1048576 Feb 27  2013 SourceFileGroup3.ndf
    root@ramses7:/# ls -l /data3/ReadOnlyDatabases/SSA/
    total 1312260312
    -rw-rw-rw- 1 mssql users  16310796288 Feb 27  2013 CurationFileGroup4.ndf
    -rw-rw-rw- 1 mssql users 581390696448 Feb 27  2013 DetectionFileGroup4.ndf
    -rw-rw-rw- 1 mssql users 368949395456 Feb 27  2013 IndicesFileGroup4.ndf
    -rw-rw-rw- 1 mssql users    134160384 Dec 14  2012 LogFileGroup4_data.LDF
    -rw-rw-rw- 1 mssql users 179613007872 Feb 27  2013 NewSourceFileGroup4.ndf
    -rw-rw-rw- 1 mssql users 197355307008 Jan 20 12:17 PrimaryFileGroup4.mdf
    -rw-rw-rw- 1 mssql users      1048576 Feb 27  2013 SourceFileGroup4.ndf

    I'm guessing that this is simply an issue with the path notation.  What is the proper OS path notation for the attach script?

    Thursday, March 2, 2017 11:33 AM