Category Archives: Servers

So a SQL Server Transaction Log ate Your Free Space.

This weekend I came across an unusual circumstance that I thought I would share with many of those part-time SQL Server admins. I currently maintain more than a couple of SQL servers. Because SQL Server has a good maintenance program I don’t spend the money on a third party software for backup. Instead I setup the maintenance plan to create a backup every 6 hours and then push the file to a network share. For some reason or another, the network share came detached and the backups filled up the local data volume. This effectively shutdown the server. I cleared up the space, restored the mapping, and didn’t think much more about the problem. I noticed that I was getting a backup file from each database but failed to pay attention to the transaction log.
This is where my new problem that consumed my weekend started. Friday night at 7pm I got another phone call about the SQL server being out of disk space again. Again I had no space on the volume, but the space wasn’t consumed by the backups. Instead, the transaction log which is normally a couple of gigs in size had ballooned to 100GB in size. I had attached an external USB drive to push a backup of the transaction log to and tried to shrink the transaction log from the SQL Server Manager. This only give me about 3 GB of storage back, but they were quickly consumed as soon as the end users started using in their application again. I then kicked off a backup of the database and then transaction log. I now had 99% of the space free in the transaction log file, but still could not shrink the database. I had fought and fought with the database trying to get that free space back.

Finally at about 2am, running out of ideas, I deleted the transaction log file and started up the database again which effectively locked the database for a lot of people. Having migrated the database before, and knowing that a simple restore of the database could easily fix the problem, I took the most recent backup which was actually taken after end users were cut off from the server and restored the database. After the restore, I again had the same problem of a database with a 100 GB transaction log file. This time however, I for some reason threw caution to the wind and performed yet another shrink to the transaction log file. Finally, I freed up 75% of the space on the volume which allowed everything to return to normal.
Why I had to backup and restore the database before I could perform an effective shrink of the database, I do not know. If this has happened to other people, I would like to know the reason behind this.
My corrective actions include scripting a compression command on the backups to reduce their size.  I also plan on creating a trigger to notify me by email when the disk space is low, 20% is one of my favorite guide lines as far as that is concerned. I am considering running a network mapping script to reattach the volume of the server before the files are moved over so that the network volume that I monitor won’t be so easily missed with the other backup files that I file on the backup storage volume.  I don’t like using compression because of how having to decompress a file to restore it effectively adds to the lengthy process of getting the database back to working order.  Then again, having a few extra copies of the database around is also handy.

I am open to other input. I thought I would just share my wonderful late night experience with others in hope to get some improvements or perhaps help out other admins who might run into the same problem.

Changing Work Item “Created By” in TFS

I recently tried to change work item “created by” in TFS. TFS blocks you from doing this, so I tried the backdoor method: direct database updates. However, I found that just modifying the “Created By” column in WorkItemsLatest causes the work items to not allow themselves to be edited afterwards. The trick is to also update the “Created By” column in WorkItemsAre and WorkItemsWere. As long as there is agreement for the item in all three tables, this will work just fine.


Using Windows Home Server as a server at home

WI spent some time with the eval version of it. I’ll say that I think that it is a good product. What people talk about doing with it, in terms of storage and multimedia stuff, it’s there and it looks pretty cool. But for me, I really wanted an email server and a Web/FTP server at home. I have this already running FreeBSD, but since I prefer developing in .Net lately, having a Windows server here would get me a lot of motivation in terms of writing more personal applications.

Sadly, Windows Home Server is not well suited for my needs. For one thing, its user management is designed for consumers to “get”, which means that I don’t want to be using it for FTP purposes, and it won’t be a good basis for an email server. Even worse, like Windows Small Business Server, WHS uses its own wizards to manage things, and using the standard tools (which I still included) will break things. Frankly, I think that this is a bad decision. Either make the standard tools do what you need them to do, or remove them. But to leave them there and simply warn me is asking for trouble. This is one of my biggest grievances with SBS, and by itself, it rules out WHS as being suitable for my purposes. I simply don’t want to risk “breaking” something while working the way I am used to working. Because of the tools modifications, I did not feel safe trying to install DHCP server, DNS server, or any of the other services that my FreeBSD server provides. If I don’t feel comfortable with it, I am not going to use it. Finally, I hate that it is based on Server 2003, and not 2008. 2003 is good, but 2008 is GREAT.

WHS may be a good product, but if you are looking for a lightweight personal server (not just a file/media server, but a personal Internet server), WHS is probably not right for you.


Misery: Team Foundation Server Dual-Server Install

Last week I wrapped up the installation of our new Team Foundation Server 2008 setup. I had previously installed it in a single-server archtecture, but we decided to go to the dual-server configuration. Why? Because I am trying to consolidate everything by purpose into different VMs on our new app server. This means that I have 1 VM for SQL server, another one for SharePoint and other collaborative applications, and so on. When I went to install TFS into this, it was an incredible headache.

For one thing, TFS requires 32 bit Windows; since this is TFS 2008, and because so many of the 2007 and 2008 Microsoft server products (Exchange, for example) require 64 bit Windows, I think that this is going to be a real problem, especially for small shops. So now I have a 32 bit Windows 2008 VM just for TFS.

The installation took forever. I went through all of the checklists, but there was always something wrong. Oddly enough, the #1 offender was SQL Server Reporting Services. The TFS installer is supposed to configure the unconfigured SSRS install, but the default install in broken. I had to delete the encryption keys (for whatever reason) to make it work, which could bite me in the rear down the road.

I then had further problems with installing the SharePoint extensions on my 64 bit SharePoint install. Apparently, the TFS disk doesn’t ship with a 64 bit version of this, and the error message doesn’t say anything about it, just throws a dumb, useless error message. Luckily, they made a 64 bit version a few months ago.

Over all, I must say that this install was one of the most miserable installations that I ahve ever done of a Microsoft product. I know that TFS is supposed to be “enterprise class”, but many of their other “enterprise class” products are smooth sailing. I can’t see what makes TFS so special that it can’t be an easier installation. If Microsoft wants more shops to use it, they need to make it easy to deploy. No one can evaluate it in its current state, it requires too much work.


More Vista-isms in Windows Server 2008

I keep finding more and more places in Windows Server 2008 where I get the distinct impression that someone desparately needs to do s/Vista/Windows Server 2008/ on it (regex for “replace ‘Vista’ with ‘Windows Server 2008′”). Everywhere I look, some part of the system is referring to itself as “Vista”. These are not items being driven by the Windows version number, this is documentation and so on. It’s just plain sloppy, and shame on Microsoft for releasing it in this condition.


Implementing VLAN trunking


  • Introduction
  • Cisco switch configurations
  • Cisco router configurations
  • Windows configuration with Intel Pro Series adapters

In my last article “Introduction to VLAN trunking”, I wetted your appetite for a hot new technology that is revolutionizing the way network topology are being designed and interconnected. In this piece, I will show you how to actually implement this new technology in the three most common types of equipment you will come across. The three types of equipment are Cisco switches, routers, and Servers or Workstations running the Windows operating system. The only prerequisite for this article is a basic working knowledge of Switches, Routers, and PCs running Windows for their respective sections. Click here for a network diagram of the lab environment created in this article. Note that the examples I use are on based on the 802.1q standard.

Cisco switch configurations
Cisco switches primarily come in two flavors, CatOS (Catalyst OS) and IOS (Internetworking OS). Although Cisco is trying to migrate almost everything to the IOS type operating system on their equipment, there is still a large install base for CatOS switches. Cisco’s flag ship 6500 series switch can actually run CatOS or IOS, but most people I know run CatOS on the 6500s. Smaller switches like the 2950 and the 3550 all run IOS. Then there is the odd ball 2948g-L3 that really is more of a router than a switch (2948 without the “L3” is a normal IOS switch) and you should refer to the next section on routers for it’s configuration.

Note: In many ways, I personally love the CatOS over IOS for it’s UI’s (User Interface) superior method of entering system configuration. For example, if you ever need to apply a common configuration to 48 Ethernet ports on module 4, you simply need to apply a command to “4/1-48”. On the IOS UI, you would need to enter each interface for all 48 ports and apply 96 individual commands vs. one command on the CatOS! Viewing the configuration on IOS is equally bloated.

Here is a breakdown of trunking support for the various Cisco switches

2900 Series (on some IOS versions) 2980 (Same IOS image as the 4000)
2948 (Non L3) 4000 Series
2950 Series 5000 and 5500 Series
3548 6000 and 6500 Series
3550 Series
6500 running IOS

To set up the CatOS or IOS on Cisco Switches, the port that needs to trunked must be configured for the right kind of VLAN trunking (Note that not every module and interface on a switch support trunking and will give you an error message if you try to set it for trunking, this may require you look up the port capabilities for each port).

Here is the configuration guide for both IOS and CatOS.

Configuring and locking down IOS switches:

IOS Command Description
Enable Switch to enable mode
Configure Terminal Enter global configuration mode
Interface FastEthernet0/1 Entering interface configuration for port 0/1. This is where you pick the port you want to trunk.
Switchport mode trunk Set port to trunking mode.
Switchport trunk encapsulation dot1q Set trunk type to 802.1q. If your switch only supports either ISL or 802.1q, this command does not exist because there is nothing to specify. This command only works when you can choose between the two.
Switchport trunk allow vlan 10-15,20 Allow only VLANs 10 through 15 and VLAN 20. It is important that you restrict the VLANs to only the ones you need for security best practices.
Exit Exit interface
Exit Exit global configuration
Write memory Commit changes to NVRAM

Locking down CatOS for security:

CatOS Command Description
Enable Switch to enable mode
Clear trunk 1/1-2 1-1005
Clear trunk 2/1-2 1-1005
Clear trunk 3/1-24 1-1005
…fill in the pieces…
Clear trunk 12/1-24 1-1005
Set trunk 1/1-2 off
Set trunk 2/1-2 off
Set trunk 3/1-24 off
Set trunk 4/1-24 off
…fill in the pieces…
Set trunk 9/1-24 off
This is an example of how to lock down a Cisco 6500 switch. First it clears VLANs from all ports on a 6500 switch, and then it explicitly disables trunking from every single port. Whether you intend to use trunking on your CatOS switch or not, you would be very wise to implement this lock down on all of your CatOS switches. Otherwise, a hacker can bypass all Layer 3 (firewall) security by simply hopping VLANs. I included this section before the “Configuring CatOS” section because the lockdown needs to be done before any custom configuration is entered.

Although this section is not really mandatory for trunking to function, I felt irresponsible not to include this layer 2 security lockdown procedure. Although the CatOS switch has a far more streamlined UI compared to the IOS switches, it is notoriously promiscuous with it’s default settings on VLAN trunking. The trunking auto-negotiation is equally alarming on both the IOS and CatOS switches, which if left default will automatically connect switches as fully enabled and wide open. You would be shocked to see the sloppy Layer 2 security on most networks. If left unchecked, you are not only opened to malicious hacks, but someone could accidentally plug in a Cisco switch with a VTP engine and accidentally nuke your network by changing your VLAN configuration.

Configuring CatOS switches:

CatOS Command Description
Enable Switch to enable mode
Set trunk 1/1 on dot1q 10-15,20 The “on” switch enables trunking on this port. “Dot1q” sets the port to 802.1q mode. “10-15,20” enables VLAN 10-15 and 20 to be supported on this trunking interface.

You may find it funny that it was so much work to lock down your switch while it only took one command to enable trunking. If you didn’t bother to follow the lockdown procedure shown above, specifying the “10-15, 20” VLAN IDs are useless because it simply adds them to the existing 1-1005 pool which remains wide open. This behavior of the CatOS is very annoying and insecure by default. The IOS switches on the other hand only permit the VLANs you enter last, which also has it’s user friendliness downside. On an IOS switch, if you enter “10-15,20” with your “allow VLAN” statement, it nullifies any other allowed VLAN out side of 10-15 and 20. The big plus to this is default security.

Cisco router configurations
Cisco router configuration for trunking is fundamentally different from Cisco Switch configuration. A router encapsulates traffic to be carried on the switch infrastructure and behaves as a multi-home node on the network just like a Server, Workstation, or Firewall. A switch performs as the infrastructure to carry traffic for VLANs (for those that are allowed) on the Layer 2 infrastructure as the VLAN traffic director where as the router performs a higher layer function as a network gateway that can route Layer 3 traffic. You can basically configure a router with number of desired virtual interfaces (AKA sub-interface) from a single interface and designate the VLAN you want those interfaces to be switched to. The switch determines where the traffic from that router’s virtual interface will wind up based on the VLAN ID portion of the 802.1q tag that was inserted in to the Ethernet frame header by the router.

Configuring Cisco Routers:

IOS Command Description
Enable Switch to enable mode
Configure terminal Switch to global configuration mode
Interface FastEthernet0/0.1 Creates first sub-interface for FastEthernet0/0
Encapsulation dot1q 10 Injects 802.1q tag with VLAN ID 10 into every frame coming from first sub-interface.
IP address Defines IP/mask for this first sub-interface
Exit Exits first sub-interface
Interface FastEthernet0/0.2 Creates second sub-interface for FastEthernet0/0
Encapsulation dot1q 11 Injects 802.1q tag with VLAN ID 11 into every frame coming from second sub-interface.
IP address Defines IP/mask for this second sub-interface
Exit Exits second sub-interface
Exit Exit global config
Write memory Commits changes to NVRAM

You can continue to add any number of sub-interfaces you need. Once FastEthernet0/0 is connected to a switched port configured for 802.1q trunking as shown in the above switch examples, all the sub-interfaces of FastEthernet0/0 becomes a routable node (can be default gateway) on the subnets that correspond to their VLAN.

Windows configuration with Intel Pro Series adapters
Conceptually, trunking a Windows workstation or server to a switch is the same a trunking a router to a switch. The only difference is the procedure, and a much easier one I might add. The ubiquitous Intel Pro Series adapters provide a simple to use graphical tool called PROSet that any one can learn within a minute or even someone who is just winging it. Note that the same Intel adapters with the ANS drivers can provide similar capabilities on Linux. You can get more information on Linux here from Intel.

To get started, simply invoke the Intel PROSet or PROSet II utility (assuming PROSet is installed). This can be done by simply double clicking the PROSet icon in the system tray on the lower right hand corner of the desktop. The following utility should come up.

Next we must add a VLAN interface. Simply right click on Intel adapter with the PCI Card icon and click “Add VLAN”. Note in the following screen capture, the virtual interface for VLAN 100 is already there and we are adding an additional one.

The “Add New VLAN” window comes up. Enter the VLAN ID you want this interface to trunk in to in the ID field, then give it a name that describes the VLAN function. In this case, we will be adding VLAN 69 labeled the Wireless LAB.

Once this is completed and you click “OK”, simply click “Apply” and “OK” on the PROSet window to commit the changes and get out of the PROSet utility. The next step is to configure the virtual interfaces. Simply open up the “Network Connections” window and begin configuring the virtual interface as you would any other physical interface. Note that the interface names already correspond to the names of the VLAN interfaces you added. However, auto-naming only works in Windows XP. Windows 2000 just gives them generic names, so you must add one interface at a time and rename the interface under “Network Connections” before you add another VLAN interface. If you don’t do that, it is impossible to tell which Interface goes to which VLAN without some tedious trial and error. One other very important thing to note, the physical interface it self “Local Area Connection” is not bound to anything except for the “Intel Advance Network Services Protocol”. It is not used for anything else and only serves as a host for all of the virtual interfaces and it does not have it’s own IP address or VLAN.

Just remember that only your primary interface is registered with internal Dynamic DNS and WINS and is the only interface that can have a default gateway. This is the same as when you have multiple physical network interfaces. In both cases whether there are multiple physical or virtual interfaces, you must set manual routes to take advantage of the other non-primary interfaces. This is why in the TCP/IP configuration window above, I deliberately left the Default gateway and DNS settings blank because those settings went on to the VLAN 100 interface. If you put a default gateway on the VLAN 69 interface, it will take over and the default gateway for the VLAN 100 interface will disappear. All the default gateway means is the route for network with mask (which really just means any IP destination) will route to the default gateway. You can easily tell this with the “route print” command.

From this point on, you may add as many VLANs as you need using the example above. The only other thing you should be aware of when dealing with these VLAN Interfaces is that you should not “Disable or Enable” them from the “Network Connections” folder, and instead you should deal with the Interface from the PROSet tool. Doing so will cause you to encounter some strange behaviors.

An introduction to VLAN Trunking


  • Introduction
  • Applications of VLAN Trunking
  • VLAN encapsulation types
  • Trunking requirements

There are many Network Devices in the Data Center that require multi-homing (multiple network adapters) to tie in to multiple network segments.  As the number of those systems increase, it becomes more and more difficult to provide the network infrastructure (due to the sheer number of Ethernet connections that need to be provided) from the perspective of cost, space, and wire management.  A technology called VLAN (Virtual LAN broadcast domains logically segmented on an Ethernet switch) trunking that was once primarily the domain of network switches has now trickled down to the rest of the Data Center to address these issues.  Now it is possible for these multi-homing devices to be multi-homing in function without the need for multiple physical network adapters and the additional infrastructure associated with them.  VLAN trunking allows a single network adapter to behave as “n” number of virtual network adapters, where ”n” has a theoretical upper limit of 4096 but is typically limited to 1000 VLAN network segments.  In the case where a single gigabit Ethernet adapter is trunked in place of using multiple FastEthernet adapters, higher performance at a lower cost while increasing flexibility can be achieved.  This really is the best of all worlds.  In this article, I will give you an overview of VLAN trunking, how it works what it is used for.

Applications of VLAN Trunking:
Here are some common examples of Network Devices that benefit from VLAN trunking:

  • Routers
  • Firewalls (software or hardware)
  • Transparent proxy servers
  • VMWare hosts
  • Wireless Access Points

Routers can become infinitely more useful once they are trunked in to the enterprise switch infrastructure.  Once trunked, they become omnipresent and can provide routing services to any subnet in any corner of the enterprise network.  This is in essence what a routing module in a high-end core or distribution L3 (Layer 3) switch provides.  This technique can be a poor man’s substitute for a high-end routing module on a switch, or it can complement the high-end L3 switch by providing additional isolated routed zones for test labs, guest networks, and any other network segment that requires isolation.

Firewalls are another device that can greatly benefit from VLAN trunking now that all the big players like Cisco, Nokia/CheckPoint, and NetScreen support it.  In today’s high stakes environment where security concerns are ever increasing, the more firewall zones (subnets connected by separate virtual or physical network adapters) a firewall provides the better.  With the exception of NetScreen firewalls, firewalls can only block potentially hazardous traffic between zones and not traffic within the same zone.  Therefore, the more you separate devices like routers and servers by logical function and security level, the better off you are since you can limit unnecessary traffic and mitigate many security threats.  Since VLAN trunking provides a nearly unlimited number of virtual network connections at a lower cost and higher performance, it is the perfect addition to firewalls.  You can read more on this in:

Understand how to design a secure firewall policy

Increase firewall protection with a better network topology

Transparent proxy servers such as a Windows server running Microsoft ISA or a Linux server running Squid can now be built with a single gigabit Ethernet adapter costing as little as $40.  A traditional proxy server can be built with a single network connection, but a transparent proxy server usually cannot.  Since transparent proxy servers can be implemented with zero client deployment or SOCKS compliance; they are an extremely attractive new technology.  Trunking just makes it that much simpler and cheaper to implement.

VMWare hosts are servers that host multiple virtual servers for the purpose of server virtualization or system modeling for laboratory testing and research.  Although VMWare already provides the ability to have multiple VLANs within the VMWare host, it’s ability to connect those VLANs to physical VLANs is limited to the number of network adapters on the VMWare host.  A VMWare host can provide up to 3 network connections to each virtual machine.  Since applications cannot tell the difference between a virtual adapter and a physical one, a VMWare host armed with a trunked interface is significantly more flexible and simpler to manage.

One of the hottest new applications of VLAN trunking is wireless networking.  The new Cisco AP 1200 for example can behave as 16 virtual Wireless LAN infrastructures.  Some VLANs can be used for low security guest Internet access, others for minimum security enterprise users, and administrators can be put on a high security VLAN with enhanced firewall permissions.  All this can be achieved using a single Wi-Fi infrastructure to emulate up to 16 Wi-Fi infrastructures.  The Cisco AP 1200 does this by assigning each of the 16 VLANs it’s own Wi-Fi SSID, so when you look at it from NetSumbler (free wireless sniffer), you will think you are looking at up to 16 different wireless networks.  Those 16 VLANs are then trunked over the AP 1200’s FastEthernet port.  This offers wireless nirvana in Wireless LAN capabilities.

VLAN encapsulation types:

There are several types of VLAN encapsulation.  The two most common types are Cisco’s proprietary ISL (Inter Switch Link) and the IEEE 802.1q specification.  ISL is an older standard that Cisco was using to connect it’s switches and routers, but now that 802.1q is ratified, all of the newer Cisco gear either support both ISL and 802.1q or only 802.1q.  Older Cisco equipment may only support ISL trunking, so you must look up the individual specifications of your gear before attempting to connect them.

The 802.1q standard works by injecting a 32 bit VLAN tag into the Ethernet frame of all network traffic in which 12 of those bits define the VLAN ID.  The VLAN ID simply declares what VLAN the Ethernet frame belongs to, and the switch uses that ID to sort out and place the frames in their proper VLANs.  Once a frame reaches the end of the line or hits a non-trunked port, the VLAN tag is stripped from the frame because it no longer needs it.  This also means that if you attempt to trunk a host to a non-trunked port, it obviously will not work because that non-trunked port will strip the VLAN tags upon entry.  Note that there are very serious security implications of using VLAN technology; I will elaborate on that in a future article on VLAN Layer 2 security.

Given that a VLAN tag must be inserted into each and every Ethernet frame, it does mean that there is a little overhead in terms of slightly increased frame sizes and some CPU over head required to inject the tags.  Because of this, separate physical network adapters will always perform better than virtual network adapters on a single adapter of the same speed.  But remember, this performance deficiency is quickly reversed if a single gigabit Ethernet adapter is used in place of multiple FastEthernet adapters.  Given all the rewards of VLAN trunking, the small overhead is more than justified.

Trunking requirements:
VLAN Trunking requires that the network switch, the network adapter, and the drivers for the operating system all support VLAN tagging in order for them to trunk.  Almost any enterprise grade switch made by Cisco, Extreme, Foundry, and others support 802.1q.  A few examples of this on the smaller scale are the Cisco’s 2950 series and Netgear’s FSM726.   Most high end client adapters support VLAN trunking, but one of the most common ones you will find is the Intel Pro/100 and Pro/1000 adapters because it is included on almost every server manufacture’s motherboard.  For those without an integrated Intel adapter, a separate Pro/1000 PCI card can be bought for as little $40.  Drivers support on the Intel adapters are excellent and covers almost everything from BSD to Linux to Windows client and server operating systems.  My follow up article on how to actually implement VLAN trunking will focus on Cisco and Intel equipment.