Configuring Applications to connect to SQL Servers on Non Standard SQL Ports

Following MS Best Practice, we always try to change the default Port used by our SQL Servers. Middleware and front end servers which connect to the SQL Servers can normally use the SQL Browser (UDP 1434) to find the port used by an instance. If you happen to have disabled the SQL Server Browser service or maybe have a firewall in between the SQL server and client, then the port that a SQL Instance resides on can be defined along with a suitable alias.

To do this use the 'SQL Server Client Network Utility' by going to Start > Run > cliconfg.exe and choose the 'Alias' tab. To add a new association of server alias and port click 'Add' and enter the relevant details. I shall never have to Google this again now :o)

Get SQL Database File Size Information

I had a query from a colleague this morning regarding the size of a database on one of our SQL Server 2005 clusters. He wanted to know whether it had run out of space, as the database is not configured to grow automatically.

I queried the 'sys.database_files' view on the database in question in order to determine it's current size, only to find what I thought to be a mismatch between what values were being returned in the size, max_size and growth fields and those returned from the SSMS GUI. Upon reading the MSDN article for sys.database_files, it appears that the values that are returned don't represent the actual file size but the file size in 8KB pages. Therefore I've concocted a SELECT statement which can be executed against a database and will return what I think is the more pertinent values in their expected form. 

SELECT name as [SQL Logical Name], physical_name as [File Path],
size * 8/1024 as [Current Size (MB)],
max_size * 8/1024 as [Maximum Size(MB)],
growth * 8/1024 as [Growth Increment (MB)]
FROM sys.database_files

As someone who dabbles with SQL (usually when something's went wrong), I find the above T-SQL very useful for assessing database sizes.

Getting to grips with VMware's PowerCLI

Yesterday I downloaded VMware's PowerCLI installer and started playing with some of the supplied cmdlets. The motivation behind it was to find a simple way of listing all of the current snapshots on our army of VMs. With the best will in the world, we find that the odd snapshot is not removed once we're finished a change and therefore can be in place for weeks before being re-discovered and removed.

The first step in using the PowerCLI cmdlets is to connect to the vCentre or Virtual Centre server and that's done using Connect-VIServer . If the optional arguments are supplied you'll be promped with a login dialog.

Once connected, you then have access to run the cmdlet's that are allowed as per your level of permissions in Virtual Centre. In my case I looked at the Get-Snapshot cmdlet, which accepts a VM name as an argument and returns details of any snapshots associated with the supplied VM name.

Using some basic PowerShell I was able to get a tabular list of all VMs with snapshots, showing the name of the VM, the date that the snapshot was created, the snapshot's name and description. Very handy for quickly finding those forgotten snapshots.

Get-VM * | Get-Snapshot | Select VM, Created, Name, Description

Using PowerShell with VMware looks to make those painful admin tasks easier but there was one 'gotcha' that i noticed. When finished using PowerShell, use the Disconnect-VIServer or your session will stay open on the VC Server.

More MDM SP1 Info.. Proxy and Work Exceptions

As part of the Mobile Device Manager work that i'm involved with I've been looking at Mobile Device GPOs. I've started reading about the Network Connection settings. There's an interesting article over at the enterprisemobile with regards to the Proxy/Work network configuration. I've found it very helpful and ended up following it for our implementation.

Virgin's Advanced Network Error Search

In a semi hungover state, I tried browsing over to Facebook but accidentally entered www.face. Instead of receiving the traditional address not found error page, I got the following;

Contrary to the rumours this is not a modification to your machine or browser but instead the way in which Virgin are handling DNS requests for addresses that are not found (NXDOMAIN). I've not decided whether it's good or bad at the moment but would rather it be an Opt-In service rather than Opt-Out. El-Reg covered this back in August and there's a lengthly discussion on cable forum regarding this - link

MDM SP1 SCW Manifests

Before deploying servers into the production environment at work, we run the Security Configuration Wizard to harden the Operating System. Hardening involves such things as disabling unnecessary Services, adjusting authentication methods and setting Windows Firewall Exception in an effort to reduce the overall attach surface of the Operating System. While Server 2003 SP1 includes a number of default templates, additional templates are available for download on the Internet.

As part of the MDM deployment that I'm involved with, I've been looking to harden the servers before they go live. The MDM Dev Team at Microsoft were kind enough to create manifests and bundle them in the MDM SP1 Server Resource Kit. I've yet to try them but I expect them to function as the others do. The MDM SP1 Resource Kit can be downloaded here.

64bit VM on a 32bit OS with VMware Workstation

I'm in the process of deploying MS SCMDM SP1 and have decided to first install the various components (MDM Gateway, Enrolment and Management Servers) in a isolated local VMware Environment before doing the live install. I stumbled upon a problem with running the 64bit Windows 2003 OS on my 32bit Windows XP laptop OS. Basically VMware Workstation was complaining that the CPU on the laptop was not compatible with the 64bit OS that i was trying to run on the VM.

There was two steps to resolve this;

• Enable Intel Virtualisation Technology in my laptop's BIOS
• Power off the laptop and then power on (a hot restart was not enough for this BIOS option to be recognised.)

There's also a handy CPU tester from VMware which will check your Host CPU for 32/64bit compatibility. It can be found here