Last active
July 17, 2019 09:32
-
-
Save yutopio/11539123014de24572b7 to your computer and use it in GitHub Desktop.
Adding ACL for directory full access. (SQL Server on Azure VM can have tempdb on D Drive for performance. Allow MSSQLSERVER for full control in such case)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// See this article for the background. | |
// http://blogs.technet.com/b/dataplatforminsider/archive/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions.aspx | |
using System; | |
using System.Diagnostics; | |
using System.IO; | |
using System.Linq; | |
using System.Security.AccessControl; | |
using System.Security.Principal; | |
using System.ServiceProcess; | |
using System.Threading; | |
class Program | |
{ | |
const string TempDbPath = @"D:\tempdb"; | |
const string ServerAccount = @"NT Service\MSSQLSERVER"; | |
const string EventLogSource = "SQL Server Launcher"; | |
static void Main(string[] args) | |
{ | |
AppendLog( | |
"Set ACL started.", | |
EventLogEntryType.Information); | |
var target = new DirectoryInfo(TempDbPath); | |
if (!target.Exists) target.Create(); | |
var acl = target.GetAccessControl(); | |
var ruleCount = acl.GetAccessRules(true, true, typeof(NTAccount)) | |
.OfType<FileSystemAccessRule>() | |
.Select(x => (x.IdentityReference as NTAccount).Value) | |
.Count(name => string.Compare(name, ServerAccount, ignoreCase: true) == 0); | |
if (ruleCount == 0) | |
{ | |
var rule = new FileSystemAccessRule( | |
ServerAccount, | |
FileSystemRights.FullControl, | |
InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, | |
PropagationFlags.None, | |
AccessControlType.Allow); | |
acl.AddAccessRule(rule); | |
target.SetAccessControl(acl); | |
} | |
// Sleep until ACL is persisted. | |
Thread.Sleep(TimeSpan.FromSeconds(10)); | |
var services = ServiceController.GetServices() | |
.Where(x => x.DisplayName.StartsWith("SQL Server (")) | |
.Select(service => | |
{ | |
var t = new Thread(() => | |
{ | |
while (service.Status != ServiceControllerStatus.Running) | |
{ | |
try | |
{ | |
service.Start(); | |
} | |
catch (Exception e) | |
{ | |
AppendLog( | |
e.GetType().FullName + ": " + e.Message, | |
EventLogEntryType.Error); | |
} | |
Thread.Sleep(TimeSpan.FromMinutes(5)); | |
} | |
}); | |
t.IsBackground = false; | |
t.Start(); | |
return t; | |
}); | |
services.ToList().ForEach(t => t.Join()); | |
AppendLog( | |
"Set ACL successfully ended.", | |
EventLogEntryType.Information); | |
} | |
static void AppendLog(string message, EventLogEntryType logLevel) | |
{ | |
using (var eventLog = new EventLog("Application")) | |
{ | |
eventLog.Source = EventLogSource; | |
eventLog.WriteEntry(message, logLevel, 101, 1); | |
} | |
} | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment