Tag Archives: Myths

Time to bust some myths around Microsoft SQL Server.

#0370 – SQL Server – Myths – CREATE DATABASE FOR ATTACH_REBUILD_LOG will not work for read-only databases


In the past, I have written about attaching data files to a SQL Server instance when log files are missing by the use of the FOR ATTACH_REBUILD_LOG clause of the CREATE DATABASE statement. Recently, I was referring the CREATE DATABASE documentation on MSDN. It’s a comprehensive document and and the following line for read-only databases caught my attention.

For a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database with a log that is unavailable, you must provide the log files, or the files in the FOR ATTACH clause.

This statement was contrary to what I had observed before. Hence, I decided to re-validate the findings via a demo.

The first step, of course is to create a database.

USE [master];
GO
CREATE DATABASE ReadOnlyDBForAttach
ON PRIMARY (NAME = ReadOnlyDBForAttach_Data,
            FILENAME='C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Data.mdf'),
   FILEGROUP RODefault (NAME = ReadOnlyDBForAttach_RODefault,
              FILENAME='C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_RODefault.mdf')
LOG ON (NAME = ReadOnlyDBForAttach_Log,
        FILENAME='C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Log.ldf');
GO

As can be seen from the script referenced above, the database we created as 2 data file groups, one is the default PRIMARY filegroup. Because we cannot make the PRIMARY filegroup READ_ONLY, we will be making the other filegroup (RODefault) READ_ONLY. To add complexity, we will also make it the default filegroup, i.e. any new objects created without specifying a filegroup, it will be created in the filegroup marked as default.

Finally, we will also mark the database as READ_ONLY.

USE [master];
GO
ALTER DATABASE ReadOnlyDBForAttach
MODIFY FILEGROUP [RODefault] DEFAULT;
GO

ALTER DATABASE ReadOnlyDBForAttach
MODIFY FILEGROUP [RODefault] READ_ONLY;
GO

ALTER DATABASE ReadOnlyDBForAttach SET READ_ONLY;
GO

Now, let us check out the database and filegroup properties.

USE ReadOnlyDBForAttach;
GO
SELECT 'Database Properties',
       sd.[name],
       sd.is_read_only,
       sd.is_cleanly_shutdown
FROM sys.databases AS sd
WHERE sd.[name] = 'ReadOnlyDBForAttach';

SELECT 'File properties',
       sdf.file_id,
       sdf.type,
       sdf.data_space_id,
       sdf.type_desc,
       sdf.name,
       sdf.is_read_only,
       sdf.is_media_read_only
FROM sys.database_files AS sdf;
GO
Image showing database and database file properties for the read-only database - ReadOnlyDBForAttach

Read Only database and database file properties

Now, let us detach the database, and delete the log file. (NOTE: We are removing the log file for the purposes of this demo only. Please do not do this in your QA or production environments).

USE [master];
GO
EXEC sp_detach_db @dbname = 'ReadOnlyDBForAttach';
GO
Image showing the log file physically removed from the file system

Read Only Database – Log File Removed

Finally, let us attach the database back to the SQL Server instance using the CREATE DATABASE…FOR ATTACH_REBUILD_LOG clause.

USE [master]
GO
CREATE DATABASE [ReadOnlyDBForAttach]
ON  PRIMARY ( NAME = N'ReadOnlyDBForAttach_Data',
                FILENAME = N'C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO
File activation failure. The physical file name "C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_Log.ldf" may be incorrect.
New log file 'C:\SQLData\MSSQL12.SQL2014\MSSQL\DATA\ReadOnlyDBForAttach_log.ldf' was created.

As can be seen from the message above, the log file was successfully created and the database was successfully attached to the SQL Server instance.

Let us cross-check the database and file properties again:

USE ReadOnlyDBForAttach;
GO
SELECT 'Database Properties',
       sd.[name],
       sd.is_read_only,
       sd.is_cleanly_shutdown
FROM sys.databases AS sd
WHERE sd.[name] = 'ReadOnlyDBForAttach';

SELECT 'File properties',
       sdf.file_id,
       sdf.type,
       sdf.data_space_id,
       sdf.type_desc,
       sdf.name,
       sdf.is_read_only,
       sdf.is_media_read_only
FROM sys.database_files AS sdf;
GO
Read Only Database and Data File Properties After Attach showing that the database is no longer Read Only

Read Only Database and Data File Properties After Attach

Conclusion

  • Contrary to the MSDN remark, a read-only database can be successfully attached to a SQL Server instance even when the log file does not exist by the use of CREATE DATABASE…FOR ATTACH_REBUILD_LOG
  • A read-only database becomes a read/write database if it has been attached to the SQL Server using FOR ATTACH_REBUILD_LOG and the log file was rebuilt

My findings above do not agree with the MSDN remark. At the moment, I am inclined to believe that this is a bug in the documentation as I have found a couple of years ago as well (see references). Please do share your views on the same in the post comments.

References/Further Reading

  • SQL Server Myth: Log files are removed when a database is made READ_ONLY [Link]
  • Creating a database without the log backup file – Error Msg. 5120 [Link]
  • CREATE DATABASE…ATTACH_REBUILD_LOG resets database recovery mode to SIMPLE [Link]
  • Setting database to READ_ONLY does not change the file-group properties [Link]
  • sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified) [Link]
  • CREATE DATABASE [MSDN Documentation]

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

#0368 – SQL Server – Myths – Windows Authentication – Windows login name change does not mandate a change to SQL login


In today’s world, mergers and acquisitions of organizations are a reality. Having been through a couple of M&As in the last couple of years myself, I have had a unique opportunity to experience changes which would not be experienced in the normal course of work.

One such change is the changing of the domain logins. M&As often come with renaming of domain logins to confirm to a common standard. The question that came up in our team was:

What happens to our SQL Servers that use windows authentication if the login name changes?

The answer? Nothing. SQL Server continues to work as usual if the login name is the only thing that changed (and your applications do not use the physical “login name” in any way).

Here’s a demo for clarity.

On my VM, I created a user “OldUser” and logged in to SSMS using Windows Authentication when running as that user.

Screenshot showing the use of an existing windows login for authentication into a SQL Server instance.

Using an Old Login to login to SSMS

For the record, we will also execute the following query:

SELECT SUSER_SNAME() AS LoginName,SUSER_SID() AS LoginSID;
Query showing the Old User's SID

OldUser’s SID

Now, because this is a local login, all I do is rename the Windows login using Computer Management. The same behaviour applies to logins renamed via Active Directory as well.

Renaming the "OldUser" to "NewUser" in Computer Management

Renaming the “OldUser” to “NewUser”

I load the SSMS again – this time as “NewUser”.

Login to SSMS using the new, renamed login

Login to SSMS using the new, renamed login

Notice how the authentication works even though the login name has changed.

Wrapping it up…

Windows authentication in SQL Server is based on the SID. Hence, even though the login name changed, SQL Server allowed me to login because the SID did not change. In fact, this is why when we create or modify a login, the only requirement is to ensure that the SID of the login is same as the one we want to create/modify.

In a practical scenario, the IT teams would not re-generate the SIDs because that would mean too much work – realigning file & folder shares, resetting SSO and what have you. Instead, they simply update the login name (or the human interpretative part of the login).

What this means is that the authentication on the SQL Servers continue to work as usual – even after the login changes. No manual intervention is required!

Note of caution: Because the SQL Login name and the Windows login name are independent, renaming the Windows login will NOT rename the SQL login. The entry under sys.server_principals continues to read the old value. So, if your applications are designed to work based on the login name in sys.server_principals, you need to use ALTER LOGIN statements whenever IT changes the Windows login names.

Further Reading

In case you are really interested in digging deeper into SQL Server,

  • Security considerations for a SQL Server installation [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.