July 9, 2019 at 12:00 am
Comments posted to this topic are about the item The Dangers When Changing Database File Names
July 9, 2019 at 2:11 pm
Please do not forget non-English installations. This example will not work with them and the environment variables %ProgramFiles% should be used because on non-English systems there may not be a path "C:\Program Files". Whether the MS-SQL environment will evaluate these environment variables is a different matter, and one that I have not tested.
Other than that, am I the only one that wonders why there is a seeming need from Microsoft for their default database location being in precisely the wrong place? Especially considering Microsoft's own rules and guidelines on file locations? The "Program Files" directory is strictly for program files and not for data files. Data and code should never be mixed. Many, many years of pain were suffered with hapless application developers that assumed that all users had local administation to a system and "Program Files" was an acceptable place to write things to. It wasn't, it never was, and caused no end of issues when later Windows operating systems started to have security retro-fitted to them.
I also find it strange that a database transaction log file needs to be identified both by file extension and by name. Maybe I suffer from a bit of OCD on this front but where possible I always name them the same file component but with different extensions. For multi-file databases this scheme does not work, but then none of the MS-SQL databases that I manage have needed multiple files and I suspect that getting to the database scale where they may be required is going to require rather more consideration than just database file naming conventions.
Interesting, and useful, cautionary point about the process only working on database (re)start.
July 9, 2019 at 3:24 pm
The example won't work with anyone that doesn't have the same named instance name as me. This isn't intended to be something you follow without changing paths.
Your point about default locations makes perfect sense. The files should not be in Program Files, but that's not likely to change now.
For the log file, it doesn't need to be noted by filename and extension. As noted the extension is convention, not required. Same for the name. Those are defaults,. but not requirements.
July 9, 2019 at 4:33 pm
Other than that, am I the only one that wonders why there is a seeming need from Microsoft for their default database location being in precisely the wrong place? Especially considering Microsoft's own rules and guidelines on file locations? The "Program Files" directory is strictly for program files and not for data files. Data and code should never be mixed. Many, many years of pain were suffered with hapless application developers that assumed that all users had local administation to a system and "Program Files" was an acceptable place to write things to.
Well I agree it's the wrong suggested default location for data files, but what do you want to set it to by default for an installation routine otherwise? Just think about it for a second, can you (using the same installer for any edition of SQL Server) provide a better Default location? First I would assume you cannot by default suggest any other drive than C: because that's the only default drive letter available on a standard Windows Installation - remember ideally you can in the worst case just click "Next" until you reach the "Finish" button and you'll somehow have your SQL Server running. Now the issue with security as you mentioned in this case could be permissions on the drive root Level (C:) and even if it's not: Does C:\MSSQL_Data (or whatever) sound much more promising to you? To me it sounds even less promising because of drive root level permissions and potential GPOs denying users to create any folder directly under C: so while I agree with your statement I fail to see a better potential default suggestion.
What I also do see is at least since SQL Server 2016 it's rather easy to move any data files during Setup to a different path, wether it's unattended or "click your way through" doesn't matter.
And finally btt: Not sure if it's fixed by now but I at least do remember there might be weird behavior from SQL Server if there is more than one .mdf File per DB, just don't have any reference at hand right now - and personally my customers don't run DB's with 1 Data File, wether it's 50 GB or 40 TB in size and the reasons are most of the time decrease potential file contention and up to now this has worked very well to my customers' satisfaction and performance increase.
July 9, 2019 at 10:44 pm
ALTER DATABASE OneWorld
MODIFY FILE (NAME= 'OneWorld_Archive', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\Demo\OneWorld_Archive.mdf')
GO
Did you mean to change something or am I missing it?
July 9, 2019 at 11:00 pm
Typo from testing. Should be ndf. Corrected.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply