Installing Microsoft SQL Server

I recently upgraded my working computer so had to reinstall all of my software. I ran into problems with my SQL Server installation.

When I am installing any major piece of software I must remember to install it with Administrator privileges.

In the installation process I give the SQL Server Instance ID a name. If you don’t do this your SQL Server instance will be named mssqlserver. You will use this for all database connection strings in your applications.

With SQL Server it is always good practice to install your software on your main drive and install your databases on another drive or partition. In a production environment this is essential. Initially I tried to change my default database location to a folder that contained the databases from my previous computer and this was a big mistake. My installation crashed when SQL Server didn’t have access to change the permissions of each database in the folder.

I reinstalled SQL Server and this time allowed it to use the default data folders on my main drive.

My database directory had all of the SQL Server accounts installed so I tried to allow the files to inherit those permissions but this didn’t work. looking at each file I noticed that they didn’t have an owner account attached to the file.

To fix this issue I had to add my user account to each database file with full permissions. I then removed the unknown account from each file as well. The image below shows the file with full permissions.

Once I had completed this process for each database file I allowed all the files to inherit the folder permissions.

Now that all files had the correct permissions I changed the default data folder back to my data drive.

When I run SQL Server I also run it in Administrator mode because there are certain processes that will fail without elevated permissions.