Powershell,PSEexec and lock pages in memory – made easy

27 April,2011 by Jack Vamvas

What if you had to create a script stack that installs SQL Server from an image library? And  customisations – such as adding the SQL Server Start up account to the Lock Pages in Memory Local Policies. ?

If this needs to be rolled out quickly across hundreds of servers , or just one , then there is a way to do this using Group Policy Security Templates and Security Database.  – along with secedit , PSExec and Powershell

 secedit.exe is a command line tool and comes with Windows. You would use Powershell to call secedit.exe , pass a couple of parameters and job done!

 Main steps are:

Step 1 : Create Security template and Security Database

Step 2 : Write syntax for secedit.exe

Step 3: Create Powershell script to grab parameters and execute the command

 Before you start – create a MMC consoled and add 2 snap ins

1)Security Configuration and Analysis

2)Security templates

 Step 1: Create Security template and Security Database

 Expand Security Templates and right click on the security path : Click New template : Create a template name – for this example I’m using “sqlmemlock”. Click OK and you should see the  template appear on the template list.

Expand the template “sqlmemlock” | Local Policies | User Rights Assignment . I added LOCAL SERVICE for the example, but normally I have a dedicated SQL Server Start Up acct.

You should now have something like this:

Lock_pages_ps_1 

 

 

Next – create the Security database. Right click the snap-in Security configuration and Analysis| Open Database and then add a filename : for the example : I’m using “sqlmemlockdb” | Click Open | Import Template : “sqlmemlock” (the one you created a few minutes ago).

 

Right click on  Security configuration and Analysis and you’ll see 2 options : Analyze this computer and Configure Computer now. Analyze this computer  is useful to do a pre and post check.

 Once you’ve done the analysis – choose Configure now and using gpedit.msc – you should see the desired result.

If this has not worked – troubleshoot and ensure you have this part working!

 Step 2 : Draft syntax for secedit.exe

Assuming – you’ve got the template  in “C:\windows\security\templates\” and  and sdb  in “C:\windows\security\databases\”

 The syntax is :

Secedit /configure /cfg  templatefilename /db databasefilename/overwrite /log logfilename

 For our example:

 Secedit /configure  /cfg C:\windows\security\templates\sqlmemlock.inf /db sqlmemlockdb.sdb  /log C:\windows\security\logs\sqlmemlock.log

Lock_pages_ps_2 

Step 3: Create Powershell script to grab parameters and execute the command

 There are many ways to execute the script on multiple servers. Powershell & psexec – are fantastic tools – which can be used  to manage your servers.

 This is just one way of managing your servers

 1- Write the above syntax into a cmd file – I’ll call it – lockpages.cmd

 Secedit /configure  /cfg C:\windows\security\templates\sqlmemlock.inf /db sqlmemlockdb.sdb  /log C:\windows\security\logs\sqlmemlock.log

 2- Drag sqlmemlock.cmd onto the remote server

 3-       Create a powershell script and write this syntax :

 cmd /c psexec \\MYSERVER1  "C:\sqlunattended\lockpages.cmd"

 3-      Execute the powershell command – and presto!

 There are many possibilities of combining this process with a list of servers . Syntax that can iterate a list of servers.  Check this post Powershell : SQLCMD and invoke-expression  - adapt the syntax to your purposes

If you want syntax to to iterate through a list of servers, let me know and I’ll post.

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Powershell,PSEexec and lock pages in memory – made easy


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer