SQL Server – How big are my tables?

Code Snippet

create table #t
(
    name sysname primary key clustered,
    rows bigint,
    reserved sysname,
    data sysname,
    index_size sysname,
    unused sysname
);

exec sp_MSforeachtable 'insert into #t exec sp_spaceused ''?''';

select
    name,
    rows,
    convert( bigint, replace( reserved, ' kb', '' ) ) / 1024 as [reserved MB],
    convert( bigint, replace( data, ' kb', '' ) ) / 1024 as [data MB],
    convert( bigint, replace( index_size, ' kb', '' ) ) / 1024 as [index MB],
    convert( bigint, replace( unused, ' kb', '' ) ) / 1024 as [unused MB]
from
    #t
order by
    3 desc;

drop table #t;

which

unix has a which command to tell you the path of the executable file which is first found on the path and which will get executed when you type the command without an explicit path.

The simplest use can be aped really easily in the Windows command interpreter. Just create a batch file called which.cmd and bung it on the path, e.g. in C:\Windows (well, %SystemRoot% for purists):

@echo off
for %%i in (%1) do echo %%~$path:i

So now, from a command prompt, you can find which (if any) of those pesky command-line tools is going to run:

C:\>which xsd.exe
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\xsd.exe

C:\>

Yay!

Windows 2003 is old hat

In Windows 2008+, setting a different SSL certificates for each IP:Port binding is point and click, so for an exchange server where the internal and external names (and therefore required certificates) differ:

It is easy to select the correct certificate for each binding:

[Note that if you want to use the same IP:Port for multiple SSL sites by using host headers, you can in Windows 2008+, but it’s back to the command line.]

In Windows 2003, it’s a bit fiddlier:

C:\Scripts>httpcfg query ssl
    IP                      : 192.168.100.10:443
    Hash                    : c1f2b0f6fbd1c115f79535ea6c503b14dbd64416
    Guid                    : {4dc3e181-e14b-4a21-b022-59fc669b0914}
    CertStoreName           : MY
    CertCheckMode           : 0
    RevocationFreshnessTime : 0
    UrlRetrievalTimeout     : 0
    SslCtlIdentifier        :
    SslCtlStoreName         :
    Flags                   : 0
——————————————————————————
    IP                      : 192.168.100.34:443
    Hash                    : c1f2b0f6fbd1c115f79535ea6c503b14dbd64416
    Guid                    : {4dc3e181-e14b-4a21-b022-59fc669b0914}
    CertStoreName           : MY
    CertCheckMode           : 0
    RevocationFreshnessTime : 0
    UrlRetrievalTimeout     : 0
    SslCtlIdentifier        :
    SslCtlStoreName         :
    Flags                   : 0
——————————————————————————

This shows the same certificate bound to two different IP addresses. You need to delete one before you can add it again, otherwise you’ll get the following fail message:

HttpSetServiceConfiguration completed with 183.

Here we go:

C:\Scripts>httpcfg delete ssl -i 192.168.100.10:443
HttpDeleteServiceConfiguration completed with 0.

Now we can put it back with the correct certificate, but first you need the Thumbprint. You can get this by looking at the certificate in the filesystem by clicking on the .cer file or in the Certificates MMC console

or from Powershell for Exchange on an Exchange Server 2007+ server:

[PS] C:\Documents and Settings\Administrator>Get-ExchangeCertificate

Thumbprint                                Services   Subject
———-                                ——–   ——-
3972B12B864B4DF7C627609A184DF1B3884E09BA  IP…      CN=dc1
E837A8E0148D778744B06F2CC4B6157D37C19B8E  IP..S      CN=dc1.acs-solutions.local
C1F2B0F6FBD1C115F79535EA6C503B14DBD64416  …WS      CN=*.acs-solutions.co.uk, OU=Domain Control Validated – RapidSSL(R), OU=
648AB8210EDC23793E0ECE63731E6AB86C207603  …..      CN=ACSRootCA, DC=acs-solutions, DC=local

So, now we have the Thumbprint, configure the Certificate of choice onto the IP Address:Port:

C:\Scripts>httpcfg set ssl -i 192.168.100.10:443 –h 3972B12B864B4DF7C627609A184DF1B3884E09BA -g “{4dc3e181-e14b-4a21-b02259fc669b0914}” -c MY
HttpSetServiceConfiguration completed with 0.

And finally check the result:

C:\Scripts>httpcfg query ssl
    IP                      : 192.168.100.10:443
    Hash                    : 3972b12b864b4df7c627609a184df1b3884e 9ba
    Guid                    : {4dc3e181-e14b-4a21-b022-59fc669b0914}
    CertStoreName           : MY
    CertCheckMode           : 0
    RevocationFreshnessTime : 0
    UrlRetrievalTimeout     : 0
    SslCtlIdentifier        : (null)
    SslCtlStoreName         : (null)
    Flags                   : 0
——————————————————————————
    IP                      : 192.168.100.34:443
    Hash                    : c1f2b0f6fbd1c115f79535ea6c503b14dbd64416
    Guid                    : {4dc3e181-e14b-4a21-b022-59fc669b0914}
    CertStoreName           : MY
    CertCheckMode           : 0
    RevocationFreshnessTime : 0
    UrlRetrievalTimeout     : 0
    SslCtlIdentifier        :
    SslCtlStoreName         :
    Flags                   : 0
——————————————————————————

Hope that helps.

A little Active Directory DNS Server Magic

By default, Windows DNS servers register a static A record for every IP address that the DNS server service is bound to.

If you have a multi-homed AD Server which is also a DNS server (as they often are), you’re fine as long as there’s no routing involved in your local network. The netmask ordering feature magically gives the correct IP address out to clients depending on the LAN segment they’re attached to. But if you also have routing, that breaks the netmask ordering, and clients just get round-robin randomised IPs. If they have routes to both IP’s fine. In the more common case, they don’t (after all, why else did you segregate your network and multi-home your DC/DNS server).

REGEDIT to the rescue:

See http://support.microsoft.com/kb/246804:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\DNS\Parameters\PublishAddresses

Data type: REG_SZ
Range: IP address [IP address]
Default value: blank

This value specifies the IP addresses that you want to publish for the computer. The DNS server creates A resource records only for the addresses in this list. If this entry does not appear in the registry, or if its value is blank, the DNS server creates an A resource record for each of the computer’s IP addresses.

This entry is designed for computers that have multiple IP addresses. With this entry, you can publish only a subset of the available addresses. Typically, this entry is used to prevent the DNS server from returning a private network address in response to a query when the computer has a corporate network address.

DNS reads its registry entries only when it starts. You can change entries while the DNS server is running by using the DNS console. If you change entries by editing the registry, the changes are not effective until you restart the DNS server.

The DNS server does not add this entry to the registry. You can add it by editing the registry or by using a program that edits the registry.

Thank you Microsoft. No really, a GUI would be nice, but thanks anyway.

SSL Wildcard Certificate enables Host Headers on SSL in IIS7

But not with the GUI…

Here’s an example:

C:\Windows\System32\inetsrv>appcmd.exe list site
SITE "Default Web Site" (id:1,bindings:http/*:80:,state:Started)
SITE "borrowers" (id:2,bindings:http/109.235.146.120:80:borrowers.fasttrac.co.uk,https/109.235.146.120:443:borrowers.fasttrac.co.uk,state:Started)
SITE "lenders" (id:3,bindings:http/109.235.146.120:80:lenders.fasttrac.co.uk,https/109.235.146.120:443:lenders.fasttrac.co.uk,state:Started)

In order to get the host header bindings you need this arcane syntax for each website:

C:\Windows\System32\inetsrv>appcmd.exe set site /site.name:"borrowers" /+bindings.[protocol='https',bindingInformation='109.235.146.120:443:borrowers.fasttrac.co.uk']
SITE object "borrowers" changed

And in order to remove the existing https bindings:

C:\Windows\System32\inetsrv>appcmd.exe set site /site.name:"borrowers" /-bindings.[protocol='https',bindingInformation='109.235.146.120:443:']
SITE object "borrowers" changed

If you had previously bound to any IP (not a static one), the substitute ‘*:443:’ instead of ‘<ip address>:443:’

Check it all again with appcmd list site.

Impossible to remember, but easy to do…

Hyper-V – Mount ISO image from remote file share

I’m always forgetting how to do this and getting an error like the following:

The explanation at http://virtuallyaware.wordpress.com/2008/06/23/hyper-v-constrained-delegation-of-authority-remote-mounting-of-iso-with-management-console/ is perfect, but in case it ever goes away, here’s my summary:

In ADUC, find the Hyper-V server, properties, Delegation Tab:

Add the computer which will host the shared ISO images and OK.

Wait a minute of two – trust me!

Now you’re good to go.

DOS Bootable USB Drive

Oh, what a horrible afternoon. I just needed to update the firmware on a couple of Adaptec 5805 SAS/SATA controllers as part of re-purposing a server with Windows Storage Server 2008 R2. I splashed about for ages, but the solution was easy once I got all the bits together.

1. Unless you have a floppy drive, you’ll need a virtual one. If you have a virtual machine, you can use that to create a virtual floppy drive, otherwise:
   1. Download Ken Kato’s VFD driver from http://sourceforge.net/projects/vfd/
   2. If you’re on x64
      1. get the x64 patch from http://levicki.net/downloads/ (and read his story if you have the time)
      2. Copy the two files in Levicki’s download and save them over the ones from SF
      3. Remember to repeatedly hit F8 while windows is booting so you can Disable Driver Signature Enforcement
   3. Run vfdwin.exe as administrator to install and start the Virtual Floppy driver
   4. Create a driver using RAM and remember to assign it a Drive letter (in the vfdwin tool)
2. Format the virtual floppy drive with Windows, and make it into a DOS bootable disk. This gets the DOS boot files.
3. Download and install the HP USB Disk Storage Format Tool from SP27213.exe from http://code.google.com/p/opensourcemid/downloads/detail?name=SP27213.exe&can=2&q=
4. Use the HP tool to copy the boot files to the USB drive:

   

5. Put Windows Explorer in “show everything” mode
   
6. Copy all of the files from the Virtual Floppy to the USB stick, except IO.SYS, MSDOS.SYS and COMMAND.COM, as they’re already there
7. Reboot and select the USB stick as the boot device. Try F12 for a boot menu…

Links

• My SkyDrive