SQL Server imports, multiple columns with the same name

I needed to set up a recurring import of CSV data into SQL Server using BIDS. The source data contained multiple columns with the same name. SQL Server does not like this situation – it errors out with “There is more than one data source column with the name ‘First Name'”. One option is to change the column names manually in the source data, but that’s not a very good solution for a recurring import.

After a little searching I found that you can rename the source columns when you set up your data source. Change each offending name one time when you set up the import, then the error disappears.

change column name on import

Windows 2012 DHCP – Migration and Fault Tolerance

A long overdue feature in Windows Server 2012 is the ability to have a truly distributed DHCP infrastructure. You used to have to use the 80/20 split scope configuration which was at best administratively burdensome, and at worst unmanageable for a large enterprise. Alternatively, you’d have to run a Windows cluster for DHCP – who actually wants to do that? Windows Server 2012 now offers distributed DHCP failover.

I’ll start by talking about migrating your DHCP configuration from Windows 2008 to Windows 2012. First, you obviously have to install Windows 2012 and add the DHCP role. Microsoft has made it ridiculously easy to export and import the configuration with PowerShell – you can complete the process with 2 commands.

Open a PowerShell window on your 2012 server and run
Export-DhcpServer -ComputerName your2008DHCPserver.foo.com -Leases -File C:\path\to\exportfilename.xml -verbose

export 2008 DHCP configuration
Exporting Windows 2008 DHCP configuration

A bit of advice from my lab environment – if you have a clock mismatch between domain controllers, you won’t be able to run these remote Powershell commands. If you’re getting strange errors in your lab, check the clocks on your DCs. My 2012 DC was many hours behind my 2008 DC and I couldn’t run any commands. I fixed the clock and the problem disappeared.

When your export is complete, run the import.
Import-Dhcpserver -Leases -File C:\path\to\exportfilename.xml -BackupPath C:\windows\temp\ -verbose

import dhcp configuration
Importing DHCP configuration from a Windows 2008 export

 

dhcp import complete
Import of 2008 DHCP configuration completed.

 

The 2008 imported scopes on the 2012 DHCP server.
The 2008 imported scopes on the 2012 DHCP server.

 

 

Moving on to configuring the failover –

Configuring failover on a DHCP scope
Configuring failover on a DHCP scope

Not much to see here, but if you don’t have any scopes visible you aren’t going to be able to go much farther. One possible solution to the problem is in this post.

First screen of the failover wizard
First screen of the failover wizard

Specify another Windows 2012 server with the DHCP role already installed.

Specifying a failover partner in the failover wizard
Specifying a failover partner in the failover wizard

 

The failover relationship must be named. Maximum client lead time refers to amount of time the surviving DHCP host will wait before assuming complete control over the scopes. For mode, you can pick either load balanced or hot standby – basically an active-active or active-passive relationship.

Defining the failover relationship
Defining the failover relationship

 

Finish will kick off the process.

Last screen of the failover wizard
Last screen of the failover wizard
Failover progresses to completion
Failover progresses to completion

The scope and leases are now visible from the secondary DHCP server.

The failover scope as seen on the secondary DHCP server
The failover scope as seen on the secondary DHCP server

I will now shut down the primary DHCP host and leave the secondary host online. You can see my Linux guest with the DHCP address of 192.168.237.201.  After shutting down primary DHCP, I reboot this Linux guest.

Client IP prior to reboot
Client IP prior to reboot

The guest comes back with the same .201 IP address, this time served by the secondary DHCP host.

Client IP after reboot - DHCP served by failover host.
Client IP after reboot – DHCP served by failover host.

Now I boot a second Linux guest and it draws a .220 IP address from the secondary DHCP host.

A second client VM booted with primary DHCP server disabled
A second client VM booted with primary DHCP server disabled

Here are the leases as seen from the secondary DHCP host. Both of the Linux hostnames are the same because I didn’t bother customizing them in the guest. However, the MAC addresses are different and each VM is drawing the correct IP.

DHCP leases on failover host
DHCP leases on failover host

I now bring the primary DHCP host back up. Immediately after boot, you can see that the lease for the .220 IP is not listed.

DHCP leases on DC2 after reconnected
DHCP leases on DC2 after powerup

We could wait for the hosts to do this themselves, but we’re impatient and force a replication.

Forcing scope replication to pull leases from failover host
Forcing scope replication to pull leases from failover host

Now the .220 lease is visible from the primary host.

DHCP leases on primary host
DHCP leases on primary host

Now we take down the secondary DHCP host, leaving only the primary online. Reboot both of the Linux guests and they both correctly draw the .201 and .220 IP address.

DHCP IP on client 1 after reboot
DHCP IP on client 1 after reboot
Client 2 rebooted
DHCP IP on client 2 after reboot

Once you have this set up you can configure your network infrastructure with multiple DHCP addresses. Vendors have various names, but Cisco refers to this as the ip helper address. DHCP requests will always be sent to both DHCP hosts, but each scope is active on one host at a time. One DHCP request will be acknowledged and the other will be ignored.

Windows 2012 DHCP Failover – Cannot enable

I tried setting up the new Windows 2012 DHCP failover and got it working, but I couldn’t get some of my scopes that I’d imported from 2008 to 2012 working. If I built the same scope (or what I THOUGHT was the same scope) from scratch in 2012, I could configure failover.

I posted in the Microsoft forums but didn’t find resolution. However, one month later somebody posted the solution on my thread. My 2008 scopes happened to have both DHCP and BOOTP enabled, and you can’t enable failover on a scope set to both DHCP and BOOTP.

Configuring View security servers

I recently spent a very long time troubleshooting a View security server, documented in this post. As part of my sanity checking, I checked and rechecked all of the necessary settings for a security server paired up with a connection server. I didn’t find any helpfile or blog post that clearly spelled out what I needed to have configured, so I’m putting up a quick post.

This is the View connection broker that’s paired with the security server. The external URL and the PCoIP external URL are the private IPs of the connection broker itself. The security server in the DMZ must have access to these IPs. I won’t rewrite the firewall rules required, but check out KB1027217 for a detailed list of ports. The IP here is the IP that the security server sees for the connection broker. If you’re doing some kind of NAT from your DMZ to the inside, you’re going to have to key in the IP address on the DMZ side of the NAT.

View Connection Server

This is the paired security server. Here we use public IP addresses. This IP must be NATed externally to the private IP of your security server… unless of course you use public IP addressing inside your DMZ (yikes).

View Security Server

 

 

 

 

 

 

 

 

It’s not easy to test your changes externally. You could work remotely with multiple computers, one on the VPN to make your configuration changes and another testing connectivity from the public internet. That’s not always a feasible option for a consultant. You will typically not have a good way to test externally while inside the client’s network. I do a lot of work in education and many schools are built like bunkers – no cellular signal at all, so a MiFi or tethering is out as well. One easy way to test communication from the DMZ to the inside is to put a temporary VM in your DMZ segment. Give it an IP in the same subnet as your security server, and install the View client. In View, configure the security server to use the private DMZ IP instead of the public IP for the external URL and the PCoIP external URL. You can then test from your temporary VM. Once you get it working, change the external URL and the PCoIP external URL back to the public IP. Then you can get the external NAT and external firewall rules working – this is not a complex configuration and won’t require much trial and error.