As we know Microsoft does not support compression for SQL Server Express but time to time we use express edition database engine when all you need is small database that might not even grow more than 1 gig or 5 gigs.
we need 7z zip program its an opensource software will help us compress the files, download the latest version 64 bit (http://www.7-zip.org/)
Enable xp_cmdShell ( we are going to use xp_cmdshell to execute the batch command to compress or delete files)
TSQL Script( Where Magic happens)
How does script work: it first looks at mypath and lists all .bak files and folders it loops each of these files using a cursor and execute the 7zip command to compress the .bak to .zip and cleanuphrs parameter will clean the backup zip files older than specified number of hours.
As part of database life cycle management process organizations ensure database is patched and kept up to date to ensure database system is secured from any vulnerabilities and to stay in support life cycle.
Microsoft Support Life cycle provides a consistent and predictable guidelines for product support availability when a product releases and throughout that product’s life. By understanding the product support available, customers are will be able to maximize the management of their IT investments and strategically plan for a successful IT future.
To know the current status of Microsft life cycle is not easy, MS provides an Excel file that contains all the build versions together with their current support life cycle stage for 2005 through the current version is available.(please note as we speak MS ended support for 2005)
So put to together a powershell script that will load the excel file into SQL Server table.
Powershell Script to load the excel
When you are setting up alerts using WMI and if your intention is to capture the event data with sql agent job make sure you change below settings in SQL Server Agent Configuration.
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 GO
This will allow SQL Server Agent to replace the tokens with running values (what database changed, who changed it etc.). Read more about using Tokens in Job Steps here (make sure you read the security note in the article and understand the security risk).
Create a table with some data
Check Fragmentation using sys.dm_db_index_physical_stats
Detailed mode is more accurate than any other mode because it scans every page of the index but this might create some performance bottle neck. It depends on where each of this mode can be used if an index with high fanout (index key length) which is directly proportional to the number of levels , it makes sense to check the fragmentation at intermediate levels using detailed mode at least once a week as part of Index Maint plan.
B-Tree Index Structure:
Non Clustered or Clustered Index
If you encounter below error when using SQL Server and TLS protocol in conjunction with SSL .There is setting in the Group policy under Administrative Template/ Network / SSL Configuration Setting this should be disabled or left not be configured
An TLS 1.0 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 1205.
Changes required to fix this.
How to fix
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error.
Could not start the network library because of an internal error in the network library.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Make below registry changes.
Make sure TLS 1.0 Server and SSL3.0 Server are enabled
Check these links as well
Great post on how to setup TLS
Make sure you check the certificate is setup properly.
SQL SSL Check ( Use below link to check validity of the SSL available on the SQL Server)
Zip file contains an .exe file download and run in cmd window.