Category Archives: Blog

SQL Server and PowerShell (SQLPS) Starter

The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance.

$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)";

Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT>

$Server = (Get-Item .)

I mention this because I was asked what the simplist entry point to PowerShell for SQL person was. I can think of nothing simpler than opening the SQL PS shell from SSMS and typing the folllowing:

(gi .)

Note that gi is shorthand for Get-Item.
For example now that we have a reference to out instance we can then start to explore.

(gi .).Databases.FileGroups.Files|Select @{Name="UsedSpace_MiB";Expression={($_.UsedSpace/1KB)}},Name

 

(gi .).Databases|Select Name,LastBackupDate

 

(gi .).Databases

 

(gi SQLSERVER:\SQL\$ENV:ComputerName\DEFAULT).Databases

Samsung Gear VR2

Samsung VR for S6 Edge Plus in the UK is here

***** https://shop.samsung.com/uk/samsung-gear-vr-lite-286321/ *****

Update 2 new link
http://www.samsung.com/uk/consumer/mobile-devices/wearables/gear/SM-R322NZWABTU

Original Moan below:

There may be a few of us geeks in the UK looking for the latest VR offering from Samsung, which appears to be pre-releasing everywhere but here.

vrfocus are running updates as and when they occur. Their post linking to Amazon UK looks to be what we are waiting for although annoyingly no product details. http://www.amazon.co.uk/gp/product/B017WA5VI6

From what I can gather it the newer VR units are referenced as SM-R322NZWAxxx and then a 3 character country code. (The older headsets are SM-32x). Therefore the reference SM-R322NZWABTU looks good for the UK based on BTU being used before. I am not holding my breath for a release this year tho. Amazon quoting 1-3 Months… ho ho ho. Not exactly the best product launch from Samsung…

The new headset is “Compatible with Galaxy Note 5, S6 edge+, S6 and S6 edge.”

Note that the older headsets are prefixed as follows:
Gear VR Innovator Edition for S6 SM-R321.
Gear VR Innovator Edition for Samsung’s Galaxy Note 4 SM-R320.

Links to vrfocus articles:
http://vrfocus.com/archives/25783/amazon-launch-uk-gear-vr-pre-orders/
http://vrfocus.com/archives/25467/gear-vr-now-listed-australia-new-zealand-sweden-korea/

Links to Samsung:

Sweden
http://www.samsung.com/se/consumer/mobile-devices/wearables/gear/SM-R322NZWANEE

Germany
http://www.samsung.com/de/consumer/mobile-devices/wearables/gear/SM-R322NZWADBT

New Zealand
http://www.samsung.com/nz/consumer/mobile-devices/wearables/gear/SM-R322NZWAXNZ

Australia
http://www.samsung.com/au/consumer/mobile-phone/wearables/gear/SM-R322NZWAXSA

South Korea
http://www.samsung.com/sec/consumer/mobile-tablet/gear/gear-series/SM-R322NZWAKOO

US
http://www.samsung.com/us/mobile/wearable-tech/SM-R322NZWAXAR

Bits, Bytes and the Binary Decimal confusion

Quick Binary Bit background

In computing we use a unit of information with two values 0 or 1, which is called a bit. Now with either a 0 or 1, how do we go about representing the number 6 or 9? We could write 111111 and 111111111111, but you will see that this will require a lot of space to show larger number. If we use Binary (base-2), then the position of the 0/1 will relate to a corresponding value which in binary are 1,2,4,8,16,32,64,128 etc. Note that this is 2 to the power n where n is the position of the value starting from 0. The positions would be read from right to left, so Pos 7, Pos 6, Pos 5,Pos 4, Pos 3, Pos 2, Pos 1 and finally Pos 0. Perhaps therefore the decimal number values would be more easily understood if written as 128,64,32,16,8,4,2,1.

2 to Power 0 = 1
2 to Power 1 = 2
2 to Power 2 = 4 (2×2)
2 to Power 3 = 8 (2x2x2)
2 to Power 4 = 16(2x2x2x2)
2 to Power 5 = 32(2x2x2x2x2)
2 to Power 6 = 64(2x2x2x2x2x2)
2 to Power 7 = 128(2x2x2x2x2x2x2)

The number 6 is therefore represented by setting 1 in position 1, and position 2. Which would look like this 0 0 0 0 0 1 1 0. (Position zero is set to 0 so even number)
The number 9 is therefore represented by setting 1 in position 0, and position 3. Which would look like this 0 0 0 0 1 0 0 1. (Position zero is set to 1 so odd number)

Time for a quick Byte?

Now I have not chosen to show 8 bit positions randomly, as this group of 8 is referred to as a Byte. Where this gets complicated is that from this point on there are two ways to group the data units, which are either binary (base-2) or decimal (base-10).

As numbers get larger we assign a prefix denoting that it is to be multiplied by X to get the actual value. In decimal (base-10) a prefix is added for multiplies of 1000, in binary (base-2) however this is multiples of 1024.

So how much is a kilo byte? Well do we mean a binary or decimal?

1024 bytes = 1 binary kilo byte
1000 bytes = 1 decimal kilo byte

Confused? The easiest way to relieve this confusion is to use an appropriate prefix which denotes this difference. A multiple of 1000 in decimal is referred as k or kilo, where as in binary a multiple of 1024 is stated as Ki or kibi.

A more comprehensive list can be found online with a quick search but below is the first few. https://en.wikipedia.org/wiki/Binary_prefix

k kilo Ki kibi
M mega Mi mebi
G giga Gi gibi
T tera Ti tebi

So RAM in a computer is referred to as 4 GB or 4 Gigabytes, it would be more accurately described as 4 GiB or 4 GibiBytes as it is denoting a binary value. A 1000 BASE Ethernet card refers to decimal value, therefore the card is 1 GiBits. Storage is often labelled by hardware vendors as decimal multiples.

Useful Specifications

Here is a useful link for data transfers: https://en.wikipedia.org/wiki/Data_rate_units

USB https://en.wikipedia.org/wiki/USB
Mode Gross data rate Introduced in
Low Speed 1.5 Mbit/s USB 1.0
Full Speed 12 Mbit/s USB 1.0
High Speed 480 Mbit/s USB 2.0 (60 MB/s)
SuperSpeed 5 Gbit/s USB 3.0 (625 MB/s)
SuperSpeed+ 10 Gbit/s

PCI Express https://en.wikipedia.org/wiki/PCI_Express

v1.x: 250 MB/s (2.5 GT/s)
v2.x: 500 MB/s (5 GT/s)
v3.0: 985 MB/s (8 GT/s)
v4.0: 1969 MB/s (16 GT/s)

SATA Decimal Prefix Units https://en.wikipedia.org/wiki/Serial_ATA
SATA 1.0 – 1500 Mbit/s – 150 MB/s
SATA 2.0 – 3000 Mbit/s – 300 MB/s
SATA 3.0 – 6000 Mbit/s – 600 MB/s
SATA 3.2 – 16 Gbit/s – 1969 MB/s

Appendix: Throwing new hardware at an old server?

Is it a good idea to fully populate an R710 with 8 x expensive consumer grade SATA SSD? (This is an 11 Generation Dell PowerEdge Server from 2009.)

Well the server itself supports SATA 2.0 so that means each connected drive could potential push 300 MB/s, and most SSD will do more than that. So bottleneck one is that a modern SSD will want to go faster than SATA 2.0. So buy cheap SSD, as max we are going to start with is 300 * 8 = 2400 MB/s.

Next thing we hit is the controller card. A PCI Express x8 card with only 4 lanes wired Gen 1. 250 MB/s * 4 = 1000 MB/s.

 

Mmm, it’s really not going to take much to saturate this server using modern storage. Good idea to add SSD? No, unless you have them laying about.

Dell Documentation for the R710:

http://www.dell.com/downloads/global/products/pedge/en/server-poweredge-r710-tech-guidebook.pdf

11.1 Overview
The PowerEdge R710 has two PCI Express risers: Riser 1 and Riser 2. Each riser connects to the planar through a x16 PCI Express connector.
* Riser 1 consists of two x4 slots and a third x4 slot dedicated for internal SAS storage through the PERC 6i or SAS 6/iR.
* The default Riser 2 consists of two x8 PCI Express connectors.
* There is also an optional x16 Riser 2 that supports one x16 PCI Express card.

11.2 PCI Express Risers
The two PCI Express risers provide up to four expansion slots and one slot dedicated for the
integrated storage controller card. The slots meet the following requirements:
* Two x8 and two x4 PCI Express Gen2 slots, each connected to the IOH
* One x4 PCI Express Gen1 slot for internal storage connected to the IOH

12.4.1 SAS 6/iR
The R710 internal SAS 6/iR HBA is an expansion card that plugs into a dedicated PCI Express x8 slot (four lanes wired). It incorporates two four-channel SAS IOCs for connection to SAS or SATA hard disk drives. It is designed in a form factor that allows the same card to be used in the PowerEdge R610 and PowerEdge T610.

 

New Lab Environment 2015 and vpro VNC blackscreen

Now when I purchased nuc5i5mybe it was with the Intel® vPro Technology in mind. This technology requires no software for you to install and can be very useful for management purposes. There are lots of cool tools that can be used, but one of the most common is going to be the inbuilt vnc solution, which gives the ability see exactly what is happening and even login remotely.

Now the one stumbling block with the vnc solution which I did not initially realize is that a headless system (one without a monitor) may return a black screen when trying to logon.

https://support.realvnc.com/knowledgebase/article/View/261/2/problems-viewing-vnc-server-on-headless-devices

The solution that I found easiest was to get a EDID dongle which is basically a plugin dummy monitor device about the size of small thumb drive. If you do search for “edid headless plug” you should be able to get one for around £10 pounds or less. This device will then pretend to be a monitor and solve the issue of a black screen on the remote desktop session.

New Lab Environment 2015

I have recently been looking at solution for hardware for a new home lab. The primary need was a lower power solution, with plenty of ram and the ability to run hyper-v.

The solution I have chosen is a small form factor from Intel called nuc5i5mybe. The hardware purchase list is as follows:

Crucial 16GB (2x 8GB) DDR3 1600 MT/s CL11 SODIMM 204 Pin 1.35V/1.5V Memory Module Kit CT2KIT102464BF160B ***NOTE THIS IS LOW VOLTAGE RAM compatible with the board***
http://www.amazon.co.uk/gp/product/B007B5S52C
Price: £97.60

BLKNUC5I5MYHE
http://www.scan.co.uk/products/intel-nuc-core-i5-5300u-dual-core-23ghz-ddr3l-so-dimm-m2-plus-sata-iii-6gb-s-25-internal-intel-hd-55
Price: £346.98

M.2 Type 2280 500GB Fast Solid State Drive/SSD Crucial MX200
http://www.scan.co.uk/products/500gb-crucial-mx200-ssd-m2-type-2280-with-555-mb-s-read-500-mb-s-write-100k-iops-random-read-87k-iop
Price: £168.60

Total Cost: £613.18

This enables the running of several visualized guests, which so far I have two domain controllers, and one edge server allowing a site to site vpn to azure. This means my home lab can integrate seamlessly with the cloud offering from MS.

 

Surface Pro 3

Surface Pro 3

My first impression of the Surface Pro 3 was that Microsoft had produced a tablet/laptop that was really tempting. The choice of which model to buy is obviously dependent on how you want to use it, but will more than likely be heavily influenced by price. As you step up the model specification it is important to note that as well as processor/memory changes there is also an associated graphics change.

Surface Pro 3 – 64 GB / Intel Core i3-4020Y HD 4200 / 4GB RAM £639.00 incl. VAT
Surface Pro 3 – 128 GB / Intel Core i5-4300U HD 4400 / 4GB RAM £749.00 incl. VAT
Surface Pro 3 – 256 GB / Intel Core i5-4300U HD 4400 / 8GB RAM £849.00 incl. VAT
Surface Pro 3 – 256 GB / Intel Core i7-4650U HD 5000 / 8GB RAM £1,239.00 incl. VAT
Surface Pro 3 – 512 GB / Intel Core i7-4650U HD 5000 / 8GB RAM £1,549.00 incl. VAT
http://www.microsoft.com/surface/en-gb/products/surface-pro-3
http://en.wikipedia.org/wiki/Microsoft_Surface_Pro_3

Now I went for i5 128GB, as I was looking for a mid range spend for the purchase. However luckily for me I purchased it for around £670 as I got a returned unit from a retail store.

Issues

Storage:

The one thing that infuriates me most about this device is storage. Take for example the premium i7 models, for an additional £310 you can have a 512GB SSD. Ouch.. really?

I am not for one moment suggesting that this device is alone in creative pricing for hardware upgrades, but what is annoying is that this one is aimed to be a laptop replacement. A device with the same zero upgrade possibilities as other tablets.

Now if you could open the Surface easily then the internal mSATA drive could be replaced, however this is practically impossible to all but the very very determined. I am not even sure anyone has successfully done this with a Surface Pro 3, as the only detailed documented attempt resulted in damage.

https://www.ifixit.com/Teardown/Microsoft+Surface+Pro+3+Teardown/26595

http://uk.crucial.com/gbr/en/ssd/series/M550
Crucial M550 512GB mSATA Internal SSD £191.99 inc. VAT
Crucial M550 256GB mSATA Internal SSD £110.39 inc. VAT
Crucial M550 128GB mSATA Internal SSD £65.99 inc. VAT

USB:

Another issue with the device is that it has only one USB 3.0, which is not a major point but does limit options of what can be plugged in without adding an additional hub device (or dock).

The problem I came across was that the USB port did not provide enough power to keep my blu ray player or external hard dive connected. It kept dropping out which I knew was more than likely a power issue, and a quick search found that many others had encountered this. Now on a plus side the actual power brick for the device has a USB port for power. A quick search again and I found the cable I needed “USB 3.0 Y-CABLE 2x TYPE A Male to TYPE A Female”

Fan:

The fan does occasionally become a little noisy, but on the whole is fine.

Additional purchases

A must have purchase is the the type cover and it is strange not to see this bundled.

Surface Pro Type Cover

Two other items worth considering are a decent mouse and a dock for additional ports.
Surface Pro 3 Docking Station
Sculpt Comfort Mouse

My final thoughts

Now I know I bashed the Surface Pro 3 on a few points it is however a very good tablet/laptop replacement. I have in the past purchased several different IPADs, Nexus 10/7 devices and I can say that by far the most productive device for me is the Surface. It can do all the things my laptop did as well as providing similar functionality to my IPAD. I would like to see the mSATA drive accessible in future versions of the hardware, and it may also be good if Microsoft put a note in the box about potential power issues with a high power USB devices.

SQL Event for July 2014

Almost July 2014 and looking forward to what will no doubt be another great SQLBits. This time in Telford.

SQLBits XII 17th – 19th July 2014 The International Centre, Telford
http://sqlbits.com/

However before SQLBits there is another event that you can still go to if you are quick, and it is in the South West!

There are currently a few spots available to see Brent Ozar present “Are AlwaysOn Availability Groups Right for You?” in Bristol. So get in quick

Updated 2 July 2014 No places left..

http://www.sqlserverclub.co.uk/sql-server-bristol-user-group-events.aspx