March 6, 2019 at 1:16 pm
I have an issue where I want to do a rolling upgrade on a 2 node HADR AG from 2012 SP4 to SQL 2016. My drives are arranged as such;
SYSDB D:
SQLDATA E:
SQLTLogs F:
etc
I do not use folders on those drives I just install the .mdfs, ndfs, ldfs. directly in the root of those drives as it just makes it easier not having to click through folders to see the files in case I ever have a problem. Now that I'm installing SQL 2016 it wants me to create a sub-folder on each drive to store the data. This is killing me on my rolling upgrade as with HADR AG all if the Drives must match exactly since the will be a mix of SQL 2012 SP4 and 2016 prior to failing up to the 16 boxes. Is there any way to get SQL 16 or 17 to install the data drives to the root of those drives. Otherwise I'm off to plan B and there goes my 0 downtime rolling upgrade.
Thanks,
Phil
March 8, 2019 at 1:06 pm
Typically, you should be able to specify the directories for user database, tempdb, etc. during setup. If not, you can change the location of the files after the installation: https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017.
March 8, 2019 at 1:23 pm
there is a limitation imposed - even when doing a create db (and possibly a restore)
Program files and data files cannot be installed in the following situations:
On a removable disk drive
On a file system that uses compression
To a directory where system files are located
On a mapped network drive on a failover cluster instance
But as far as setup goes the system dbs should probably not be on the root directory.
For all user databases, as long as the rules above are met can be installed on a root of for example D:\ - and I did just tried it to confirm on a 2016 edition
so your restores onto your desired drives should still work
March 11, 2019 at 12:48 pm
sschampion, Thanks, it's good to know that you are able to get it to go the the root of D with SQL 2016:. I've tried the alter DB approach prior to making this post with no luck. I'll take a look and see if there are hidden System Files or permissions causing the issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply