PowerShell - SQL Module

How to load SQL PowerShell module:

Import-Module SQLPS

PowerShell - Help

Detailed help on cmdlet get-service:


get-help get-service -detailed

All content on cmdlet get-service:

get-help get-service -full

Show examples only:

get-help get-service -examples

List all the provider help topics such as FileSystem, Registry, or SQLServer:

get-help -category provider

PowerShell - Start here

List all cmdlets:

Get-Command

List all commands that retrieve information:

Get-Command Get*

List all commands having to do with SQL:

Get-Command *sql*

List all commands having to with services:

Get-Command *service*

Show all services:

Get-Service

Restarts the browser service:

Restart-Service browser

Gets documentation on specified cmdlet - in this case, get-service:

Get-Help get-service

Cmdlets can be interacted with by calling methods and setting or retrieving properties. By piping the output of Get-Service to Get-Member we can see all the methods and properties available from Get-Service:

Get-Service | Get-Member

How to download and archive a website using wget on OSX

(This is how to get wget without installing homebrew or installing someone else's compiled code.  I just wanted to remind myself how to install from source.)

Install xcode from Apple.  I think the most recent versions of OSX have this built-in.  Mine did and I cannot remember if I installed it previously or not.

Initialize xcode so no weird errors happen when we call it later.  (Use Launcher to find xcode and run it.  Accept the EULA).

Install the xcode command line tools:

xcode-select --install

Thankfully, curl is already built-in.  So use curl to download wget:

cd ~/Downloads

curl -O http://ftp.gnu.org/gnu/wget/wget-1.15.tar.gz

tar -zxvf wget-1.15.tar.gz

cd wget-1.15/

./configure --without-ssl

make

sudo make install

cd ..

rm -rf ~/Downloads/wget*

wget

The line above simply tests wget.  Now test wget:

wget http://ftp.gnu.org/gnu/wget/wget-1.15.tar.gz

Now download the desired web site:

cd ~/Downloads

wget --recursive --no-clobber --page-requisites --html-extension --convert-links --restrict-file-names=windows --domains www.whatever.com --no-parent www.whatever.com/.

Sources:

I followed these two pages but put my corrections in my instructions above.

https://coolestguidesontheplanet.com/install-and-configure-wget-on-os-x/

http://www.linuxjournal.com/content/downloading-entire-web-site-wget

How to set up TDE, back up certificates, and restore all to a new server

/**

-- This lets us look at existing encryption bits.

select * from sys.symmetric_keys

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases


- This defines a master key

USE MASTER;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO

select * from sys.certificates

-- Now create the certificate

CREATE CERTIFICATE [SQL-A]
WITH SUBJECT = 'SQL-A CERTIFICATE'

select * from sys.certificates

-- Need to be in the user database

use mydb
go

-- Create an encryption key within the user database
-- I wonder if AES_128 is appropriate

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE [SQL-A];
GO

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

-- This turns on encryption for that database

alter database mydb
set encryption on 
go

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

select * from students;

-- Backups are not that important.
-- Restores, however, are critical.

use master;
go

-- So let us back up the certificate

BACKUP CERTIFICATE [SQL-A]
TO FILE = 'C:\BACKUP\SQL-A.CER'
WITH PRIVATE KEY 
(
FILE = 'C:\BACKUP\MYDB.PVK',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
);
GO

-- And back up the actual database

BACKUP DATABASE [mydb] 
TO  DISK = N'C:\backup\mydb.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


-- We can restore that database to the original server 
-- without messing with certificates, keys, and passwords.
-- This would work if we were restoring on the same server as we started on.
-- That's because the certificate and keys are already installed/configured.
-- The restored database would already be encrypted.

USE [master]
RESTORE DATABASE [mydb2] 
FROM  DISK = N'C:\backup\mydb.bak' 
WITH  FILE = 1,  
  MOVE N'mydb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb2.mdf',
  MOVE N'mydb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydb2_log.ldf',  
  NOUNLOAD,  STATS = 5

GO

-- The remaining commands would be run on a NEW destination server.
-- This server would not have an existing master key 
-- nor would it have the original certificates.

select * from sys.symmetric_keys

select [name]
, [is_master_key_encrypted_by_server]
, [is_encrypted]
from master.sys.databases

-- So we need to set up a bunch of stuff.

USE [master]

-- Notice we have a brand new server with a brand new master key password

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewPa$$w0rd';
GO

-- This is the critical key component.  
-- Here we re-create the original certificate
-- from the original certificate, private key, and password.
-- Note I had some trouble with SQL telling me that the certificate did not exist or that I did not have permissions to the file
-- It turned out there was an NTFS permission issue on the file.
-- When I copied the files to a new directory instead of attempting to restore from the original directory, 
-- all worked well.
-- So there was an NTFS permission issue, but I do not know exactly what.

CREATE CERTIFICATE [SQL-A]
FROM FILE = 'c:\RESTORE\SQL-A.CER'
WITH PRIVATE KEY (FILE = 'c:\RESTORE\MYDB.PVK',
DECRYPTION BY PASSWORD = 'Pa$$w0rd')
GO

-- And now we can restore the actual database.

RESTORE DATABASE [mydb] 
FROM  DISK = N'C:\RESTORE\mydb.bak' 
WITH  FILE = 1,  
  MOVE N'mydb' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\DATA\mydb.mdf',
  MOVE N'mydb_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\DATA\mydb_log.ldf',  
  NOUNLOAD,  STATS = 5

GO






*/

How to tell if TDE (encryption) has been enabled on a SQL server

Say you inherited a pile of SQL servers.  How do you tell if a database has ever been encrypted on any of them?

Run this query on each server.  For bonus points, use something like Idera Multi Query so it runs against all the servers at once.  Multi Query is part of Idera's SQL Admin Toolset.

select [name], is_master_key_encrypted_by_server, is_encrypted

from master.sys.databases

go

Another approach is to view the symmetric keys:

select
        [name]
      , [principal_id]
      , [algorithm_desc]
      , [create_date]
from master.sys.symmetric_keys

If a master key exists, you will find one of the [name] fields populated with "##MS_DatabaseMasterKey##"

Fix Time Problems on Multiboot OS X and Windows System

Registry location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation

Add DWORD:
RealTimeIsUniversal

Value:
1

Reboot to OSX, then back to Windows.