Unable to start SQL Resource on the SQL Server Cluster environment

Question

Unable to start SQL Resource on the SQL Server Cluster environment

SSC Enthusiast

Points: 152

November 11, 2017 at 9:40 am

Hi,
I have tried to find answer on this question, reading the documentation and searching the internet, but without success.
In my test environment I have 3 virtual nodes belonging windows/sql server cluster.
OS: Windows Server 2012 R2 64-bit
SQL Server: SQL Server Enterprise Edition 2014
I have idea to simulate migration of sql server cluster disk from one storage to the new storage located on second virtual node.
On default sql server I don't have any user database, only system databases (master, model, msdb and temp).
Idea is to move system databases on the new storage cluster disk (only one disk) and give it the same partition name (in my case J:) and just try again to bring resources online.
I have followed next action plan (from web address https://gallery.technet.microsoft.com/SAN-Migration-step-for-SQL-d66dea19 ), but without success:

S.NO

Activity

1

Open “Failover Cluster Manager”

2

Expand out the cluster you’re working on and select “SQL Server” under Services and applications

3

Select “Add storage” from the menu on the right

4

Select the new drive from the list, and click OK

5

Take SQL offline

6

Verify that SQL Server and SQL Server Agent are offline

7

Open Windows Explorer and copy the SQL data from the old drive to the new drive

8

Open Windows Explorer and copy the SQL Log from the old drive to the new drive

9

In New Temp DB drive --Create the folder structure as it is in old Temp DB drive

10

Back in Failover Cluster Manger right click on the old disk and select “Change drive letter”

11

Make the old drive a temporary drive letter other than what it currently is, Click OK

12

Next right click the new drive and select change drive letter, set the new drive’s letter to what the old drive was for both Data /Log and Temp

13

Right click on SQL Server and select “Bring this resource online”, do the same for SQL Server Agent

14

Do the failover to Second node and test the failover

15

Verify the all your databases and other components in SQL server

16

Now bring the SQL offline

17

Remove the Old storage drives from SQL dependencies and Windows cluster

18

remove the old storage from server level

19

Bring the SQL online and check

I have problem during implementation step 13. when I wish to online the SQL Server resource . I know that this is action plan for user databases, but please tell me is there anything specific and different, when I want to move system databases on the same partition name by copying all data from old partition to the new partition using the same partition name.

Could you please advice me about this.
Thanks in advance,
BR,
Dragon

Viewing 13 posts - 1 through 13 (of 13 total)

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

dragan.radosavljevi SSC Enthusiast Points: 152 More actions · Answer 1

Hi,
Additionally I have modified Dependencies attribute of the SQL Server Resource to be based on second disk (Cluster Disk 8) from added storage:

I have similar error/behavior after this modification.

BR,
D.

goher2000 SSCertifiable Points: 5479 More actions · Answer 2

goher2000

SSCertifiable

Points: 5479

November 14, 2017 at 1:09 pm

#1968080

check/fix permissions on the new drive

dragan.radosavljevi SSC Enthusiast Points: 152 More actions · Answer 3

Hi,
Thanks a lot Goher on this reply, but I am not sure about which permissions do you think.
I have compared permissions on both disks, but I don't see any difference (X: old disk, J: new disk with full copy of content of X:):

Please advice me if you have any additional idea.
Regards,
d.

Ant-Green SSC Guru Points: 113563 More actions · Answer 4

what errors are reported in the eventvwr when trying to start the service?

goher2000 SSCertifiable Points: 5479 More actions · Answer 5

stop the cluster service on both nodes, then start sql server from sql configuration manager and see if you can start the sql, post the sql error log if you are unsuccessful

dragan.radosavljevi SSC Enthusiast Points: 152 More actions · Answer 6

anthony.green - Wednesday, November 15, 2017 7:07 AM
what errors are reported in the eventvwr when trying to start the service?

Hi,
Thanks, yes definitely you have right this is related with permissions on these files.
From the eventvwr I get this error about permissions>

These are the permissions on file from log>

Please advice me how to copy files from source partition to the new partition with kept/same permissions of all files/folders.

Regards,
d.

Ant-Green SSC Guru Points: 113563 More actions · Answer 7

dragan.radosavljevi - Wednesday, November 15, 2017 7:51 AM
anthony.green - Wednesday, November 15, 2017 7:07 AM
what errors are reported in the eventvwr when trying to start the service?
Hi,
Thanks, yes definitely you have right this is related with permissions on these files.
From the eventvwr I get this error about permissions>
These are the permissions on file from log>

Please advice me how to copy files from source partition to the new partition with kept/same permissions of all files/folders.
Regards,
d.

Go to the folder and grant the following account full control

NT Service\MSSQLSERVER

You will need to search the local machine for that account not the domain.

Repeat for all nodes in the cluster

dragan.radosavljevi SSC Enthusiast Points: 152 More actions · Answer 8

anthony.green - Wednesday, November 15, 2017 8:44 AM
dragan.radosavljevi - Wednesday, November 15, 2017 7:51 AM
anthony.green - Wednesday, November 15, 2017 7:07 AM
what errors are reported in the eventvwr when trying to start the service?
Hi,
Thanks, yes definitely you have right this is related with permissions on these files.
From the eventvwr I get this error about permissions>
These are the permissions on file from log>

Please advice me how to copy files from source partition to the new partition with kept/same permissions of all files/folders.
Regards,
d.
Go to the folder and grant the following account full control
NT Service\MSSQLSERVER
You will need to search the local machine for that account not the domain.
Repeat for all nodes in the cluster

Thanks a lot,
I have granted access to user MSSQLSERVER and started Resource SQL Server.

Now I am not able to start resource SQL Server Agent (I didn't find anything specific in error logs during trying to start resource).
Any idea about this?
Thanks!Post reply
Regards,
d.

Ant-Green SSC Guru Points: 113563 More actions · Answer 9

Again what error messages do you get in the event viewer and error logs.

Remember we cannot see your server so you need to give us all the usual information to help debug.

Ffrom this I take it you have moved the installation drive where SQL was installed, best way to avoid all this would of been to uninstall and reinstall or do a side by side migration

dragan.radosavljevi SSC Enthusiast Points: 152 More actions · Answer 10

anthony.green - Thursday, November 16, 2017 1:45 AM
Again what error messages do you get in the event viewer and error logs.
Remember we cannot see your server so you need to give us all the usual information to help debug.
Ffrom this I take it you have moved the installation drive where SQL was installed, best way to avoid all this would of been to uninstall and reinstall or do a side by side migration

Thanks,
I have granted user NT Service\SQLSERVERAGENT full control on the following LOG directory:

After this modification I have successfully started SQL Server Agent resource:

Thanks for your help.
Regards,
d.

goher2000 SSCertifiable Points: 5479 More actions · Answer 11

use icACLs command to copy the permission, google it dont ask me 🙂

easy way: give permission to 'everyone' on the root of the drive.. (not recommend)

dragan.radosavljevi SSC Enthusiast Points: 152 More actions · Answer 12

Thanks,

I have just ask you for your experience regarding this. I have used robocopy for this.

Thanks a lot for your time and effort to help.

Regards,

d.