Sql-server – On SQL Server 2017 (Linux), where is the Resource Database persisted (located on disk)

database-internalslinuxsql serversql-server-2017

In my default install of SQL Server 2017 on Linux, under /var/opt/mssql/data I see the following databases,

master.mdf – master Database – Records all the system-level information for an instance of SQL Server.
model.mdf – model Database – Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
msdbdata.mdf – msdb Database – Is used by SQL Server Agent for scheduling alerts and jobs.
tempdb.mdf – tempdb Database – Is a workspace for holding temporary objects or intermediate result sets.

All of these databases are actually pretty well documented. However my install's omission of the Resource Database stands out. The description of the Resource Database seems to indicate that it is its own database,

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Even more confusing, for SQL Server 2012 the docs say, (not 2017 which is what I'm running)

Physical Properties of Resource

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

I don't see a mssqlsystemresource.mdf anywhere though. Where is this database stored? Is this compiled into the server itself?

Checking it out, I started up database in single-user mode and connected to mssqlsystemresource and started digging,

1> USE mssqlsystemresource
2> GO
Changed database context to 'mssqlsystemresource'.
1> SELECT type, type_desc, physical_name FROM sys.database_files;
2> GO
type type_desc                                                    physical_name                                                                                                                                                                                                                                                       
---- ------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   0 ROWS                                                         d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.mdf                                                                                                                                                        
   1 LOG                                                          d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.ldf                                                                                                                                                        

(2 rows affected)

I don't see these .ldf or .mdf anywhere though. And, being a Linux install I'm guessing d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\ is a virtualized internal directory.

Further sys.master_files lacks the db too

1> SELECT LEFT(name,20), LEFT(physical_name,35) FROM sys.master_files WHERE name LIKE 'ms%';
2> GO

-------------------- -----------------------------------
MSDBData             /var/opt/mssql/data/MSDBData.mdf   
MSDBLog              /var/opt/mssql/data/MSDBLog.ldf    
(2 rows affected)

When I start up the database in single user mode sudo -u mssql /opt/mssql/bin/sqlservr -m I see this,

spid9s   Starting up database 'mssqlsystemresource'.
spid9s   The resource database build version is 14.00.3008. This is an informational message only. No user action is required.

Best Answer

I've confirmed that this Resource Database is packed in the archive, /opt/mssql/lib/sqlservr.sfp along with the rest of the files in Binn. So the answer to the question is that it resides in the sfp archive. More to the answer, you can extract that sfp with the method here creating a new file with the contents,

/opt/mssql/lib/sqlservr/Content/binn/mssqlsystemresource.mdf

So the contents there are likely virtualized in the In the Drawbridge layer as

d:\dbs\sh\s17o\1116_092427_2\cmd\43\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\mssqlsystemresource.mdf

Related Solutions

Sql-server – Sql server 2008 R2 installation fails with Error: 17204

This is a known issue which has several causes:

The first thing to check is your permissions on local machine:
Are You a local Administrator?
Is there a group policy that might prevent you from installing \ debugging?

If these do not resolve the problem in your case, and you have verified the required permissions, please provide more info:

What parameters do you use during installation (beside the default ones)?
Which Edition of SQL Server are You installing?
Do You use Default instance name?
On which O/S are You installing?

EDIT:
Next steps: 1. open the folder ":\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log
2. Find a file named Detail.txt
3. This file has a complete log, You need to find the FIRST error that occured during installation.
4. Paste it here, only the part with the error.

EDIT:
Added Solution to comments, waiting to hear if the problem is solved

Sql-server – SQL Server: Collation of the resource database

The database Collation for the first SELECT statement in my query is Latin1_General_CI_AS.

Well, not exactly. There are a few problems with this statement:

The Database's default Collation only matters in a query when using string literals, variables, and return values from UDFs. AND, that default Collation only matters if there is no column or COLLATE keyword being used.
SELECT statements / queries, as a whole, do not use Collations. Collation is assigned per each string field, and it can be different for each field in a query.
The column in the first / top query, stopword, is not using the Latin1_General_CI_AS Collation (more on this in a moment).

I see that the system-stopwords are stored in the resource database, which can explain the difference in collation.

A column (or expression) coming from a different Database does not necessarily explain a difference in Collation. As stated above, Collation is set per each field of a query, whether that field comes from a column in a table or is an expression. Collation is usually derived naturally from Collation Precedence: Column Collation overrides literals and variables, and the COLLATE keyword overrides both. When there is a conflict, then you need the COLLATE keyword.

The main point here, however, is that if the stopword column in sys.fulltext_system_stopwords comes from a column in a Table in the Resource Database (i.e. mssqlsystemresource), OR if it comes from an expression in a View in the Resource Database that has its Collation set via the COLLATE keyword, then the default Collation of the Resource Database is irrelevant.

I can 'solve' my error by using COLLATE in my query

Yes, the COLLATE keyword is the way to go. However, to fix this conflict, you only need to specify the COLLATE keyword in one query, though it does not matter which query.

For example, if I run the query with no COLLATE keyword, I get:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw

UNION

SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the UNION operation.

So, assuming that the conflict is in the second (i.e. bottom) SELECT statement, I could fix it by applying the current DB's default Collation:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

And that works. BUT, what if we try putting COLLATE on the first / top SELECT:

SELECT ftsw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

That also works. And in fact, both of the following also work:

SELECT ftsw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

-- and:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

Also, rather than use the DATABASE_DEFAULT option, which here equates to Latin1_General_CI_AS, I would use Latin1_General_BIN (or better yet: Latin1_General_100_BIN2, which is newer and better) in this particular case because it will ensure that different strings that can be normalized into the same string via the "distinct" behavior of the UNION (without the ALL) and the case-insensitivity of the Latin1_General_CI_AS Collation show up as different rows.

Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

That Collation comes from an unlikely source. Let's look at the system catalog views in the query:

EXEC sys.sp_help N'sys.fulltext_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of Latin1_General_BIN (not expected). But wait, if the Latin1_General_BIN Collation is coming from sys.fulltext_stopwords, then what about sys.fulltext_system_stopwords and where is the other Collation coming from? Let's look:

EXEC sys.sp_help N'sys.fulltext_system_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of SQL_Latin1_General_CP1_CI_AS (not expected).

We can now dig a little deeper into the definition of each of those system catalog views:

EXEC sys.sp_helptext N'sys.fulltext_stopwords';

Returns (simplified):

SELECT 
    fts.stopword,
FROM sys.sysftstops fts

And then:

EXEC sys.sp_help N'sys.sysftstops';

The results indicate that this is a system table, and that the stopword column indeed has a Collation of Latin1_General_BIN.

Next we can move on to the other system catalog view:

EXEC sys.sp_helptext N'sys.fulltext_system_stopwords';

Returns (simplified):

SELECT convert(nvarchar(64), stopword) as stopword, language_id
FROM OpenRowset(TABLE FTSYSSTPWD)

And FTSYSSTPWD comes from the Resource Database, so there isn't much more we can do at this point.

Still, there is one last thing we can do to be clear about the Collation of the data coming from the Resource Database -- sys.fulltext_system_stopwords.stopword:

CREATE DATABASE [FullTextCollationTest] COLLATE SQL_EBCDIC277_CP1_CS_AS;
GO
USE [FullTextCollationTest];

EXEC sys.sp_help N'sys.fulltext_stopwords';
-- Collation for [stopword] column: Latin1_General_BIN      (same as before)

EXEC sys.sp_help N'sys.fulltext_system_stopwords';
-- Collation for [stopword] column: SQL_EBCDIC277_CP1_CS_AS (same as DB's default Collation)

GO
USE [master];
DROP DATABASE [FullTextCollationTest];

Best Answer

Related Solutions

Sql-server – Sql server 2008 R2 installation fails with Error: 17204

Sql-server – SQL Server: Collation of the resource database

Related Question