My WTF….Really? Moment

So, to join into this month’s #tsqltuesday88, we are to write about a “WTF” moment. Mine is not directly database related, but it is related.

I was presenting at the NESQL User Group monthly meeting. I was giving a lightning talk on Powershell for a DBA. (See…somewhat related.) It was not meant to be a long presentation. At most about 15 minutes. I was coasting along talking about the enhancements made by Microsoft and the community when all of a sudden my machine would not accept any input from my keyboard or mouse. I have a Surface Pro 4, that suddenly decided to stop talking to the keyboard. Now, I was not about to let this stop me. Yes, it slowed me down, but stop me no…well not yet. I tried to use the touch screen. Yay…it was working. So I tried to continue on using the touch screen but then realized that I could not. There is no F8 key or way to highlight text, then copy and paste the text.

After a few minutes of trying to live troubleshoot, I had to give in to the Demo Gods. They have not smiled favorably upon me this day. I took my Surface and went to my seat and started looking around at what was going on. Windows Update hit me earlier in the day and had not properly finished. It was acting up and I had to hard stop it. I purposely had not wanted to do any updates just to prevent this from happening. I even made sure my demos were practiced and typing would not be needed. I remember the tip I have heard many times, never type in a demo)

After several reboots and a firmware update, and more reboots, everything was working fine. All I could say on this day was WTF…Really?

Now to put together the blog post on the talk I was going to give and figure out what my next talk will be on. Maybe give this talk at the RI SQL User Group.

 

DSC Install of SQL Server

It has been a while since I have made a post and figured it was long over due. I figured for my first post in a while, it would be about something I have been working on lately. The automation of installing and configuring of SQL Server.

So the installation of SQL Server is now fairly straightforward. The wizard does a nice job of guiding you along the way. 2016 even includes best practice suggestions for tempdb and instance file initialization. Along the way, Microsoft as given us ways to automate the installation of SQL Server. You can sysprep an instance, but this does not really automate the installation. It just helps create a template of an instance. At the end of the day, you still need to do things manually. You can also use a configuration file to assist here. This is a great step forward, but it does not allow for all of the things you need to do to configure a SQL server.

Powershell does. Desired State Configuration (DSC) is functionality built into Powershell that allows for the installation and configuration of a SQL Server.

First, to get started, you need to have a base understanding of DSC. Without this, the rest of this post will be hard to follow, as DSC can be difficult to follow. The link below will take you to the videos on the Microsoft Virtual Academy site that Jeffrey Snover put together that explains how DSC works.

Getting Started with Powershell Desired State Configuation (DSC)

Also, the modules you will need to make DSC work in your environment can all be downloaded from the Powershell Gallery. I have also found that searching for the module on GitHub, has returned back better documentation on how each module works.This has been great in my learning of how to get DSC going.

For the example what I have put together, we will use a Push mode for our DSC script.

The snippet that is below, uses a Powershell section called Configuration. This is similar to a Powershell Function in construction and how it works.

Configuration SQLServerInstall
    {
        param
        (
            [string]$ComputerName,
            [int32]$MAXDOP,
            [int32]$MAXMemory
        )
        Import-DscResource –Module PSDesiredStateConfiguration
        Import-DscResource -Module xSQLServer
        Import-DscResource -Module SecurityPolicyDsc
        Import-DscResource -Module xPendingReboot

        Node $ComputerName
        {
            WindowsFeature NET-Framework-Core
            {
                Name = "NET-Framework-Core"
                Ensure = "Present"
                IncludeAllSubFeature = $true
            }
            xSqlServerSetup InstallSQL
            {
                DependsOn = '[WindowsFeature]NET-Framework-Core'
                Features = $Configuration.InstallSQL.Features
                InstanceName = $Configuration.InstallSQL.InstanceName
                SQLCollation = $Configuration.InstallSQL.SQLCollation
                SQLSysAdminAccounts = $Configuration.InstallSQL.SQLSysAdminAccounts
                InstallSQLDataDir = $Configuration.InstallSQL.InstallSQLDataDir
                SQLUserDBDir = $Configuration.InstallSQL.SQLUserDBDir
                SQLUserDBLogDir = $Configuration.InstallSQL.SQLUserDBLogDir
                SQLTempDBDir = $Configuration.InstallSQL.SQLTempDBDir
                SQLTempDBLogDir = $Configuration.InstallSQL.SQLTempDBLogDir
                SQLBackupDir = $Configuration.InstallSQL.SQLBackupDir

                SourcePath = $Configuration.InstallSQL.SourcePath
                SetupCredential = $Node.InstallerServiceAccount
            }
            xSQLServerNetwork ConfigureSQLNetwork
            {
                DependsOn = "[xSqlServerSetup]InstallSQL"
                InstanceName = $Configuration.InstallSQL.InstanceName
                ProtocolName = "tcp"
                IsEnabled = $true
                TCPPort = 1433
                RestartService = $true
            }
            xSQLServerConfiguration DisableRemoveAccess
            {
                SQLServer = $ComputerName
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                OptionName = "Remote access"
                OptionValue = 0
            }
            UserRightsAssignment PerformVolumeMaintenanceTasks
            {
                Policy = "Perform_volume_maintenance_tasks"
                Identity = "Builtin\Administrators"
            }
            UserRightsAssignment LockPagesInMemory
            {
                Policy = "Lock_pages_in_memory"
                Identity = "Builtin\Administrators"
            }
            xPendingReboot PendingReboot
            {
                Name = $ComputerName
            }
            LocalConfigurationManager
            {
                RebootNodeIfNeeded = $True
            }
            xSQLServerAlwaysOnService EnableAlwaysOn
            {
                SQLServer = $ComputerName
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                Ensure = "Present"
            }
            xSQLServerMaxDop SetMAXDOP
            {
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                MaxDop = $MAXDOP
            }
            xSQLServerMemory SetMAXDOP
            {
                SQLInstanceName = $Configuration.InstallSQL.InstanceName
                DependsOn = "[xSqlServerSetup]InstallSQL"
                MaxMemory = $MAXMemory
                DynamicAlloc = $False
            }
        }
    }

To break down the script and the areas in the Configuration function, the names are thankfully fairly self explanatory.
WindowsFeature – Ensures that Windows features are present. This is a function that will initiate the installation if needed
xSqlServerSetup – This section will install SQL Server. If you look at the properties it is asking for, it will mirroring most of the properties that are in a configuration file
xSQLServerNetwork – This section is used to enable protocols, like TCP and configure what port SQL Server should use.
xSQLServerConfiguration – Is used to set values that you would normally set via sp_configure
UserRightsAssignment – Will set values in the Local Security Policy. In this case, I am setting who can Lock Pages in Memory and Perform Volume Maintenance Tasks
xPendingReboot – Will check to see if a reboot is required and reboot if needed
xSQLServerAlwaysOnService – Used to enable the Always On Service
xSQLServerMaxDop – Used to set MAXDOP
xSQLServerMemory – Used to set MIN and MAX memory values

Any place you see $Configuration.InstallSQL.* this is a value from a JSON file. I use this a parameter file that will feed this script. This allows me to easily reuse this script as many times as I need.

When the Powershell Script runs, the DSC will output a text file with an extension of MOF. DSC will then push this configuration file to the server and then proceed to configure the server as instructed. Then I use scripts that I have written or found along the years to finish the configuration. Ideally, using Powershell, I can configure the SQL Server any way I would like.

 

I have posted my entire set of scripts for installing and configuring SQL Server on Githib. You can find the scripts here. Here you can see the full example. Enjoy.