SQL Server Always On Availability Group PerformancePosted: April 5, 2018 Filed under: Availability Groups, Microsoft SQL Server, Scheduling and Yielding | Tags: Availability Groups, Microsoft SQL Server Leave a comment
SQL Server on Linux updatePosted: 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 comment
SQL Server on Linux
Since 2017 CU4, the SQL Server Agent is no longer a seperate package.
To enable SQL Server Agent:
- sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
- sudo systemctl restart mssql-server
- 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
SQL Server Availability Groups – items to checkPosted: August 2, 2017 Filed under: Availability Groups, Microsoft SQL Server | Tags: Availability Groups, Microsoft SQL Server Leave a comment
When 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;
ORDER BY member_name;
SELECT * FROM sys.dm_hadr_availability_replica_cluster_nodes ORDER BY replica_server_name;
FROM sys.dm_hadr_availability_replica_cluster_states A,
WHERE A.replica_id = B.replica_id and A.group_id = B.group_id
ORDER BY replica_server_name;
FROM sys.dm_hadr_availability_replica_cluster_states A,
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.
- 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”
- 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 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 comment
I have been playing with the new SQL Server on Linux command line tools
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 comment
Here are the slides for my recent talks on SQL Server on Linux – SQL Server 2017 goes cross platform!
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 comment
If 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.
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 comment
In 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 ?
SQL Server – checking for Instant File Initialization PermissionsPosted: May 9, 2017 Filed under: Microsoft SQL Server, Uncategorized | Tags: Microsoft SQL Server Leave a comment
To 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.
SQL Server 2016, upgrading to compatability level 130,Trace flag 139 and additional one-off dbcc checks.Posted: February 27, 2017 Filed under: Microsoft SQL Server, Microsoft SQL Server 2016 | Tags: Microsoft, Microsoft SQL Server Leave a comment
When upgrading to SQL Server 2016 RTM CU3/SP1 and upgrading to database compatablity leve 130 there are additional DBCC checks which should be performed.
These are hidden behind Trace flag 139 which should be temporarily enabled as part of the process of changing database compability level to 130.
- Enable trace flag 139 by running DBCC TRACEON(139, -1).
- Run DBCC CHECKDB/TABLE..WITH EXTENDED_LOGICAL_CHECKS to validate persisted structures
- Run DBCC CHECKCONSTRAINTS commands (if rows are affected the associated where clause to identify the row will be returned).
- Disable trace flag 139 by running DBCC TRACEOFF(139, -1)
- Change the database compatibility level to 130.
- REBUILD any structures that you identified in step 1.
There are impovements to expression evaluation in database level 130 and this affects persisted structures
- Check constraints
- Persisted Computed columns
- Indexes using computing columns whether as part of the key or as included columns
- Filtered indexes
- Indexed views
Upgrade to compatability level 130 BEFORE attempting to fix issues so the new expression evaluation logic is used for the fixed.
- Check constraints – change data or drop/recreate constraint with new expression
- Persisted Computed columns – Update a column referenced by the computed column to the same value to force recalcuation of the computed column
- Index/filtered index/indexed views – Either A) Put db in single user mode and run DBCC CHECKTABLE with REPAIR_REBUILD B) ALTER INDEX…REBUILD and if supported in your edition of sql server consider adding the WITH (ONLINE=ON) clause.
NOTE: There are some queries in the Appendix C/D of the article above which can be used to help identify affected objects.
London BI/SQL Server user groups.Posted: February 20, 2017 Filed under: BI and Analysis, Microsoft SQL Server, Uncategorized | Tags: BI and Analysis, Microsoft SQL Server Leave a comment
If you are BI based and want to meetup with other in London, whether technical or business based here as some contacts.
https://sqlserverfaq.com/ – General sql server (covers lots of usergroups)
http://sqllondon.pass.org/ – London SQL Pass Chapter for SQL Server but BI people do attend as well.
https://www.meetup.com/London-PUG/ London Power BI Users Group – they meet at Skills Matter, 10 South Place near Moorgate tube, big group, lots of people attend – 50+,
https://www.meetup.com/London-Business-Analytics-Group/ – London Business Analytics Group – Very power user/Business based, less technical