Monday, 20 August 2018

SQL Jobs Report






Hilighter On The Page








SQL Jobs Report

Code Below.


SELECT DISTINCT
[sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]

, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, CASE [sJHIS].run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS [LastRunOutcome]
,COALESCE(SUBSTRING(CAST([sJHIS].run_date AS CHAR(8)), 1, 4) + '/'
+ SUBSTRING(CAST([sJHIS].run_date AS CHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST([sJHIS].run_date AS CHAR(8)), 7, 2), '') AS [LastRun]
,COALESCE(SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 1, 4) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 7, 2), '') AS [NextRun]
,STUFF(STUFF(STUFF(next_run_time, 1, 0, REPLICATE('0', 6 - LEN(next_run_time))),3,0,':'),6,0,':') AS [NextRunTime]
, [sSVR].[name] AS [OriginatingServerName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]


FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
JOIN [msdb].[dbo].[sysjobhistory] AS [sJHIS]
ON [sJOB].[job_id] = [sJHIS].[job_id]

LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE ([sJOB].[name] LIKE 'NESI%' OR [sJOB].[name] LIKE 'NICE%' )
AND [sJHIS].run_date = (SELECT MAX(SJH1.run_date) FROM [msdb].[dbo].[sysjobhistory] SJH1 WHERE [sJHIS].job_id = SJH1.job_id)
--ORDER BY [JobName]

UNION
SELECT DISTINCT
[sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]

, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
,'N\A' AS [LastRunOutcome]
,'N\A' AS [LastRun]
,CASE [sJOBSCH].next_run_date
WHEN '0' THEN 'N\A'
ELSE COALESCE(SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 1, 4) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST([sJOBSCH].next_run_date AS CHAR(8)), 7, 2), '') END AS [NextRun]

,STUFF(STUFF(STUFF(next_run_time, 1, 0, REPLICATE('0', 6 - LEN(next_run_time))),3,0,':'),6,0,':') AS [NextRunTime]
, [sSVR].[name] AS [OriginatingServerName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]


FROM
[msdb].[dbo].[sysjobs] AS [sJOB]

LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE ([sJOB].[name] LIKE 'NESI%' OR [sJOB].[name] LIKE 'NICE%' )
AND [sJOB].[name] NOT IN (SELECT
[sJOB].[name] AS [JobName]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
JOIN [msdb].[dbo].[sysjobhistory] AS [sJHIS]
ON [sJOB].[job_id] = [sJHIS].[job_id])









Check last 100 database backups





Check last 100 database backups














Check last 100 database backups

By Himalaya Dua


SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
--WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO








TLS/SSL Settings





Arduino














TLS/SSL Settings


Applies To: Windows Vista, Windows Server 2008, Windows 7, Windows 8.1, Windows Server 2008 R2, Windows Server 2012 R2, Windows Server 2012, Windows 8
This reference topic for the IT professional contains registry setting, Group Policy, and network port information for the Windows implementation of the Transport Layer Security (TLS) protocol and the Secure Sockets Layer (SSL) protocol through the Schannel Security Support Provider (SSP).
This topic is divided into the following sections:

Schannel SSP registry entries

The following registry subkeys and entries can help you administer and troubleshoot the Schannel SSP, specifically the TLS and SSL protocols.
Warning
This information is provided as a reference to use when you are troubleshooting or verifying that the required settings are applied. We recommend that you do not directly edit the registry unless there is no other alternative. Modifications to the registry are not validated by the Registry Editor or by the Windows operating system before they are applied. As a result, incorrect values can be stored, and this can result in unrecoverable errors in the system. When possible, instead of editing the registry directly, use Group Policy or other Windows tools such as the Microsoft Management Console (MMC) to accomplish tasks. If you must edit the registry, use extreme caution.

CertificateMappingMethods

This entry does not exist in the registry by default. The default value is that all four certificate mapping methods, listed below, are supported.
When a server application requires client authentication, Schannel automatically attempts to map the certificate that is supplied by the client computer to a user account. You can authenticate users who sign in with a client certificate by creating mappings, which relate the certificate information to a Windows user account. After you create and enable a certificate mapping, each time a client presents a client certificate, your server application automatically associates that user with the appropriate Windows user account.
In most cases, a certificate is mapped to a user account in one of two ways:
  • A single certificate is mapped to a single user account (one-to-one mapping).
  • Multiple certificates are mapped to one user account (many-to-one mapping).
By default, the Schannel provider will use the following four certificate mapping methods, listed in order of preference:
  1. Kerberos service-for-user (S4U) certificate mapping
  2. User principal name mapping
  3. One-to-one mapping (also known as subject/issuer mapping)
  4. Many-to-one mapping
Applicable versions: As designated in the Applies To list that is at the beginning of this topic
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

Ciphers

This entry does not exist in the registry by default. For information about ciphers that are used by the Schannel SSP, see Supported Cipher Suites and Protocols in the Schannel SSP.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
To disable a cipher, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. When you disable any algorithm, you disallow all cipher suites that use that algorithm. To enable the cipher, change the DWORD value to 1.

CipherSuites

Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\CipherSuites
To disable a cipher suite, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. When you disable any algorithm, you disallow all cipher suites that use that algorithm. To enable the cipher suite, change the DWORD value to 1.
For information about cipher suites that are used by the Schannel SSP, see Supported Cipher Suites and Protocols in the Schannel SSP.

ClientCacheTime

This entry controls the amount of time that the operating system takes in milliseconds to expire client-side cache entries. A value of 0 turns off secure-connection caching. This entry does not exist in the registry by default.
The first time a client connects to a server through the Schannel SSP, a full TLS/SSL handshake is performed. When this is complete, the master secret, cipher suite, and certificates are stored in the session cache on the respective client and server.
Applicable versions: See the following default client cache time table.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
Default client cache time
Windows Version Time
Windows Server 2012 R2 and Windows 8.1 10 hours
Windows Server 2012 and Windows 8 10 hours
Windows Server 2008 R2 and Windows 7 10 hours
Windows Server 2008 and Windows Vista 10 hours

FIPSAlgorithmPolicy

This entry controls Federal Information Processing (FIPS) compliance. The default is 0.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\LSA
Windows Server FIPS cipher suites: See Supported Cipher Suites and Protocols in the Schannel SSP.

Hashes

This subkey controls use of hash algorithms.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
To disable a hash algorithm, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. When you disable any algorithm, you disallow all cipher suites that use that algorithm. To enable the hash algorithm, change the DWORD value to 1.
Hashes subkey table
Subkey Description Default
MD5 Controls the use of MD5 as the hashing algorithm. Enabled
SHA Controls the use of SHA1, SHA256, and SHA384 as the hashing algorithm. Enabled
Note
SHA256 and SHA384 were introduced in Windows Server 2008 R2.
For a list of supported cipher suites and hash algorithms, see Supported Cipher Suites and Protocols in the Schannel SSP.

IssuerCacheSize

This entry controls the size of the issuer cache, and it is used with issuer mapping. The Schannel SSP attempts to map all of the issuers in the client’s certificate chain—not only the direct issuer of the client certificate. When the issuers do not map to an account, which is the typical case, the server might attempt to map the same issuer name repeatedly, hundreds of times per second.
To prevent this, the server has a negative cache, so if an issuer name does not map to an account, it is added to the cache and the Schannel SSP will not attempt to map the issuer name again until the cache entry expires. This registry entry specifies the cache size. This entry does not exist in the registry by default. The default value is 100.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

IssuerCacheTime

This entry controls the length of the cache timeout interval in milliseconds. The Schannel SSP attempts to map all of the issuers in the client’s certificate chain—not only the direct issuer of the client certificate. In the case where the issuers do not map to an account, which is the typical case, the server might attempt to map the same issuer name repeatedly, hundreds of times per second.
To prevent this, the server has a negative cache, so if an issuer name does not map to an account, it is added to the cache and the Schannel SSP will not attempt to map the issuer name again until the cache entry expires. This cache is kept for performance reasons, so that the system does not continue trying to map the same issuers. This entry does not exist in the registry by default. The default value is 10 minutes.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

KeyExchangeAlgorithm

This subkey controls the use of key exchange algorithms.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
To disable a key exchange algorithm, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. When you disable any algorithm, you disallow all cipher suites that use that algorithm. To enable the key exchange algorithm, change the DWORD value to 1.
Exchange algorithm subkey table
Subkey Description Default
Diffie-Hellman\Enabled Controls the use of Diffie-Hellman (DH) for key exchange. Enabled
PKCS Controls the use of RSA for key exchange. Enabled

MaximumCacheSize

This entry controls the maximum number of cache elements. Setting MaximumCacheSize to 0 disables the server-side session cache and prevents reconnection. Increasing MaximumCacheSize above the default values causes Lsass.exe to consume additional memory. Each session-cache element typically requires 2 to 4 KB of memory. This entry does not exist in the registry by default. The default value is 20,000 elements.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

PCT 1.0

This subkey controls the use of the Private Communications Transport (PCT) protocol.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To disable the PCT protocol, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. To enable the protocol, change the DWORD value to 1.
PCT subkey table
Subkey Description Default
Client Controls the use of the PCT protocol on the client Enabled
Server Controls the use of the PCT protocol on the server Enabled
DisabledByDefault Controls disabling the PCT protocol by default. This entry does not exist in the registry by default. Enabled

SendTrustedIssuerList

This entry controls the flag that is used when the list of trusted issuers is sent. In the case of servers that trust hundreds of certification authorities for client authentication, there are too many issuers for the server to be able to send them all to the client computer when requesting client authentication. In this situation, this registry key can be set, and instead of sending a partial list, the Schannel SSP will not send any list to the client.
Not sending a list of trusted issuers might impact what the client sends when it is asked for a client certificate. For example, when Internet Explorer receives a request for client authentication, it only displays the client certificates that chain up to one of the certification authorities that is sent by the server. If the server did not send a list, Internet Explorer displays all of the client certificates that are installed on the client.
This behavior might be desirable. For example, when PKI environments include cross certificates, the client and server certificates will not have the same root CA; therefore, Internet Explorer cannot chose a certificate that chains up to one of the server’s CAs. By configuring the server to not send a trusted issuer list, Internet Explorer will send all its certificates.
This entry does not exist in the registry by default. This value is True by default.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

ServerCacheTime

This entry controls the amount of time in milliseconds that the operating system takes to expire server-side cache entries. A value of 0 disables the server-side session cache and prevents reconnection. Increasing ServerCacheTime above the default values causes Lsass.exe to consume additional memory. Each session cache element typically requires 2 to 4 KB of memory. This entry does not exist in the registry by default.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
Default server cache time: 10 hours

SSL 2.0

This subkey controls the use of SSL 2.0.
SSL 2.0 is disabled by default on Windows client computers.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic excluding Windows client versions.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To disable the SSL 2.0 protocol, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. To enable the protocol, change the DWORD value to 1.
SSL 2.0 subkey table
Subkey Description Default
Client Controls the use of SSL 2.0 on the client. Disabled
Server Controls the use of SSL 2.0 on the server. Enabled
DisabledByDefault Flag to disable SSL 2.0 by default. Enabled

SSL 3.0

This subkey controls the use of SSL 3.0.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To disable the SSL 3.0 protocol, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. To enable the protocol, change the DWORD value to 1.
SSL 3.0 subkey table
Subkey Description Default
Client Controls the use of SSL 3.0 on the client. Enabled
Server Controls the use of SSL 3.0 on the server. Enabled
DisabledByDefault Flag to disable SSL 3.0 by default. Enabled

TLS 1.0

This subkey controls the use of TLS 1.0.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To disable the TLS 1.0 protocol, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. To enable the protocol, change the DWORD value to 1.
TLS 1.0 subkey table
Subkey Description Default
Client Controls the use of TLS 1.0 on the client. Enabled
Server Controls the use of TLS 1.0 on the server. Enabled
DisabledByDefault Flag to disable TLS 1.0 by default. Enabled

TLS 1.1

This subkey controls the use of TLS 1.1.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic excluding those versions prior to Windows Server 2008 R2 and Windows 7.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To disable the TLS 1.1 protocol, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. To enable the protocol, change the DWORD value to 1.
TLS 1.1 subkey table
Subkey Description Default
Client Controls the use of TLS 1.1 on the client. Enabled
Server Controls the use of TLS 1.1 on the server. Enabled
DisabledByDefault Flag to disable TLS 1.1 by default. Enabled

TLS 1.2

This subkey controls the use of TLS 1.2.
Note: For TLS 1.2 to be enabled and negotiated on servers that run Windows Server 2008 R2, you MUST create the "DisabledByDefault" entry in the appropriate subkey (Client, Server) and set it to "0". The entry will not be seen in the registry and it is set to "1" by default.
Applicable versions: As designated in the Applies To list that is at the beginning of this topic excluding those versions prior to Windows Server 2008 R2 and Windows 7.
Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To disable the TLS 1.2 protocol, create an Enabled entry in the appropriate subkey. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 0. To enable the protocol, change the DWORD value to 1.
TLS 1.2 subkey table
Subkey Description Default
Client Controls the use of TLS 1.2 on the client. Enabled
Server Controls the use of TLS 1.2 on the server. Enabled
DisabledByDefault Flag to disable TLS 1.2 by default. Enabled

OCSP stapling

Online Certificate Status Protocol (OCSP) stapling enables a web server, such as Internet Information Services (IIS), to provide the current revocation status of a server certificate when it sends the server certificate to a client during the TLS handshake. This feature reduces the load on OCSP servers because the web server can cache the current OCSP status of the server certificate and send it to multiple web clients. Without this feature, each web client would try to retrieve the current OCSP status of the server certificate from the OCSP server. This would generate a high load on that OCSP server.
By default, OCSP support is enabled for IIS websites that have a simple secure (SSL/TLS) binding. However, this support is not enabled by default if the IIS website is using either or both of the following types of secure (SSL/TLS) bindings:
  • Require Server Name Indication
  • Use Centralized Certificate Store
In this case, the server hello response during the TLS handshake won't include an OCSP stapled status by default. This behavior improves performance: The Windows OCSP stapling implementation scales to hundreds of server certificates. Because SNI and CCS enable IIS to scale to thousands of websites that potentially have thousands of server certificates, setting this behavior to be enabled by default may cause performance issues.
To enable OCSP stapling for SNI and CCS bindings, locate the following registry subkey:
Registry path: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL]
To this subkey, add the following key:
"EnableOcspStaplingForSni"=dword:00000001
Note Enabling this registry key has a potential performance impact.

Group Policy settings

The following table lists and describes the Group Policy setting that is associated with the Schannel SSP.
Group Policy setting associated with Schannel SSP
Group Policy Setting Description
System cryptography: Use FIPS-compliant algorithms for encryption, hashing, and signing.

(This is a Security options Group Policy setting.)
Changes to this setting determine whether the Schannel SSP will support the TLS protocol as a client(or as a server, if applicable), and if it will use only the following algorithms:

- Triple Data Encryption Algorithm (Triple DES) for the TLS traffic encryption
- Rivest-Shamir-Adleman (RSA) public key algorithm for the TLS key exchange and authentication
- Secure Hash Algorithm version 1 (SHA-1) for TLS hashing

The client and the server must support these algorithms and TLS to communicate by using a secure channel application. For example, if you enable this policy setting, you also need to configure Internet Explorer to use TLS (which is Off by default) to connect by using Secure Hypertext Transfer Protocol (HTTPS) to a server with this setting.






Change owner of all SQL jobs to SA





Change owner of all SQL jobs to SA














Change owner of all SQL jobs to SA

By Himalaya Dua

DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT [name] FROM msdb..sysjobs
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
@job_name = @name_holder,
@owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END
CLOSE My_Cursor
DEALLOCATE My_Cursor








Tuesday, 8 March 2016

Cleanly Uninstalling Stubborn SQL Server Components

Problem
There are scenarios where SQL Server is difficult or impossible to uninstall, upgrade, or replace (and these can block you from installing a new version or using a specific named or default instance):
  1. An expired Evaluation Edition:
    Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy

  2. An expired Management Studio:
    Your Microsoft SQL Server Management Studio evaluation period has expired.

    You can get a key to extend your trial by registering this copy of Microsoft Management Studio online. You may also purchase a key to activate the product.

  3. Unsupported operating system (after an OS upgrade):
    The operating system on this computer does not meet the minimum requirements for SQL Server xxxx.

  4. Missing MSI files, e.g.:
    Slp: Target package: "C:\...\sql_engine_core_inst.msi"
    Slp: InstallPackage: MsiInstallProduct returned the result code 2.

  5. Too many instances to remove individually, or other various errors during uninstall.
In these situations, is there a way to cleanly uninstall SQL Server?
Solution
There is a built-in command called msiexec which has an uninstall parameter (-x). This command can be used to remove stubborn programs through brute force. First, though, you need to get an inventory of the GUIDs that represent the programs you need to remove. All you see in the Control Panel are the friendly names, as you can see here:
Control Panel : Before
(This is a brand new system where I installed multiple SQL Server 2014 components in order to demonstrate my approach of cleanly removing them without using the Control Panel or SQL Server's own setup, but rather using msiexec and the GUIDs for the products.)
You can get the GUID associated with each installed product by searching around the registry, but I would rather use the MSI Inventory tool, which you can download (msiinv_new.zip) from this OneDrive folder, and extract msiinv.exe to a folder, say C:\temp\.
Once that file is there, you can open a PowerShell console, and run the following code:
c:\temp\msiinv.exe -s | Select-String "SQL Server" -Context 0,1
Here is a truncated version of what my output from that command looks like (there is a benign error you can ignore):
Powershell command output
This output is marginally useful for now; I can quickly scan the list and see all the GUID codes for the products I want to remove, and start mentally filtering any out as necessary. I could manually copy and change the output to feed each GUID individually to msiexec, but ultimately where I want to end up is a set of commands I can run directly from the command line, for example:
rem Microsoft SQL Server System CLR Types
msiexec /x "{C3F6F200-6D7B-4879-B9EE-700C0CE1FCDA}"

rem SQL Server 2014 Integration Services
msiexec /x "{327B1B40-2434-4DC5-9D4D-B9B24D4B2EDE}"

rem SQL Server 2014 SQL Data Quality Common
msiexec /x "{2D95D8C0-0DC4-44A6-A729-1E2388D2C03E}"
  
...
And I get there with a little help from my good buddy, co-worker, and PowerShell officionado, Allen White (@SQLRunr):
$a = c:\temp\msiinv.exe -s | Select–String "SQL Server" -Context 1,1;
$a = $a -replace "Product code: ","msiexec /x """;
$a = $a -replace ">", "rem";
$a = $a -replace "\t", "";
$a = $a -replace "}","}""";
$a | Out-File c:\temp\remove.bat -encoding ascii;
Now, you can open up remove.bat, look through the list, and remove any products you *don't* want to uninstall. The format will look like this, almost exactly how I wanted them:
rem Microsoft SQL Server System CLR Types
  msiexec /x "{C3F6F200-6D7B-4879-B9EE-700C0CE1FCDA}"
  
rem SQL Server 2014 Integration Services
  msiexec /x "{327B1B40-2434-4DC5-9D4D-B9B24D4B2EDE}"

rem SQL Server 2014 SQL Data Quality Common
  msiexec /x "{2D95D8C0-0DC4-44A6-A729-1E2388D2C03E}"

...
Now run remove.bat from the command line. You'll get prompts like this:
Windows Installer prompts
And you'll also likely see some confirmation dialogs from User Account Control. You may get some errors or dependency warnings, and to avoid some of the prompts, you'll want to make sure all related services have been manually stopped. You might want to run from an elevated command prompt (perhaps even using the "super admin" if you're on Windows 10), and also play with some of the switches to msiexec, like /quiet. You can enable /quiet by changing the second last line in the Powershell script above to:
$a = $a -replace "}","}"" /quiet";
Also, you may have to run the script twice to completely remove things that failed the first time due to dependency order. The second time around, if you don't use /quiet, you will see multiple notifications that the product you're trying to remove is no longer installed:
You can only remove a product once
But after running through it twice, this should leave you with a much cleaner Control Panel:
Control Panel : After
The remainder of these items should really be much easier to remove manually; or, you may not want to bother; or, you could repeat the process above with different arguments to Select-String. I wanted to focus on a single run-through with the SQL Server components, since they are the ones that prove most problematic.
Summary
This is not the most intuitive approach in the world, but I hope it helps some of you remove stubborn, sticky SQL Server components from your systems. It constitutes a bit more work than normal uninstall operations, but when Control Panel or SQL Server Setup won't cooperate, this might be the next best thing. (Note that this procedure will not be effective in completely removing a clustered instance of SQL Server; I wouldn't even try.)

Monday, 21 September 2015

Subnetting

Subnetting is a process of dividing large network into the smaller networks based on layer 3 IP address.
IP->
  • 32bit decimal number. 
  • written as four numbers between 1 and 255.
  • divided into 5 classes

  • 0 [Zero] is reserved and represents all IP addresses.
  • 127 is a reserved address and is used for testing, like a loop back on an interface.
  • 255 is a reserved address and is used for broadcasting purposes.
  •  IP address has following parts-
    • First 16 bits : NETWORK ID
    • Next 8 bits : SUBNET ID
    • Next 8 bits : HOST ID
Subnet mask
Subnet mask is a 32 bits long address used to distinguish between network address and host address in IP address.
Subnet mask has only one purpose, to identify which part of an IP address is network address and which part is host address.











Subnetting is a process of breaking large network in small networks known as subnets. Subnetting happens when we extend default boundary of subnet mask. Basically we borrow host bits to create networks.

Let's take a example
Being a network administrator you are asked to create two networks, each will host 30 systems.

Single class C IP range can fulfill this requirement, still you have to purchase 2 class C IP range, one for each.
Single class C range provides 256 total addresses and we need only 30 addresses, this will waste 226 addresses. These unused addresses would make additional route advertisements slowing down the network.

With subnetting you only need to purchase single range of class C.
You can configure router to take first 26 bits instead of default 24 bits as network bits.
In this case we would extend default boundary of subnet mask and borrow 2 host bits to create networks.
By taking two bits from the host range and counting them as network bits, we can create two new subnets, and assign hosts them. As long as the two new network bits match in the address, then the address belongs to the same network. You can change either of the two bits, and you would be in a new subnet.


Friday, 11 April 2014

Configure alerts for CPU utilization higher than 80% for more than 5mins

SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization,
timestamp
from ( select timestamp, convert(xml, record)record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%')x )y  order by record_id desc
To send detailed sql server session reports consuming high cpu
 For a dedicated SQL Server you can monitor 'SQLProcessUtilization'
 if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=50
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'',
td= ses.host_name,'',  
td= er.blocking_session_id,'',
td= er.database_id,'',
td= er.command,'',
td= st.text,'',
td= er.total_elapsed_time,'',
td= er.cpu_time,'',
td= er.reads,'',
td= er.writes,'',
td= er.last_wait_type,'',
td= er.start_time,'',
td= con.net_transport,'',
td= con.num_writes,'',
td= con.num_reads,'',
td= con.client_net_address,'',
td= con.auth_scheme,''
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id  LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX))+
N'</table>'
-- Change SQL Server Email notification code here


EXEC msdb.dbo.sp_send_dbmail
@recipients='hhhhhhhh.dddd@xxxx.com',
@profile_name = 'xxx',  
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords