SQL Reading : SANS

1. Storage area network (SAN) basics every SQL Server DBA must know
2. SQL Server Best Practices Article
3. How to Specify SQL Storage Requirements to your SAN Dude
4. SAN Storage Best Practices for SQL Server
5. High Performance Storage Systems for SQL Server

Testing SQL Server Connectivity

Starting with the basics, can we see the server? (run ping from the command line)

PING ipaddress/hostname 

Is a response returned from the host?

This checks the host is present.
Caveats : PING (ICMP requests) might be blocked by a firewall.
If you can ping an IP Address by not a hostname, you need to investigate name resolution and DNS.

Check SQL is listening on TCP.

TELNET ipaddress/hostname 1433

Does Telnet open a session succesfully? (Does the command window go blank with a flashing cursor in the top left)

This tests the default instance of SQL Server on the specified (in this case default) port.
The SQL Listener/Browser service is never queried.
Annoyingly TELNET is not installed by default, hence has to be added to versions of Windows from Vista onwards.

If you don't connect successfully, your first checks should be

1. Is the SQL Server service is running? 
2. Does the firewall allows TCP 1433?

To connect to a named instance, lookup the port it is running on from it's TCP/IP Properties (under IP Addresses > IP All > TCP Dynamic Ports)
Then TELNET to the hostname with the discovered port number

TELNET ipaddress/hostname 1433

The presence of a SQL Named instance is broadcast by the SQL Browser Service which communicates on UDP port 1434.
If you can connect via port but not by specifying the instance name >

1. check the named instance is running
2. check the browser service is is running.
3. check UDP port 1434 is allowed in the firewall configuration.

If you want to test connectivity via named pipes or Shared Memory you can use SQLCMD as detailed here... Steps to troubleshoot SQL connectivity issues


Further Reading

SQL Server Connectivity Issues with Named Instances

SQL Server – Finding TCP Port Number SQL Instance is Listening on

Using named instances? Test your DAC connection!

Steps to troubleshoot SQL connectivity issues

TSQL : Revealing Foreign Key Constraints

Back in the day I blogged on revealing Referential Integrity via Information_Schema views. Here is an updated script to do the same.

SELECT 
    ForeignKeyName   = fk.name
   ,TableName   = OBJECT_NAME(fk.parent_object_id)  
   ,ColumnName   = COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
   ,ReferencesTable  = OBJECT_NAME (fk.referenced_object_id)
   ,ReferencesColumn = COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) 
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc 
   ON fk.object_id = fkc.constraint_object_id

Ctrl+E (Execute) Not working?

In SQL 2012 Management Studio, my favourite shortcut for running scripts stopped working mysteriously, A message in the bar at the bottom read ...

 (Ctrl+E) was pressed. Waiting for second key of chord...

The reset instructions are shown below the screenshot.

The 'fix' for this, is 

1. Go to Tools > Options
2. Open the 'Environment' section of the tree and select 'Keyboard'
3. On the right you will see a 'Reset' button.
4. Click it and Keyboard shortcuts will be returned to their defaults.

Windows 8.1 : VMConnect Issue and shortcut creation

I recently set up Hyper-V on Windows 8 and was happily using RDP to connect to my VMs to achieve full screen mode via a short-cut.

It wasn't until I was off my local network that I realised that I had been relying on my network router (to resolve the VM names) for Remote Desktop to work in this way. Double clicking on the VM in Hyper-V manager does indeed bring up the VM, but I ideally don't want to launch this first every time.

VMConnect is the tool that lets you access VMs.

At first I had a small issue when I ran VMConnect...

" You do not have the required permission to complete this task. Contact the administrator of the authorization policy for the computer ‘computername’ "

So I followed the advice on Joesph Turley's blog and added myself to the 'Hyper-V Administrators' group, restarted the machine and VMConnect could now see my VMs...

So, returning to my original problem, how do I quickly open a session to my Hyper-V VM without RDP?

The solution is to create a shortcut to VMConnect itself, passing the pc name and vm name as parameters. -

"C:\Windows\System32\vmconnect.exe"    myPCname    myVMname

I managed to make to launch into full screen mode, as the first time you connect you can select the resolution that it will reuse for future connections.

TIWorker.exe

Today I noticed the hard drive light on my laptop furiously flashing away. Given I had not started any applications yet I pulled up Performance Monitor to investigate. The 'Disk' tab shows 'Processes with Disk Activity' and TIWorker.exe was at the top, merrily reading and writing to my drive.

So, what is it?

My limited research (googling - a verb now!) suggests it is a Windows Installer, connected to the Windows Update Service. Even when you are not updating, it may be tidying and compressing old update files in the background!

There are multiple links to problems that used to be present with tiworker.exe, all reportedly resolved by Windows Updates (Guess what I'm doing next...)

Links

• A servicing stack update is available for Windows 8 and Windows Server 2012
• Windows 8 update bug clogs CPUs

Cannot set a credential for principal 'sa'

I was setting up a VM for testing purposes today and had cause to change my password on a SQL 2005 instance (admittedly using the 2008R2 tools). A trivial, quick task via management studio, or so I thought. I came up against the following error.

Msg 15535, Level 16, State 1, Line 1 
Cannot set a credential for principal 'sa'.

The 'Credential' tick box was not ticked, in-fact it was greyed out meaning I could not select it either.
The solution was to turn to Tsql.

Run the following against the master database

ALTER LOGIN [sa] WITH PASSWORD=N'n1ncomp00p'

No, that isn't one of my passwords, but I like it...

Links
Cannot set a credential for principal 'sa'. (SQL Server 2005/2008)