Travel tips from #sqlfamily

Inspring by a conversation seen on twitter – Travel tips from #sqlfamily

The rolling method for packing, WOW does it save space.

Eagle Creek compression cubes – https://t.co/9cjM0wc53w

Dufl service – https://ozar.me/2015/07/dufl-review-easier-way-to-pack-clothes-for-business-trips/

“You can even send Amazon/Nordstrom orders to your ‘closet’ if you buy new clothes.”

http://blogs.msdn.com/b/buckwoody/archive/2010/06/07/teched-2010-day-one-how-i-travel.aspx

Travel Tips from an Intrepid Traveling DBA

http://www.sqlskills.com/blogs/paul/the-paul-and-kimberly-travel-guide/

How I Travel

Advertisements

Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server CTP 3.2 – Basic Availablity Group

Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server CTP 3.2 – Basic Availablity Group

Basic Availability Group Setup with a Active Directory-Detached Cluster

– iSCSI disk setup
– Firewall rule setup for default Availability Group port
– DNS Setup
– Active Directory-Detached Cluster
– Enabling SQL Authentication
– Certificate Setup including Backup/Restore with Backup Encryption
– Certificate Checks before AG setup
– Database Backup/Restore
– Creating the Basic Availability Group via the GUI
– Availability Group Checks via DMV
– Availability Failover/failback

http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.18


Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server Installation – standalone SQL installs with Storage Replica,floating IP and manual failover

Following on from the previious attempt at Windows Server 2016 Storage Replica with Cluster Shared Volumes which did not work completely I tried a manual approach.

I did not give up, what if we do not use Windows Clustering but do all the steps manually?

This is 2 standalone SQL Servers both installed with data on the F: drive and then Storage Replica used to keep them in sync.

We

– add a DNS entry and a floating IP address
– manually setup Storage Replica
– manually handle stopping/starting services, failing over/back the Storage Replica and moving the floating IP

does it work?

The answer is yes-ish if you are careful and ignore the warning about data loss!

There does not seem to be an option to failover (Set-SRPartnership) synchronously to guarantee no data loss!

Also we could do with an option to validate everything is in sync or resync even if we would have to suspend writes to allow this.

Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server Installation – standalone SQL installs with Storage Replica,floating IP and manual failover

http://smooth1.co.uk/installs/dbinstalls.html#3.1.16


Checking SQL Server connectivity -PortQry/

Useful tools for checking SQL Server connectivity – PortQry and PortQryUI

http://www.smooth1.co.uk/mssql/portqry.html


Powershell for managing Storage Replicas

Powershell to manage Storage Replicas

Get-Module -ListAvailable  : List available modules

Get-Module : List modules loaded

Get-Module  | ft Version,Name

Version    Name
-------    ----
3.1.0.0    Microsoft.PowerShell.Management
3.1.0.0    Microsoft.PowerShell.Utility
1.1        PSReadline
1.0        StorageReplica

Get-Command -Module StorageReplica | ft CommandType,Name

CommandType Name
----------- ----
Function    Clear-SRMetadata
Function    Export-SRConfiguration
Function    Get-SRDelegation
Function    Get-SRGroup
Function    Get-SRPartnership
Function    Grant-SRAccess
Function    Grant-SRDelegation
Function    New-SRGroup
Function    New-SRPartnership
Function    Remove-SRGroup
Function    Remove-SRPartnership
Function    Revoke-SRDelegation
Function    Set-SRGroup
Function    Set-SRPartnership
Function    Suspend-SRGroup
Function    Sync-SRGroup
Cmdlet      Test-SRTopology

 


SQL Server Clustering (FCI) setup using Storage Replica

I have been playing with SQL Server Clustering (FCI) setup with a Widnows Server 2016 Preview 4 and Windows Storage Replica.

This involves local storage and Cluster Shared Volume with Replication Enabled.

http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.15

This is incomplete – if Node 1 goes offline the SQL Server role fails!

 


Tools used when building Windows cluster with iscsi

Tools used when building Windows cluster with iscsi:

  • Control Panel -> Network and Internet -> View network status and tasks including Change adapter settings
  • Control Panel -> System -> Device Manager including View -> show hidden devices
  • Disk Management
  • Server Manager -> Manage -> Add roles and features
  • Server Manager -> Tools -> Failover Cluster Manager
  • Server Manager -> Tools -> iSCSI Initiator
  • Server Manager->Tools->Routing and Remote Access
  • Server Manager -> File and Storage Services on left panel -> iSCSI
  • Server Manager->Tools-> Group Policy Management
  • Server Manager->Tools-> Active Directory Users and Computers
  • diskpart
  • sysprep

Tip and undo hints when things go wrong!:

  • Sysprep everything, see below, technically I read only DC needs sysprep but I was safe.
  • diskpart – to remove GPT protected or recovery partitions – list disk – select disk N – list partitions – select partition N- delete partition override
  • Server Manager->Tools-> Active Directory Users and Computers -> expand domain -> expand computers -> delete computer to allow retry – not only done on my play setup!
  • Backup all VMs BEFORE installing Failover Clustering features

Sysprep myth

After a lot of reading I found Mark Russinovich’s answer.

https://blogs.technet.microsoft.com/markrussinovich/2009/11/03/the-machine-sid-duplication-myth-and-why-sysprep-matters/

So sysprep is only needed when 1 machine will reference another machine’s SID :

  • The first domain controller in a domainhas a machine SID and this becomes the “Domain SID”.
  • All DCs have the their machine SID matching the “Domain SID”.Machines
  • All machines which are not a Domain Controller MUST not have a machine SID matching the “Domain SID”.

I sysprep’ed everything in my cluster builds so I have not tested this!