Archiv der Kategorie: Powershell

Automating Windows and SQL Updates with Powershell (T-SQL Tuesday #130 – Automate Your Stress Away)

Veröffentlicht am von
3

Elizabeth Noble (b|t) hosts this month series of blog posts with the title „Automate Your Stress Away“.
In case you are new to T-SQL Tuesday this is the monthly blog party started by Adam Machanic (b|t) and now hosted by Steve Jones (b|t). 

You can read more about the invite by clicking on the T-SQL Tuesday logo.

I’ll write about my struggle with the Windows and SQL Updates on my SQL Server.

We use WSUS as part of our deploying strategy for Updates, because we need control about what Updates are going to be shipped to our servers. So we ship Updates to some chosen Server before we ship them to all.

Next important part is when to install updates. As most of you know, during installation of SQL Server Updates, also with other Windows Updates, affected services will be restarted. Because of that, installation is not possible during working hours.

With Windows Server 2016 the update times can be adjusted, but not the day.

So we decided to manage all by ourselves.
We created a job that runs on two of the sundays of the month at 2 o’clock in the morning. During this job, the updates were fetched from the WSUS server, installed and the machine will be rebooted afterwards, if neccessary.
We started with one time per month but from time to time Updates didn’t get installed and it needs a second try. So we changed to two times a month.

This is the PowerShell Script we use. As I’m not an expert any help or hint for doing better is really appreciated.


# This is the modul we use
# Install-Module PSWindowsUpdate -force

# Here we are logging what Updates got installed.
get-date -Format u >> c:\WUInstall.log

Get-WUInstall -Install -AcceptAll -verbose >> c:\WUInstall.log

# most of the time when Updates are installed, a restart is neccessary;
# when restart is needed send Email and restart.

$reboot=Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending"
if ($reboot -eq $true)
{

   $subject='Windows Updates on server: <servername>'
   $sendFrom=<Mail From>
   $sendTo=<Mail to>
   $smtpServer=<SMTP Server>
   
   Send-MailMessage -From $sendFrom -To $sendTo -Subject  $subject -SmtpServer $smtpServer -Attachments 'c:\WUInstall.log'

    Restart-Computer -Force
}

Then we use the task planner of windows to start the script at the time described above.

One Problem we are struggling with in the moment is that the task planner also starts the update on saturdays at the same weekends we want it to run on sundays. We have no idea what’s going on there.

Thanks for reading,
Volker

Credits for some parts of the script go out to Eelco Drost and his article https://eelcodrost.com/2019/10/16/patching-sql-server-ag-using-sccm-and-powershell/
Thanks for sharing!

Disable and enable multiple Subscriptions in PBIRS/SSRS 2017

Veröffentlicht am von
Antworten

Our Reporting Team wanted to disable all of the actual subscriptions because our production has to be stopped in case of the Coronavirus Pandemie and reports don’t show realistic data. But they want to be able to activate the same reports again later – and not all reports!

Our first „solution“ to disable all of the reports was to send the reports by email
to „/dev/nul“ 😉
Therefore we changed the SMTP Mailserver in the Reporting Services Configuration. Below is a link to a mini PowerShell SMTP Server „Blackhole“ which we tested on the SSRS. Later I’ve found that there doesn’t need to be a SMTP Server running. Changing the entry works without any problem.

The success lasted only one day.
Then the Reporting Team needed to activate some reports again, so our solution doesn’t work anymore.

A Google Search shows up several PowerShell snippets to disable or enable simple reports via the subscription id or do this for all of the reports (Link in the notes at the end of this article). But there is no solution for a huge number of reports (~600 Reports).
BTW: Enabling and disabling Report Subscriptions only works from version 2016 and beyond!

With a litte help from my friend Claudio @ClaudioESSilva I implemented the idea of storing the subscription IDs in a sql table and using them for disabling and enabling all IDs in a loop.

<# 
Name: SSRS enable disable Reports.ps1
Author: Volker Bachmann
Date: 11.4.2020

:
Disable and Enable Reports or their subscription with a list of SubscriptionIDs

#>

Function LogWrite
{
    # simple function for logging to a file.

    Param ([string]$logstring)
   $Logfile=".\enable_disable_reports.log"

   $logstring += " ["
   $logstring += (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
   $logstring += "]"

   Add-content $Logfile -value $logstring
   
}


# $URI="http://<SSRS Server>/ReportServer/ReportService2010.asmx" 
[int]$counter=0


# ############################################################################
# Disable specific Subscriptions, defined in a referenced SQL Table

<# SQL Table Definition and Load

    CREATE TABLE [dbo].[Subscriptions](
    [id] [INT] IDENTITY(1,1) NOT NULL,
    [subscription_id] [UNIQUEIDENTIFIER] NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO Subscriptions (subscription_id)
    VALUES

    ('D0DD9A1C-2393-4529-9F93-85DEC5FCD42F'),
    ('A023E6B9-943E-4A53-806D-1A05A8FBE0A8'),
    ('F28A93D5-FB56-46BE-A057-5B16945728B6');

#>

[string]$serverName = '<SSRS Server>' # ServerName for the list of subscriptionIDs

# connection to the table of subscription ids
$SubscriptionList = (Invoke-SQLCmd -query "select * from [Subscriptions]" -Server $serverName )

# connection to the Reporting Server with actual credentials
$rs2010 = New-WebServiceProxy -Uri $URI -Namespace SSRS.ReportingService2010 -UseDefaultCredential;  

# create new chapter in the logfile
LogWrite  "############# Start disable ###################"

# loop through all subscription ids of the list and disable them
ForEach ($subscription in $SubscriptionList)  
{  
    # Error-handling with a try/catch 
 
try {
    $rs2010.DisableSubscription($subscription.ItemArray[1]);  
        
        write-host "disabled :"  $subscription.ItemArray[1]

        $output = "disabled : "+  $subscription.ItemArray[1]
        LogWrite  $output
    
        $counter ++ # counting the amount of successful disabled subscriptions

    }
catch {
    write-host "Error during disabling of id :"  $subscription.ItemArray[1]

    $output =  "Error during disabling of id : "+  $subscription.ItemArray[1]
    LogWrite  $output
    }    
}# End ForEach

$output =  "=> Amount of disabled Subscription IDs : " 
$output += $counter 
LogWrite  $output

# Chapter End in Log
LogWrite  "############# End disable  ###################"

This was for disabling the subscription IDs – enabling is nearly the same:

# ###################################################################
# Enable specific Subscriptions, defined in a referenced SQL Table
 
# Definition and load of the table see above.

# ServerName for the list of subscription ids
[string]$serverName = '<SSRS Server>' 

# connection to the table of subscription ids
$SubscriptionList = (Invoke-SQLCmd -query "select * from Subscriptions" -Server $serverName )

# connection to the Reporting Server with actual credentials
$rs2010 = New-WebServiceProxy -Uri $URI -Namespace SSRS.ReportingService2010 -UseDefaultCredential;  

# create new chapter in the logfile
Log-Write  "############# Start enable ###################"

# loop through all subscription ids of the list an enable them
ForEach ($subscription in $SubscriptionList)  
{  
    #try/Catch see above
try {
    $rs2010.EnableSubscription($subscription.ItemArray[1]);  

        write-host "enabled :"  $subscription.ItemArray[1]

        $output = "enabled : "+  $subscription.ItemArray[1]
        LogWrite  $output
    
        $counter ++ # count the amount of successful subscriptions
    }
    catch
    {
    write-host "Error enabling id :"  $subscription.ItemArray[1]

    $output = "Error enabling id : "+  $subscription.ItemArray[1]
    LogWrite  $output
    }
} # End ForEach

$output =  "=> Amount of enabled Subscription IDs : "
$output += $counter 
LogWrite $output

# End in Log
LogWrite  "############# End enable ###################"

These two snippets work for me.
Disabling Subscriptions brings up some Errors for me when:

  • Owner of the Subscription is not valid any more -> change Owner
  • Subscription is not a report – it was a Power BI or other subscription
  • In the subscription was an End date set – than the disable didn’t work for me.

Any comments, additions?

Thanks for Reading,
Volker

Notes: Links to documentation from Microsoft:

SMTP Blackhole (German only): https://www.msxfaq.de/tools/sonstige/smtp_blackhole.htm 

Speaking at SQLGrillen 2018

Veröffentlicht am von
Antworten

[German version below]

Hello #SQLFamily,

as previously announced I was selected to speak in the Newcomer Track of SQL Grillen 2018.

My first ever session on a SQL Server conference was in German and called „Mission SQL Migration – Aus Blech wird VM“ and was about migrating physical SQL Server to virtual ones.
The important parts were the VMware architecture, guest configuration and how to migrate the whole SQL Server with one single command.

About 20 people were in the room and the session went smooth. Also the demo on a remote server in our company network works like a charme. Because of some deeper discussions about the need of virtualization and the pros and cons, I was not able to show some more of the fantastic dbatools commands I’ve prepared.
So only the Start-DbaMigration was shown and the „Best Practices“ commands like Test-DbaMaxMemory, Test-DbaMaxDop, Test-DbaTempDBConfiguration (…) and the important Invoke-DbaDatabaseUpgrade for upgrading the migrated Databases to the last compatibility level were not shown.

But at the end I finished just in time and I was happy how it works.
I’ve got some direct Feedback from friends and also my mentor Björn Peters (t) who had helped me a lot preparing the session. Thanks a lot!

Hopefully I’ll be able to present on other SQL conferences in the future.

Here is the complete (german) presentation: Mission SQL Migration – Aus Blech wird VM 2018-06-20

Thanks for reading,
Volker

[German]

Hallo # SQLFamily,

Wie bereits angekündigt, wurde ich ausgewählt, im Newcomer Track von SQL Grillen 2018 zu sprechen.

Meine allererste Session auf einer SQL Server-Konferenz war auf Deutsch und hieß „Mission SQL Migration – Aus Blech wird VM“. Es ging darum, physikalsche SQL Server auf virtuelle SQL Server zu migrieren.
Die wichtigsten Teile waren die VMware-Architektur, die Guest-Konfiguration und die Migration des gesamten SQL-Servers mit einem einzigen Befehl.

Ungefähr 20 Leute waren im Raum und die Session lief glatt. Auch die Demo auf einem Remote-Server in unserem Firmennetzwerk funktionierte reibungslos. Aufgrund einiger tiefer gehender Diskussionen über die Notwendigkeit von Virtualisierung und die Vor- und Nachteile, konnte ich einige der fantastischen dbatools-Befehle, die ich vorbereitet hatte, nicht mehr zeigen.
Es wurde also leider nur die eigentliche Migration mit dem Kommando Start-DbaMigration gezeigt und die „Best Practices“ -Befehle wie Test-DbaMaxMemory, Test-DbaMaxDop, Test-DbaTempDBConfiguration (…) und das wichtige Invoke-DbaDatabaseUpgrade für das Upgrade der migrierten Datenbanken auf die letzte Kompatibilitätsstufe wurden aus Zeitmangel leider nicht mehr gezeigt.

Aber am Ende war ich gerade rechtzeitig fertig und ich war glücklich, wie es geklappt hat. Ich habe ein direktes, positives Feedback von Freunden und auch von meinem Mentor Björn Peters (t) erhalten, der mir bei der Vorbereitung der Session sehr geholfen hat. Vielen Dank nochmal dafür!

Ich hoffe auch auf zukünftigen SQL Konferenzen noch als Sprecher vortragen zu können um meine Erkenntnisse und Erfahrungen mit der SQL Community zu teilen.

Hier findet sich noch die Präsentation: Mission SQL Migration – Aus Blech wird VM 2018-06-20

Vielen Dank fürs Lesen,
Volker

#PSBlogWeek – free PDF with my „SQL Server Migration with PowerShell dbatools“ now available

Veröffentlicht am von
Antworten

Hello,

a compilation of all the PowerShell articles of the #PSBlogWeek 2017 is now available.
Included is my article about „SQL Server Migration with PowerShell dbatools“.

This is the PDF: PSBlogWeek eBook PowerShell-Server-Management

And here is the link to the original announcement from Adam Bertram (t | b)): http://www.adamtheautomator.com/psblogweek-powershell-blogging-entire-week/

You’ll find Links to the original six blog articles on the activity Page from #PSBlogWeek.

http://psblogweek.com/psblogweek-activity

Thanks go out to all contributors and for Adam for hosting the event and for compiling the eBook.

Thanks for reading!
Volekr

 

Migration of SQL Server with PowerShell dbatools #PSBlogWeek

Veröffentlicht am von
5

This article is about server management with PowerShell and is part of the #PSBlogWeek series (http://psblogweek.com) , created by Adam Bertram.

Index:

  1. Introduction to dbatools
  2. Migration Prerequisites
  3. Best Practices
  4. Migration
  5. References

It is also part of my blog series about migrating our physical SQL Server to a VMware Environment. For now, all of these articles are in German only – sorry. The first three articles describe the basic server configuration, installation, and VM guest configuration of the VMware Environment. This article describes the migration itself.
I’ll write a recap of the whole series in English later on. 🙂

  1. Introduction to dbatools

I got in contact with PowerShell some years ago, but I wasn’t satisfied with what needed to be done to maintain SQL Server.

However, Microsoft has made a lot of improvements since then, and with contributions from several PowerShell Experts and MVPs – such as Chrissy LeMaire, Claudio Silva, Rob Sewell, Constantine Kokkinos and many more, there is now a module that helps to maintain SQL Server 2005+. It’s called dbatools, and you can find it here https://dbatools.io. The project is hosted on GitHub and the module is available totally free of charge!

The dbatools community has grown to over 50 contributors with more than 300 SQL Server best practice, administration and migration commands. An overview of the commands can be found here: https://dbatools.io/functions/.

2. Migration Prerequisites

Now, let’s turn our attention to the prerequisites for the migration of a physical SQL Server 2008 to a VMware-based SQL Server 2016 on Windows Server 2016. The positive thing here was that there was no need to reinstall everything  on the same physical hardware over the weekend. Instead, we bought a totally new VMware Environment with three Dell servers, two network switches, and new storage. There was enough time to test the new SQL Server, the SAN, and build a good configuration for the virtual machines. Most of the VM configuration is based on the blog series „Stairway to Server Virtualization“ by David Klee, which can be found on SQL Server Central.

For migration purposes, we installed an additional Windows Server 2016 with PowerShell 5, with SQL Server 2016 as an admin workstation. On the SQL Server, we installed the dbatools by using the easy install-module command:

# allow execution of scripts that are signed from a trusted author, if not already set
Set-ExecutionPolicy RemoteSigned 

# install 
install-module dbatools

During installation, you may get a confirmation dialog prompting you to accept installation of the NuGet Package Manager. You should accept; otherwise, you’ll need another installation option. These options are described on the dbatools website: https://dbatools.io/download.

The dbatools module is in permanent development – meanwhile, they are near the first major release 1.0 – so you should check for the latest version and update often. Updating is as easy as the installation:

Update-dbatools
# Get version number
Get-Module -Name dbatools
# Get all available versions that are installed
Get-Module dbatools -ListAvailable

dbatools available versions

 

 

On the screenshot we see five versions of the tools installed, so we have to activate the latest version with the comand import-module.

#Import specific version - here 0.9.39
import-module 
"c:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.39\dbatools.psd1"

# List all available commands of the loaded module version
Get-Command –module dbatools

With the last command above you get a quick overview of all the dbatools commands.

After installation of the base SQL Server VM we need to check some basic configuration options first. dbatools can help us with this as well. 🙂

All commands are created by experts with references to the corresponding articles where the code comes from.

3. Best Practices

  • Max Memory
    • Test-DbaMaxMemory
    • This tests the actual max memory setting against the recommended setting.
    • Test-DbaMaxMemory -SqlServer sqlserver01
# command to set the max memory to the recommended
Set-DbaMaxMemory -SqlServer sqlserver01
# or to a fixed value of 2 GB
Set-DbaMaxMemory -SqlInstance sqlserver01 -MaxMb 2048
  •  TempDB
    • Test-DbaTempDbConfiguration
    • With SQL Server 2016, you get the option to configure the tempdb configuration during installation, but not with older versions. With this command, you can control and later adjust it.
    • Evaluates tempdb against a set of rules to match best practices. The rules are:
      – TF 1118 Enabled: Is Trace Flag 1118 enabled? (See KB328551)
      – File Count: Does the count of data files in tempdb match the number of logical cores, up to 8?
      – File Growth: Are any files set to have percentage growth? Best practice is that all files have an explicit growth value.
      – File Location: Is tempdb located on the C:\ drive? Best practice says to locate it elsewhere.
      – File MaxSize Set (optional): Do any files have a max size value? Max size could cause tempdb problems if it isn’t allowed to grow.
    • Test-DbaTempDbConfiguration
    • The right configuration can be set by using the corresponding Set- command
      A service restart is necessary after reconfiguration, see following screenshot:
    • Set-DbaTempDbConfiguration
  • Disk
    • Test-DbaDiskAlignment
      • This command verifies that your non-dynamic disks are aligned according to physical requirements.
      • Test-DbaDiskAlignment -ComputerName sqlserver01| Format-Table
      • Test-DbaDiskAlignment
    • Test-DbaDiskAllocation
      • Checks all disks on a computer to see if they are formatted to 64k block size, the best practice for SQL Server disks.
      • Test-DbaDiskAllocation -ComputerName sqlserver01 | Format-Table
      • Test-DbaDiskAllocation
  • PowerPlan
    • Test-DbaPowerPlan
      • The Power Plan should be set to High Performance on every SQL Server.
      • Test-DbaPowerPlan -ComputerName sqlserver01
      • Test-DbaPowerPlan

 

  • SPN
    • We use DNS CNAMEs for referring to our SQL Server (See the article „Using Friendly Names for SQL Servers via DNS“ below). We need to adjust the SPN settings manually. That is easy with these commands:
      Get-DbaSpn and Set-DbaSPN
  • SQL Server Name
    • We created a Single VM template where all SQL Server are created from. With CPU, Memory and Disk Layout as described in the Stairway I mentioned above (1).
    • After creating a new VM out of the template the server name changes but the internal SQL Server name does not. Help comes again with dbatools command Repair-DbaServerName
      Works fine for me!

4. Migration

  • Now for the best part – the migration itself. You normally only need a single command to migrate everything from one SQL Server to another. As described in the Help documentation, this is a „one-button click“.
    Start-DbaMigration -Source sql2014 -Destination sql2016 -BackupRestore -NetworkShare \nas\sql\migration
  • This migrates the follwing parts as listed below. Every part can be skipped with a -no*** parameter as described in the Help documentation – for example, use -NoLogins if you don’t want to transfer the logins.
    • SQL Server configuration
    • Custom errors (user-defined messages)
    • SQL credentials
    • Database mail
    • User objects in system databases
    • Central Management Server
    • Backup devices
    • Linked server
    • System triggers
    • Databases
    • Logins
    • Data collector collection sets
    • Audits
    • Server audit specifications
    • Endpoints
    • Policy management
    • Resource Governor
    • Extended Events
    • JobServer = SQL Server Agent
  • If any error comes up, use the functions, that are called out of the Start-DbaMigration commands step by step.
  • Keep in mind that the server configuration is also part of the migration, so min and max memory and all other parameter in sp_configure are transferred. If you want to keep this settings as set by the best practices commands, you should skip the configuration during transfer. Use -NoSpConfigure!
  • So what is missing in the moment?
    • Most of the special parts of the additional services:
      • SSIS
      • SSAS
      • SSRS
  • You can test the whole migration with the -WhatIf parameter, which shows what’s working and what isn’t. Sometimes the connection to the target computer isn’t working because PowerShell remoting is not enabled (see above).
    There is a command to test the connection to the server, and you can find that here:
    https://dbatools.io/functions/test-dbacmconnection
    There is no need for updating the new server to the latest version of PowerShell, Version 3.0 is enough.
  • The whole command looks like this for me:
    • Start-SqlMigration -Verbose -Source desbsql1 -Destination desbaw2 -BackupRestore -NetworkShare \\DESBAW2\Transfer -DisableJobsOnDestination -Force
  • The parameter DisableJobsOnDestination is extremly helpful when you go to the next step and test the migration itself. When you do this more than once, you also need the parameter –Force, which overwrites the target objects (logins, databases and so on) if they exist from a previous test.
  • The parameter -Verbose is useful when an error comes up and you need to dig deeper into the problem.
  • Before we wrap up, her’s a link to a YouTube video that shows how fast the migration works. Of course it’s all going to depend on the size of your databases:
    https://youtu.be/PciYdDEBiDM

5. References:

  1. Stairway to SQL Server Virtualization by David Klee
  2. Using Friendly Names for SQL Servers via DNS

Thanks for reading,
Volker Bachmann

TSQL2sday #94 – SQL Server Best Practices Test with PowerShell dbatools #tsql2sday

Veröffentlicht am von
2

T-SQL Tuesday #94 – Lets get all Posh!

Rob Sewell aka sqldbawithabeard (b|t) hosts this month T-SQL Tuesday and surprisingly his subject is PowerShell.

I will describe what I’ll use for testing against Best Practices Commands for SQL Server.

I got in contact with PowerShell some years ago, but was not satisfied with what needs to be done before maintaining SQL Server.

Meanwhile Microsoft has done a lot more and with the contribution from several PowerShell Experts and MVPs as Chrissy LeMaire, Claudio Silva, Rob Sewell, Constantine Kokkinos and many more, there is a module created that helps to maintain SQL Server 2005+. This is called dbatools and the website can be reached at https://dbatools.io. The Project is hosted on Github and using the commands is totally free of charge!

The community has grown to over 50 contributors and over 200 SQL Server best practices, administration and migration commands. An Overview of the commands can be found here: https://dbatools.io/functions/

Now I will describe some of the dbatools commands to Test our SQL Server against Best Practices mentioned by SQL Server Experts. You can find all of the information and Links in the description to the commands.

  • Max Memory
    • This tests the actual max memory setting against the recommended setting.
    • Test-DbaMaxMemory -SqlServer sqlserver01
# command to set the max memory to the recommended
Set-DbaMaxMemory -SqlServer sqlserver01
# or to a fix value of 2 GB
Set-DbaMaxMemory -SqlInstance sqlserver01 -MaxMb 2048
      ​
  • TempDB
    • with SQL Server 2016 you get the option to configure the TempDB Configuration during installation, with this commands you can control and fix it.
    • Test-DbaTempDbConfiguration
    • Evaluates tempdb against a set of rules to match best practices.
      The rules are:
      – Is Trace Flag 1118 enabled (See KB328551).
      – File Count: Does the count of data files in tempdb match the number of logical cores, up to 8.
      – File Growth: Are any files set to have percentage growth, as best practice is all files have an explicit growth value.
      – File Location: Is tempdb located on the C:\? Best practice says to locate it elsewhere.
      – File MaxSize Set(optional): Do any files have a max size value? Max size could cause tempdb problems if it isn’t allowed to grow.
    • Test-DbaTempDbConfiguration(Screenshot from dbatools.io – my configurations are all fine 🙂 )
    • The right configuration can be set by using the corresponding Set- command
  • Disk-Configuration
    • Test-DbaDiskAlignment
      • This command verifies that your non-dynamic disks are aligned according to physical requirements.
      • Test-DbaDiskAlignment -ComputerName sqlserver01| Format-Table
      • Test-DbaDiskAlignment
    • Test-DbaDiskAllocation
      • Checks all disks on a computer to see if they are formatted to 64k, the best practice for SQL Server disks
      • Test-DbaDiskAllocation -ComputerName sqlserver01 | Format-Table
      • Test-DbaDiskAllocation
  • PowerPlan
    • Test-DbaPowerPlan
      • The Servers Powerplan should be set to High Performance on every SQL Server
      • Test-DbaPowerPlan -ComputerName sqlserver01
      • Test-DbaPowerPlan

 

  • SPN
    • We use DNS CNAMEs for referring to our SQL Server (2). We need to adjust the SPN Settings manually. That is easy with these commands:
      Get-DbaSpn / Set-DbaSPN
  • SQL Server Name
    • We created a Single VM template where all SQL Server are created from. With CPU, Memory and Disk Layout.
    • After creating a new VM out of the template the Server Name changes but not the SQL Server Name internally. Help comes again with dbatools command: Repair-DbaServerName
      Works fine for me!

Theses are the commands we use often in our environment, there are many more to choose from on the dbatools website.

Thanks to Rob for hosting this month T-SQL Tuesday and thanks to all other attendees to convince me writing a blog post. Thanks also to Adam Bertram who chooses me writing for #PSBlogWeek. You all can attend until this friday (2017-09-15). http://psblogweek.com/

Thanks for Reading,
Volker