Float vs. Decimal data types in Sql Server

update: 4/5/2017
‘Name’ and ‘Alias’ have a 64 character limit, so when creating new groups with temporary values and prepending ‘NEW’, sometimes this can go over the 64 character limit. I’ve added code to remove spaces and truncate after 64 characters when creating ‘NEWName’ and ‘NEWAlias’.  I’ve also added RoomList type to the distribution group creation process.

update: 1/25/2017
If you have over 500 groups to delete, AD Connect will prevent synchronization as a protection mechanism.  I’ve added cmdlets to disable and re-enable this mechanism on AD Connect.

update: 6/20/2016
I’ve added LegacyExchangeDN to export and modified clean section and PowerShell sections to account for this new attribute.

Migrate Distribution Groups from Exchange

update: 12/3/2015
I’ve updated all PowerShell sections to include more attributes, including SMTP domain, so you can easily choose which groups to migrate based on domain.   I’ve also included mail-enabled security groups.  If you wish to exclude mail-enabled security groups from migration, they are easily identifiable in each export. Simply sort/filter by “RecipientTypeDetails”, and delete anything with “MailUniversalSecurityGroup” if desired.

——————————————

I came across a scenario where I needed to move Exchange On-Premise distribution groups to Exchange Online distribution groups (cloud-only), and developed this process.  This provided a few benefits : 1) simplify administration, bypassing hybrid server 2) allow distribution group owners ability to self-manage and modify members in Outlook.

Since distribution groups are used daily, I took extra steps to reduce impact. Basically prep everything, then cutover during slow time (at night). Here are the high-level steps. Steps 1-3 are considered Prep and steps 4-5 are considered Cutover.

Prep 

1) Exchange On-Premise: export all information for distribution groups
2) Prepare and clean data, add columns that prepend “NEW” to avoid conflicts (this includes nested groups)
3) Exchange Online: create “NEW” distribution groups, hide from GAL, and add members
Cutover
4) Exchange On-Premise: delete distribution groups, and force synchronization with Azure AD
5) Exchange Online: rename distribution groups (remove “NEW”), unhide, and add SMTP/x500 aliases

Ok let’s start working through all the details. All output/input is in c:tempdg in my sample scripts.

Prep

Step 1 – Exchange On-Premise: export all information for distribution groups

Exchange On-Premise: Export data


We’re going to export distribution groups, their settings, SMTP aliases, and members from Exchange On-Premise into three different files. Here are the PowerShell scripts you’ll need to run. (Note: If you need localization exports/imports, see my blog here on foreign characters ).  By the way, some commenters have reported issues with ‘Alias‘ field (in EXO) having issues with foreign characters, and they had to go back and adjust these characters during clean-up step 2, for example change (Ä –>A).

Step 2 – Prepare and clean data, add columns that prepend “NEW” to avoid conflicts

Clean Files


Use exports from the previous step to prepare and clean data. I’m not a fan of manipulating data “on the fly” in PowerShell, because it’s nearly impossible roll-back quickly. I like to create CSV files that have both ‘old’ and ‘new’ data, which allows quick roll-back if necessary. It’s best to use Excel, since we can filter and use macros. When it’s time to delete data, delete cell contents rather than delete rows – this is due to limitations when using Excel ‘filters’.  After deleting data, remember to sort the columns which will remove blank rows.

Migrate Distribution Groups from Exchange

1) Clean file from export 1 (Distribution Groups file, distributiongroups.csv)

Create “NEW” values. Insert columns after the following (Name, Alias, DisplayName, PrimarySmtpAddress), and prefix column header with “NEW” by using following formula. Then copy the formula down through data, so that all data is prefixed with “NEW”. You should now have the following columns (Name, NEWName, Alias, NEWAlias, DisplayName, NEWDisplayName, PrimarySmtpAddress, NEWPrimarySmtpAddress)

Clean up any attribute that has a full path for a user account, most notably “ManagedBy“, “AcceptMessagesOnlyFrom”, “AcceptMessagesOnlyFromDLMembers”, and “AcceptMessagesOnlyFromSendersOrMembers” columns. Leave the semicolons in place and do NOT add quotes even though DisplayName values are being used (which contain spaces). You can use “Find and Replace”, CRTL+H to complete this task. You should note, if there are blank values altogether, you might want to specify a group admin, otherwise whoever creates the new groups in powershell will become the owner by default. This can be important if a group requires approval to add/remove members. (e.g.: contoso.local/User Accounts/USA/FTEmployees/Ryan Jackson; contoso.local/User Accounts/JPN/FTEmployees/Dave Rowe —should become–> Ryan Jackson;Dave Rowe)

Note: if you want to exclude mail-enabled security groups, filter columns, and in “RecipientTypeDetails” select rows with “MailUniversalSecurityGroup” and hit delete key.

Save the csv file as distributiongroups_modified.csv

2) Clean file from export 2 (SMTP Proxy/ALIAS file, distributiongroups-SMTPproxy.csv)

Let’s remove everything except alternate smtp and x500, this includes removing Primary SMTP address. We’ll need to add a few columns and use macros to help us find what we’re looking for.

First let’s add a few columns.

Highlight Column C (TYPE), right-click and copy, then paste into Column E (skip column D). Rename Column E header to “FULLADDRESS”. Go back and highlight Column C, then select ‘Data’ tab in Excel ribbon and select ‘Text to Columns’ button.

Migrate Distribution Groups from Exchange

Select ‘Delimited’, click Next, uncheck everything except ‘Other:’ checkbox and insert colon “:”, then click ‘Finish’. Afterwards, in Column D, give the header name “ALIAS”.

Migrate Distribution Groups from Exchange

We’ll add one more column to help us identify uppercase SMTP. Insert a blank column after Column C, and give header name “PRIMARYCHECK”. The following formula is case sensitive and will help us identify primary SMTP and not smtp – copy the formula in Column D (PRIMARYCHECK) down through all data.

Now that we have all of our columns, we can now filter data and delete what we do not need. To filter data (see filter screenshot above).

• First let’s delete primary SMTP. In Column D (PRIMARYCHECK) select “Primary”, then highlight the data and hit delete key. Now view all results in filter, and sort to remove blank rows.
• Second let’s delete everything EXCEPT smtp and x500 (e.g. x400, EUM). While data is filtered, in Column C (TYPE), uncheck smtp, x500, and blanks – so that everything else is selected – then highlight the data and hit delete key. Now view all results in filter, and sort to remove blank rows.

As long as there is only lowercase “smtp” and “x500” in Column C (TYPE), you are good to go. Now if you scroll to bottom you’ll see all the blank rows. You should highlight these rows from left side, right-click then select delete. Otherwise these rows will error when running scripts.

Save the csv file as distributiongroups-SMTPproxy_modified.csv

 3) Clean file from export 3 (Distribution Groups and Members file, distributiongroups-and-members.csv)

This section will fix nested-groups since they are members. In the export you can verify if any nested-groups exist. If no nested-groups exist, then just copy previous values in new columns.

Create “NEW” values. Insert columns after the following (Group, GroupSMTP), and prefix column header with “NEW” by using following formula. Then copy the formula down through data, so that all data is prefixed with “NEW”. You should now have the following columns (Group, NEWGroup, GroupSMTP, NEWGroupSMTP)

Create “NEW” values for nested groups only, and use previous values for individual members. Copy the entire column from “MemberSMTP” and insert as new column right next to it, then rename column header to “NEWMemberSMTP”. You should now have the following columns (MemberSMTP, NEWMemberSMTP). Now filter data (see previous screenshot) and go to “MemberType” column and select the following values (MailUniversalDistributionGroup, DynamicDistributionGroup, MailUniversalSecurityGroup) and unselect the rest. Now you should only see nested groups in “NEWMemberSMTP” column. Replace the value with the following formula (depending on where first cell is, modify formula to that cell), and copy formula to rest of cells that are displayed. This ensures the nested groups are updated with “NEW”.

Note: if you excluded mail-enabled security groups from distributiongroups_modified.csv, you might consider also removing from this file too. Otherwise you’ll see errors when trying to add members to groups that don’t exist.  Filter columns, and in “GroupType” select rows with “MailUniversalSecurityGroup” and hit delete key.

Save the csv file as distributiongroups-and-members_modified.csv

 

Step 3 – Exchange Online: create “NEW” distribution groups, hide from GAL, and add members

Exchange Online: Create Groups


Let’s create the new distribution groups (and security groups if included) and hide from GAL in Exchange Online. We’ll use one of the files we cleaned up earlier (distributiongroups_modified.csv). Take note, if a group did not have a previous owner (ManagedBy), then whoever creates the distribution group in PowerShell will be the owner by default.


Exchange Online: Add Members to Groups
After we’ve created the distribution groups, we can now add members. We’ll use the file (distributiongroups-and-members_modified.csv) to complete this task.

 

Cutover

Step 4 – Exchange On-Premise: delete distribution groups, and force sync

Exchange On-Premise: Delete groups


You’ll run the following script on your Exchange server that is On-Premise. Although we’ve taken precautions to minimize impact, it’s best to do this (and remaining) steps at off-peak hours (like Friday night). We’ll use the file (distributiongroups_modified.csv) to complete this task.


AAD Connect / AADSync: Force synchronization


In order to speed things up, you’ll want to force delta syncs (a few) on the  AADConnect / AADSync server. This will ensure the old distribution groups (On-Premise) are no longer visible in Exchange Online. You can do this directly on the AADConnect / AADSync server with miisclient.exe, local PowerShell, or use remote PowerShell from a machine on the same network. You must be an administrator on the server and in AADConnect / AADSync local admin group (ADSyncAdmins / FIMSyncAdmins). Make sure to insert your AADConnect / AADSync server name in PowerShell.

Note: For larger environments, AADConnect has a protection mechanism that prevents synchronization when over 500 object-deletions are detected. This will prevent you from deleting the groups if you have over 500. To bypass this protection mechanism, run the following commands on AADConnect.  For more information on this, click here.

When you are done with the entire migration process, make sure you re-enable this protection mechanism afterwards!

 

Step 5 – Exchange Online: rename distribution groups (remove “NEW”), unhide, and add SMTP aliases

Exchange Online: Rename distribution groups and unhide


After you’ve validated the old distribution groups are no longer visible in Exchange Online, we can now unhide the new ones and remove “NEW” from the names. We’ll use the file (distributiongroups_modified.csv) to complete this task.

Exchange Online: Remove NEWPrimarySmtpAddress from -EmailAddresses for all Groups


Since the previous step just moves “NEWPrimarySmtpAddress” into an alternate smtp alias, we can now remove it. We’ll use the file (distributiongroups_modified.csv) to complete this task.


Exchange Online: Add Aliases and LegacyExchangeDN


Last thing to do is add the SMTP, X500, and LegacyExchangeDN aliases in Exchange Online.