Update Invoke-WebRequest HTTPS Protocol

If you’re trying to talk to a web server via PowerShell with the Invoke-WebRequest command, you may get an error: Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a send.

While I’m sure there are other situations that can cause these error message, this is the error you’ll get if the server you’re talking to has disabled SSLv3 and TLS1.0.

To make a connection, you’ll need to tell PowerShell to use something newer.  The following command will tell it to use TLS1.2.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::TLS12

 

Add MS SQL Always On Database via Powershell

You can use the GUI to set up SQL AO groups, but if you want to include it any database creation scripts, you can also do it in PowerShell.

First you’ll need to import the PowerShell module for SQL.

Import-Module SQLPS -DisableNameChecking

Then we’ll set some variables to get ready.  First you set up the database name and the network share to store and distribute the initial sync’s backup.

$dbname = 'databasename'
$networkshare = '\\servername\sharename\'

Then it uses those to build the file names for the backups.

$dbfilebackup = $networkshare + $DBName + '.bak'
$dblogbackup = $networkshare + $DBName + '.trn'

Now you need to tell it about the SQL AO group name, as well as the primary and secondary SQL server names.

$sqlaogroup = 'sqlao01'
$sqlprimary = 'server01'
$sqlsecondary = 'server02'

Then we use that data to build the SQL strings to connect to the availability group.

$sqlstringprimary = 'sqlserver:\sql\' + $sqlprimary + '\default\availabilitygroups\' + $sqlaogroup
$sqlstringsecondary = 'sqlserver:\sql\' + $sqlsecondary + '\default\availabilitygroups\' + $sqlaogroup

Now we’re ready to get to work.  First we’ll take the initial backup of the database.

Backup-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlprimary
Backup-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlprimary -BackupAction Log

Once that’s done, we’ll restore the database to the secondary server.

Restore-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlsecondary -NoRecovery
Restore-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlsecondary -RestoreAction Log -NoRecovery

After that completes it is time to add the database to always on, first on the primary, then on the secondary server.

Add-SqlAvailabilityDatabase -Path $sqlstringprimary -Database $DBName
Add-SqlAvailabilityDatabase -Path $sqlstringsecondary -Database $DBName

Now you’re done and the database should be syncing.  This process also works for a tertiary server, just add the data restore and add commands.

Import-Module SQLPS -DisableNameChecking
$dbname = 'databasename'
$networkshare = '\\servername\sharename\'
$dbfilebackup = $networkshare + $DBName + '.bak'
$dblogbackup = $networkshare + $DBName + '.trn'
$sqlaogroup = 'sqlao01'
$sqlprimary = 'server01'
$sqlsecondary = 'server02'
$sqlstringprimary = 'sqlserver:\sql\' + $sqlprimary + '\default\availabilitygroups\' + $sqlaogroup
$sqlstringsecondary = 'sqlserver:\sql\' + $sqlsecondary + '\default\availabilitygroups\' + $sqlaogroup
Backup-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlprimary
Backup-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlprimary -BackupAction Log
Restore-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlsecondary -NoRecovery
Restore-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlsecondary -RestoreAction Log -NoRecovery
Add-SqlAvailabilityDatabase -Path $sqlstringprimary -Database $DBName
Add-SqlAvailabilityDatabase -Path $sqlstringsecondary -Database $DBName

.

 

 

Moving a SQL Log File

Sometimes you need to move a log file for a SQL database, when I originally learned to do this the process was to detach the database, move your files and then attach and modify the locations.  I had a need to do this last weekend, and found that there is a much better way to do this (and a bit safer).

You do this by using the ALTER DATABASE command to MODIFY FILE and tell it where the log will go.  This command won’t take effect until the next time the database is brought online.

ALTER DATABASE dbname MODIFY FILE ( NAME = dbname_log, FILENAME = 'S:\newlocations\dbname_log.ldf');

So now the database knows where to look for the log file the next time it’s started.  So now you need to take that database offline and move the file.  You can shut down SQL, but you can use ALTER DATABASE again to just have downtime for the one database.

ALTER DATABASE dbname SET OFFLINE;

When this completes your database will be offline, but still registered on the SQL server, which is why this is safer than the detach/attach method.  If you want to confirm your settings you can run the following.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'dbname);

Now move the log file to the location you specified and bring the database back online.

ALTER DATABASE dbname SET ONLINE;

.

Chrome PAC Error Detection

Chrome is more strict than IE in dealing with PAC files.  So you may (and I did) have a PAC file that works in IE, but is ignored by Chrome.  To see what proxy file chrome is gathering, and hopefully an error to point you to why it’s being ignored, load this in chrome:

chrome://net-internals/#proxy

From here you can reload the proxy, and see what file is being pulled down.  After you try to browse to a page, you can also click the link to events to hopefully find a useful error.

SQL Server File Sizes

Not wading into the shrinking is evil debate, but sometimes you’ve legitimately moved data between databases or systems and need to know where your wasted space is on the sever.  There are several (all clunky) ways to do it.  I’m capturing this one, since I really like it and it was a pain to find.

http://www.sqlservergeeks.com/sql-server-get-free-space-in-all-files-in-an-instance/

SET NOCOUNT ON
DECLARE @DBName NVARCHAR(100) = NULL, --Provide DBName if looking for a specific database or leave to get all databases details
        @Drive NVARCHAR(2) = NULL --Mention drive letter if you are concerned of only a single drive where you are running out of space
 
DECLARE @cmd NVARCHAR(4000)
IF (SELECT OBJECT_ID('tempdb.dbo.#DBName')) IS NOT NULL
DROP TABLE #DBName
CREATE TABLE #DBName (Name NVARCHAR(100))
 
IF @DBName IS NOT NULL
INSERT INTO #DBName SELECT @DBName
ELSE
INSERT INTO #DBName SELECT Name FROM sys.databases WHERE state_desc = 'ONLINE'
 
IF (SELECT OBJECT_ID('tempdb.dbo.##FileStats')) IS NOT NULL
DROP TABLE ##FileStats
CREATE TABLE ##FileStats (ServerName NVARCHAR(100), DBName NVARCHAR(100), FileType NVARCHAR(100), 
FileName NVARCHAR(100), CurrentSizeMB FLOAT, FreeSpaceMB FLOAT, PercentMBFree FLOAT, FileLocation NVARCHAR(1000))
 
WHILE (SELECT TOP 1 * FROM #DBName) IS NOT NULL
BEGIN
 
    SELECT @DBName = MIN(Name) FROM #DBName
 
    SET @cmd = 'USE [' + @DBName + ']
    INSERT INTO ##FileStats
    SELECT @@ServerName AS ServerName, DB_NAME() AS DbName, 
    CASE WHEN type = 0 THEN ''DATA'' ELSE ''LOG'' END AS FileType,
    name AS FileName, 
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB,
    100*(1 - ((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)/(size/128.0))) AS PercentMBFree,
    physical_name AS FileLocation
    FROM sys.database_files'
     
    IF @Drive IS NOT NULL
    SET @cmd = @cmd + ' WHERE physical_name LIKE ''' + @Drive + ':\%'''
 
    EXEC sp_executesql @cmd
     
    DELETE FROM #DBName WHERE Name = @DBName
     
END
 
SELECT FreeSpaceMB*100/CurrentSizeMB AS PercentFree, * FROM ##FileStats
ORDER BY FreeSpaceMB DESC
DROP TABLE #DBName
DROP TABLE ##FileStats

Offline Folders Unavailable

If you’re rolling out Windows 10 build 1511 or higher, you may have a problem with your offline files.  If the network folders are on a Windows 2012 R2 share, you probably liked the sound of the feature continuous availability of the share.  If you made your share continuously available, they can no longer be synced offline.

Why?  I guess it the features conflict and starting in 1511 it’s blocked.  Once you turn CA off on the share (and restart your client) the share will again be available to sync offline.

Further reading: https://social.technet.microsoft.com/Forums/en-US/4d234757-0334-441d-aa39-8a2f8179a4a3/the-option-make-available-offline-not-available-in-build-1511?forum=win10itpronetworking

Shrinking WinSXS

That $&*^ WinSXS folder.  Using up so much space, but not safe to clean up manually.  Luckily in > Windows 8.1 and Windows Server 2012 R2 we have a way to shrink them a bit.  Note: Run any of these at your own risk, have a backup, etc.

First, up the safe(r) option.  Run this one to clean out previous versions of updated components (old patches).

Dism.exe /online /Cleanup-Image /StartComponentCleanup

Second, the more extreme version.  This one removes the ability to uninstall any patches, so make sure this is a solid install first.

Dism.exe /online /Cleanup-Image /StartComponentCleanup /ResetBase