data-konzept

'Create a .txt and write down all your server name like...

'A performance issue I see often is when users need to match part of a string with a query like the following:

... WHERE SomeColumn LIKE N'%SomePortion%';

With a leading wildcard, this predicate is "non-SARGable" – just a fancy way of saying we can't find the relevant rows by using a seek against an index on SomeColumn. 

One solution we get kind of hand-wavy about is full-text search; however, this is a complex solution, and it requires that the search pattern consists of full words, doesn't use stop words, and so on. This can help if we're looking for rows where a description contains the word "soft" (or other derivatives like "softer" or "softly"), but it doesn't help when we're looking for strings that could be contained within words (or that aren't words at all, like all product SKUs that contain "X45-B" or all license plates that contain the sequence "7RA").'...

https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

'You are probably wondering what this is, quite honestly I was wondering too. Well it is an extended events session known as telemetry_xevents that is shipped with SQL Server 2016. This is not the same as the system_health session that we have come to know and love, it captures very different information.

There are a lot of queries within this blog post but I think you will find them really useful because getting practical with it is the best way to show everyone what it is all about.

You can see the telemetry_xevents session via SSMS (SQL Server Management Studio).

The script definition of telemetry_xevents is massive but I think it is important to show what it captures.'...

https://blobeater.blog/2017/01/23/extended-events-telemetry-session/

'I often get questions regarding debugging of SSIS packages with the most common scenario being a need to “see” the data flowing  down the data pipeline. SQL Server Integration Services (SSIS) packages are explored and edited visually within the SQL Server Data Tools (SSDT) and hence, the mechanism to troubleshoot an SSIS package when developing it also has to be visual.

The Data Viewer allows a developer to pause the data flow in a data flow task and look at the data as it is on that point in the pipeline. Today, I will introduce you to the Data Viewer.'...

https://nakulvachhrajani.com/2017/01/23/0399-sql-server-ssis-debugging-exploring-the-data-viewer/

'You’ve got a Microsoft SQL Server that somebody else built, or that other people have made changes to over the years, and you’re not exactly sure what kind of shape it’s in. Are there dangerous configuration settings that are causing slow performance or unreliability?
You want a fast, easy, free health check that flags common issues in seconds, and for each warning, gives you a link to a web page with more in-depth advice.'...

https://www.brentozar.com/blitz/

'The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have quite a few comments and links in the script on how to interpret the results after each query. 

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (SSMS) to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet.'...

https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-february-2017/

'Contains useful functions that I use and am able to share. 

Get-DriveSize - Returns the Drive or Volume Name, Label, Size in Gb, Free Space in GB and Free space as a percentage of drives and mount points on local and remote servers

Test-SQLDefault - Runs a series of Pester tests against a SQL Instance or an array of SQL Instances. Should be easy to customise to your own environments required defaults 

Show-DatabasesOnServer - Returns the Name and sizes of databases on a server or array of servers

Set-OlaJobSchedule - Sets the agent job schedule for the jobs created by Ola Hallengrens Maintenance plan

When-WillSQLComplete - Quick function to check progress of SQL Commands via sys.dm_exec_requests. Useful for DBCC, Backup, Restore and indexing progress

Test-OLAInstance - Wrapper to call a Pester Test script Test-OLA to test Ola Hallengren Maintenance Solution is installed correctly'...

https://github.com/SQLDBAWithABeard/Functions

'EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). 

EPPlus supports:

• Cell Ranges 
• Cell styling (Border, Color, Fill, Font, Number, Alignments) 
• Charts 
• Pictures 
• Shapes 
• Comments 
• Tables 
• Protection 
• Encryption 
• Pivot tables 
• Data validation 
• Conditional formatting 
• VBA 
• Formula calculation 
• Many more...'...

http://epplus.codeplex.com

'This PowerShell Module wraps the .NET EPPlus DLL (included). Easily integrate reading and writing Excel spreadsheets into PowerShell, without launching Excel in the background. You can also automate the creation of Pivot Tables and Charts.'...

https://github.com/dfinke/ImportExcel

'SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2014, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 R2 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.

Discover

Find SQL Server Services on your network by:​

• Active Directory DNS
• Subnet Scan 
• Computer Name 

Document

Collect comprehensive details about SQL Server instances and their underlying Windows OS, including:

• Subnet Scan
   
• Computer Name
   
• Service Details For All Installed SQL Sever Services
   
• Database Engine
  • Configuration
    • Security
    • Server Objects
    • Databases
      • Security
      • Storage
      • Service Broker
      • Database Objects
      • Configuration
         
• SQL Agent
  • Configuration
  • Jobs
  • Alerts
  • Operators 
     
• Windows OS
  • Machine Information
  • OS Information
  • Software 

This documentation is useful for:

• Baselines - know what your SQL Server environment looked like last week, last month, etc.

• Security Audits 

• Licensing Audits 

• Provide a complete look at how your servers are configured without having to grant access

• Troubleshooting 

• Comparing servers and databases 

• Creating a runbook that you can give to your operations team 

• Planning upgrades - see what hidden features are in use on an instance 

Diagnose

SQL Power Doc performs over 100 checks to find hidden problems and performance bottlenecks on your SQL Servers before they turn into major headaches.

But Wait, There's More!

SQL Power Doc isn't limited to just SQL Server - you can also use it to collect an inventory of all the Windows machines on your network. If you're in need of a free documentation solution for Windows SQL Power Doc is up to the task!'...

https://sqlpowerdoc.codeplex.com