Share via

Custom SQL Server Startup Script

Jeffrey Beck 0 Reputation points
2024-10-21T16:08:00.77+00:00

I'm hoping someone here has faced a similar challenge and could offer insights on how to best approach this. We are running several SQL Server virtual machines in our environment, and we're using the SQL Server IaaS Extension in Azure. We're relying on this extension for automated database backups and Entra ID authentication on the SQL Server instances.

These SQL Server VMs are connected to an elastic SAN via iSCSI mounts, and we've followed best practices for the configuration. However, we’re encountering an issue when our SQL Server VMs need to reboot: the SQL Server service starts before the system completes NTFS checks on the iSCSI-mounted drives. This results in the databases going into pending recovery mode, as SQL Server can't access the database files in time.

To address this, I wrote a custom startup script that waits for a successful read/write test to the drives before starting the SQL Server services. When configuring this in Task Scheduler, I noticed that the SqlIaaSExtension.SqlServerStarter.exe is already set to run on startup (due to the extension installation).

I don't want to disable this task since we rely on the SQL IaaS extension for automated database backups, Entra ID authentication, and other benefits.

My questions are:

  1. Has anyone encountered a similar issue where iSCSI drives aren’t accessible or fully mounted before SQL Server starts?
  2. If so, how did you handle it? Did you write a custom startup script like I did, or did you manage to adjust the timeout settings on SQL Server to prevent it from failing so quickly?

Any suggestions or insights would be greatly appreciated.

Thanks!

SQL Server on Azure Virtual Machines
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,223 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
13,486 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jeff Beck 5 Reputation points
    2024-10-24T13:57:15.1666667+00:00

    Mahesh, I really appreciate your feedback on this. What I ended up doing was disabling the SqlStartUp task that was created by the IaaS SQL extensions and added the action from it as an extra step in my custom startup script task. So, in the end, my startup script will run, then launch the .exe for the extension. My worry about this was if it was possible for the SqlStartUp task to get enabled again automatically causing a conflict again without knowing it. Do you know if that task could be modified and enabled again on its own if there is an update to the extension? We did put these SQL VMs into a maintenance window to only reboot on weekends when necessary decreasing the surface area of SQL having issues upon startup. I also have a script running every 5 minutes looking at the status of the databases to alert if any of them are in a status other than Online.

    Since I have my custom startup script, I set both the SQL Server and SQL Server Agent services to be manual rather than automatic. I thought about setting the dependency where SQL server doesn't start until the iSCSI service is available, but I was able to see in the event logs where the iSCSI service started, SQL server then tried to start and then after that the NTFS system checks completed to make the drives available. So as you can see, having that as a dependency isn't foolproof, hence why my custom startup script actually tests writing a file to all iSCSI mounted drives before starting SQL.

    I think we found a good solution as long as the SqlStartUp task that was automatically created doesn't get enabled again on it's own.

    1 person found this answer helpful.
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.