Office 365 – Day to Day Migration Commands

Connect to Remote Powershell

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session

Create a new move request

(with an existing remote powershell open)

$OPCred = Get-Credential ### use CORP\user.name.1

New-MoveRequest -Remote -RemoteHostName mrs.onpremdomain.com -RemoteCredential $OPCred -TargetDeliveryDomain tenant.onmicrosoft.com -SuspendWhenReadyToComplete -Identity user.name@fqdn.com -BadItemLimit 5 -WhatIf

IF this proceeds, re-run the new move request cmdlet without -WhatIf to queue an SWRC.

Create a new migration batch

https://technet.microsoft.com/en-us/library/jj219166(v=exchg.150).aspx

(with an existing remote powershell open)

New-MigrationBatch -name “Batch of Users” -SourceEndpoint mrs.onpremdomain.com -TargetDeliveryDomain tenant.onmicrosoft.com -BadItemLimit 5 -CSVData ([System.IO.File]::ReadAllBytes(“C:\Path\To\UserList.csv”))

CSV file should have a single column, named EmailAddress, and should contain each user’s primary SMTP address, one per line

Cutover a single mailbox

The mailbox should have an existing move request to do this, and in the AutoSuspended state.

get-moverequest <upn/username> | get-moverequeststatistics

If everything is okay:

Resume-moverequest <upn/username>

CASE WHEN “I need to learn more T-SQL” THEN GoogleTheResult() END

One of my favorite recent discoveries in my SQL Server work recently, is the awesome T-SQL “CASE…WHEN…THEN” statement. I can’t describe how much I love this thing.

It’s pretty simple really:


SELECT
CASE
WHEN fruit = 'apples' THEN 'not oranges'
WHEN fruit = 'oranges' THEN 'not apples'
ELSE 'neither apples nor oranges'
END AS fruit
FROM fruits

As can be expected, for every row where the column is set to “apples”, this will output “not oranges”. For those with the value “oranges”, it will output “not apples”, and of course, any other value (such as “bananas”) would output “neither apples nor oranges”.

To make things more interesting, you can combine the comparison portion (i.e. x = y) with any WHERE based comparisons, such as:

  • X IS NULL
  • X IN (value1,value2)
  • X >= 7
  • X =1 AND >=27
  • X+Y>27 OR ISNULL(Z,0) = 0
Using the comparison, you can even output different columns as the result. I.e.
  • WHEN columnA is null THEN columnB
  • WHEN columnA+columnB > 27 THEN columnB/(SELECT MAX(columnA) FROM TABLE)

Thanks Google. I really love this statement, it’s allowed for some pretty funky complex T-SQL scripts recently, and made my life alot easier!

Backing up all SQL Server user databases to file

The ultimate lazy backup script. I wasn’t feeling to selective about which databases to backup, and I figured, it’d be best to back them all up, even that testing database I occasionally use for data import/export. Never know when I might need it again…

This sits inside a SQL Server Agent job, and runs nightly. Simple enough really.

All works as expected on SQL Server 2012.

DECLARE @Database varchar(max)
DECLARE @path varchar(max)
SET @path = concat(N'D:\BACKUP\',convert(varchar,getdate(),112),' ALL DATABASES BACKUP.bak')
DECLARE my_cursor CURSOR FOR
select name from sys.databases where database_id>4
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BACKUP DATABASE @Database TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'SQL Server all user databases backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM my_cursor
INTO @Database
END
CLOSE my_cursor
DEALLOCATE my_cursor

I’m sure there’s a better way to do this, but there doesn’t appear to be a column in the sys.databases table which denotes a database as a system or user database. Instead, I’ve used the WHERE database_id > 4 – which removes master, model, msdb, and tempdb from the backup.

I left the homeland, Australia, in early January. My employer proposed an opportunity of a lifetime, and alas, here I am now in Seattle, the capital of IT for those Microsoft-ies among us.

One of the things I am passionate about, is photography, and it is on that note of visiting a new city, that I have pulled out my trusty Canon EOS 5D, and put the most expensive toy I own to use. So without further adieu, I present to you, the best of the best of my Seattle photography.

Screenshot_10781_1000000

Server Posterpedia Windows 8 Application

You’re designing a new SQL Server infrastructure for a client, but can’t remember if that high availability solution topology requires shared storage or not, and need to quickly look it up without searching through TechNet. How? With this application! Some genius has combined all of Microsoft’s server architecture posters into a handy Windows 8 application which is now available on the Windows App Store here

Windows 8 Without a Tablet? Nonsense!

Today, Microsoft launched Windows 8 to the masses. And I’m frustrated.

Over the past year, I have had the opportunity to play with Windows 8, blowing away my daily work laptop and installing the new operating system at each release – beta, release preview, consumer preview, and RTM.

Yes. A laptop. Not a touch screen tablet. A laptop with a keyboard and an external mouse. My experience could be likened to running Windows 8 on a standalone desktop PC, with a separate external mouse and keyboard. This is how I work on my laptop, this is how I work on my desktop. And Windows 8 lets me do that with very little change. Continue reading