SQL Server Availability Groups – items to check
Posted: August 2, 2017 Filed under: Availability Groups, Microsoft SQL Server | Tags: Availability Groups, Microsoft SQL Server Leave a commentWhen there is an availability group issue
Run the following set of queries on the primary:
SELECT cluster_name,quorum_type_desc,quorum_state_desc FROM sys.dm_hadr_cluster;
SELECT member_name,member_type_desc,member_state_desc,number_of_quorum_votes
FROM sys.dm_hadr_cluster_members
ORDER BY member_name;
SELECT primary_replica,primary_recovery_health_desc,synchronization_health_desc
FROM sys.dm_hadr_availability_group_states;
SELECT * FROM sys.dm_hadr_availability_replica_cluster_nodes ORDER BY replica_server_name;
SELECT A.replica_server_name,A.join_state_desc,B.role_desc,B.operational_state_desc,
B.connected_state_desc,B.recovery_health_desc,B.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_cluster_states A,
sys.dm_hadr_availability_replica_states B
WHERE A.replica_id = B.replica_id and A.group_id = B.group_id
ORDER BY replica_server_name;
SELECT A.replica_server_name,B.database_name,B.is_failover_ready,B.is_database_joined,
C.synchronization_state_desc,C.synchronization_health_desc,C.database_state_desc
FROM sys.dm_hadr_availability_replica_cluster_states A,
sys.dm_hadr_database_replica_cluster_states B,
sys.dm_hadr_database_replica_states C
WHERE A.replica_id = B.replica_id and
B.replica_id = C.replica_id and
B.group_database_id = C.group_database_id
ORDER BY replica_server_name;
and check the following items:
- SQL Server Errorlogs
- Windows cluster log – Powershell Get-ClusterLog -> %WINDIR%\cluster\reports -> Cluster.log
- Windows System event log
- Clustered diagnostic log files in the SQL Server \LOG directory with file names SRVNAME_SQLINSTANCENAME_SQLDIAG_XXX.XEL. The cluster diagnostic log contents can be viewed and filtered by opening the files in SQL Server Management Studio.
Also check items in
- Accounts – Same domain account+login in master on both servers OR different domain accounts+login in master on both servers+grant the account connect on the mirroring endpoint OR use certificates.
- Check mirroring endpoints with correct port and in STATE=STARTED
- Check login on other server has connect permission on the mirroring endpoint
- Check endpoint URL, fully qualifeid domain name guaranteed to work
- Check connectivity to the endpoint port from the other machine in both directions
- Check READ_ONLY_ROUTING_URL port connectivity.
and
- Open Clustered diagnostic log files in SSMS and filter on state_desc=error
- Open Cluster diagnostic logs and check for name component_health_result and availability_group_is_alive_failure
- Open the Cluster Log and check for “is not healthy” and “SQL Server Availability Group”
and
- Check Windows Cluster Log for failoverCount and check Failover Cluster Manager->Roles->Properties->Failover tab->Maximum Failures in the Specified Period
- SQL Server Database Engine resource DLL connects to the instance of SQL Server that is hosting the primary replica by using ODBC in order to monitor health. NT AUTHORITY\SYSTEM login account needs Alter Any Availability Group,Connect SQL,View server state on secondary replicas. Check Windows Cluster Log for messages like “Failed to run diagnostics command” and “The user does not have permission to perform this action”
- Use queries below to check secondary replica is in SYNCHRONIZED status and is_failover_ready=1.
- The attempt to create network name and IP address for the listener is failed.
- Check that if the ‘Primary DNS suffix of this computer’ is configured correctly
- Add start up account of cluster service to SQL Server login and grant sysadmin role (Start up account of cluster service will be nt authority\system by default).
Also Failover Cluster Manager->Services and applications->AG Properties->Increase VerboseLogging .
SQL Server Management Studio – SQL Server Diagnostics Extension
Posted: June 26, 2017 Filed under: Uncategorized Leave a commentThe SQL Server engineering (TIGER) team announcement a preview for a SSMS SQL Server Diagnostics extension.
My writeup on this is at http://smooth1.co.uk/sqlserver2017/SSMS_SSD.html
SQL Server on Linux – new command line tools.
Posted: May 28, 2017 Filed under: Microsoft SQL Server, Microsoft SQL Server 2017, SQL Server on Linux, Uncategorized | Tags: Microsoft SQL Server, Microsoft SQL Server 2017 Leave a commentI have been playing with the new SQL Server on Linux command line tools
http://smooth1.co.uk/sqlserver2017/LINUX_CMD_TOOLS.html
Very nice that sql-scripter has an option to limit output to a given SQL Server Version and even better Edition although did find an issue with this option and will be providing feedback to Microsoft.
SQL Server on Linux – SQL Server 2017 goes cross platform!
Posted: May 23, 2017 Filed under: Linux, Microsoft SQL Server, SQL Server on Linux, Uncategorized | Tags: Linux, Microsoft SQL Server Leave a commentHere are the slides for my recent talks on SQL Server on Linux – SQL Server 2017 goes cross platform!
http://smooth1.co.uk/presents/201705_SSOL/201705_SSOL.zip
SQL Server – Changing Recovery Model from Full to Bulk Logged whilst a Tranasction is active.
Posted: May 10, 2017 Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized | Tags: Microsoft, Microsoft SQL Server, Microsoft SQL Server 2016 Leave a commentIf have a SQL Server database and change the Recovery Model from Full to Bulk Logged whilst a transaction is open what happens?
If the ongoing transaction which was started under Recovery Model Full does an operation which can be minimally logged what happens?
Does the operation become minimally logged which then means the VLF is tagged as a minimally logged logfile which then does not allow certain operations e.g. restores from the next log backup with STOPAT.
We test with STOPAT and also use fn_dump_dblog to see exactly what ends up in the log backup and how we can identify minimally logged operations in a log backup file.
http://smooth1.co.uk/sqlserver2016/RM_BL_ML.html
SQL Server 2016 – Database Scoped Configuration Parameters and Always On Availability Group failovers.
Posted: May 10, 2017 Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized | Tags: Microsoft, Microsoft SQL Server, Microsoft SQL Server 2016 Leave a commentIn SQL Server 2016 we have database scoped parameters.
With an AlwaysOn Availability Groups we can have different database scoped parameter values on the primary compare to the secondaries.
How does this work with an Always On Availability Group failover ?
http://smooth1.co.uk/sqlserver2016/AA_AG_DSP.html
SQL Server – checking for Instant File Initialization Permissions
Posted: May 9, 2017 Filed under: Microsoft SQL Server, Uncategorized | Tags: Microsoft SQL Server Leave a commentTo check for Instant File Initialization Permissions:
- Go to https://gallery.technet.microsoft.com/scriptcenter/Grant-Revoke-Query-user-26e259b0
- Download UserRights.ps1
- Add privilege SeDelegateSessionUserImpersonatePrivilege to the list within the script
- Download http://smooth1.co.uk/powershell/CheckSQLServerUserRights.ps1
NOTE: This script needs to be locally on the machine where you are checking for permissions.
The script assumes the SQL Servers are running under a server where the DisplayName starts with “SQL Server (“, please adapt for your own needs.
DB2 LUW announcements at IDUG NA 2017
Posted: May 3, 2017 Filed under: DB2 10, DB2 11.1, DB2 9.7, DB2 LUW | Tags: DB2 10, DB2 11.1, DB2 9.7, DB2 LUW Leave a commentFrom Peter Bradford @scend we have:
#1 We made requests for enhancements (RFEs) public!
#2: DB2 Developer-C! All DB2 features, free for development, a MySQL substitute! Download 6/22
#3: The new “Download & Go” installation experience, available with Developer-C Download 6/2. Download a platform specific binary. Small download, less than 15 minutes to install. Install reduced to 3 clicks and User/Password input only.
#4: We will support DB2 V11 on Solaris Sparc later this year with our V11.1.3.3 release, by 12/15/17 (if not sooner)
#5: New “Continuous Support” offering for customers on DB2 V9.7 and V10.1!
How To and Workaround assistance, no new defect fixes
This still provides existing fixed but no new custom special builds or codes fixed
If you have questions contact
Email: askcs@us.ibm.com
Twitter: @db2support or https://twitter.com/db2_support
Setting up password-less ssh on CentOS
Posted: April 28, 2017 Filed under: Uncategorized Leave a commentFirst set the hostname on both machines:
hostnamectl status
hostnamectl set-hostname newhostname
Add both hostnames to /etc/hosts on both machine
On source machine:
ssh-keygen
ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host
On target machine:
restorecon -Rv ~/.ssh
ssh remote-host
Informix write listener – Rest API setup issues
Posted: April 25, 2017 Filed under: Informix, Informix 12, Informix wire listener | Tags: Informix, Informix 12, Informix wire listener Leave a commentFor my talk at IIUG 2017 I was working with the wire listener on Informix.
When setting up a wire listener for the REST API I found some Informix packaging issues and issues with the DeveloperWorks article.
I was following the developerWorks article Informix and NoSQL: First Steps with the REST API
NOTE: This series of articles REALLY helped when getting my talk ready!
I was working with the latest Informix server version 12.10.FC8 on CentOS 7 and found:
a) When unpacking nosql_sdk.zip this unpacks to folder nosql-sdk-1.2.2 – there was no explict step with the command to add a link called nosql_sdk to make the examples work, I add this below.
cd $INFORMIXDIR
unzip bin/nosql_sdk.zip
ln -s nosql-sdk-1.2.2 nosql_sdk
b) The library ${INFORMIXDIR}/nosql_sdk/tomcat-embed-core.jar is missing!
Noticing that the other libaries were for version 8.0.38 I went to
and on the “Files” line clicked the download link.
I then copied tomcat-embed-core-8.0.38.jar into $INFORMIXDIR/nosql_sdk/lib
and added this to the classpath.
3. In order to get class com.ibm.nosql.server.ListenerCLI I also had to add ${INFORMIXDIR}/nosql_sdk/com.ibm.nosql.informix-1.2.2.jar to the classpath.
The final result was:
cat rest_listener_start.sh
#!/bin/sh
${INFORMIXDIR}/extend/krakatoa/jre/bin/java \
-cp ${INFORMIXDIR}/bin/jsonListener.jar:${INFORMIXDIR}/nosql_sdk/lib/tomcat-embed-core-8.0.38.jar:${INFORMIXDIR}/nosql_sdk/com.ibm.nosql.informix-1.2.2.jar \
com.ibm.nosql.server.ListenerCLI \
-config ${INFORMIXDIR}/etc/restListener.properties \
-logfile /work/martinfu/731/restListener.log \
-start &
exit
cat rest_listener_stop.sh
#!/bin/sh
${INFORMIXDIR}/extend/krakatoa/jre/bin/java \
-cp ${INFORMIXDIR}/bin/jsonListener.jar:${INFORMIXDIR}/nosql_sdk/lib/tomcat-embed-core-8.0.38.jar:${INFORMIXDIR}/nosql_sdk/com.ibm.nosql.informix-1.2.2.jar \
com.ibm.nosql.server.ListenerCLI \
-config ${INFORMIXDIR}/etc/restListener.properties \
-stop
exit
I hope this helps and am talking to IBM about the issues I found.