SQL SERVER – Starting / Stopping SQL Server Agent Services using PowerShell

Writing utility scripts are one way to become smarter when working with computers. From time to time I get asked about some tasks folks use repetitively and are being counterproductive. On this note, I wanted to paint a scenario which I saw one of the DBA’s doing on their environment of multiple SQL Server – restarting or stopping the SQL Server Agent Services. He was painstakingly expanding each of the notes using SSMS and stopping the services.

The reason for that is beyond the scope here. But I do remember it was a highly available AlwaysOn Cluster of SQL Server running. When I saw that, I immediately asked him if this was something he wanted to automate? The immediate reaction was – “Definitely Yes. If you can.”

So gave a simple powershell script to stop the Agent Services on his patch of multiple servers. The script was as shown below:

foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName  $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StopService",$null)
}

This quick script allowed for stopping and obviously with a simple change, I was also able to start the 3 SQL Server instances which were stopped. You might want to change this in your environments accordingly.

foreach($replica in "localhost", "Server2", "Server3")
{
"StartAgent instance " + $replica.tostring() + " "
(Get-wmiobject -ComputerName  $replica Win32_Service -Filter "Name='SQLSERVERAGENT'" ).InvokeMethod("StartService",$null)
}

But interestingly, when I did it the first time on the server – I was getting an error.

PS C:\WINDOWS\system32> E:\StartSQLAgent.PS1
File E:\StartSQLAgent.PS1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
+ CategoryInfo          : SecurityError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnauthorizedAccess

If you go through the error and do it as per the documentation, we need to make sure the PowerShell window has been started in “Administrator mode” and post this, I need to run the below scrip

t:

[c
ode language="powershell" gutter="false"]
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
[/code]

This will showup and big dialog box as executed from my “Windows PowerShell ISE” window:

agent start powershell 01 SQL SERVER   Starting / Stopping SQL Server Agent Services using PowerShell

Accept the same with “Yes to All” or “Yes” and we are ready to run the above script.

The more I explore and look at Windows PowerShell, more automated I seem to make processes that I start to love this technology. Do let me know if you every did something like this in your environments?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Working with Event Viewer and PowerShell

Sometimes I go into these simple explorations that make me learn something new that gets me off the routine. SQL Server is always on the agenda and that passion will never die. This exploration was based on the fact when I was talking to someone about some of the super cool stuff people work on – I heard a DBA complain to me that he was working on a “Windows Server Core” machine and he had no UI. I was pleasantly taken aback and got back search on the internet about. I also got to know “SQL Server” can be installed on these server machines. It was fascinating for me to hear such things and I wondered, how can someone work on such machines.

I thought, let me see if we can use some sort of scripting to work on things I take for granted when troubleshooting some error messages. I always look at opening up the Windows Event Viewer to watch through the errors. Now I thought it would be great not to open up Event Viewer and yet be able to query. That was surely a challenge and I wanted to learn something new and interesting to share.

This blog is at the moment a rudimentary shot at going the scripting route using Powershell. Here are some commands that I used. Let me progress from few simple commands to little interesting, complex queries I figured out to write:

1) List the event viewer logs on a given system. This is simple because I need to know what can be queried at any point in time.

get-eventlog -list

 SQL SERVER   Working with Event Viewer and PowerShell

As you can see I have about some 27k+ messages and this is a great place to make our query.

2) Let us next try to select the newest 50 messages of application log:

Get-EventLog -LogName Application -newest 50

The next, I was wondering if we can dump on data based on a date value. This gets me to the next query that was written.

3) Gathering logs after a particular date, we can also use “before” to select the messages prior to given dates as shown below:

Get-EventLog -LogName Application -after 1/10/2016

I wanted to make the query less complex by searching on a specific Event type and Event Source. Since I work with SQL Server and the source had to be MSSQLSERVER (default instance name).

4) Selecting only the messages which are logged as “information” for a source like “MSSQLSERVER” and using a clip to basically copy the output to the clipboard:

Get-EventLog -logname application -EntryType information -newest 50 -source *MSSQLSERVER* | clip

When I was writing the above query, I was little clueless to what are valid source types we can use. So I made a query to identify the source names.

5) Find the relevant source to be used in a query:

Get-EventLog -logname "Application" 
| Select-Object Source -unique

 SQL SERVER   Working with Event Viewer and PowerShell

As you can see, if we know how to play around with Powershell and know the query we need – we can always find nice and easy way to get the data. How many SQL DBA’s who read this blog have already tried playing around with PowerShell? What have you been doing with it? Please let me know via the comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell – Knowing SQL Server Information

If you search this blog, there are a number of PowerShell scripts that I have shared in the recent past. Some of them include:

Using PowerShell and Native Client to run queries in SQL Server

Identify Disk Latency on SQL Server Box Using PowerShell

How to Find Logical and Physical Processors on SQL Server?

As I write a number of them, every time I explore and do something totally different. So in this blog, I want to do a neat trick of using PowerShell. Here I we will try to get the details of SQL Server running on my localhost and then we will try to create an HTML page which will hold and show the data.

</pre>
$OutputFile = "c:\temp\test.html"
$SQLServer = "localhost"
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Result = @()
$SQLServerInfo = new-object “Microsoft.SqlServer.Management.Smo.Server” $SQLServer
$Result += $SQLServerInfo | Select Name, Edition,ProductLevel, Version, ServerType, Platform, IsClustered, PhysicalMemory, Processors
if($Result -ne $null)
{
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH><B>Server Name</B></TH>
<TH><B>Edition</B></TD>
<TH><B>ProductLevel</B></TH>
<TH><B>Version</B></TH>
<TH><B>ServerType</B></TH>
<TH><B>Platform</B></TH>
<TH><B>IsClustered</B></TH>
<TH><B>PhysicalMemory (MB)</B></TH>
<TH><B>Logical Processors</B></TH>
</TR>"
$HTML += "<TR>
<TD>$($Result.Name)</TD>
<TD>$($Result.Edition)</TD>
<TD align=center>$($Result.ProductLevel)</TD>
<TD>$($Result.Version)</TD>
<TD>$($Result.ServerType)</TD>
<TD>$($Result.Platform)</TD>
<TD align=center>$($Result.IsClustered)</TD>
<TD align=center>$($Result.PhysicalMemory)</TD>
<TD align=center>$($Result.Processors)</TD>
</TR>"
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
}
<pre>

The output of this will be a file on our c:\temp folder. If we open the same in a browser, you can see the SQL Server Information about version, edition, Server type, Memory and Processor on the box. This is a neat way to explore and play with a scripting language and PowerShell to create something interesting.

Have you ever created something really interesting like this in your environments? Will you be willing to share something via the comments so that others can also take advantage of this collective knowledge.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

PowerShell: How to Find Logical and Physical Processors on SQL Server?

Working on PowerShell scripts have been an interesting journey. I have written a couple of posts before on using WMI events. Was pondering on this thought and this blog is inspired by combining using of WMI with PowerShell. So what are we trying to solve?

When SQL Server changed its whole licensing process from processor based licensing to core based licensing, a lot of us were confused to how this has to be calculated. Recently, one of my DBA friends asked how can I find the number of logical processors (cores) on my SQL Server machine? Now this question got me thinking and the first place I always go to search is SQLAuthority – trust me on this. Once figured out there isn’t much on this topic, I thought to write a simple script which I can refer in the future.

$Computer = 'localhost'
$ErrorActionPreference = 'SilentlyContinue'
$Error.Clear()
$ProcessorConfig = Get-WmiObject -class Win32_Processor -computername $Computer -namespace root\CIMV2 | Select PSComputerName, Name, NumberOfCores, NumberOfLogicalProcessors
write-Debug $Error.Count
If ($Error.Count -gt 0)
{
$ProcessorConfig = New-Object psobject
$ProcessorConfig | Add-Member -type NoteProperty -name ComputerName ("$Computer-failed to connect")
$ProcessorConfig | Add-Member -type NoteProperty -name Name -value 'Unable to get ProcessorInfo'
$ProcessorConfig | Add-Member -type NoteProperty -name NumberOfCores -value $null
$ProcessorConfig | Add-Member -type NoteProperty -name NumberOfLogicalProcessors -value $null
Write-Debug "Failed to connect to $Computer"
}
$ErrorActionPreference = 'Continue'
$ProcessorConfig | FT * -AutoSize

When you run this above script inside PowerShell, we will get to see an output like below:

logical physical processor 01 PowerShell: How to Find Logical and Physical Processors on SQL Server?

As you can see I have a 4 core processor and have enabled HT (Hyper-Threading) which makes the number of Logical Processor to 8. I personally felt this was a quick way to learn PowerShell when you have a typical problem in hand.

If you have read this far, please let me know how you have used WMI or PowerShell to solve interesting problems. Let me know over the comments and I will surely try to publish a few more like these in the future too based on your inputs.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell to Count Number of VLFs in SQL Server

If you are a seasoned DBA, then the conversations and monitoring VLFs is something you are already doing. To learn about basics of VLF, check my earlier blog at: SQL SERVER – Detect Virtual Log Files (VLF) in LDF. Having large number of VLFs have a performance impact and there are a number of blogs that discuss the same. As a DBA in this blog I thought of showing a simple script that will help identifying the number of VLFs available inside a SQL Server instance or a number of instances.

As part of my powershell learnings, this is a simple script that you can use in your environment and take corrective action if you have large number of VLFs in your servers.

<#
EXAMPLE
   Get-VLFCount -ComputerName ComputerName
EXAMPLE
   Get-Content D:\ServerList.txt | Get-VLFCount

   If you are using Windows Authentication and have a list of servers you can use this.
   Get-VLFCount -ComputerName ComputerName1, ComputerName2
#>
function Get-VLFCount
{
    [CmdletBinding()]
    Param
    (
        # SQLInstance is the name(s) of the SQL Instances to scan
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$true,
                   Position=0)]
        $SQLInstance
    )
    Begin
    {
        #Load the Assembly to connect to the SQL Instance.
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

    }
    Process
    {
        ForEach ($Instance in $SQLInstance)
        {
            $SrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
            $SrvConn.ServerInstance=$Instance
            #Use Integrated Authentication
            $SrvConn.LoginSecure = $true
            $SrvConn.ConnectTimeout = 5
            Write-Debug "Attempting to check $Instance for VLF Counts"
            $srv = new-object Microsoft.SqlServer.Management.SMO.Server($SrvConn)
            $dbs = $srv.Databases
            try
            {
                ForEach ($db in $dbs)
                {
                    Write-Debug "Getting VLFInfo for $db"
                    if ($db.IsAccessible)
                    {

                        $VLFs = $db.ExecuteWithResults("DBCC LOGINFO")
                        $NumVLFs = $VLFs.Tables[0].Rows.count

                        $VLFinfo = $db | Select @{Name='Instance Name'; expression={$Instance}}, @{Name='Database Name'; Expression = {$_.name}} `
                        , @{Name='VLFCount()'; Expression={$NumVLFs}}

                    }
                    else
                    {
                        $VLFInfo = New-Object psobject
                        $VLFInfo | Add-Member -type NoteProperty -name InstanceName ($Instance)
                        $VLFInfo | Add-Member -type NoteProperty -name DBName ("$DB is Inaccessible")
                        $VLFInfo | Add-Member -type NoteProperty -name VLFCount 0
                    }
                    Write-Output $VLFinfo
                }

            }
            catch
            {
                $ex = $_.Exception
                Write-Debug "$ex.Message"
                Write-Error "Could not pull SQL DB Info on $Instance"
            }

        }
    }
}

#If we had our instances in a file, we can use
Get-VLFCount 'localhost'

The above script when run on my server returns the following:

PS VLF Count 01 SQL SERVER   PowerShell to Count Number of VLFs in SQL Server

As you can see in our example output, our AdventureWorks database has large number of VLFs. Incase you have hundreds of VLFs in your environments, then you can look at SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file to reduce the number of VLFs for your databases.

Just out of curiosity, I would love to know what is the highest number of VLF in your databases? How long have these been running in your systems? It would be great if you can share the same with us over comments so that it would a great learning for me too.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server

I have been getting my hands dirty working with PowerShell commands for a fun learning experience and few have made to this blog. The after effect of this is that I have been pinged for few queries on them too. Recently I got a call from one of my friend and he wanted to use PowerShell to connect to SQL and run some queries. Initially I thought that he is talking about SQL PowerShell, but his intention was to connect to SQL using SQL Server Native Client and run the query. That was strange but I was ready and up for the challenge in front of me. The curiosity is just too much that I couldn’t sleep till I found a solution to this challenge he put to me.

I wrote a small sample and sharing here for your benefit. First, we need to create a sample database and a table which would be used by PowerShell.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE EmployeeMaster (
EmployeeID INT
,FirstName VARCHAR(10)
,
LastName VARCHAR(10)
)
GO

Now, our task is to use PowerShell and insert data into EmployeeMaster. Here is the step by step PowerShell script.

# Make Connecting string to the SQL Server Instance and the Database
$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=.; Initial Catalog=SQLAuthority; Integrated Security=SSPI")
# Define variables and the values to be inserted
$EmployeeID = 1
$FirstName = 'Pinal'
$LastName = 'Dave'
# Open database connection
$con.Open()
#Create SQL Insert Statement for table EmployeeMaster with the values
$stmt_insert = "INSERT INTO [EmployeeMaster]
([EmployeeID],[FirstName],[LastName])
VALUES($EmployeeID ,'$FirstName' ,'$LastName')"
# Create command to execute to SQL connection
$cmd = $con.CreateCommand()
$cmd.CommandText = $stmt_insert
# Invoke the Insert statement
$cmd.ExecuteNonQuery()

Once we execute above, the data would be inserted into the table. Now, to fetch the data, I am going to use invoke-SQLCMD cmdlet

Add-PSSnapin SqlServerCmdletSnapin100
$DataSet = Invoke-Sqlcmd -Database "SQLAuthority" -Query "SELECT * FROM [EmployeeMaster]" -ServerInstance .
foreach ($element in $DataSet)
{
$element
}
# Close database Connection
$con.Close()

We can run all the statements together in one PowerShell script and use Windows PowerShell ISE to execute it.

sql ps sample 01 SQL SERVER   Using PowerShell and Native Client to run queries in SQL Server

You might get a few errors while using Invoke-SQLCMD which I have explained here

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

Hope this would give you a head start of using PowerShell to connect to SQL and run queries. This was a cool experiment for me and I thought was worth sharing. Have you ever had such requirements? Do share your experience over comments below.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Identify Disk Latency on SQL Server Box Using PowerShell

Couple of week back, I was playing around with using Powershell for some simple activities. The fall out of that was the ability to search the SQL Server Error Logs. Though we can search for anything, I just showed you a case of usage. In this blog post, I will talk about one of the most sought after request for searching what the disk latencies are.

Disks are one of the most notorious when it comes to bring the server performance down. It is sometimes quite difficult to identify the problems. A lot of times, the first point of search would be using resource monitor. But in this blog, I thought let us try to automate this collection process using a PowerShell script. This ultimately shows how powerful this scripting language is and how one can use it to collect and process few Performance Counter values.

param (
    [string]$ServerName = "localhost",
    [int]$SampleFrequencySeconds = 10,
    [int]$CollectionDurationSeconds = 120
)

# do a check to ensure that collection duration is greater than
# or equal to the frequency rate
if ($CollectionDurationSeconds -lt $SampleFrequencySeconds) {
    Write-Error "CollectionDurationSeconds cannot be less than SampleFrequencySeconds"
    exit
}

# loop through all of the drives, sampling them
$DrivesOutput = for ($i = 0; $i -lt [int]($CollectionDurationSeconds / $SampleFrequencySeconds); $i++) {
    Get-Counter -Counter "\LogicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"LOGICAL"}},
            CookedValue

    Get-Counter -Counter "\PhysicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"PHYSICAL"}},
            CookedValue

    # Sleep for the specified frequency before continuing in the loop
    Start-Sleep -Seconds $SampleFrequencySeconds
}

# Group by the drive and Calculate the average for each drive we have
# round to the nearest [ms]
$DrivesOutput |
    Group-Object InstanceName, Type |
    Select-Object @{Name = "InstanceName"; Expression = {$_.Group.InstanceName[0]}},
        @{Name = "Type"; Expression = {$_.Group.Type[0]}},
        @{Name = "DiskLatencyMs"; Expression = {
            [int](($_.Group.CookedValue | Measure-Object -Average).Average * 1000)}
        } |
    Sort-Object InstanceName

As part of the script we have gone ahead and started our collection of counters once every 10 seconds. In this script, I have used 2 mins interval for the collection process. We have collected two counters – “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”.

PowerShell DiskLatency 01 SQL SERVER   Identify Disk Latency on SQL Server Box Using PowerShell

I have gone ahead and shown a typical output from my PC. I have two physical drives and 3 logical drives. And we can see the disk latencies in (milliseconds).

I am sure the output would vary in your environments. Do let me know if such scripts are useful and you would want me to write more about them. I am slowly starting to love this scripting language that I plan to explore the various ways people use this powerful features.

Do let me know via comments some of the ways you have used PowerShell in your environments and which of those are even used in Production?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell Script – When Was SQL Server Last Restarted?

I have always wanted to learn a bit of scripting and I was always searching for a good reason to learn something new. As I said, this is my learning and I am open to learning some scripting tricks from you too. So do let me know how the below script can be optimized in your comments. Now, what was my reason to learn this script?

Someone asked me, “How can I find out when SQL Server was started?” There are multiple ways to find out, but I took the simple route and said – “Why don’t you check the SQL Server Error logs?” I know, the SQL Server error logs can get recycled and this data maynot be available. But in most cases, this gives us some indication.

So the other question was how can we script this requirement? So the below script is written to read SQL Server ErrorLog, find a specific text and report the number of times the string was found. We also show the time when the last/recent occurrence was found.

param
 (
 $serverinstance = ".", #Change this to your instance name
 $stringToSearchFor = "Starting up database ''master''"
 )
 # Load Assemblies
 [Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
 # Server object
 $server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $serverinstance
 $searchCriteria = "Text like '%{0}%'" -f $stringToSearchFor
 $SQLErrorLogs = $Server.EnumErrorLogs()
 $SearchEntry = @()
 ForEach ($SQLErrorLog in $SQLErrorLogs)
 {
 $SearchResult = $server.ReadErrorLog($SQLErrorLog.ArchiveNo).select($searchCriteria) | Select-Object -Property LogDate, Text
 $SearchEntry = $SearchEntry + $searchResult
 }
 $MeasureOccurences = $SearchEntry | Measure-Object -Property LogDate -Minimum -Maximum
 $SQLSearchInfo = New-Object psobject -Property @{
 SearchText = $stringToSearchFor
 Occurances = $MeasureOccurences.Count
 MinDateOccurred = $MeasureOccurences.Minimum
 MaxDateOccurred = $MeasureOccurences.Maximum
 }
 Write-Output $SQLSearchInfo | FT -AutoSize

Do let me know if you ever used such scripting capability to search your errorlogs? You can change the search string and the default instance to your environment.
I wrote this generically because I was planning to use this to find error messages inside ErrorLogs like “I/O requests taking longer than 15 seconds to complete”. The script can be modified to your needs. Do share your scripts too over the comments so that others can benefit from the sharing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

helppushbutton SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdletIt’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

Invoke SQLCMD 01 SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdlet

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Invoke SQLCMD 02 SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdlet

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Invoke SQLCMD 03 SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdlet

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell way of Restarting SQL Server Service

HELP (1) SQL SERVER   PowerShell way of Restarting SQL Server ServiceI learn a lot when I go for conferences all around the world. There are styles, content and cultural differences that I get a chance to learn and enjoy as part of my trips. Most of these trips I make are geared towards at least one learning experience I can get end of the day. With these travels, I also attend and speak at local user groups to get a flavour of what is the neat and coolest way to work with SQL Server. Recently at our local user group (SQL Server Bangalore UG) UG meeting, I was vividly stumped by a question from one of the speakers. Here he was talking about various ways to shut down or start SQL Server.

This is was an interesting quiz and I was pleasantly surprised by the most common methods and wanted to participate in this quiz. The answers were flowing all over and here are some that I would like to share which were part of audience interactions:

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Net Start Command.
  3. SQL Server Configuration Manager.
  4. Windows Services.
  5. Using the Sqlservr.exe executable

I thought I had known most of these and the usage of sqlservr.exe was something I wanted to check as it had been ages. For complete list of startup parameters refer book online.

Getting back to UG meet, I couldn’t hold my curiosity back and I was quick to raise my hand to give what I thought could be yet another answer. I shouted out loud the “SHUTDOWN” command. This was something I thought would fit the bill too. The speaker was understanding to say, this was one of the partially correct answer. Taken aback, I eagerly waited for the explanation.

Though the answer was correct, it was to shut down the service and didn’t allow us to restart our SQL Server service. And the speaker, waited for more answers. This made me curious of the fact that there was more to what I knew.

Welcome PowerShell

The speaker was quick to say, now that we have exhausted all the options available – let me talk about Windows PowerShell and it can be yet another way to restart SQL Server Service. It completely slipped my mind about this option. PowerShell is a powerful way to script and do activities with SQL Server. If you haven’t played around, then I strongly suggest to have a look into the same.

Now, how can I restart SQL Server Service? In this example below, I have used the PowerShell ISE IDE to execute the command as identified below:

restart sql powershell 01 SQL SERVER   PowerShell way of Restarting SQL Server Service

Restart-Service -Force MSSQLSERVER

 restart sql powershell 02 SQL SERVER   PowerShell way of Restarting SQL Server Service

Since there was no feedback from the console, I was curious to know if the SQL Server service did restart properly. On examining the Task manager I could confirm that the service did restart because the PID values were completely different after the command got executed.

Sometimes, we carry of knowledge that needs a refresher. Hence taking a student approach to everything we do can help a lot in this learning experience.

Do let me know if you found any other way to restart SQL Server and I would be more than happy to learn it from you.

Reference: Pinal Dave (http://blog.sqlauthority.com)