Archive for the ‘Sys. Admin.’ Category

Azure: No current subscription has been designated

Friday, April 17th, 2015

Somethings a process doesn’t go to plan, this isn’t unusual by any stretch of the imagination. However we can all feel a little lost when the instructions set out before us just don’t seem to work. That was how I was feeling today when I got the following message while trying to use the Azure Resource Manager API through PowerShell:

Get-AzureLocation : No current subscription has been designated. Use Select-AzureSubscription -Current  to set the
current subscription.
At line:1 char:1
+ Get-AzureLocation
+ ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [Get-AzureLocation], ApplicationException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Resources.GetAzureLocationCommand

As it turns out the error message is less than helpful as there were no subscriptions defined at all, tracking this all the way back through my process I noticed this warning message:

C:\Source> Add-AzureAccount
WARNING: There is no subscription associated with account powershell@richardslaterasos.onmicrosoft.com.

Id                             Type       Subscriptions      Tenants
--                             ----       -------------      -------
powershell@amido.com           User                          12ab34cd-f113-49c7-8f5f-e998ff53b7c1

I thought this was a little odd, that is until I logged into the Azure Management Portal to find out that indeed there were no subscriptions. As it turns out I had forgotten to add the service account as a Co-Administrator through the Azure Management Portal (Settings -> Administrators), once I had done this everything worked as expected.

Elevated Command Prompt on Windows 8

Tuesday, March 12th, 2013

Two and a half years ago I posted about openening an elevated command prompt on Windows Vista and Windows 7. The process has changed slightly in Windows 8 as we have lost the traditional start menu.

In Windows 8, applications don’t automatically get administrator privileges, they either need to request it or the user needs to explicitly start the application as an Administrator. The way to do this with the Command Prompt is as follows:

  1. Press the “Start” button.
  2. Type “Command”.
  3. The list of all applications will be filtered by the search term:
    elevated-command-prompt-on-windows-8-type-command
  4. elevated-command-prompt-on-windows-8-select-command-prompt

  5. Right click or press the menu key on the keyboard:
    elevated-command-prompt-on-windows-8-right-click-command-prompt
  6. Select “Run as Administrator” from the bottom of the screen:
    elevated-command-prompt-on-windows-8-click-run-as-administrator
  7. Click “yes” when prompted to confirm:
    elevated-command-prompt-on-windows-8-uac

The Command Prompt running as an administrator will appear in much the same way as Windows 7 or Vista:

elevated-command-prompt-on-windows-8-command-prompt

Connect to VPN with Windows 7

Monday, July 11th, 2011

Windows 7 and Windows Server 2008 R2 has some great new ways of connecting your road warriors and teleworkers to your network. For most of us Systems Administration types Routing and Remote Access is still the most common way of achieving this connectivity without DirectAccess. Because I have written this documentation up twice now for two separate companies I thought it would be worth writing it a third time and placing it in the public domain for reference in the future.

The documentation assumes that the VPN End Point is Windows Server Routing and Remote Access, however the instructions work for a number of other vendors VPN end points.

What you will need:

  • Windows 7.
  • A workplace with a compatible VPN End Point.
  • Your account enabled to use the VPN.
  • The IP address of your VPN End Point.
  • Your Username, Password and Domain.

1. Click the “Networking” tray icon

It is a monitor with a network cable to the left of it, although quite understandably some users see it as a “monitor and a fork”:

2. Go to Network and Sharing Center

It is the link at the bottom of the popup you see after clicking the Networking tray icon.

3. Click “Connect to a network”

4. Click “Connect to a workplace”

5. Click “Use my Internet connection (VPN)”

6. Enter your settings then click Next

The “Internet address” is the four part IP address of your work place, your friendly help desk or company intranet should be able to provide this information. The destination name is purely a useful name for you to refer to your connection as I would suggest the name of your company.

7. Enter your Login details then click Next

Chances are these Login details are going to be the same details you login to the computer with. Whilst the domain is labelled as optional it may be required for your workplace, again your IT Helpdesk or Intranet should be able to provide this to you.

8. Wait

This bit can take a while, the VPN Client will connect to your workplace VPN send your login details then setup the connection.

9. Your Done!

You may find that anything that was connected such as MSN Messenger has disconnected, it should eventually connect again however it will be done through your workplaces internet connection so beware what you are doing. You shouldn’t do what you shouldn’t do, get over it.

10. Remember to disconnect (and how to reconnect)

Remember that “Monitor and Fork” icon in the bottom right hand corner of your screen, if you click that you will be able to see that your new VPN connection is connected. Once you are done you can disconnect by clicking the connection then clicking “Disconnect”. You can reconnect in the future in a similar way, click the “Monitor and Fork” select the connection then click “Connect”.

Listing Table Sizes

Friday, December 24th, 2010

Databases are a pain in the neck to look after, poorly designed models and processes that don’t remove temporary data can cause a database to grow in size. A database that is allowed to grow large beyond its requirements becomes a burden on the nightly backup, takes longer to restore in the event of a recovery scenario and slows down the development process by preventing developers from testing things out on “live” data.

More often than not I have found that the problem lies with log or analytic tables sometimes this information is liberally logged (which it should be) and then totally ignored without a thought for trimming the data on a regular basis.

SQL Server Management Studio provides a way of looking at the storage usage of tables individually from the properties context menu item of the table.

SSMS Storage Properties

In large databases this can be laborious, I found a script that will collect this information and present it as a table. I have adapted it a little so that I can see the total size of the table and sort by each column to drill down to the problem tables.

SET NOCOUNT ON
CREATE TABLE #spaceused (
  name nvarchar(120),
  ROWS CHAR(11),
  reserved VARCHAR(18),
  DATA VARCHAR(18),
  index_size VARCHAR(18),
  unused VARCHAR(18)
)
 
DECLARE TablesFromSysObjects CURSOR FOR
  SELECT name
  FROM sysobjects WHERE TYPE='U'
  ORDER BY name ASC
 
OPEN TablesFromSysObjects
DECLARE @TABLE VARCHAR(128)
 
FETCH NEXT FROM TablesFromSysObjects INTO @TABLE
 
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #spaceused EXEC sp_spaceused @TABLE
  FETCH NEXT FROM TablesFromSysObjects INTO @TABLE
END
 
CLOSE TablesFromSysObjects
DEALLOCATE TablesFromSysObjects 
 
SELECT	name AS TableName,
		ROWS AS ROWS,
		CAST(LEFT(reserved, LEN(reserved) - 3) AS INT) AS Reserved,
		CAST(LEFT(DATA, LEN(DATA) - 3) AS INT) AS DATA,
		CAST(LEFT(index_size, LEN(index_size) - 3) AS INT) AS IndexSize,
		CAST(LEFT(unused, LEN(unused) - 3) AS INT) AS Unused,
		(CAST(LEFT(reserved, LEN(reserved) - 3) AS INT) + CAST(LEFT(DATA, LEN(DATA) - 3) AS INT) + CAST(LEFT(index_size, LEN(index_size) - 3) AS INT) + CAST(LEFT(unused, LEN(unused) - 3) AS INT)) AS Total
FROM #spaceused
ORDER BY Total DESC
DROP TABLE #spaceused

Ordinance Survey OpenData (Part 3 – Cleaning Up)

Friday, December 17th, 2010