SQL exam prep

one of my goals this year is to learn more about SQL.  As part of that I have committed to taking the 70-461 exam which is the 1st exam for the DBA.  I have picked up all of the exam material that I need by a company called Joes 2 Pros.  The material is pretty straight forward and seems to be well written.

My next goal is to write up a timeline for studying the material and then get it all ingested to take the exam in the Aug time frame.

High level

April-Jun

study material and ingest

July

take practice exams

Aug

practice exams and parametric exam sometime around the 20th

 

 

Upgrade and conversion options for Windows Server 2016

Pulled from Technet

This topic includes information about upgrading to Windows Server® 2016 from a variety of previous operating systems using a variety of methods.

The process of moving to Windows Server 2016 might vary greatly depending on which operating system you are starting with and the pathway you take. We use the following terms to distinguish among different actions, any of which could be involved in a new Windows Server 2016 deployment.

  • Installation is the basic concept of getting the new operating system on your hardware. Specifically, a clean installation requires deleting the previous operating system. For information about installing Windows Server 2016, see System Requirements and Installation Information for Windows Server 2016. For information about installing other versions of Windows Server, see Windows Server Installation and Upgrade.
  • Migration means moving from your existing operating system to Windows Server 2016 by transferring to a different set of hardware or virtual machine. Migration, which might vary considerably depending on the server roles you have installed, is discussed in detail at Windows Server Installation, Upgrade, and Migration.
  • Cluster OS Rolling Upgrade is a new feature in Windows Server 2016 that enables an administrator to upgrade the operating system of the cluster nodes from Windows Server 2012 R2 to Windows Server 2016 without stopping the Hyper-V or the Scale-Out File Server workloads. This feature allows you to avoid downtime which could impact Service Level Agreements. This new feature is discussed in more detail at Cluster operating system rolling upgrade.
  • License conversion In some operating system releases, you can convert a particular edition of the release to another edition of the same release in a single step with a simple command and the appropriate license key. We call this “license conversion.” For example, if you are running Windows Server 2016 Standard, you can convert it to Windows Server 2016 Datacenter.
  • Upgrade means moving from your existing operating system release to a more recent release while staying on the same hardware. (This is sometimes called “in-place” upgrade.) For example, if your server is running Windows Server 2012, or Windows Server 2012 R2, you can upgrade it to Windows Server 2016. You can upgrade from an evaluation version of the operating system to a retail version, from an older retail version to a newer version, or, in some cases, from a volume-licensed edition of the operating system to an ordinary retail edition.

Important

Upgrade works best in virtual machines where specific OEM hardware drivers are not needed for a successful upgrade.

Important

For releases of Windows Server 2016 prior to 14393.0.161119-1705.RS1_REFRESH, you can only perform conversion from evaluation to retail with Windows Server 2016 that has been installed by using the Desktop Experience option (not the Server Core option). Starting with version 14393.0.161119-1705.RS1_REFRESH and later releases, you can convert evaluation editions to retail regardless of the installation option used.

Important

If your server uses NIC Teaming, disable NIC Teaming prior to upgrade, and then re-enable it after upgrade is complete. See NIC Teaming Overview for details.

Upgrading previous retail versions of Windows Server to Windows Server 2016

The table below briefly summarizes which already licensed (that is, not evaluation) Windows operating systems can be upgraded to which editions of Windows Server 2016.

Note the following general guidelines for supported paths:

  • Upgrades from 32-bit to 64-bit architectures are not supported. All editions of Windows Server 2016 are 64-bit only.
  • Upgrades from one language to another are not supported.
  • If the server is a domain controller, see Upgrade Domain Controllers to Windows Server 2012 R2 and Windows Server 2012 for important information.
  • Upgrades from pre-release versions (previews) of Windows Server 2016 are not supported. Perform a clean installation to Windows Server 2016.
  • Upgrades that switch from a Server Core installation to a Server with a Desktop installation (or vice versa) are not supported.
  • Upgrades from a previous Windows Server installation to an evaluation copy of Windows Server are not supported. Evaluation versions should be installed as a clean installation.

If you do not see your current version in the left column, upgrading to this release of Windows Server 2016 is not supported.

If you see more than one edition in the right column, upgrading to either edition from the same starting version is supported.

If you are running this edition: You can upgrade to these editions:
Windows Server 2012 Standard Windows Server 2016 Standard or Datacenter
Windows Server 2012 Datacenter Windows Server 2016 Datacenter
Windows Server 2012 R2 Standard Windows Server 2016 Standard or Datacenter
Windows Server 2012 R2 Datacenter Windows Server 2016 Datacenter
Windows Server 2012 R2 Essentials Windows Server 2016 Essentials
Windows Storage Server 2012 Standard Windows Storage Server 2016 Standard
Windows Storage Server 2012 Workgroup Windows Storage Server 2016 Workgroup
Windows Storage Server 2012 R2 Standard Windows Storage Server 2016 Standard
Windows Storage Server 2012 R2 Workgroup Windows Storage Server 2016 Workgroup

Per-server-role considerations for upgrading

Even in supported upgrade paths from previous retail versions to Windows Server 2016, certain server roles that are already installed might require additional preparation or actions for the role to continue functioning after the upgrade. Consult the specific TechNet Library topics for each server role you intend to upgrade for details of additional steps that might be required.

Converting a current evaluation version to a current retail version

You can convert the evaluation version of Windows Server 2016 Standard to either Windows Server 2016 Standard (retail) or Datacenter (retail). Similarly, you can convert the evaluation version of Windows Server 2016 Datacenter to the retail version.

Important

For releases of Windows Server 2016 prior to 14393.0.161119-1705.RS1_REFRESH, you can only perform this conversion from evaluation to retail with Windows Server 2016 that has been installed by using the Desktop Experience option (not the Server Core option). Starting with version 14393.0.161119-1705.RS1_REFRESH and later releases, you can convert evaluation editions to retail regardless of the installation option used.

Before you attempt to convert from evaluation to retail, verify that your server is actually running an evaluation version. To do this, do either of the following:

  • From an elevated command prompt, run slmgr.vbs /dlv; evaluation versions will include “EVAL” in the output.
  • From the Start screen, open Control Panel. Open System and Security, and then System. View Windows activation status in the Windows activation area of the System page. Click View detailsin Windows activation for more information about your Windows activation status.

If you have already activated Windows, the Desktop shows the time remaining in the evaluation period.

If the server is running a retail version instead of an evaluation version, see the “Upgrading previous retail versions of Windows Server to Windows Server 2016” section of this topic for instructions to upgrade to Windows Server 2016.

For Windows Server 2016 Essentials: You can convert to the full retail version by entering a retail, volume license, or OEM key in the command slmgr.vbs.

If the server is running an evaluation version of Windows Server 2016 Standard or Windows Server 2016 Datacenter, you can convert it to a retail version as follows:

  1. If the server is a domain controller, you cannot convert it to a retail version. In this case, install an additional domain controller on a server that runs a retail version and remove AD DS from the domain controller that runs on the evaluation version. For more information, see Upgrade Domain Controllers to Windows Server 2012 R2 and Windows Server 2012.
  2. Read the license terms.
  3. From an elevated command prompt, determine the current edition name with the command DISM /online /Get-CurrentEdition. Make note of the edition ID, an abbreviated form of the edition name. Then run DISM /online /Set-Edition:<edition ID> /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula, providing the edition ID and a retail product key. The server will restart twice.

For the evaluation version of Windows Server 2016 Standard, you can also convert to the retail version of Windows Server 2016 Datacenter in one step using this same command and the appropriate product key.

Tip For more information about Dism.exe, see DISM Command-line options.

Converting a current retail edition to a different current retail edition

At any time after installing Windows Server 2016, you can run Setup to repair the installation (sometimes called “repair in place”) or, in certain cases, to convert to a different edition. You can run Setup to perform a “repair in place” on any edition of Windows Server 2016; the result will be the same edition you started with.

For Windows Server 2016 Standard, you can convert the system to Windows Server 2016 Datacenter as follows: From an elevated command prompt, determine the current edition name with the command DISM /online /Get-CurrentEdition. For Windows Server 2016 Standard this will be ServerStandard. Run the command DISM /online /Get-TargetEditions to get the ID of the edition you can upgrade to. Make note of this edition ID, an abbreviated form of the edition name. Then run DISM /online /Set-Edition:<edition ID> /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula, providing the edition ID of your target and its retail product key. The server will restart twice.

Converting a current retail version to a current volume-licensed version

At any time after installing Windows Server 2016, you can freely convert it between a retail version, a volume-licensed version, or an OEM version. The edition remains the same during this conversion. If you are starting with an evaluation version, convert it to the retail version first, and then you can inter-convert as described here.

To do this, from an elevated command prompt, run: slmgr /ipk <key>

Where <key> is the appropriate volume-license, retail, or OEM product key.

SQL Server 2014 Express fails install

SQL 2014 Install failure

I tried installing SQL2014 and encountered a failure with the below messages in the Summary file in folder C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\

Detailed results:

Feature:                       Database Engine Services

Status:                        Failed: see logs for details

Reason for failure:            An error occurred during the setup process of the feature.

Next Step:                     Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

Component name:                SQL Server Database Engine Services Instance Features

Component error code:          0x851A001A

Error description:             Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

I looked at the Event Viewer to see what caused it and noticed the below messages logged in there:

The server was unable to initialize encryption because of a problem with a security library. The security library may be missing. Verify that security.dll exists on the system.

TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support. The group or resource is not in the correct state to perform the requested operation.

TDSSNIClient initialization failed with error 0x139f, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The group or resource is not in the correct state to perform the requested operation.

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I installed SQL 2014 with the same setup file before successfully, but this time it failed. I tried to figure out what changed recently in the company and remembered that TLS 1.0 and SSL 3.0 protocols were disabled (because of known security vulnerabilities) on all the servers and newer version of TLS & SSL was enabled which is TLS 1.1/1.2 . Disabling those protocols will cause problems in SQL Install process. This issue is addressed in CU1 (after SP3) of SQL 2012.

To resolve this, I had go enable the registry keys of the older SSL 3.0 (which is TLS 1.0, Microsoft renamed SSL 3.0 as TLS 1.0) as indicated below

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL.3.0\Client:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on ‘Enabled’, click on Modify and enter the value 1 (hexadecimal).

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL.3.0\Server:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on ‘Enabled’, click on Modify and enter the value ffffffff (hexadecimal).
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on ‘Enabled’, click on Modify and enter the value 1 (hexadecimal).

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server:

Right–click on DisabledByDefault, click on Modify and enter the value 0 (hexadecimal)

Right click on ‘Enabled’, click on Modify and enter the value ffffffff (hexadecimal).
After I modified the registry keys, the SQL Install completed successfully. After the install is complete, make sure you patch it with SP3+CU1. At that point you can modify the registry keys back to normal.

SCCM lessons learned

a current install I am working on is proving to be one of the most difficult.  It seems like all of the prerequisites I sent over are not just suggestions.

local admin is needed for all service accounts and all computer accounts on every server in the SCCM infrastructure.

sccm accounts as well as computer accounts need to have SA on the SCCM do server

standard windows ports need to be open between all servers in infrastructure.

 

More items to come as I think about them

Windows 2012 R2 Super Ping

Test-NetConnection PowerShell Portscan

SUPERPING – POWERSHELL TEST-NETCONNECTION

inShare1

With Windows 8.1 and Windows Server 2012 R2 Microsoft released PowerShell v4 with new PowerShell modules and new PowerShell cmdlets. One of them is the new Test-NetConnection cmdlet which does basically replaces some simple network tools like Ping, Traceroute, Portscanner and more.

First if you just run Test-NetConnection this will check a Microsoft edge server and will tell you if your internet is working or not.

PowerShell Test-NetConnection

You can also ping other servers

Test-NetConnection Ping

If we have a closer look at this cmdlet we can see that we can do much more.

get-help get-testnetconnection

What we can do is something like a port scan. In this example I check if the RDP port is open on my webserver. Which is hopefully not ;-)

1
Test-NetConnection thomasmaurer.ch -CommonTCPPort RDP

Test-NetConnection PowerShell Portscan

You can also check for not so common ports by using the -Port parameter and entering the port number.
Another thing you could do would be a simple traceroute.

1
Test-NetConnection thomasmaurer.ch -TraceRoute

Test-NetConnection PowerShell Traceroute

If you want to do a ping -t you could use the following command

1
while ($true) {Test-NetConnection -InformationLevel Quiet}

Ping -t with PowerShell

I hope this helps you a little :)

VMWare on a MAC

Using a Mac with VMWare vSphere (ESXi) 5

One of the biggest complaints I’ve had with VMWare vSphere and VMWare ESX/ESXi over the last few years is that managing my virtual machines from my Mac computer was a hassle. The VMWare management utilities are all Windows-only, and even the few web-based tools either do not work or are extremely limited from a Mac. While it isn’t perfect yet, VMWare vSphere 5 has made it so you can actually do just about anything you need to using a Macintosh computer; you just need to go through a few hurdles.

To enable the administration of your various virtual machines, storage, clusters, datacenters, and the like, you can now use the vSphere 5 Web Client. Before it can be used, it must be authorized; the best instructions I found for this are here. Follow the steps in the “Authorizing the vSphere Web Client (Server)” section. This is a one-time configuration necessary to enable the vSphere Web Client.

Once authenticated, you will see something that looks very similar to the Windows-based vSphere Client running in your browser.

vSphere Web Client

This will satisfy most of your management needs, but it leaves out an all-important capability; the ability to remotely view the console of the systems. There’s a Console button, but it won’t work on a Mac. Once you’ve installed a machine, you can typically enable some sort of remote desktop capability in the operating system, but what do you do before then? If you’re running Windows, you use the vSphere client and open a console, but on a Mac, you’re out of luck. Right? Wrong.

There is an under-documented feature of vSphere that allows the capability of opening up VNC connections from the host directly to the console of the virtual machine. To perform this, we first have to enable incoming connections to your vSphere server, as vSphere 5 has an integrated firewall. This is the one step you will actually need to use the Windows vSphere Client; everything else can be done using the Web Client. This step needs to be executed once for each vSphere or ESXi host running virtual machines you want to access using VNC.

In the Windows vSphere client, choose the host you wish to enable VNC connections on. Choose the Configuration tab and on the left choose Security Profile. On the right, next toFirewall click Properties… As VMWare does not include VNC as a protocol, it is not listed as an available option. However the ports allowed by the gdbserver protocol will suit our purposes. Check the box next to gdbserver. (It is also wise to highlight the gdbserver line and click the Firewall… button and lock down where you will allow these VNC connections to take place from; in ours I restricted this to our intranet.) Click OK and you’ve now enabled the incoming ports to be used for VNC.

Finally, enabling VNC access to the console machines is a matter of setting advanced configuration parameters on each virtual machine, which can only be done when the virtual machine is off. To open up the advanced configuration:

  • In the Windows vSphere client, choose the machine, click Edit Settings…, click theOptions tab, choose Advanced->General on the left, and click Configuration Parameters… on the right.
  • In the Web client, choose the machine, click Edit Settings… under the VM Hardwaresection, click VM Options, click Advanced, and click Edit Configuration….

In both cases, you now want to add three rows by clicking the Add Row button.

Name Value
RemoteDisplay.vnc.enabled true
RemoteDisplay.vnc.port 5900-5999 are the “standard” ports, choose one different from other VMs on the host.
RemoteDisplay.vnc.password the VNC password used to access the VNC session; only the first 8 characters are encrypted using the VNC protocol, and weakly at that. Don’t rely on this for security.

Once you’ve added these rows and click OK, you can now use a VNC client to connect to the console of the machine. Power up the machine, and then using Finder on the Mac, choose Go->Connect to Server (or hit Command-K), and type the following:

vnc://<ip or name of esxi host>:<port chosen in configuration settings>/

and click Connect. You will be prompted for your password, and depending on your client/version of OSX you may receive a warning about how keystroke encryption is not enabled. Accept the warning, and you will see the console of the virtual machine! (And note, since Macs don’t already use the three-finger salute, you can safely just press Ctrl-Alt-Del in that VNC-window to log into Windows systems!)

Once you’ve installed the operating system of choice, and enabled that OS’ remote desktop capability, you may want to disable this VNC access. Just shut down the VM, go back into the advanced options and change the RemoteDisplay.vnc.enabled setting to false.

Hopefully at some point soon, VMWare will enable a true web-based console application (which doesn’t require host-specific plugins to be installed) to go with their nice new web client. Until then, this is a reasonable workaround for accessing virtual machines using a Mac.