I assume you've restored entire partitions, but the partition drive letters are now wrong.
If SQL is looking for I: then you have three choices
a) Fix the drive letter of the partition that's now got the data. You said this is not an option. Is that because I: is already taken, or because something else is now looking for this data based on its new drive letter (E:)
b) Add an additional drive letter for the partition - nothing stops you referring to the same partition by more than one drive letter. This isn't an option if I: is already taken and you can't move that data from I:
c) Create a symbolic link on your new I: drive to point things back to what's now on E:. Start an elevated command prompt. Then type
I:
cd "\Program Files\"
mklink /d "Microsoft SQL Server" "E:\Program Files\Microsoft SQL Server"
If you already have a Microsoft SQL Server folder on your new I: then change the mklink command to make the MSSQL.1 folder as the symlink rather than the Microsoft SQL Server folder.
I quite often have to setup MS SQL Server and wondered if anyone can
provide advice on configuring the accounts the services should run as.
IMO this has been vaguely documented by Microsoft, while they point
you in the right direction I have never been able to find any concrete
examples.
It's actually documented quite thoroughly: http://msdn.microsoft.com/en-us/library/ms143504.aspx
Is there a part of that you're not sure about?
For simple deployments\development environments it is OK to use the
virtual account defaults the installer uses: e.g. NT
SERVICE\MSSQLSERVER
This is going to depend on the environment. I, personally, hate finding a server someone setup using a local account and asking to get access to network resources some time in the future, among other issues.
For production and in domain environments it's recommended to use
either a Managed Service Account, or create a domain user account(not
an admin) for each service.
Again, depends, but generally I would agree (a counter example would be availability groups where it makes sense to use a single domain account across all instances).
Allegedly if you use a domain account at installation time the
installer will set any required permissions for you.
Unless there is a failure, etc, it will do so. I'm not sure why the "Allegedly" part.
If changing the service account on an existing install from a virtual
account to a domain account the recommendation is to use the SQL
Server configuration manager to set the new service accounts.
Allegedly this will set any required permissions for you.
When changing any of the services for SQL Server, always use SSCM. Always. Period. It will set the permissions for the new account to the basics. If before the local system account was used and unrestricted permission to everything on the system was had, I would expect something to fail permissions after the change due to tighter controlled security. That's not a SQL Server SSCM fault, that's an admin fault of not granting proper EXTRA permissions (such as accessing a network share, restricted folders, items outside of the SQL Server install purview, etc.)
I just tried changing the service account in an existing install to a
domain account and it would give me a logon failure until I granted
the account 'log on as service' permission, which contradicts the part
where the SQL Server configuration manager will set any required
permissions. (Although im not sure if a GPO may have interfered with
setting this local security policy)
Sounds like a GPO is causing an issue (IMHO). Wouldn't be the first time :)
So my question is, if you create a new domain user account for each of
the SQL Server processes what permissions should be set for each
account?
I would explicitly set any permissions outside those stated in the msdn link I have above (also given by @joeqwerty and in your OP). For example, on a "backup" folder on a network share, on a new drive added to hold new databases (where setup was already run but the drive didn't exist), etc.
But it's not clear to me if that is something I should be doing
manually for the user I create to run the service as, or whether using
the SQL config manager should automatically set these permissions.
Unless something is extremely broken with the server, these shouldn't have to be manually given.
Best Answer
Typically they aren't run under a different account, but all under the same Windows account. This makes the management much simpler as there is only one account's password to change. The downside to this is that if the password needs to be changed for the account all the servers need to be restarted at once.
As for permissions, just set the SQL account as a normal domain user, and let the SQL installer modify the rights on the machine that are needed. It will give the account the rights that it needs to run SQL.
The benefit to having one account per service is that you get a more fine grained control of what objects on the domain each account gets rights to. While more secure in larger environments it often isn't practical to control things at this level as you'll quickly end up with hundreds or thousands of accounts just for running SQL Services.