Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for January, 2018

An existing connection was forcibly closed by the remote host

Posted by Simon Cho on 01/19/2018

A couple of cases for this error message.

  1. Network device hardware failure.
  2. Old Driver version
  3. SQL bug. Please check the latest patch.
  4. TCP Chimney setting and the SyncAttackProtect setting.
  5. AG listener or Endpoint port misconfiguration.
  6. Encryption/Decryption issue during data transit.
    1. SSL or TLS cipher mismatch
    2. AG endpoint encryption method discrepancy.
  7. During AG or Mirroring failover transit.
  8. Network packet loss due to any reason
    1. Firewall blocking for certain case.
    2. Jumbo frame or MTU size misconfiguration.
  9. Login Authentication issue.

https://sqlperformance.com/2013/11/system-configuration/ag-connectivity

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2013/12/03/tcp-chimney-setting-and-sql-server-error-tcp-provider-an-existing-connection-was-forcibly-closed-by-the-remote-host/

https://blogs.msdn.microsoft.com/docast/2017/07/27/sql-connectivity-troubleshooting-checklist/

https://technet.microsoft.com/en-us/library/ms187005(v=sql.105).aspx

https://documentation.red-gate.com/clone2/troubleshooting/installation-issues/an-existing-connection-was-forcibly-closed-by-the-remote-host-error

http://sirsql.net/content/2016/11/09/availability-groups-issue-with-2016-cu2/

Posted in Common | Tagged: | Leave a Comment »

Meltdown and Spectre update for SQL 2012 and SQL 2014

Posted by Simon Cho on 01/18/2018

Finally, MS release new service pack update for SQL 2012 and SQL 2014.

Please follow the below link for guidance for SQL server.

All official links keep updating in the same article.

SQL Server – https://support.microsoft.com/en-us/help/4073225/guidance-for-sql-server
Windows Server – https://support.microsoft.com/en-us/help/4072698/windows-server-guidance-to-protect-against-the-speculative-execution

 

Here is the recent update for SQL 2012 and SQL 2014.

4057117 Description of the security update for SQL Server 2014 SP2 CU: January 16, 2018
4057120 Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018
4057116 Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018 

 

Here are all supported versions’ link.

SQL Server 2017 GDR
SQL Server 2016 SP1 CU7*
SQL Server 2016 SP1 GDR
SQL Server 2016 RTM CU
SQL Server 2016 RTM GDR 
SQL Server 2008 SP4
SQL Server 2008 R2 SP3
SQL Server 2012 SP4 GDR
SQL Server 2012 SP3 CU
SQL Server 2012 SP3 GDR
SQL Server 2014 SP2 CU*
SQL Server 2014 SP2 GDR 

Posted in Common | Tagged: , , , , | Leave a Comment »

Meltdown and Spectre

Posted by Simon Cho on 01/10/2018

<Google Prject Zero>

It started from “Google Project Zero”.

https://googleprojectzero.blogspot.com/

Variants of this issue are known to affect many modern processors, including certain processors by Intel, AMD and ARM. For a few Intel and AMD CPU models, we have exploits that work against real software. We reported this issue to Intel, AMD and ARM on 2017-06-01.

 

Here is the guide line for SQL Server and Windows Server.

SQL Server – https://support.microsoft.com/en-us/help/4073225/guidance-for-sql-server
Windows Server – https://support.microsoft.com/en-us/help/4072698/windows-server-guidance-to-protect-against-the-speculative-execution

SQL Server Patch available for below version.

SQL 2012 and SQL 2014 should release soon.

SQL Server 2017 CU3*
SQL Server 2017 GDR
SQL Server 2016 SP1 CU7*
SQL Server 2016 SP1 GDR
SQL Server 2016 RTM CU
SQL Server 2016 RTM GDR
SQL Server 2008 SP4 (This is new version of SP4. Version number is slightly different.)
SQL Server 2008 R2 SP3(This is new version of SP3. Version number is slightly different.)

It seems like not that many articles reported SQL 2008 SP4 and SQL Server 2008 R2 SP3 with this patch.

 

<Here is the related blogs and articles>

https://www.brentozar.com/archive/2018/01/sql-server-patches-meltdown-spectre-attacks/

https://www.sqlskills.com/blogs/glenn/microsoft-sql-server-updates-for-meltdown-and-spectre-exploits/

 

Perfermance

https://cloudblogs.microsoft.com/microsoftsecure/2018/01/09/understanding-the-performance-impact-of-spectre-and-meltdown-mitigations-on-windows-systems/

 

ploited Vulnerability CVE Exploit
Name		 Public Vulnerability Name Windows Changes Silicon Microcode Update ALSO Required on Host
Spectre 2017-5753 Variant 1 Bounds Check Bypass Compiler change; recompiled binaries now part of Windows Updates

Edge & IE11 hardened to prevent exploit from JavaScript

 No
Spectre 2017-5715 Variant 2 Branch Target Injection Calling new CPU instructions to eliminate branch speculation in risky situations Yes
Meltdown 2017-5754 Variant 3 Rogue Data Cache Load Isolate kernel and user mode page tables No

In general, our experience is that Variant 1 and Variant 3 mitigations have minimal performance impact, while Variant 2 remediation, including OS and microcode, has a performance impact.

  • With Windows 10 on newer silicon (2016-era PCs with Skylake, Kabylake or newer CPU), benchmarks show single-digit slowdowns, but we don’t expect most users to notice a change because these percentages are reflected in milliseconds.
  • With Windows 10 on older silicon (2015-era PCs with Haswell or older CPU), some benchmarks show more significant slowdowns, and we expect that some users will notice a decrease in system performance.
  • With Windows 8 and Windows 7 on older silicon (2015-era PCs with Haswell or older CPU), we expect most users to notice a decrease in system performance.
  • Windows Server on any silicon, especially in any IO-intensive application, shows a more significant performance impact when you enable the mitigations to isolate untrusted code within a Windows Server instance. This is why you want to be careful to evaluate the risk of untrusted code for each Windows Server instance, and balance the security versus performance tradeoff for your environment.

 

Posted in Common | Tagged: , , , | Leave a Comment »

SQL Compression backup with TDE

Posted by Simon Cho on 01/10/2018

SQL Compression backup wasn’t work before SQL 2016.

 

<SQL Server 2014 and below>

https://msdn.microsoft.com/library/bb934049(SQL.120).aspx

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

 

<After SQL server 2016 and above>

It could work with special command.!

https://blogs.msdn.microsoft.com/sqlcat/2016/06/20/sqlsweet16-episode-1-backup-compression-for-tde-enabled-databases/

It is important to know that while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536. In fact, 65537 will do just good. It is recommended that you determine your optimum MAXTRANSFERSIZE through testing, based on your workload and storage subsystem. The default value of MAXTRANSFERSIZE for most devices is 1 MB, however, if you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped.

 

Please Note :

Update April 6th, 2017

We have recently discovered some issues related to the use of TDE and backup compression in SQL Server 2016. While we fix them, here are some tips to help you avoid running into those known issues:

  • Currently it is not advisable to use striped backups with TDE and backup compression
  • If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.
  • Avoid using WITH INIT for now when working with TDE and backup compression. Instead, for now you can use WITH FORMAT.

Posted in Common | Tagged: , , | Leave a Comment »