New Year Technology Maintenance

With the start of a new year, this seems like a good time to do some maintenance and updates on the computers and related technology items and devices in your home. If possible, you should also do this in the homes of your immediate relatives and friends.

New Year Technology Maintenance

Many devices do a pretty decent job of automatically pulling down and installing updates by themselves (or they can be configured to do so). Despite this, you probably have a number of devices that need some attention and help.

These might include your various computers, wireless router, NAS, printers, home automation and AV gear, etc..

Windows Computers

For Windows-based computers that are owned/managed by individuals, you should manually check for updates by clicking the “Check for Updates” button. You should also make sure that the “Give me updates for other Microsoft products when I update Windows” option is enabled, under Advanced options. This will give you updates for things like Microsoft Office. This is important to do even if the machine is supposed to automatically check for updates, since many people (especially non-computer savvy people) don’t ever manually check for updates.  Many people do not leave their machines running for extended periods, so it is very common to find them woefully out of date.


Figure 1: Windows Update Dialog


Figure 2: Windows Update Advanced Options

 Windows Disk Cleanup

It is also a good idea to run Windows Disk Cleanup, and click on the “Clean up system files” button. This will give you the option of cleaning up Windows Update files and even removing previous versions of Windows (in Windows 10), meaning previous semi-annual update versions. Removing those old versions can often free up 20-40GB of disk space, although you won’t be able to rollback to the old versions anymore. Be warned that running disk cleanup can take quite a bit of time, even with a fast SSD.


Figure 3: Disk Cleanup Default Dialog


Figure 4: Disk Cleanup System Files Dialog

After getting Windows updated and running Disk Cleanup, you should make an effort to see if there are any BIOS/firmware or driver updates for each device. Most large hardware vendors have utilities that you can use to automatically look for any updates, and then download and install the updates. You may have to go to the vendor’s support site to download and install the utility (or update an old version of the utility), but once you have done that, getting the latest updates is usually pretty easy.

Updating White Box Machines

If the machine was built from parts or is not from a major vendor, you will probably need to do some research and manual updating, once you figure out what you are dealing with. A couple of good tools to identify the components in a strange computer are CPU-Z and HWiNFO64, which you can have on a USB thumb drive. These will let you identify the motherboard model and BIOS version, along with many other components, such as video cards and storage devices. Most motherboard vendors also have utilities that can be used to check for and install the latest updates for that motherboard.


Figure 5: CPU-Z Mainboard Tab


Figure 6: HWiNFO64 System Summary

Many individual components in a system may require updates, such as video cards and storage devices. Once you know what components you are dealing with, you should be able to go to the vendor’s support site and either use a utility to check for and install updates, or simply manually do it yourself. For example, Samsung and Intel have utilities to maintain and update their storage devices and NVidia and AMD have utilities to maintain and update their video cards.

Other Devices

After you have updated all of your computers, you should try to update all your other updateable devices as much as you can. I’m talking about things like routers, managed network switches, printers, home automation gear, AV components and the like. Many devices will have a web interface or downloadable utility program that you can use to check for and install firmware and software updates. Often they will have a built-in method for checking for updates, buried somewhere in the setup or configuration interface. You should make the effort to find whatever method is necessary, and then check for and install updates.

Here are some links for some of the more common brands and devices you may run into.

Firmware Updates

NETGEAR Download Center

Marantz Updates and Upgrades

HP Customer Support

Brother Support and Downloads

Software Updates

Samsung Magician

Intel Solid State Drive Toolbox

Synology Download Center

Sonos Support

Driver Updates

NVidia GeForce Drivers

AMD Radeon Drivers

ASUS Download Center

ASRock Download Center

Gigabyte Download Center

MSI Download Center


Perhaps you are wondering why you should go to this trouble, for yourself and for your family and friends? The main reason is that it will make their devices be more secure and work better.  It will also make it a little more difficult for bad things like viruses, trojans, and worms to spread so quickly in the wild. Since you are likely in the technology field if you are reading this, your friends and family probably rely on you (at least to a certain extent) for their computer and technology support. For example, if you are a DBA, that means you are “in computers”. That means you must know everything there is to know about desktop and network support, right?

Windows Server Servicing Model Changes

Microsoft has announced some changes to the release schedule and servicing model for Windows Server. The new Semi-Annual Channel is a twice-per-year feature update release with an 18 month servicing timeline (meaning that Mainstream support ends 18 months after that Semi-Annual Channel release becomes available).

The current release in this channel is Windows Server, version 1709, which became available on October 17, 2017. This release will fall out of Mainstream support on April 18, 2019, and there is no Extended support period. In this new model, Windows Server releases are identified by the year and month of release: for example, in 2017, a release in the 9th month (September) would be identified as version 1709.

Microsoft describes the Semi-Annual Channel below:

“The Semi-Annual Channel provides opportunity for customers who are innovating quickly to take advantage of new operating system capabilities at a faster pace, both in applications – particularly those built on containers and microservices – and in the software-defined hybrid datacenter.”

You also have the option of staying on the Windows Server 2016 Long-Term Servicing Channel (LTSC) with the traditional five years of Mainstream support, five years of Extended support, and the option to purchase Premium Assurance, for six more years of support. The current release in this channel is Windows Server 2016, version 2016 which became available on October 12, 2016. This release will fall out of Mainstream support on January 11, 2022, and it will fall out of Extended support on January 11, 2027. The LTSC is scheduled to have new releases every two to three years.

The Semi-Annual Channel will be available to volume-licensed customers with Software Assurance, as well as via the Azure Marketplace or other cloud/hosting service providers and loyalty programs such as Visual Studio Subscriptions.

The Semi-Annual Channel can be installed as a Nano Server or Server Core, but is not available as Server with Desktop Experience. The Long-Term Servicing Channel can be installed as a Server with Desktop Experience or Server Core, but is not available as Nano Server.


Implications for SQL Server Usage

If you want to use the Semi-Annual Channel, you will have to be comfortable running SQL Server on Server Core (with no integrated GUI). You can either start using Powershell or you can use tools like Project Honolulu, which is a locally deployed, browser-based, management tool set that enables on-premises administration of Windows Servers with no Azure or cloud dependency.

The two most interesting new features for SQL Server in Windows Server, version 1709 are Storage-Class memory support for Hyper-V VMs and Virtualized Persistent Memory (vPMEM) for Hyper-V VMs.

Storage-class memory support for VMs enables NTFS-formatted direct access volumes to be created on non-volatile DIMMs and exposed to Hyper-V VMs. This enables Hyper-V VMs to leverage the low-latency performance benefits of storage-class memory devices. Virtualized Persistent Memory (vPMEM) is enabled by creating a VHD file (.vhdpmem) on a direct access volume on a host, adding a vPMEM Controller to a VM, and adding the created device (.vhdpmem) to a VM. Using vhdpmem files on direct access volumes on a host to back vPMEM enables allocation flexibility and leverages a familiar management model for adding disks to VMs.+

Virtualized Persistent Memory (vPMEM) is enabled by creating a VHD file (.vhdpmem) on a direct access volume on a host, adding a vPMEM Controller to a VM, and adding the created device (.vhdpmem) to a VM. Using vhdpmem files on direct access volumes on a host to back vPMEM enables allocation flexibility and leverages a familiar management model for adding disks to VMs.

Using Storage-class memory in a VM will let you use the Persisted Log Buffer feature (aka “tail of the log caching”) that was introduced in SQL Server 2016 SP1, as described here.




The Accidental DBA (Day 4 of 30): SQL Server Installation and Configuration Best Practices

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at Enjoy!

SQL Server 2012 is deceptively easy to install. You can pretty much click on the “Next” button a number of times, and you can get SQL Server 2012 installed without really having any idea what you are doing. SQL Server 2012 will run with all of the installation and configuration defaults, and depending on your workload, you may be perfectly fine for some time. On the other hand, this is somewhat like a ticking time bomb, waiting to blow up in the face of the unfortunate accidental DBA at the worst possible time.

If you have a more substantial workload or you just want to make sure that you have the best installation and configuration possible, you need to do some preparation work and planning before you install SQL Server 2012, and you need to make the correct choices during the installation process. You also need to make some key instance-level configuration changes after you are done installing and updating SQL Server 2012.

This begins with having an appropriate server and storage subsystem available. The exact details of your server and processor selection matter a great deal because of the core-based licensing in SQL Server 2012. Once you have the server and storage subsystem in place, and you have Windows Server 2012 installed, you want to make sure that the main system BIOS, all of the firmware, and all of the device drivers have been updated to the latest versions. You also want to make sure that Windows Server 2012 has been fully patched, using Microsoft Update (which is a superset of Windows Update).

In most environments, you are going to want to have a dedicated Windows domain account for each SQL Server service that you will be using, such as the Database Engine, SQL Server Agent, etc. These should be regular domain accounts with no special rights, since the SQL Server installation program will grant them the local rights that they need during the installation. Depending on your organization, getting these accounts created could take some time, but you will need them (along with their passwords) during the installation.

You also want to make sure that your hardware power management (in your BIOS) is either disabled or set to OS control. Along with this, you need to ensure that the Windows Power Plan is changed from the default of “Balanced” to the “High Performance” setting. Forgetting to make these changes is one of the most common mistakes that I see in the field, even among experienced database administrators. It is also very important to make sure that Intel Turbo Boost is enabled in the BIOS (which it usually is by default). Intel hyper-threading (HT) is somewhat more controversial. If you have an OLTP workload, I would always enable HT on newer processors (Nehalem and newer) unless you have done testing with your workload that shows that you are better off without HT (which will be very rare). With DW workloads, you may want to disable HT, but you really should do some testing with your workload before you decide.

You want to think about how you are going to configure your storage subsystem in terms of RAID levels, and the number and composition of logical drives that will be presented to Windows. This will be determined by how many drives you have available, your anticipated workload, your desired performance characteristics and your necessary drive space. After your logical drives have been configured and presented to Windows, you should test their performance, first with CrystalDiskMark, and then with SQLIO, to make sure you are getting the performance you expect. If you are using RAID controllers with dedicated hardware caches, you want to make sure that the cache policy is enabled so that the cache is actually being used.

You should also (in almost all  cases) enable Windows Instant File Initialization (IFI) and Lock Pages in Memory (LPIM) by granting these rights to the SQL Server Service account. Once you have installed SQL Server 2012 you will want to get it fully patched with the latest Service Pack and Cumulative Update (CU). Installing SQL Server Cumulative Updates is also somewhat controversial, but I am a proponent. Especially with SQL Server 2012, many important issues have been corrected with CUs and Microsoft has even added new features though the CU process.

You should also set the instance-level Maximum Server memory setting to an appropriate value (especially if you have enabled LPIM), and you should enable the “Optimize for ad hoc workloads” instance-level setting. I also generally will start out with at least four tempdb data files, making sure they are the same size, with the same autogrowth increment. Finally, I will add Trace Flag (TF) 3226 as a startup parameter to disable the logging of successful database backup messages to the SQL Server Error log.

I wrote a three-part article series that goes into more detail on this subject last year:

Provisioning a New SQL Server Instance – Part One

Provisioning a New SQL Server Instance – Part Two

Provisioning a New SQL Server Instance – Part Three

I covered how to install, patch and configure SQL Server 2008 R2 in my book, SQL Server Hardware.

Our online training (Pluralsight) courses that can help you with this topic: