Why does GP Utilities not point to my default SQL data locations ?

Every time you need to create a new company in GP, you have to pull in GP Utilities and select the option “Create a Company”. After you click on the “Process” button, you’ll get faced with the Create Company dialog box to enter some values and parameters. You enter the DB or company ID (max 5 characters) and the company name (Up to 65 characters).

When you click on Next, GP Utilities will pull in some default values for the physical SQL files names and their location:

Somehow, someone at the time of writing the code decided that every related GP company DB file name would be pre-fixed with GPS. No problem with that, but you’re not bound to that rule and could select a different name if you think it’s easier to sort out on the SQL server folders. I usually rename my files as CPNY_Dat.mdf and CPNY_Log.ldf, so I can quickly spot visually the correct files on the server if I need to move them around for some reason (CPNY being replaced by your Company ID of course).

What is much more annoying is the default locations for the two files (Data & Log names), which points often time to the same place where your SQL server is installed (usually C:\Program Files\Microsoft….. ), which doesn’t make it easy to find your files in the system when and if you need to do something physically with those files.. Aside of the tact that it’s a very bad idea from a DBA management point of view to have all the files in the same location and sitting on the boot drive.

In case you didn’t notice, the two folders are exactly the same, despite common SQL Admin rules that would dictate to separate the Data & Log files.

You may ask : “But I have changed the default location of all my Data & Log files after the setup in SSMS to point to different drives, so how come GP Utilities doesn’t use them ?” Typically you’d check this out under the SQL Server properties in SSMS and under the ‘Database Settings’ find the default location path for the Data, Log & Backup files.

Fig. 3: Database default location in SSMS

The answer is pretty simple: GP Utilities doesn’t care about those values! Instead, it’s picking another set of values from the Registry keys (as far as I could validate this) from the SQL Server directly. Needless to say that the values are actually pulled from the default location where the SQL ‘master’ database is located, and often times when you weren’t even the one who installed SQL server, and the one who did it, didn’t really care about this and accept all the default values proposed by the installer. (See Fig. 2)

The section underneath the GP Utilities screen is pulled from the Windows Registry and shows the parameters passed to the SQL engine at the startup. The 3 arguments are in order “-d” for data location, “-e” for Error log location, and “-l” for Log file location (aka transaction log).
As you can see from the above screenshot, GP Utilities doesn’t even care to pull the “-l” location path, but rather uses twice the “-d” location.. what a lousy job of a programmer!
As a proof, I changed the default locations thru the SQL Configuration Console to point them to the place where I store my user database files, but as you can see, GP Utilities uses the same for both files:

Fig. 5: after changes were made to SQL Configuration

You may argument that it doesn’t really matter, as You don’t add a new company every day.. True, but think of large corporation that have dozens of companies in GP, and also own a comparable sized DEV and/or TEST environment where they try to maintain the same level of accuracy in terms of GP companies as in PROD.. that can quickly become a challenge.

How do I change that ?

If your SQL server was configured upfront by a true DBA, you shouldn’t have to worry and those system DB’s should already be on the proper location. But in case it’s not, then you need to open the SQL Server Configuration Manager on the SQL server. Pay attention to which SQL instance you’re selecting, in case you have multiple ones installed on the same box. Right-click on the “SQL Server” line of your instance and select properties.

In the properties windows, jump to the tab “Startup Parameters” and select the first line with the “-d”. Now update manually the new location where the master.mdf file will be residing. Be careful to not remove the master.dbf at the end and make sure the path is valid. Technically you should also change the path for the 3rd line (-l ) to align with the global settings.

After the changes are made, you should now see something like this :

Those changes only take effect after you restart the SQL server..
WARNING : make sure you have the proper .mdf & .ldf files in place!
The best thing is to STOP the SQL server services completely, move or copy the master.mdf & mastlog.ldf to their new location, and then restart the SQL services.

Once your SQL server is restarted and everything is running fine, you can re-run the GP Utilities and it will default to the correct location. Though you still have to change the Log file path, but at least it usually isn’t completely misplaced.

And in the Future ?

Ideally Microsoft should fix what I consider as a bug and use the default Data & Log location provided by the registry, based on the SSMS values.

In case you want to change the default location values by scripting, it can be done thru a SQL query too (this is not the start location path, just the regular data & log paths).

-- This script allows for remote execution to the SQL server registry and update/change the values of the
-- default location of the Data, Log & Backup files.
-- source : https://thomaslarock.com/2017/06/changing-default-database-file-locations-in-sql-server-2016/
-- with enhancements by B. Bucher / GP Geek Inc. 2019-09-27


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'DefaultData'
	, REG_SZ
	, N'D:\SQL\Data'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'DefaultLog'
	, REG_SZ
	, N'E:\SQL\Logs'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'BackupDirectory'
	, REG_SZ
	, N'F:\SQL\Backups'
GO

Of course you need to adjust the path above to fit your own environment.
Hope this helped to understand how GP Utilities pulls default locations for your new company files.

Until next time, happy reading.
@GP_Beat