Partitioned Tables and ColumnStore

Published On: 2017-04-26By:

ColumnStore indexes are all the rage with data warehouses. They’re fast, they’re new(ish) and they solve all sorts of problems when dealing with massive amounts of data.  However they can cause some issues as well if you aren’t very careful about how you setup your partitions on the ColumnStore index.  This is because, you can’t split a ColumnStore partition once it contains data.

Now, if everything is going according to plan you create your partitions well in advance and there’s no issues.

However, if everything hasn’t gone according to plan and someone forgets to create the partitions and you end up with rows in the final partition, you can’t create any more partitions because you can’t split the partition.

We can see this pretty easily with a quick repro.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO

create partition scheme myScheme1
as partition myRangePF1
all to ([primary])

create table t1
(c1 int)
on myScheme1(c1)

create clustered columnstore index cs on t1 on myScheme1(c1)

insert into t1
(c1)
values
(1), (10), (100), (600)

With our table here we’ve got a partition function with break points at 1, 100, and 1000. We then insert rows into a columnstore index on that table with the values of 1, 10, 100 and 600. If we try and split our partitions using the ALTER PARTITION FUNCTION command with any value between 1 and 1000 we’ll get an error message.

ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);

This is because those partitions contain data. If we try and more any of the partition ranges (1, 100, or 1000) then we’ll get an error because we can’t merge them together either.

So how to be work around this issue? Well the easiest way is to delete the data from the table, then fix the partitions, then put the data back. And frankly, that’s the only option. There’s a variety of ways to do that. The easiest option is probably to partition switch out that partition to an un-partitioned table. Then it’s just a matter of moving that data back into the partition in question.

Now if the partition that you’re currently writing to is the last partition, and you need to keep writing to that partition while this is all happening thing are going to get a little more interesting as you’ll need to take an outage in order to do all this work. There’s really no good solution besides taking an outage to move data around and get it correct in order to resolve this issue.

I wish I had a better answer here, but so far there’s no good solution.

Denny

The post Partitioned Tables and ColumnStore appeared first on SQL Server with Mr. Denny.

Correcting the Outbound Email Address for Office 365 Users When Using AD Connect

Published On: 2017-04-19By:

That’s one hell of a long title isn’t it? I ran across a little issue in our Office 365 account recently. That problem was users having the wrong outbound email address in Office 365. I was trying to figure out why a new account that I had setup in our dcac.co domain kept showing up in Outlook at username@dcassoc.onmicrosoft.com instead of username@dcac.co.

Well the reason that it was showing up this way was because that’s how Office 365 was configured, of course. Now when I create a user (we’ve never had Exchange on prem, so we can’t see any of the Exchange tabs) I setup the account for the user, let it sync to Azure and added the licenses and didn’t think anything of it.

Later I saw the issue. Fixing it is pretty easy, if you know where to look.

Fixing it before the user is in AAD

If you are creating a new user in AD, making it so that the account is created correctly is actually pretty easy.ad1

In the properties of the user, fill out the email address field in the users account. This will tell Azure Active Directory and Office 365 that the users outbound email address should be whatever you put in that field (assuming that it’s a domain that you have control over).

If the users account has already synced to Azure as you discover this problem, this won’t fix it (or at least it didn’t for me).

In the testing that I did, using this field to fix it only works on account creation.  But that’s ok, it’s still a pretty easy fix to fix this after the user has had their account synced to Azure.

Here’s what the user account looks like in Azure after the initial sync has completed with the email field blank.

office1

Fixing it after the user is in AAD

If the user is in AAD, and Office 365’s Exchange properties shows the user as the onmicrosoft.com account, all is not lost. You don’t need to do anything drastic like deleting the user or anything.

In a domain controller, probably the one with AD Connect installed on it, open Active Directory Users and Computers.  Click on View then Advanced Features.

Now go find the problem user and open their account.  You’ll see a new tab called “Attribute Editor”, select it.  Scroll down until you find the proxyAddresses field.  It’s probably listed as “<not set>”. That’s fine, we’re going to fix this.  This proxyAddresses field is how we tell Azure and Office 365 (and Exchange) what the email addresses are that the user can receive email as, and what email address is their outbound email address.Setting outbound email address in Active Directory

If we edit that we’ll get a dialog that allows use to add items to the list. Now don’t just add in email addresses, that isn’t going to work.  You can list all sorts of items in there.  Specifically we want to add in our SMTP addresses.  We do this in the format of smtp:username@domain.com.  In the case of our test user it’ll be testuser@dcac.co.  Now we can put in as many email addresses as we need them to get email at.  So how do we set the outbound email address?  We make the “smtp” part uppercase.

If you are a company that changes users email addresses when then change their name (they get married or divorced for example) then you’ll want to keep their old email address in there.  So you just set it as “smtp:oldname@domain.com” in all lowercase.

In the screenshot we’ve two SMTP addresses, username@dcac.co with the upper case SMTP and oldname@dcac.co with the lowercase SMTP.  This allows the user to received email on both username@dcac.co and oldname@dcac.co while sending any outbound emails as username@dcac.co.

office2If I go look at the list of accounts which the user has, you’ll now see that the SMTP in upper case is the bolded one (that’s how Office 365 shows which email is the outbound).  And if we look at the user in the mailbox list we’ll now see that the email address has changed from username@dcassoc.onmicrosoft.com to username@dcac.co.

office3

A fairly easy change, and no impact to the user, other than the impact that we wanted, which is that the users email address is now correct and matches the companies branding. It’ll take a while at this point for the users email address to be corrected in everyone’s Global Address List (GAL). It’ll take a little time, the GAL doesn’t update that often, but when it gets around to updating the users will all get the update.

Using PowerShell to fix things in bulk

This change can be made in PowerShell as well. In this case I’ve setup the PowerShell to only for against a single user, but you can setup the -Filter parameter anyway you need to in order to get things working the way you need.

$users = Get-ADUser -Filter {UserPrincipalName -eq 'testuser@dcac.co'} -Properties SamAccountName, ProxyAddresses, UserPrincipalName
$users | Foreach {
Set-ADUser -Identity $_.SamAccountName -Add @{Proxyaddresses="SMTP:"+$_.UserPrincipalName} -whatif
}

I’ve left the -whatif in there so you can see what it’ll do before running it. You’ll want to be careful running PowerShell against your Active Directory domain as you can really screw things up if you aren’t careful. Be warned, if you already have values in this ProxyAddresses field, this will simply append to them. If there’s a default in there you’ll now have two and Office 365 will get cranky. So you may need more logic and checking in your script. But I’ll leave that to you.

If you are planning a cloud migration, or you have already started one, be sure to contact DCAC.  We are one of the premier experts in cloud migrations, frequently training other consulting companies on how to perform cloud migrations.

Denny

The post Correcting the Outbound Email Address for Office 365 Users When Using AD Connect appeared first on SQL Server with Mr. Denny.

It’s time to grow DCAC again, this time adding Monica Rathbun to the company

Published On: 2017-04-17By:

It’s been an interesting ride running DCAC and growing the company so that we can handle more projects and more clients at once.  I’m pleased to report that we’re growing the company again.  We are adding Monica Rathbun (b | t) to the company as our newest consultant.

Monica brings 16 years of experience working as a SQL Server DBA to DCAC, so needless to say, she’s a great fit.

Monica has been recognized by Microsoft as a Microsoft MVP for Data Platform (the third one at DCAC) and has spoken at a variety of community events including SQL Saturday’s all around the US.  She also runs her local SQL Server user group and is a Regional Mentor for the PASS organization.

We’re looking forward to having Monica on the team, and doing some great work together with her.

You can read Monica’s announcement on her blog here.  Like everyone else’s blogs, we’ll be syndicating Monica’s blog here so that you can see all the great posts that she’s got with the rest of the teams.

Denny

ZyXEL with client VPN and Azure Site to Site VPN Configuraton

Published On: 2017-04-12By:

I’m going to start out by saying that I’m not a networking guy, at all. But that said sometimes I have to pretend to be a networking guy. In our lab we have a decent set of machines. We also have our Azure environment that’s up and running which is where our websites, etc. are all running out of. To keep things simple we have our VPN setup at the CoLo as that’s where we need to go most of the time when we’re VPNed in since that’s where we can spin up servers for free.

Ever since we setup the CoLo we have had this annoying issue of having to RDP to a VM in the CoLo before being able to talk to Azure. It isn’t a major thing, but it’s gotten annoying. Eventually I got sick enough of it to try and fix it.

4 hours later I had a solution.

Now we have a ZyWall 110 but this should be similar for most of their hardware. The first thing I needed to do what add another address object. That address object needs to be able to cover all the networks inside the network as well as the subnet that you’re using to give IPs to your VPN clients. Our CoLo is 172.30.0.0/21 and we’re using 172.30.250.0/24 for our VPN endpoints. Because I’m lazy it setup as 172.30.0.0/16.  This is going to be needed later on.  I’ve got another address object named “Azure-US-West-Production” that defines our IP Subnet for our Azure Virtual Network.

subnet

Once that’s created you’ll need to modify the VPN Connection to use the new address object as the local policy.

vpn_policy

When this was done the VPN dropped, so I had to tell the VPN to reconnect (or allow it to reconnect by itself).

Once that’s done you’ll need to add a route to direct the network traffic to where it needs to go.  For the route, you’ll need to setup the Incoming as “Tunnel”, then select the tunnel that your users VPN through (we use L2TP over IPsec and our tunnel is named ClientVPN).  For the source address I left that as “any”. For the Destination I selected the subnet for the Azure vNet that I want to allow people to connect to.  In the Next-Hop section Change the type to VPN Tunnel and select the correct VPN Tunnel that connects to the correct vNet. In my case it’s called Azure-USWest-Production.

policy

We have several vNets in Azure (4 to be specific) and we want to be able to access all of them directly, so I had to repeat this for each one of them.

Now users that are VPNed in are able to access the VMs in Azure directly from their desktops and laptops without having to RDP to a VM in the CoLo.

Denny

The post ZyXEL with client VPN and Azure Site to Site VPN Configuraton appeared first on SQL Server with Mr. Denny.

1 2 3 310

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.