SQL Server Prerequisites .NET Framework

2014/10/02 § Leave a comment

As of SQL Server 2012 the .NET Framework prerequisite of .NET 3.5 SP1 is no longer installed for you by the SQL Server installer if it is found to be missing from the server. Now with Window Server 2008 R2 SP1 or higher this is simply enabling the feature within Windows Server, no actual installation. This can be easily accomplished with PowerShell as a quick script to run prior to doing the installation of SQL Server.

The PowerShell Windows cmdlet that is used in this instance will be Get-WindowsFeature and Install-WindowsFeature (Windows Server 2008 R2 you will use Add-WindowsFeature).

The .NET 3.5 SP1 feature in Windows Server is referenced as “Net-Framework-Core” in PowerShell. You can find this by calling the command:

Get-WindowsFeature Net*

NETFrameworkCore_ps

The below script is going first verify it is not installed and then will enable the feature (or technically install it I guess). In order to do this though you will need the OS media as you have to pass in the source path to “<drive letter>:\sources\sxs”. Note: You have to execute this in an elevated PowerShell console, so “Run as Administrator”.

#verify installed first
Get-WindowsFeature Net-Framework-Core).Installed

#Add feature
Install-WindowsFeature Net-Framework-Core -source 'G:\sources\sxs'

When you run this command you will see a status “bar” of sorts appear while it is performing the installation. Once completed you should see output that lets you know it was successful.

Now if you happen to see any errors you might reference the KB 2734782 – .NET Framework 3.5 installation error as there are various reasons why this command might fail.

The treasures of the SQL Server Error Log

2014/09/23 § Leave a comment

I am fairly active on Database Administrators QA site on StackExchange.com.  On average, most questions you find folks asking are about troubleshooting some error they are getting running code or some application is returning. I have noticed one of the most common comments we end up adding to a question is “have you looked in the error log” or “what error messages show up in the error log”, at least with SQL Server related questions.

SQL Server error log is something that every DBA should know how to search and review regularly for the instances in their responsibility. SSMS (GUI) is a common method to look through the error log for an instance, but is only efficient for one instance at a time. Another common method is an undocumented procedure that came around in SQL Server 2005: sp_readerrorlog. I will not go over that one as it is covered pretty well in this MSSQLTip.com article.

Now I am partial to using PowerShell because it offers a way to perform the same task against one or multiple instances with the a few keystrokes. Now you likely know you don’t necessarily start out with a few keystrokes. I have picked up that if you plan on doing everything more than once, you write it where it only takes you a few keystrokes the next time.

SQL Server Management Objects offer a fairly easy method for pulling the error log for an instance. Creating a new object to an instance and passing it to Get-Member you can see the definition shown:

System.Data.DataTable ReadErrorLog(), System.Data.DataTable ReadErrorLog(int logNumber)

By default this will return the latest error log for an instance. You can pass in an integer value for older error logs based on how many the instance is configured to keep. Which by default will be 6 (six), so entering a value from 1-6 will give you that particular error log.

$srv = New-Object Microsoft.Sqlserver.Management.Smo.Server ORKO\SQL12
$srv.ReadErrorLog()

Capture1

If you do a Get-Member on the method you can see the property values that you can base a filter on: LogDate, ProcessInfo, Text. You can obviously do filtering even easier passing this to Out-GridView, but I will let you play with that on your own.

As an example, say I am reviewing the error log file for possible login issues and I want to return only those failed attempts:

$srv = New-Object Microsoft.Sqlserver.Management.Smo.Server ORKO\SQL12
$srv.ReadErrorLog() | where {$_.ProcessInfo -eq &quot;Logon&quot;}

Capture2

Now if you have an instance configured to log successful and failed login attempts you would want to filter the Text on “failed” to limit the results. As well your error log would obviously be a considerable size on active instances so it can take some time using this method, but it offers a nice break.

Now as I stated previously, writing something the first time so after that it is only a few keystrokes, means you would setup a function to call the code above. I do this with a few extra things added for my purposes, and have it in PowerShell profile so it is available more easily. Below is the current function I use in my profile on a regular basis:

function Get-SQLErrorLog ($server,$LogNum,$HowMuch,[switch]$filter)
{
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
switch ($LogNum)
{
0 { if ($filter)
    {
	# Filter log to exclude:
	#- successful db/log messages 
	#- login failed
	#- login succeeded
	#- Information message about runtime of instance (e.g. "This instance of SQL Server has been using process ID...")
	Write-Host "Filtered SQL ERRORLOG on $server" -ForegroundColor Red
	$srv.ReadErrorLog(0) | Where { $_.Text -notmatch 'Login failed' -and $_.Text -notmatch 'Login succeeded ' -and $_.Text -notmatch 'Error: 18456' -and $_.Text -notmatch 'backed up' -and $_.Text -notmatch 'This instance of SQL Server'}
     }
     else
     {
	$srv.ReadErrorLog(0) | Select -Last $HowMuch
     }
}
1 {
     if ($filter)
     {
	# Filter log to exclude:
	#- successful db/log messages 
	#- login failed
	#- login succeeded
	#- Information message about runtime of instance (e.g. "This instance of SQL Server has been using process ID...")
	Write-Host "Filtered SQL ERRORLOG on $server" -ForegroundColor Red
	$srv.ReadErrorLog(1) | Where { $_.Text -notmatch 'Login failed' -and $_.Text -notmatch 'Login succeeded ' -and $_.Text -notmatch 'Error: 18456' -and $_.Text -notmatch 'backed up' -and $_.Text -notmatch 'This instance of SQL Server'}
     }
     else
     { $srv.ReadErrorLog(1) | Select -Last $HowMuch }
}
2 {
     if ($filter)
     {
	# Filter log to exclude:
	#- successful db/log messages 
	#- login failed
	#- login succeeded
	#- Information message about runtime of instance (e.g. "This instance of SQL Server has been using process ID...")
	Write-Host "Filtered SQL ERRORLOG on $server" -ForegroundColor Red
	$srv.ReadErrorLog(2) | Where { $_.Text -notmatch 'Login failed' -and $_.Text -notmatch 'Login succeeded ' -and $_.Text -notmatch 'Error: 18456' -and $_.Text -notmatch 'backed up' -and $_.Text -notmatch 'This instance of SQL Server'}
      }
      else
      { $srv.ReadErrorLog(2) | Select -Last $HowMuch }
}
}
} # End Get-LatestSQLErrorLog

CTRL plus ALT plus what?

2014/09/15 § Leave a comment

Depending on how far back you go in the technology field you all remember having to use CTRL+ALT+DELETE to change your password for your domain account.

change-password

Well, I always remote into servers now a days with consulting for various clients. I never like to keep the password assigned and generally will attempt to change it using PowerShell:

$cn = gpresult /R | Where {$_ -match “CN”} | Select -Skip 1 | clip
$acct = [adsi]“LDAP://$cn”
$acct.psbase.invoke(“SetPassword”,”MyNewPassword”)
$acct.psbase.CommitChanges()

Most of the time though I get access denied so how can you change it over VPN, when your machine is not part of their domain? Well starting in Windows Server 2008 (I think) Microsoft added a new key sequence that acts like CTR+ALT+DELETE for that machine.

You can now execute CTRL+ALT+END to get to the prompt to change your password. On Window Server 2012 it looks similar to this:

Capture

Click on “Change a password” and you get the similar prompt to older versions of Windows:

Capture

SQL Server Activity Monitor Script

2014/07/30 § Leave a comment

You ever wonder what goes in the background of Activity Monitor? Me neither, I just used it every so often when I wanted a quick peek at what was going on for a server. Microsoft has actually made changes to Activity Monitor in SQL Server 2012 Service Pack 1 that can cause errors, and requires you to modify OS level permissions [Reference]. I work over VPN supporting clients remotely and it never been that useful to me.

I liked all the information it showed but just did not like having to use the UI it provided to filter and such. So I did a trace against an instance while I opened Activity Monitor to pull out what query it was running to populate the active sessions.

So this post is pretty much just a bookmark for the script:

SELECT [Session ID] = s.session_id
	,[User Process] = CONVERT(CHAR(1), s.is_user_process)
	,[Login] = s.login_name
	,[Database] = ISNULL(db_name(p.dbid), N'')
	,[Task State] = ISNULL(t.task_state, N'')
	,[Command] = ISNULL(r.command, N'')
	,[Application] = ISNULL(s.program_name, N'')
	,[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0)
	,[Wait Type] = ISNULL(w.wait_type, N'')
	,[Wait Resource] = ISNULL(w.resource_description, N'')
	,[Blocked By] = ISNULL(CONVERT(VARCHAR, w.blocking_session_id), '')
	,[Head Blocker] = CASE 
		WHEN r2.session_id IS NOT NULL
			AND (
				r.blocking_session_id = 0
				OR r.session_id IS NULL
				)
			THEN '1'
		ELSE ''
		END
	,[Total CPU (ms)] = s.cpu_time
	,[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024
	,[Memory Use (KB)] = s.memory_usage * 8192 / 1024
	,[Open Transactions] = ISNULL(r.open_transaction_count, 0)
	,[Login Time] = s.login_time
	,[Last Request Start Time] = s.last_request_start_time
	,[Host Name] = ISNULL(s.host_name, N'')
	,[Net Address] = ISNULL(c.client_net_address, N'')
	,[Execution Context ID] = ISNULL(t.exec_context_id, 0)
	,[Request ID] = ISNULL(r.request_id, 0)
	,[Workload Group] = N''
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT JOIN sys.dm_os_tasks t ON (
		r.session_id = t.session_id
		AND r.request_id = t.request_id
		)
LEFT JOIN (
	SELECT *
		,ROW_NUMBER() OVER (
			PARTITION BY waiting_task_address 
ORDER BY wait_duration_ms DESC ) AS row_num FROM sys.dm_os_waiting_tasks ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id) LEFT JOIN sys.sysprocesses p ON (s.session_id = p.spid) WHERE s.is_user_process = 1 ORDER BY s.session_id;

SSMS 2012–Object Reference issue with Maintenance Plans

2014/07/21 § Leave a comment

I am amazed at some of the information you can find on the Internet when searching for an issue. Especially with forum posts that mention your issue (or flat out are the exact same), but do not include any final resolution. The best one I came across was this one on SQLTeam’s forum site, the post by “prett”, and note that this is posted back in 2013:

The Maintenance Plan is actually built with few services which Microsoft releases such as SSIS and SQL Server Job Agent, hence if you want to schedule the maintenance plan then your server needs to have SSIS in order to build the maintenance plan, and Job Agent in order to run at regular periods.
This error message “Object reference not set to an instance of an object” occurs because of one of the required component was not available on the server.

SQL Server 2005 started out requiring SSIS to be installed for maintenance plans to run, however when Service Pack 2 was released they removed that requirement. As far as I know after that any release of SQL Server kept that same functionality.

Well I came across this issue on my VMWare View desktop that I utilize for one particular client. I was getting this pretty little box every time I tried to create a new maintenance plan on a newly built SQL Server 2012 Failover Cluster Instance:

image

The version of SSMS I was using:image

I thought I would apply Service Pack 2 to try and resolve it and noted during that installation it was showing “SQLExpress”. Which did bring to mind that I had installed SQL Server 2012 Management Studio for Express when it was announced that it was the full version of SSMS now. I confirmed this by going to the “Setup Bootstrap” and checking the Summary files for the previous installations on the desktop. I found this:

image

So I went to Programs and Features and removed it. I then located the installation media for SQL Server 2012 Standard Edition and installed the Management Tools. Then for good measure applied Service Pack 2 for SQL Server 2012:

image

Low and behold, that solved my issue. I hope those in the future can find this bit of information useful.

PS C:\Check-InstanceConfig (part 2)

2013/12/09 § Leave a comment

In the previous post I discussed the data table that holds the default values for the configuration options in SQL Server 2000 up to SQL Server 2014.

In this post I will go over the function(s) that are included in the script. I will keep the same format as I did in the previous post:

  • How it was written
  • What it contains
  • A few examples

How it was written

If you have already downloaded the script you notice it had one function. I was attempting to make the output similar to what Mike did in his T-SQL script with the text output. After looking at it a bit more and thinking on best practices for PowerShell, I chose a different route. I always try to ensure whatever my functions output, you can do something with them down the pipeline. In order to keep that going I opted to break this up into separate functions. I also included a function that allows you to just pull the raw configuration options from the instance as you wish.

What it contains

There are 4 functions included in the current version of the script:

  • Pull-SqlConfig – Just pull the configuration options information on the instance
  • Check-SqlRunConfig – Compare the running value to the configured value
  • Check-SqlRunDefault – Compare the running value to the default value
  • Check-SqlBadDefaults – Compare the running value to the default value for specific options.

I decided to also include help text with each function so loading the script into your PowerShell session you can simply use the Get-Help cmdlet and it will show you the information on each function. Adding the “-full” switch to the command will output more detailed information as well.

image

A few examples

As mentioned above the function help information also includes a few examples of each function. So if you want to see them you can simply use the Get-Help function along with the “–Examples” switch.

image

Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

PS C:\Check-InstanceConfig (part 1)

2013/12/04 § 2 Comments

I was reading a blog post Mike Walsh published on Linchpin People last week: SQL Server Configuration Check Script.

In that script he is, as the title says, checking the configuration of a SQL Server instance for particular things like those options not set to default values and a few particular ones that still are set to default. I was intrigued by this and by the time I got to the end of the blog post was like “bet I can do that in PowerShell too”. I had a slow weekend with the holidays so thought I would sit down and see what I could come up with.

Well I came up with two specific things that I wanted to accomplish with this script:

  1. Provide a reference point to the configuration options for each version of SQL Server
  2. Provide a function that will take that reference point and validate it against any instance

If I tried to put all the information I want to help explain the script this blog post would be longer than you have the time to read through. I decided to just break it up into two parts based on the list above. So this post will just be on the reference point I wrote and hopefully by the end you will have learned the following things:

  • How it was written
  • What it contains
  • A few examples

A few caveats about the script: I did not do anything special in PowerShell that is specific to any particular version. This script should work on PowerShell 2.0 and higher. You will notice if you open this script up in the PowerShell ISE 3.0 or higher I did use regions, just a formatting feature in the ISE. It is treated as normal comment lines outside of the ISE.

One more thing: There is more than one way to do things in PowerShell, this is just the way I decided to go. As well I did not cut corners in this script. I wanted to make sure every command and variable was understood. I tried to not use any aliases with commands (e.g. “?” instead of where or where-object).

How it was written

The first section I put in the script was a comment block that contains the BOL links for the “Server Configuration Options (SQL Server)” page of each version:

image

I went back and forth a few times on how to actually build out a list of the configuration option names and default values based on the current versions of SQL Server that are available. I knew I needed the information in order to validate it against a SQL Server instance, no matter what version I might try. In Mike’s script he simply scripted out temporary tables with this information. So I kept to the same concept and decided to use a data table (System.Data.DataTable).

What it contains

I tried to make the columns self-explanatory so the column naming convention I chose:

  • ConfigOption – name of the configuration option in sp_configure or sys.configurations with new versions of SQL Server. The list contains those configuration options available from SQL Server 2014 to SQL Server 2000, as stated in the associated links in BOL for each.
  • Default_<4 digit year of SQL Version> – there is a column for each version of SQL Server the script will support (e.g. Default_2012). If a ConfigOption name is not supported or included in any particular version this value is set to –9 (negative nine).

image

An example of the –9 usage, “access check cache bucket count” is a configuration option only available in SQL Server 2008 and higher:
image

A few Examples

Ok, I am going to assume the reader is already familiar with how to run PowerShell scripts. I prefer the dot sourcing method in most cases. You can also simply copy and past this into your profile if you desire. Remember, Out-GridView is your friend.

  • To list out the complete data table execute this command:
    $sqlConfigDefaults | Out-GridView
    image
  • I have variables already in the script to list out each version (reference lines 723-729 in the script). So if you wanted to list out the options for SQL Server 2014:
    $sql2014_Defaults | Out-GridView
    image
    I could have filtered out the columns for the other versions, but I thought it to be a nice reference point to see those options that are still around from lesser versions. You can adjust this if you wish, but note that these variables are utilized in the function as well.

Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

Follow

Get every new post delivered to your Inbox.

Join 32 other followers