Errata for my book on Querying data with Transact-SQL (MCSA 70-761)

If you find any errors in the book, please let me know. This will help not only myself, but also other readers.

Posted in MCSA SQL 2016 70-761 | 5 Comments

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