Screenshots of tables

This gallery contains 5 photos.

It appears that on some Kindle devices, it is hard to read the tables. Therefore, on request, I’ve put the screenshots of the tables here. Tip: you can also read the eBook on Amazon Cloud Reader (https://read.amazon.com)    

More Galleries | Leave a comment

Insert object in Word document with Powershell

At times, you have to create reports in Word format. If these are reports you have to create on a regular basis, you’ll want to automate this as much as possible. There is a lot you can do to automate this process with Powershell. There are some excellent resources available to get started, such as this one:

Beginning with PowerShell and Word

This week, I had to make a report with embedded objects. In Word, you can do this with Insert > Object… .  The method to call is AddOLEObject. And though this method is documented (link for Office 2013 & above), and there are plenty of code examples out there on the internet on how to call this method using, for example, C# or VBA, I could not find an example on how to call this method using Powershell. I had to work out the exact syntax using trial and error. So I decided to write this blog post to help others out there with the exact syntax. Here it is:

$Selection.InlineShapes.AddOLEObject("excel.sheet","E:\data.xlsx","False","True",$iconfile, 0,"some caption text")

A short explanation of the syntax. Following the example of the first link provided above, $Selection is the variable containing the Word object. The first parameter is the class type, in this case “excel.sheet”. The second variable is the name of the file. The third and fourth parameter are LinkToFile and DisplayAsIcon. As explained in the DevCenter web page, this determines whether the file is added, or the content of the file. The fifth parameter, IconFileName, should link to the file containing the icons. For Word 2016, this is C:\Program Files (x86)\Microsoft Office\root\Office16\Wordicon.exe. For Excel 2016, this is XLSICON.exe in the same directory. For other Office version, this location will be different.

 

Posted in Code samples, Powershell | Leave a comment

Installing SQL Server 2008 R2

This video demonstrates the installation for SQL Server 2008 R2. It is supporting material for my eBook on Database Fundamentals, which covers the Microsoft MTA 98-364 exam.

In this video, I explain the installation process step by step. The purpose of this installation is to create an instance for testing purposes, so there is not a lot of finetuning. An instance for production purposes would require a lot more steps.

 

Posted in MTA 98-364 | Leave a comment

Does the Powershell command Restart-Service -force restart dependent services?

This could be my shortest post ever, with a simple “yes”, but let me elaborate.

At times, you have to restart a service. With Powershell, this is easy enough (provided you have the right permissions): Restart-Service -name will do the trick. But every now and again, you have to restart a service that has dependent services. Like WMI, or SQL Server.  In that case, Powershell will raise an error:

PowerShell service restart error

PowerShell service restart error

For those who actually read error messages, this one is pretty clear: you have to use the -Force flag to restart a service with dependent services (actually only one in this case, but who’s counting?). But does that also restart the dependent services? The Services MMC does restart dependent services, but I don’t like guessing. Let’s find out.

The first place to look is in the help: Help Restart-Service -Full.

Force switch

Force switch

 

No answer here. So let’s test it. I’ll use the SQL Server service as an example, which has one dependent service: the SQL Server Agent service.First, let’s find out what the dependent service actually is. Get-service returns a service object with a property called Dependentservices; expanding that returns the required info.

Now, let’s restart SQL Server:

Now, the service will be restarted. You can hit the up arrow twice, and rerun the command to check the status of the dependent services. And there’s you’re answer: the dependent services are restarted as well.

Posted in Powershell, Uncategorized | Leave a comment

My book on Database Fundamentals is now available!

Finally, I’ve finished my book on the Microsoft Technology Associate 98-364 exam: Database Fundamentals. It’s available in the iTunes book store:

https://itunes.apple.com/us/book/id1043228558

I decided to make it an iBook, since this format provides a combination of text, screenshots, screencasts and interactive test questions. This combination enables me to explain each piece of information the easiest way. Some material, you just have to read to understand; other material is best demonstrated in a video. iBooks provides all this, which makes it a format I’m very excited about!

 

For those of you who do not have a device capable of reading iBooks, there is also a Kindle version, which you can read on a Kindle device or in a browser. It is available on Amazon. Here’s the link to the US Amazon site:

https://www.amazon.com/Database-Fundamentals-Microsoft-Technology-Associate-ebook/dp/B01D7NOW0U/ref=sr_1_5?ie=UTF8&qid=1465640887&sr=8-5&keywords=database+fundamentals

Book cover

 

 

Posted in MTA 98-364 | 7 Comments

How to create a list of installed SQL Server instances on a cluster

On a normal server, there are several Powershell ways to get a list of all installed SQL instances. Probably the most straightforward way is to query the registry:

You can subsequently use the output to query all instances on the server.

On a cluster, this method will also work, but the end result will not be as useful. To query a clustered instance, you’ll also need the virtual server name. In newer SQL Server versions, this is called the SQL Network Name, but apparently, under the hood the older name is still used.

There is no direct way I know of to get the full name: Virtualservername\instancename. But using the Failoverclusters module, you can retrieve the virtual server names of all the instances. Put these into an array, put the instance names into an other array and, provided you sorted both arrays the same way, concatenating both arrays will get a list of instances you can use.

A little explanation:

  • Get-Clusterresource returns a list of cluster resources
  • This needs to be filtered to get just the resources of the type SQL Server
  • One array holds the value for instancename, the other for virtualservername
  • Sort both lists by the same property, otherwise you’ll get rubbish
  • Use -exandproperty to end up with a list of values rather than a table
  • With a foreach loop, concatenate both values with a backslash in between.

Now, you get a result that you can use in an Invoke-SQLCommand.

 

 

Posted in Powershell | Leave a comment

Powershell disk inventory

Every now and then, I have to tackle a performance problem on a SQL Server I’ve never seen before. One of the things I’ll be looking for is the block size of the disks. The block size for drives containing SQL database files has to be 64k for optimal performance. This is well documented, in lots of Microsoft documents, including this one: https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx .

I used to check the block size using the DOS command fsutil. Recently, I decided to enter the 21st century and start using Powershell to perform this check. I asked my friend Google to provide me a little script. I found a lot of them, but none that worked the way I wanted. I want to see all drives and all mountpoints, using a single script without having to rely on additional functions. It should also show the size and free space, because a drive that’s almost full needs attention as well. And o yeah, it needs to display all this in a legible format.

So I decided to make my own script, and post it here, for future reference and the benefit of other beginning Powershell users.

I’m relatively new to Powershell, so maybe I’m not using the optimal way to achieve this. So, for future reference, a little explanation on the code:

  • To make the code easier to read, the WMI query is created as a separate variable.
  • Also for readability, I’ve cut the second line into chuncks using the backtick `
  • Both free space and capacity are returned as bytes, and that’s pretty hard to interpret. Powershell provides the ability to convert these bytes into Gigabytes, using: FreeSpace/1gb. You can’t use Freespace directly, you’ll have to use the notation $_.Freespace, so the conversion into gigabytes becomes $_.FreeSpace/1gb.
  • This will result in a decimal number with a lot of precision, but two decimals is accurate enough for me. So I’ll have to use a little .NET: [decimal]::Round($_.FreeSpace/1gb,2)
  • To return a calculation with the Select-Object cmdlet, there’s this bit of pretty code: @{ name = “label”; expression = {…} } . Replace the … with [decimal]::Round($_.FreeSpace/1gb,2), and you’ve got the final result.
Posted in Performance, Powershell | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 4

 

Posted in Code samples, MTA 98-364 | Leave a comment

Creating a virtual machine with Vboxmanage

Lately, I’ve been creating, deleting and recreating a lot of virtual machines with Oracle VirtualBox. So today, when I needed to start with a clean environment, I decided to create a virtual machine using the command line instead of the GUI.

Clicking through the wizard is a lot easier than creating the virtual machine from the command line, but there are some options I always change after creating the guest. For example, to install Windows, you need to mount a CD. To access other installation files on my laptop, I need to share a local folder. And I like to enable drag ‘n’ drop. So why not automate the creation process, including these settings? This is the script I created, calling VBoxManage.exe:

 

Update: in version 4.3.20, the program files directory has been changed from:

C:\Program Files\Oracle\VirtualBox

to:

C:\Program Files\Oracle VM VirtualBox

Posted in Uncategorized | 1 Comment

Two wrongs make a right: tricking the query optimizer

Sometimes, the query optimizer makes mistakes. Instead of selecting the most optimal plan, a sub-optimal plan is chosen, and it is your task as a DBA to help SQL choose the right plan. In this case, the optimizer overestimated the selectivity for a WHERE clause, and my solution to this problem was to trick the optimizer into using a clustered index seek instead of a nonclustered index.

This is going to be a short post, since I can only provide partial code. The offending query joined 5 tables, each with millions of records. It had the following WHERE clause:

The query would run for minutes. Analyzing the execution plan showed that the problem was the estimation of the number of records. Based on its statistics, SQL estimated that the first WHERE clause was the most selective. Therefore, SQL started with an index on this table (the top right operator in the query plan). In reality, however, the second WHERE clause was much more selective.

Looking at the statistics, it was pretty obvious why SQL would think that the date clause was not very selective. This particular table contains years of data, but the query would only need records from the last few hours. The statistics on the table are not that granular (for more info on the granularity of statistics, see this Technet article).

At first glance, it was not so obvious why SQL would think that the first clause (LIKE ‘R%NL’) would be selective. The statistics clearly show that most values start with ‘R%’. This is, however, where SQL makes a mistake. It assumes that the combination of the two filters would be a lot more selective. Updating the statistics doesn’t solve this problem.

Having found the underlying problem, my first attempt at a solution was to split the first clause:

That didn’t solve the problem. SQL still estimated that the combination of the filters would be selective enough to start with tableA. The solution I used was to make the clause non-SARGable, by performing an operation on the column:

 

Normally, this is something you should definitely avoid. A non-SARGable filter eliminates the optimizers ability to estimate whether it could use an index to satisfy this query. In this case, it eliminated the optimizers abililty to make the wrong estimation! So sometimes, two wrongs make a right!

 

 

 

Posted in Performance | Leave a comment