Travel tips from #sqlfamily
Posted: January 17, 2016 Filed under: speaking, Uncategorized Leave a commentInspring 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/
Windows Server 2016 Technical Preview 4 Cluster Build and SQL Server CTP 3.2 – Basic Availablity Group
Posted: January 14, 2016 Filed under: Uncategorized, Windows Active Directory-Detached Cluster Leave a commentWindows 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
Posted: January 12, 2016 Filed under: Uncategorized, Windows Server 2016, Windows Storage Replica Leave a commentFollowing 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/
Posted: January 11, 2016 Filed under: Microsoft SQL Server, Uncategorized Leave a commentUseful tools for checking SQL Server connectivity – PortQry and PortQryUI
http://www.smooth1.co.uk/mssql/portqry.html
Powershell for managing Storage Replicas
Posted: January 9, 2016 Filed under: powershell, Uncategorized, Windows Storage Replica Leave a commentPowershell 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
Posted: January 9, 2016 Filed under: Uncategorized, Windows Cluster Shared Volumes, Windows Clustering, Windows Storage Replica Leave a commentI 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
Posted: January 6, 2016 Filed under: active directory, Uncategorized, Windows ACtive Directory, Windows Clustering, Windows Domain Controller Setup Leave a commentTools 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.
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!
SQL Server and Windows Cluster Shared Volumes
Posted: January 6, 2016 Filed under: Microsoft SQL Server, Uncategorized, Windows Cluster Shared Volumes, Windows Clustering Leave a commentIntroduction
Windows Cluster Shared Volumes are a disk volume which acts as a clustered filesystem i.e. read-write on all nodes.
In fact, in Disk Management on the cold side of the cluster,the Volume is tagged with ‘(CSVFS)’
SMB 3.0 is used behind the scenes
How do I setup a Cluster Shared Volume?
See http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.14
What if a cluster node goes down?
No storage failover is needed when a node goes down
- sql server has no cluster dependency on the disks anymore!
There is still a co-ordinator node for writes.
What if all storage paths to a node go down?
If this is the co-ordinator node, another co-ordinator node is chosen and i/o rediection occurs. See http://www.smooth1.co.uk/mssql/mssql_csv_iorediection.html
How do Cluster Shared Volumes work internally?
http://blogs.msdn.com/b/clustering/archive/2013/12/02/10473247.aspx
Cluster Shared Volume Diagnostics
http://blogs.msdn.com/b/clustering/archive/2014/03/13/10507826.aspx
Cluster Shared Volume Performance Counters
http://blogs.msdn.com/b/clustering/archive/2014/06/05/10531462.aspx
Cluster Shared Volume Failure Handling
http://blogs.msdn.com/b/clustering/archive/2014/10/27/10567706.aspx
Powershell and iSCSI
Posted: January 5, 2016 Filed under: iSCSI, powershell, Uncategorized Leave a commentPowershell module notes:
Get-Module : List loaded modules
Get-Module –ListAvailable : List available modules
iSCSI target (Run on machine exporting disks)
Import-Module IscsiTarget
Get-IscsiServerTarget
Get-IscsiVirtualDisk
iSCSI (Run on machine importing disks)
Import-Module iSCSI
Get-IscsiTargetPortal
Get-IscsiConnection
Get-IscsiSession
Get-IscsiTarget
Get-Disk | Format-List -Property Path,Size,IsReadOnly
(get-wmiobject -namespace ROOT\WMI -class MSiSCSIInitiator_SessionClass).Devices | Where-Object {$_.LegacyName -ne $null} | Select-Object -Property LegacyName,DeviceInterfaceName -unique | Format-List
Disable Access to a iscsi target from a given machine
Import-Module IscsiTarget
Get-IscsiServerTarget | Format-List -Property TargetName,InitiatorIds
TargetName : target1
InitiatorIds : {IPAddress:192.168.117.210, IPAddress:10.0.0.220, IPAddress:192.168.117.211, IPAddress:10.0.0.221}
TargetName : target2
InitiatorIds : {IPAddress:10.0.0.221, IPAddress:192.168.117.210, IPAddress:10.0.0.220, IPAddress:192.168.117.211
Set-IscsiServerTarget -TargetName target2 -InitiatorId @(“IPAddress:192.168.117.210″,”IPAddress:10.0.0.220″,”IPAddress:192.168.117.211”)
Immediately the iscsi session drops!
SQL Server Clustering (FCI) setup using Cluster Shared Volumes with multipathed iSCSI disks
Posted: January 5, 2016 Filed under: SQL Server FCI, SQL Server Management Studio, Uncategorized, Windows ACtive Directory, Windows Clustering, Windows Domain Controller Setup | Tags: Microsoft SQL Server, Microsoft SQL Server 2016, Microsoft SQL Server Management Studio, Windows Active Directory, Windows Clustering Leave a commentJust finished an article on SQL Server Clustering (FCI) setup using
- Windows Server 2016 Preview 4 cluster nodes
- SQL Server 2016 CTP 3.2
- Windows Server 2016 Preview 4 iSCSI storage using iSCSI targets as disks
- Cluster Shared Volumes on top of the iSCSI disks
- Multi-subnet setup
- Domain Controller Setup
- SSMS December 2015 release to test connections
http://smooth1.co.uk/installs/dbinstalls.html#3.1.14
just dave info 