Thursday, November 1, 2018

VirtualBox SQL FCI - Given Network Name Is Unusable

VirtualBox SQL FCI - Given Network Name Is Unusable

The other day I needed to test some HADR requirements on my local machine so using VirtualBox, I created a 2 server Windows 2012 R2 failover cluster with shared storage provided by FreeNAS and a domain controller. All was good at the windows level so I proceeded to install a SQL cluster next but part way through the setup of creating the SQL cluster, I was hit with this error message:
The given network name is unusable because there was a failure trying to determine if the network name is valid for use by the clustered SQL instance due to the following error: 'The network address is invalid.'
I couldn't see anything obvious at first. I was using the domain admin account so nowt wrong with permissions. All VM's were up, IP was configured OK, cluster was OK and there nothing of use from the setup log file. So drawing a blank next I hit Google. I eventually came across a VMware based StackExchange post suggesting to remove the Guest additions but one of the comments said that this worked for VirtualBox as well. I was a bit sceptical at first, but I uninstalled the additions software from the guest OS and after rebooting the SQL setup continued perfectly!.. No idea what or why the additions where doing what they were, but problem solved with a relatively issue tweak to the OS. I only had the issue when creating the cluster with the first node. The second node was fine being added to the existing cluster with the guest additions software still being installed. Bizarre, anyway, I hope this helps someone with the same issue.
Enjoy!...

Monday, October 29, 2018

High availability for NFS on Azure VMs on Ubuntu 18.04 LTS

I was trying to follow this guide however realised that I do not have SUSE enterprise license to play with, generally I am more familiar with Red Hat hower I decided to give ubuntu a go this time.

https://docs.microsoft.com/en-us/azure/virtual-machines/workloads/sap/high-availability-guide-suse-nfs 

a few important adjustment need to be done to make this working:

Installation: 

sudo apt-get install -y drbd8-utils
sudo apt-get install nfs-kernel-server
sudo apt install pacemaker corosync fence-agents
sudo apt install crmsh
sudo apt install netcat

#I can't get azure fencing agent working so I used sbd fencing
#sudo pip install azure
#sudo pip install azure-common
#sudo pip install azure-mgmt-compute

sudo apt install sbd

adjust nfs v4 grace timeout:

sudo vi /etc/default/nfs-kernel-server
Add:  NFSD_V4_GRACE=10

full configuration for pacemaker: 

george@nfs-1:~$ sudo crm configure show
node 1: nfs-0
node 2: nfs-1
primitive drbd_NW1_nfs ocf:linbit:drbd \
        params drbd_resource=NW1-nfs \
        op monitor interval=15 role=Master \
        op monitor interval=30 role=Slave
primitive exportfs_NW1 exportfs \
        params directory="/srv/nfs/NW1" options="rw,no_root_squash,crossmnt" clientspec="*" fsid=1 wait_for_leasetime_on_stop=true \
        op monitor interval=30s \
        op start interval=0 timeout=40 \
        op stop interval=0 timeout=120 \
        meta target-role=Started
primitive fs_NW1_sapmnt Filesystem \
        params device="/dev/drbd0" directory="/srv/nfs/NW1" fstype=ext4 \
        op monitor interval=10s
primitive nc_NW1_nfs anything \
        params binfile="/bin/netcat" cmdline_options="-l -k 61000"  \
        op monitor timeout=20s interval=10 depth=0
primitive nfsserver lsb:nfs-kernel-server \
        op monitor interval=30s
primitive stonith-sbd stonith:external/sbd \
        params pcmk_delay_max=15 \
        op monitor interval=15 timeout=15
primitive vip_NW1_nfs IPaddr2 \
        params ip=10.0.1.100 cidr_netmask=24 \
        op monitor interval=10 timeout=20
group g-NW1_nfs nfsserver vip_NW1_nfs fs_NW1_sapmnt exportfs_NW1 \
        meta target-role=Started
ms ms-drbd_NW1_nfs drbd_NW1_nfs \
        meta master-max=1 master-node-max=1 clone-max=2 clone-node-max=1 notify=true interleave=true
colocation col-NW1_nfs_on_drbd inf: g-NW1_nfs ms-drbd_NW1_nfs:Master
order o-NW1_drbd_before_nfs inf: ms-drbd_NW1_nfs:promote g-NW1_nfs:start
property cib-bootstrap-options: \
        have-watchdog=false \
        dc-version=1.1.18-2b07d5c5a9 \
        cluster-infrastructure=corosync \
        cluster-name=NW1-nfs \
        stonith-enabled=true \
        no-quorum-policy=ignore \
        maintenance-mode=false \
        stonith-timeout=144
rsc_defaults rsc-options: \
        resource-stickiness=200




Thursday, April 12, 2018

Re: Can´t create a relationship between two columns because one of the columns must have unique valu

 encountered this issue when trying to establish the relationship between two tables, however when I used group by statement to find the duplication, nothing turned up.

I then imported these two tables into Power BI and use the countrows and distinctcount which revealed that there are indeed duplicated rows.

I then created a calculated column as below and put it into a grid , filter the RecordCount>1

RecordCount = COUNTROWS(filter(ALL('dw DmResources'), 'dw DmResources'[ResourceID]=EARLIER('dw DmResources'[ResourceID])) )

the above step showed what's duplicated.

I did a select with like statement in SQL to find the duplicated records, copy and paste to Excel and compare the two duplicated string char by char and Excel told me that the different was a space CHAR(32)

the funny thing started here, I used LTRIM in sql to remove the trailing space, I also tried repalce(xx, char(32),'') , no luck.

At the end I use the ASCII(SUBSTRING(xxx, m, k)) in the sql query and it told me that the difference is actually a TAB (CHAR(9) ), apparently when copying from SQL output to Excel, the process automatically convert the TAB to a space???  maybe it is due to the fact that  in Excel you can't enter a tab because it is a reserved function key?

Problem finally solved after all these drama.