Server 4024 part 2 - Management

  • Article

In the second post in this series I wanted to look at how changes to the way you manage things in Windows Server 2012 (WS 2012) affects your management of SQL Server 2012 (SQL 2012).

Multi Server Management

Traditionally to manage the OS we used to remote desktop onto every server we managed and applied changes directly to it, however that’s not really viable anymore; for one thing Virtualisation has led to a lot more servers for us to manage. Now there is server manager and as with SQL Server Management Studio (SSMS) we can now register multiple servers in one console and pretty well do everything we need to from there, including adding new features, check performance and monitor alerts, events as well as starting or stopping services..

server manager

hopefully your servers will look healthier than mine, but at least I can see where the problems are

Notes:

PowerShell 3.0

Allows all of the above to be done from the command line, and now is the time to bite the bullet and learn PowerShell;

 

  • The new built in PowerShell ISE has a lot of guidance in it to help you get started like snippets, and help to set all the switches in commands you aren’t familiar with.
  • If you have SQL management tools installed then you’ll see you can select SQLPS as a module and get help on the specific SQL2012 cmdlets as well – note you don’t have to load modules anymore in PowerShell 3 it’ll do that for you..

sql and powershell

The commands section on the right gives me help on the SQL Server PowerShell cmdlets

  • PowerShell can be executed remotely on other machines as well as in sessions which can be run in parallel and can persist after a reboot if needed, for example;

Invoke-Command -ComputerName London-SQL -ScriptBlock `
{
Backup-SqlDatabase -Database adventureworks -BackupAction Database -CompressionOption On -LogTruncationType TruncateOnlyBackup-SqlDatabase
}

..runs the PowerShell inside the braces on my London-SQL virtual machine

 

MinShell

Given that you are managing servers remotely why put all the tools on each server? We get this with SQL Server and generally don’t install SSMS on every server. WS2012 now allows you to deselect installing all the associated mmc snap-ins for every role/feature and also allows you to remove some or all of the GUI as well. In fact the default install option for WS2012 is now Server Core, with nothing on it but PowerShell, Notepad, a command prompt, Registry Editor and Task Manager.

SQL 2012 runs just fine on Server Core, and with SQL 2012 sp1 you can also run Analysis Services, Integration Services but not Reporting Services (for more info check here).  This reduces the attack surface of the OS and will cut you patching in half. 

Note: I have already got posts on how to do this and also how to properly work with sysprep using the image prepare and complete options when installing SQL Server both form the installer and form the command line

 

and finally..

  • Please  ensure your SQL Server is given a good home and try it on Windows Server 2012
  • I’ll be discussing this during my sessions at SQL Relay (in Glasgow, Leeds, Birmingham & Norwich)