MSSQL.1, MSSQL.2 Naming Convention for Folders During Install -- Avoidable?

  • Hello--

    Got an interesting dilemma for any Installation Experts out there...

    When installing MSSQL Server 2005, either via commandline or through the wizard, you're prompted for an installation directory for for binaries, IE C:\program files\Microsoft SQL Server

    The installer then creates subdirectories such as MSSQL.1 if it's the first instance being installed, MSSQL.2 if it's the second instance, etc. C:\program files\Microsoft SQL Server\MSSQL.1\ and C:\program files\Microsoft SQL Server\MSSQL.2\ would be the full paths.

    With the replication software my company uses, it would make things much easier if I could get rid of having the .1 at the end of MSSQL.1 or specify my own instance ID. For instance, I could have this folder be named C:\program files\Microsoft SQL Server\MSSQL\ or C:\program files\Microsoft SQL Server\MSSQL.DB46\ instead of C:\program files\Microsoft SQL Server\MSSQL.1\.

    (note when I say instanceid I'm not talking about a named instance, just the incremented ID that is created and appended to the end of the mssql folder for each instance)

    Does anyone know if this is possible? I have been scouring the net and haven't found anything as of yet.

    Any insight would be appreciated.

    Thanks

    -Justin Roll

    MCITP

  • As far as I know, this cannot be overwritten. The numbering is not just for the database engine, it also is defined for SSRS/SSAS and other components.

    The value is stored in the registry to translate the instance name to the correct folder and is created by the installer. So, when you go to install a new instance - it can be incremented appropriately.

    This is only required for the binaries and the system databases during the install. Once the install has been completed, you can set the default database a log location to whatever works for you so the user databases will be created wherever you want them to be created. You can also move the system databases to a different folder structure after the install.

    I normally install the binaries to the default location and during the install change the data directory to the root of my system DB drive. This places the system DB's and associated files in that directory. For example, if my system DB drive is S:\ - I use that for the install and end up with S:\MSSQL\MSSQL.1\... This will include the SQL logs, default trace and system databases.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hmmm...

    After a little research, I have been able to move the system datafiles around. For example, I just moved them from D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data to D:\Data.

    It would be really nice to move the binaries to a directory not under MSSQL.1, though. But it seems like there is no way to do this. I'm kind of tempted to hack around with an install on a test server and try messing with the registry and creating my own services (no, I don't have high hopes for this).

    Appreciate the help so far!

  • There is an INSTANCEID parameter that is supposedly usable to set your own value for .1, .2, etc. The drawback is you only have one value per run of SETUP, which implies you can only install one service at a time if you use this option.

    I have not played with this myself, but it might be worth trying if you do not mind running the install process lot of times.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • This is very interesting, EdVassie. I am going to look into this.

  • It is not a good idea to change this folder name. when you install hot fixes or new service pack installation process checking in this folder and if the name is not standard it is failing.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply