SQL Server Always On Availability Group Performance
Posted: April 5, 2018 Filed under: Availability Groups, Microsoft SQL Server, Scheduling and Yielding | Tags: Availability Groups, Microsoft SQL Server Leave a commentSee
which references
SQL Server on Linux update
Posted: March 31, 2018 Filed under: Microsoft SQL Server, Microsoft SQL Server Replication, SQL Server on Linux | Tags: Microsoft SQL Server, Microsoft SQL Server Linux, SQL Server Replication Leave a commentSQL Server on Linux
Since 2017 CU4, the SQL Server Agent is no longer a seperate package.
To enable SQL Server Agent:
either
- sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
- sudo systemctl restart mssql-server
OR
- During setup export MSSQL_AGENT_ENABLED=’true’
Replication Agents on Linux are in preview aka.ms/sqleap
- Replication Agents are packaged in the server package.
- Replication can be setup across Windows/Linux and use AD Auth
- Docker support is currently being tested and will come soon.
- Replication Agents on Linux can be managed via SSMS on Windows and normally tools as mainly done via stored procs
- Snapshot,Transactional and Merge Replication are supported
- Publisher,Distribtutor or Subscriber can be on Linux
- SQLOps Studio can be used to configure snapshot replication via stored procs
CPU Issues and Speculative Execution
Posted: January 28, 2018 Filed under: CPU Issues, Linux kernel, Security, Uncategorized | Tags: CPU Issues, Linux, Security Leave a commenthttp://smooth1.co.uk/security/CPU_issues.html
Updated Linux kernel patches and added HardenedBSD.
CPU Issues and Speculative Execution
Posted: January 22, 2018 Filed under: CPU Issues, Linux kernel, Security | Tags: CPU Issues, Linux, Security Leave a commenthttp://smooth1.co.uk/security/CPU_issues.html
added
- Cloud Providers – Linode,Open Telekom Cloud
- Hypervisor – QEMU
- Operating Systems – FreeBSD,OpenBSD,Gentoo,Container Linux (CoreOS)
- Userland – GCC compiler,WebKit
- Misc Vendor fixes – Raspberry Pi
Updated Linux kernel patches with more information
Windows Process Monitor
Posted: January 21, 2018 Filed under: Debugging, Uncategorized | Tags: Debugging Leave a commentWindows Proccess Monitor – https://docs.microsoft.com/en-gb/sysinternals/downloads/procmon
CPU Issues and Speculative Execution
Posted: January 21, 2018 Filed under: CPU Issues, Security, Solaris | Tags: CPU Issues, F5, Fortinet, Nexenta, Oracle, Pure Storage, Qubes, Rockwell Automation, Scality, Security, Synology, Wonderware Leave a commenthttp://smooth1.co.uk/security/CPU_issues.html
added
- Debian,Fedora
- LLVM,ZFS,Lustre
- Oracle Sparc,RISC-V
- Oracle,Nexenta,Scality,Pure Storage,Wonderware,Rockwell Automation,Synology,F5,Qubes,Qubes,Fortinet,Netap
Updated Google cloud section to mention Retpoline a compile time fix for Variant 2 (part of Spectre) with “almost no performance loss.”
CPU Issues and Speculative Execution
Posted: January 11, 2018 Filed under: Uncategorized Leave a commentA comprehensive blog post on CPU Issues and Speculative Execution:
http://smooth1.co.uk/security/CPU_issues.html
Db2 Version,Release,Modpacks,Fixpacks and Interim Fixes
Posted: December 30, 2017 Filed under: DB2, DB2 LUW, Uncategorized | Tags: DB2, DB2 LUW Leave a comment11.1.2.3 iFix001
11 (Version) seperatedly licensed, major feature changes,starts 5 year maintenance clock,download from Passport Advantage
1 (Release) Minor but significant feature changes,starts 5 year maintenance clock,download from Passport Advantage
2 (Modpack) Can include new functionality,does not start new maintenance clock, download from Fix Central
3 (Fixpack) Cumulative update of all available fixes and APARs,does not start new maintenance clock,download from Fix Central
iFix001 (iFix – Interim Fix) Tested and verified set of small number of key fixes and APARs,cumulative,Downloads found on Fix Central,Supported for the maintenance life of the Version.Release.
Disk Cleanup on Windows Server 2016
Posted: August 11, 2017 Filed under: Microsoft Windows, Microsoft Windows Server, Uncategorized | Tags: Microsoft Windows Leave a commentTo perform a full disk cleanup on Windows Server 2016
Dism.exe /Online /Cleanup-Image /AnalyzeComponentStore
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 .