Friday, August 26, 2011

Add a secondary alias to a federated Office 365 mailbox

Today, Joe called and asked me why he couldn't get emails at  Well, his account was setup for and that's also his UPN in Active Directory.  His business cards have already been printed and he's been using his email to send, so we can't just re-create things.

Office 365, open the pod bay doors

Now, you could just log into Office 365, click on Manage under Exchange Online, find the user's mailbox and add the address under E-mail Addresses.  But since your using SSO with Federation, you're getting this lovely message:
I'm sorry, Dave. I can't do that.
Really, open the bay doors, please

Well, if you managed to get SSO and Federation configured, you're probably already familiar with the key to this process, ADSIEdit.  Launch it and connect to the Default naming context.

Step 1: Get the Blue Key
Drill down through the schema until you find your user object, right click it and select Properties.

Step 2: Get the Red key
 Locate the proxyAddresses property and edit it.  Add your secondary alias using the following format (capitalization matters, significantly!):

Note the existing alias with a capitalized SMTP.  That means it's the primary, so we don't want to mess with it.  Click OK all the way back out of ADSIEdit and head over to your DirectorySync server.  Launch %programfiles%\Microsoft Online Directory Sync\DirSyncConfigShell.ps1 and type:


Later, log into the management console of Exchange Online and you should see the results you need:

Look, it's the pod bay, finally!

And according to the references below, if we extend the Active Directory schema for Exchange 2010 SP1, we can leverage additional Active Directory properties that will replicate up to Office 365 and take effect.  Or we could all learn PowerShell remoting, which I've used a few times already for other items, like mass contact creation, but I'll leave that as an exercise for the reader.


Wednesday, August 17, 2011

Refactoring sp_spaceused

A while back I refactored sp_spaceused into CTEs.  One query would return the total for a database, the other would return totals for individual user objects within the current execution context.  This only accounted for on-page data, which the original stored procedure does manage.  I keep finding myself running the object level query to determine which tables are index heavy or are ready for historical archiving, both of which are especially handy when you're working with the storage limitation of Express Edition.  (Sample use cases)

Disclaimer: By following any of these activities you hold the author harmless of any effects, averse or otherwise.  There's no fitness or guarantee for this information. 

WITH size_calculations (objectid, reserved_pages, used_pages, pages, num_rows)AS (SELECT [OBJECT_ID] AS [objectid]
, SUM(reserved_page_count) AS [reserved_pages]
, SUM(used_page_count) AS [used_pages]
WHEN (index_id < 2)
THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
) AS [pages]
WHEN (index_id < 2) THEN row_count
) AS [num_rows]FROM sys.dm_db_partition_stats GROUP BY [object_id])SELECT OBJECT_NAME(SC.objectid) AS [name]
, SC.num_rows AS [rows]
, (SC.reserved_pages * 8) AS [reserved_kb]
, (SC.pages * 8) AS [data_kb]
, ((CASE WHEN SC.used_pages > SC.pages THEN (SC.used_pages - SC.pages) ELSE 0 END) * 8) AS [index_size_kb]
, ((CASE WHEN SC.reserved_pages > SC.used_pages THEN (SC.reserved_pages -SC.used_pages) ELSE 0 END) * 8) AS [unused_kb]

FROM size_calculations AS SC
INNER JOIN sys.objects AS O
ON SC.[objectid] = O.[object_id]
ORDER BY SC.reserved_pages DESC;

Monday, August 8, 2011

Where are my SQL Servers?

How can you discover where SQL Servers are in your enterprise environment?

There's always the handy sqlcmd -L, but how can you know you've found everything?

How about this for starters:

csvde -f out.csv -r "(&(objectClass=Computer)(servicePrincipalName=*SQL*))" -l name,servicePrincipalName

It's left as an exercise for the reader to find the intersection of the results of the above tools, along with validation.

Monday, August 1, 2011

Fixing Old Laptops

For the last ten years, I've been the computer guy in the family.  That means when I'm home or visiting friends, there's an awkward moment after "How have you been?" and before "So, my computer..."

Last week, I went up to see my sister-in-law and her family, and her girls were complaining about their old "homework" laptop.  It was essentially the same hardware as my ThinkPad x41t, which actually can manage to run Windows 7 Ultimate.  That being said, with Windows XP on a Pentium M with a 4500RPM IDE drive, things just aren't going to be that fast.  And there was malware involved.  So where do we start?

Disclaimer: By following any of these activities you hold the author harmless of any effects, averse or otherwise.  There's no fitness or guarantee for this information. 

Safe Mode Cleaning

First, boot the computer into Safe Mode with Networking.  At this point, I like to cleanup as much temporary data as possible to reduce malware removal tool scan time.  Tools like Piriform's CCleaner work great to clean up the local profile.  If you need to clean other user profiles, you're on your own or logging in and out several times to run the tool.

Following up with a scan with tools like Malwarebytes (a current favorite) and you've got a head start on the malware problem.

Additionally, I typically like to check a few registry locations, however the majority of the startup items can be located by TrendMicro's Hijackthis tool.  Be cautious as many of the keys it enumerates are, in fact, supposed to exist.

Disk Problems

In safe mode, you can also check the health of the logical disk using chkdsk.  If I'm having a performance issue loading or opening programs, it's important to confirm that you're not getting disk read errors causing re-reads.  Realistically, the speed at which disks operate today actually causes more read errors that you know, but there's automatic disk correction occurring under the hood.  What I'm looking for is significant logical errors on the disk, which can also mean pending disk failure.

If chkdsk reports more errors than a volume bitmap error, then I'll schedule a chkdsk /f on the next reboot to get those index errors repaired.

Useless Programs and Features

Once back in Windows, I'll load up Add and Remove Programs and audit the installed applications.  Frequently there's a plethora of toolbars, which can slow down browser performance.  Occasionally, there's two versions of Java, which I recommend running only the latest, most up-to-date version, unless you have an application compatibility issue, such as using the web management software for Cisco PIX firewalls (Requires Java SE 1.4.14, I believe).

The biggest culprit I see slowing down older hardware running Windows XP is the Windows Desktop Search 4.0 feature that's installed via Windows Update.  It's a great tool, and I leverage it on my Windows 7 workstation, but that's newer hardware.  It's the Microsoft equivalent to Google Desktop.  If you don't need it, then you don't need it crawling across your slow disk, reading and indexing files in the background.


Speaking of disks, how about defragmenting?  Diskeeper swears by it as a performance tool, however the measurable results can be negligible.  Rather, if you have the option to re-install Windows from scratch and happen to have a Windows 7 install CD lying around, consider using it to pre-format your disks for your Windows XP install so that you can take advantage of disk partition alignment, which applies not only to servers, but to workstations.

As for actually defragmenting, I prefer both Piriform's Defraggler and Sysinternal's contig.  The former allows for a more efficient whole disk defragmentation including defragmenting free-space.  Both utilities allow for single-file defragmentation.

Additionally, some system files, namely the paging file and registry hives, cannot be defragmented in user-mode.  Instead, you could defragment free space, set your paging file to zero, reboot, set your paging file back to a 1:1 ratio with you physical memory, and reboot again, hoping that the file will be created in contiguous free space.  Or you could just use PageDefrag.  The tool does a boot-time defrag of the paging file and registry hives, just like a chkdsk /f.  I don't run this tool on a regular basis, but only when I'm being incredibly aggressive with my disk fragmentation.

General System Optimizations

Do you really need those fancy themes?  Or Fast User Switching on a computer with a single account?

Right clicking My Computer and selecting properties brings up all the tabs we need.  Under the Remote tab, are you even using Remote Desktop or Remote Assistance?  How about unchecking the option?  How about System Restore?  Checking the turn off box will also clear out the disk space it uses, where old malware files tend to hide.

The Advanced tab is where the real magic happens.  For Performance settings, set to Adjust for best performance.  You'll lose that fancy blue color, but it's a few less bitmaps to load into memory.  Switch over to the Advanced tab under Peformance, and click on Change to set your page file size.  Regardless of the size you set (I prefer a 1:1 ratio to physical memory), you should set the Custom Size minimum and maximum to the same size.  Why should Windows waste disk IO and CPU resources managing and resizing the paging file?  Don't forget to click Set to actually apply the setting, and then later reboot.  If you can put the paging file on a different volume that resides on a different physical disk, you can experience improved performance.

Under the Startup and Recovery section of the Advanced tab is also where you control the BSOD actions. If at all possible set it to not automatically reboot and to write a full memory dump.  This allows you to use tools like Nirsoft's BlueScreenView to inspect the root cause of the crash. (It's left as an exercise to the reader to learn about Crash Dump Analysis.)

The next stop is services.msc.  Now that we've got the windowing system down to the minimums, does the Themes service need to be running?  Nah, let's set it to Stopped and Disabled.  And if we're using a single account on the system, stop and disable Fast User Switching.  What about Windows Wireless Zero Configuration on a desktop system?  Or Print Spooler?  If you disabled System Restore earlier, you can also disable the service.  Windows Indexer?  Windows Search? (Which you hopefully uninstalled already.)  Be very judicious about disabling services though, as some of the dependencies are not as obvious and your risk creating an unstable system.

The End Result

Once I went through all of these items, I was able to reduce CPU usage, stabilize a dying disk, and reduce memory utilization by a whopping 50%.  The main problems I found were malware, logical disk corruption, and Windows Search causing high IO.