Decommission WSUS 3 on Small Business Server

I am slowly but surely moving all of the services from Windows SBS 2008 onto our brand new Windows 2016 Server. This article covers the steps I took to decommission WSUS 3 after switching to WSUS 4.

Firstly, I checked that none of my clients were still connecting to WSUS 3. Once I established this, I manually deleted all of the client machines from the Console.

Next, I set “Any” updates I had previously “Approved” to “Not Approved”.

Finally, configure the WSUS server to NOT store updates locally.

At this point, you can run the WSUS Server Cleanup Wizard and you should see a large amount of hard disk space reclaimed. In my case, this was almost 35GB.

 

Advertisements

Windows 10 Update 1709 woes

I tried many different methods of attack, but on every occasion, Windows 10 Update 1709 continued to fail.

The Error Message was a follows:

0x8007001F – 0x3000D The installation failed in the FIRST_BOOT phase with an error during MIGRATE_DATE operation

–

–

Now, I need to point out that my Workstation is connected to a Domain, and that we use Roaming User Profiles. So, if you don’t have this scenario, the solution won’t work for you unfortunately.

Bearing in mind the Error Message, my assumption was that it had something to do with the User Profiles.

In order to get the Workstation to update, I did the following:

[1] Remove ALL of the Roaming Profiles from the Local Workstation using “Control Panel => Advanced System Settings => User Profiles”

Only the Administrator Account (and the Default Profile) should remain.

–

–

[2] Disconnect the Workstation from the Domain (make sure you know the Local Machine Administrator Password)

[3] Using “Regedit”, navigate to “HKLM\Software\Microsoft\Windows NT\CurrentVersion\ProfileList\” and remove any remaining Domain Accounts from the list. Don’t remove the Administrator Account (or any other local system accounts).

–

–

[4] Optionally, clear out any “dead” account folders from within “C:\users”.

–

If you try the update again, you should find that it works.

When everything is complete, you can re-join the machine to the Domain.

Office 365: Your Automatic Reply Settings Cannot Be Displayed

We recently decommissioned our aging Exchange 2007 Server and switched to using Office 365 for Email.

One quirk of the new system was that some users were having difficulty accessing their Out Of Office (Automatic Reply) Settings when using Outlook 2010 or 2013.

Your automatic reply settings cannot be displayed because the server is currently unavailable. Try again later.

There seemed to be no real pattern to this, and many suggestions were offered, non of which worked for us.

The simple solution I eventually stumbled upon was to ensure that “Exchange Web Services” was enabled for each Mailbox within Office 365: Active Users => UserName =>  Mail Settings => Email Apps

I have no idea why this is required, but it would appear that MAPI doesn’t support the full functionality of Exchange when using Outlook.

Watchguard Firebox M200: Unblocking WeTransfer

If you are blocking “Personal Network Storage and Backup” at your Watchguard Firewall, users won’t be able to access wetransfer.com. In their own support documentation here, WeTransfer suggested creating the following firewall exceptions:

*.wetransfer.com

*.we.tl

*.amazonaws.com

*.wetransfer.net

However, this still doesn’t prevent the Firebox from dropping the traffic. Below is a table of exceptions I had to create to get things working properly.

Wetransfer #1	Pattern Match	*.wetransfer.com/*
Wetransfer #2	Pattern Match	wetransfer.com/*
Wetransfer #3	Pattern Match	*.we.tl/*
Wetransfer #4	Pattern Match	we.tl/*
Wetransfer #5	Pattern Match	*.wetransfer.net/*
Wetransfer #6	Pattern Match	wetransfer.net/*
Wetransfer #7	Pattern Match	*.amazonaws.com/*

Using Regular Expressions, this can be simplified further:

Wetransfer #1	Regular Expression	(wetransfer\.((com)|(net)))
Wetransfer #2	Regular Expression	(^we.tl)|(\.we\.tl)
Wetransfer #3	Pattern Match	*.amazonaws.com/*

T-SQL: Using the HAVING clause

Here is a quick differentiation between the “WHERE” clause and the “HAVING” clause within an SQL Query.

Below is a basic query that will generate a list of the Customers based upon the number of orders they have placed.

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no order by orders desc

I have used the “WHERE” clause to filter records where the Order Number is greater than zero (you could also filter by order date).

However, if I am only interested in Customers that have placed more than 100 orders, I can use the HAVING clause as shown below.

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc

The HAVING clause allows me to apply a filter based upon a value created by the GROUP BY clause (eg SUM; COUNT; AVG), rather than values in the original table.

If you only need to list your top 10 customers, use the TOP clause or the LIMIT clause.

select top 10 count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc limit 10

T-SQL: Aggregate Functions; Date Functions; and Group By Statement Woes

When combining Aggregate Functions with Date / Time functions within an SQL Query, you may run into errors relating to the “Group By” statement, such as:

• “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
• “Invalid Column Name”
• “Each GROUP BY expression must contain at least one column that is not an outer reference”

Here are some examples of how NOT to formulate the Query.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by

Msg 8120, Level 16, State 1, Line 2 Column ‘dbo.windows_quote.date_received’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Excluding “year_received” from the GROUP BY statement does not work, and neither does adding it.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, year_received

Msg 207, Level 16, State 1, Line 8 Invalid column name ‘year_received’.

I also tried to reference the field by index number..

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, 2

Msg 164, Level 15, State 1, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.

Only by including the function itself within the GROUP BY statement could I get the Query to run properly.

UPDATE: The reason for this is because the “SELECT” clause is the very last part of the query to be evaluated. The query engine works through the statement as follows: FROM => WHERE => GROUP BY => SELECT. Therefore, the attribute “year_received” within the SELECT clause doesn’t exist when GROUP BY tries to reference it.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, year(date_received)

 

SQL Server File Locations for Default Instances

Each Instance of SQL Server has its own set of Executables (sqlservr.exe) and Data Files. The file locations for the default instance are different depending upon the version installed. Essentially, this is to allow multiple versions of SQL Server to happily co-existing on the same server.

The table below lists the default file locations (as they appear on my systems).

Version	Location of Database Engine	Additional Instances	Service Names
SQL Server 2000  & MSDE(v8)	C:\Program Files\Microsoft SQL Server\MSSQL	MSSQL$MyInstance	MSSQLSERVER; MSSQL$MyInstance
SQL Server 2005 (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2005 Express (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2008 (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER	MSSQL10.MyInstance	SQL Server (MSSQLSERVER); SQL Server (MyInstance)
SQL Server 2008 Express (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS	MSSQL10.MyInstance
SQL Server 2008 R2 (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Reporting Services (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Express (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS	MSSQL10_50.MyInstance
SQL Server 2012 Express (v11)	C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS	MSSQL11.MyInstance	SQL Server (MyInstance)
SQL Server 2014 Express (v12)	C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS	MSSQL12.MyInstance	SQL Server (MyInstance)

 

Common Files
Additionally, multiple instances (within each version) share some common files.

Version	Location of Common Files	Notes
Common Files (2000)	C:\Program Files\Microsoft SQL Server\80\
Common Files (2005)	C:\Program Files\Microsoft SQL Server\90\	The 2005 installation also creates an  “80” folder.
Common Files (2008)	C:\Program Files\Microsoft SQL Server\100\
Common Files (2012)	C:\Program Files\Microsoft SQL Server\110\
Common Files (2014)	C:\Program Files\Microsoft SQL Server\120\

 

The Browser Service
There is only ever one copy of the SQL Browser Service running (and it always lives in the same folder). Normally, it will be automatically upgraded to whichever is the latest installed version of SQL Server.

The Browser Service listens for requests on UDP Port 1434 on behalf of all the installed SQL Server Instances that are installed on the Server (irrespective of version).

Version	Location of Browser Service	Notes
SQL Server 2000 Listener Service		Requires SQL Server 2000 SP3a
SQL Server 2005 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2005.90.5000.0
SQL Server 2008 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2007.100.5500.0
SQL Server 2008 R2 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2009.100.1600.1
SQL Server 2012 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2011.110.2100.60
SQL Server 2014 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2014.120.2000.8

 

64 Bit Windows
Within 64 Bit Windows, some files may be installed within “Program Files (86)” rather than “Program Files”.

SQL Server Compact Edition
These editions have very little in common with the SQL family as discussed in this document. SQL Server Compact Edition does not run as a service, nor is there any concept of database instances. The default installation directory is “C:\Program Files\Microsoft SQL Server Compact Edition\” and a file type of “sdf” is used for each data store.