viktortat / sqlserver-kit

Useful links, scripts, tools and best practice for Microsoft SQL Server Database

SQL Server KIT

Useful links, scripts, tools and best practice for Microsoft SQL Server Database

Headers:

• Repo Folders and Files
• SQL Server Web Resources
• BIML Resources and Bloggers
• SQL Server Sample Databases and Datasets
• Microsoft Transact-SQL Hints
• PowerShell and SQL Server
• TSQL Format Code
• Free SQL Server and R ebooks
• License

Repo Folders and Files

• [SQL Server Data Types](/SQL Server Data Types.md)
• [SQL Server Edition](/SQL Server Edition.md)
• [SQL Server Name Convention and T-SQL Programming Style](/SQL Server Name Convention and T-SQL Programming Style.md)
• [SQL Server Licensing](/SQL Server Licensing.md)
• [SQL Server People](/SQL Server People.md 'Most Valuable SQL Server professionals')
• [SQL Server Trace Flag](/SQL Server Trace Flag.md 'Complete list - 362 Trace Flags') (Complete list - 362 trace flags)
• [SQL Server Version](/SQL Server Version.md 'List of all Microsoft SQL Sever versions') (Complete list - from SQL Server 1.0 to SQL Server 2016)
• Articles
• CLR procedures
  • SQL# free version - QUICKEST and EASIEST way to extending the power of T-SQL with C#
  • SplitterB_CLR
• Known Errors
• Solution
  • dbWarden a free SQL Server Monitoring Package (by Stevie Rounds and Michael Rounds)
  • Base line Collector script (by Robert Virag)
  • Ola Maintenance Solution (by Ola Hallengren)
  • SQLQueryStress (by Adam Machanic)
  • SysJobHistory (by David Wentzel)
• SSMS
  • SSMS addins (29 useful free and paid SSMS Addins)
  • SSMS Snippets
  • SSMS Shortcuts
• Scripts
  • Awesome SQL Server Diagnostic Information Queries (by Glenn Alan Berry)
    • [SQL Server 2016 Diagnostic Information Queries](/Scripts/SQL Server 2016 Diagnostic Information Queries.sql)
    • [SQL Server 2014 Diagnostic Information Queries](/Scripts/SQL Server 2014 Diagnostic Information Queries.sql)
    • [SQL Server 2012 Diagnostic Information Queries](/Scripts/SQL Server 2012 Diagnostic Information Queries.sql)
    • [SQL Server 2008 R2 Diagnostic Information Queries](/Scripts/SQL Server 2008 R2 Diagnostic Information Queries.sql)
    • [SQL Server 2008 Diagnostic Information Queries](/Scripts/SQL Server 2008 Diagnostic Information Queries.sql)
  • [Table count alternative](/Scripts/Table Count alternative.sql) (by Jes Schultz Borland)
  • [Foreign Key batch rename](/Scripts/Foreign Key batch rename.sql) (by Wes Henriksen)
  • [Count character matches](/Scripts/Count character matches.sql)
  • and many others...
• Stored Procedure
  • sp_DBPermissions (by Kenneth Fisher)
  • sp_SrvPermissions (by Kenneth Fisher)
  • sp_RestoreGene (by Paul Brewer)
  • usp_who5 (by Sean Smith)
  • usp_String_Search (by Sean Smith)
  • usp_BulkUpload
  • usp_TableUnpivot
  • usp_SSIS_ScriptEnvironment
  • and many others...
• User Defined Function
  • udf_parseJSON
  • udf_RenderXMLToString
  • udf_RTF2Text
  • udf_SplitStringByDelimiter
  • udf_Tally
  • and many others...
• Utilities (complete list of 172 SQL Server paid and free Utilities and Tools)

Back to top

SQL Server Web Resources

• Blogs
  • SQL Central Blog Scripts
  • SQL Central Blog Articles
  • SQL Central Blog Stairways
  • DatabaseWeekly
  • MSSQLTips
  • BRENT OZAR scripts, videos and articles
  • Simple-talk Articles
  • SQLSentry Blog
  • Glenn Berry's SQL Server Performance
  • Kenneth Fisher SQLStudies Blog
  • Best SQL Server Perfomance Blog
  • Weblogs SQLTeam Blogs
  • SQLMag
  • SQLShack
  • LessThanDot SQL Server Blog
  • SQLBlog
  • DatabseJournal SQL Server Blog
  • SQLPass
  • Vertabelo Blog
  • Midnightdba Blog
  • Madeiradata Blog
  • SQL Server Performance Articles
  • SQL and more with KRUTI Blog
  • SQL Authority
  • TECHNET SQL Server Blog
  • SQL Server Database Engine Blog
  • SQL Server BI Blog
  • Andy Yun SQLBeck Blog
  • Curated SQL
  • Blog do Ezequiel
  • SQLHA Blog
  • SQLSecurity Blog
  • SQL.ru SQL Server (Russian)
  • C# Corner SQL Server Articles
  • TechTarget Blog
  • Toad SQL Server Blog
  • SQL-Articles
  • DallasDBAs Blog
  • UpSearch Blog
• Security (great thanks to Troy Hunt)
  • SQL injection
    • sqlmap – The tool for mounting SQL injection attacks tests against a running site
    • Drupal 7 SQL injection flaw of 2014 – great example of how impactful it still is (patch it within 7 hours or you’re owned)
    • Ethical Hacking: SQL Injection – If you really want to go deep, here’s five and a half hours worth of Pluralsight content
  • Exploit databases and breach coverage
    • seclists.org – Heaps of exploits consolidated from various bug tracking lists
    • Exploit Database – Very comprehensive list of vulnerabilities
    • PunkSPIDER – Lots of vulnerabilities of all kinds all over the web (about 90M sites scanned with over 3M vulns at present)
    • Data Loss DB – Good list of breaches including stats on number of records compromised
    • Information is Beautiful: World’s Biggest Data Breaches – Fantastic visualisation of incidents that give a great indication of scale
  • SQL Server Database Engine Permission Posters
• Free Videos
  • Youtube Brent Ozar
  • IDERA Resource Center
  • MSSQLTips SQL Server Webcasts and Videos
  • SQL Server Videos
  • TECHNET How do I Videos
  • Veeam Learn Microsoft SQL Server
  • MidnightDBA ITBookWorm Video
  • SQL Server Hangouts (by Boris Hristov, Cathrine Wilhelmsen)
  • Youtube russianVC (Russian)
  • Youtube Redgate Videos
  • Dell DBA days the Brent Ozar Unlimited 2015
  • Dell DBA days the Brent Ozar Unlimited 2016
  • User Group.tv (by Shawn Weisfeld)
  • SQLPass Virtual Chapters
  • Youtube SQLpassion
  • SQLBits Video
  • Business Intelligence Videos
  • Pragmatic Works Free Training Webinars
  • Youtube Pragmatic Works Video (by Devin Knight and Manuel Quintana)
  • MVP: Data Platform
• Free Database Podcasts
  • SQL Server Radio (by Guy Glantser and Matan Yungman)
  • SQL Data Partners (by Carlos L Chacon, César Oviedo and Adrian Miranda)
  • Away from the Keyboard (by Cecil Phillip and Richie Rump)
  • RunAs Radio (by Richard Campbell and Greg Hughes)
  • People Talking Tech (by Denny Cherry)
  • NET Rocks! (by Richard Campbell and Carl Franklin)
  • SQL Down Under Podcast (by Greg Low)
  • Free sql server video tutorials for beginners (by PRAGIM Technologies)
  • Midnight DBA Podcast (by Sean and Jen McCown)
  • Dear SQL DBA (by Kendra Little)
  • GroupBy.org - Group By is a free online event for the community, by the community (by Brent Ozar team)
  • DevopsCafe (by John Willis and Damon Edwards)
  • SQLDataPartners Podcast
• Courses
  • Free
    • Codecademy Learn SQL
    • Codecademy SQL: Table Transformation
    • Codecademy SQL: Analyzing Business Metrics
    • MVA SQL Server Courses
    • How to Think Like the SQL Server Engine
    • Number and Date Tables
  • Paid
    • Lynda Courses
    • Veeam Free Courses
    • SQLSkills Trainings
    • Brent Ozar Team Trainings
    • Pluralsight Courses
    • SolidQ Classes
    • JOOQ SQL Masterclass
    • Learn SQL Server High Availability & Disaster Recovery (by Edwin M Sarmiento)
• SQL Server Backwards Compatibility
  • 2016 Backwards Compatibility
  • 2014 Backwards Compatibility
  • 2012 Backwards Compatibility
  • 2008 R2 Backwards Compatibility
  • 2008 Backwards Compatibility
  • 2005 Backwards Compatibility
• Social, Forum and Messenger SQL Server Groups
  • SQLServerCentral Forum (more than 10^6 People)
  • Slack #sqlhelp (more than 700 People)
  • Slack #firstresponderkit (more then 70 People)
  • Twitter #sqlhelp (more than 500 People)
  • SQL.ru SQL Server Forum (more than 10^5 People, Russian)
  • VK.com #sqlcom (more than 3600 People, Russian)
• Open Source Projects
  • Brent Ozar SQL Server First Responder Kit (Github)
  • Statistic Parser (by Richie Rump) (Github)
  • SQL Generator (by Richie Rump) (Github)
  • Columnstore Indexes Scripts Library (by Niko Neugebauer) (Github)
  • MOSL - Memory Optimized Script Library (by Niko Neugebauer) (Github)
  • SQLCover - TSQL code coverage tool for SQL Server 2008+ (by Ed Elliott) (Github)
  • tSQLt testing framework for Microsoft SQL Server (Github)
  • T-SQL SimMetrics string matching algorithms (Github)
  • Azure Blob Storage Backup (by Randolph West) (Github)
  • StackExchange.DataExplorer - free tool for executing SQL queries against Stack Exchange databases (Github)
  • Machine Learning Templates with SQL Server 2016 R Services (by Sheri Gilley) (Github)
  • [Campaign Optimization - Predicting How and When to Contact Leads Implemented on SQL Server 2016 R Services] (by Sheri Gilley) (Github)
  • SQL Server Performance Dashboards - contains all Microsoft based reports, custom built reports, modified reports and the TSQL setup (by Arun Sirpal)
  • tigertoolbox - Repository for Tiger team for "as-is" solutions and tools/scripts that the team publishes for SQL Server (Github)
  • SQL Server FineBuild - makes it easy for anyone to produce a best-practice installation and configuration of SQL Server (by Brian Davis)
  • Pssdiag/Sqldiag Manager - is a graphic interface that provides customization capabilities to collect data for SQL Server using sqldiag collector engine (Github)
• Other
  • SQL Server Management Studio installation download link
  • Ola Maintenance Solution - Best free backup and index maintenance solution for SQL Server (by Ola Hallengren)
  • sp_whoisactive (by Adam Machanic)
  • SQL# CLR functions (by Sql Quantum Leap)
  • SQL Server Latch Classes Library (by Paul S. Randal)
  • SQL Server Wait Types Library (by Paul S. Randal)
  • SSIS Performance Benchmarks
  • Using Excel to parse Set Statistics IO output (by Vicky Harp)
  • Stackoverflow SQL Server
  • DBA Stackexchange SQL Server
  • SQL Server Connection Strings
  • SQL Injection Cheat Sheet (by Ferruh Mavituna)
  • RSS Most Recent SQL Server KBs
  • Stackoverflow SQL Anti Patterns
  • Azure Speed (by Blair Chen)
  • SQLFiddle
  • Practical skills of SQL language (Russian, English)
  • Experts-Exchange.com MS SQL Server Topics
  • Paste The Plan
  • StackExchange DataExplorer Query On line

Back to top

BIML Resources and Bloggers

BIML - Business Intelligence Markup Language

BIML Resources

• BimlScript.com
• Varigence
• Biml Forum at Varigence
• SQLServerCentral.com
• Stairway to Biml
• Biml User Group at LinkedIn
• Building Blocks of Biml (Pluralsight course by Stacia Misner Varga)
• Biml-tagged posts on this blog

BIML Bloggers

• Ben Weissman
• Bill Fellows
• Boris Hristov
• Brian Bønk
• Cathrine Wilhelmsen
• Datachix: Julie Smith and Audrey Hammonds
• David Stein
• Davide Mauri
• Erik Hudzik
• Hennie de Nooijer
• John Welch
• Joost van Rossum
• Koen Verbeeck
• Marco Schreuder
• Martin Andersson
• Meagan Longoria
• Nicholas Sorrell
• Paul Te Braak
• Peter Schott
• Reeves Smith
• Roelant Vos
• Rui Custódio
• Samuel Vanga
• Stephen Leach
• Tim Mitchell
• Warwick Rudd

Back to top

SQL Server Sample Databases and Datasets

• AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3
• Microsoft SQL Server Community Projects & Samples
• Adventure Works 2014 Sample Databases
• Adventure Works 2012 Sample Databases
• Microsoft SQL Server 2008 R2 SR1 Sample Databases
• Thinking Big (Adventure) (by Adam Machanic)
• WideWorldImporters Sample Database (Github)
• Bigger Fact Table for Wide World Importers (by Koen Verbeeck)
• Contoso Retail Data Warehose (Github)
• How to Download the Stack Overflow Database via BitTorrent 2016-03 (by Brent Ozar)
• Amazon product data (This dataset contains product reviews and metadata from Amazon, including 142.8 million reviews spanning May 1996 - July 2014)
• Git repo for SQL Server Reporting Services samples and community projects
• SQLSkills sample databases (also include SAMPLE CORRUPT DATABASES)
• SQLIndexWorkbook Sample Database (by Little Kendra)

Back to top

Microsoft Transact-SQL Hints

• Transact-SQL Join Hints
• Transact-SQL Table Hints
• Transact-SQL Query Hints

Back to top

PowerShell and SQL Server

• SQL Server & Windows Documentation Using Windows PowerShell (by Kendal Vandyke)
• TSQL Code Smells Finder (by Dave Ballantyne)
• Stairway to SQL PowerShell (by Ben Miller)
• SQL Server Health Check Script with Powershell (by Atul Kapoor)
• Universal SQL Server Installation Scripts (by Prakash Heda) (Github)
• Powershell SQL Server Performance Health Check (by Omid Afzalalghom) (Github)
• PSCI - Powershell Continuous Integration (by Objectivity Bespoke Software Specialists) (Github)
• SQLTranscriptase - SQL Server Documentation in Powershell (by Vijay Bandi) (Github)
• SQL Server PowerShell Extensions (SQLPSX) (by Mike Shepard) (Github)
• PowerShell dbatools for SQL Server (by Chrissy LeMaire) (Github)
• Create a Monitoring Server for SQL Server with PowerShell (by Laerte Junior)
• PowerShell SQLPass articles and video
• PowerShell Blog NetNerds
• QS Config (by Derik Hammer)
• Idera 89 Free SQL Server PowerShell Scripts
• Performance Analysis of Logs (PAL) Tool (by Clint Huffman)
• Powershell SQL Server Library (PSSQLLib) (by Sander Stad) (Github)
• Trello Board: Powershell and SQL Client Tools
• PowerUpSQL: A PowerShell Toolkit for Attacking SQL Server (Github)
• PowerShell DBA Reports (Github)
• PowerShell sqlCheck (Bitbucket) (by Josh Feierman)
• PowerShell Enterprise Policy Management Framework
• ReportingServicesTools - Reporting Services Powershell Tools (by Microsoft)

Back to top

TSQL Format Code

• http://sqlinform.com/
• http://www.dpriver.com/pp/sqlformat.htm
• http://stackoverflow.com/questions/401928/sql-formatter-for-sql-management-studio
• http://www.apexsql.com/sql_tools_refactor.aspx
• http://poorsql.com/
• http://www.architectshack.com/PoorMansTSqlFormatter.ashx
• http://www.ssmstoolspack.com/
• http://www.devart.com/dbforge/sql/sqlcomplete/
• http://www.sql-format.com/

Back to top

SQL Server Test Data Generation

• https://www.simple-talk.com/sql/t-sql-programming/generating-test-data-in-tsql/
• http://www.yandataellan.com/
• https://github.com/benkeen/generatedata
• https://sourceforge.net/projects/dbmonster/
• https://sourceforge.net/projects/spawner/
• http://databene.org/databene-benerator
• http://stackoverflow.com/questions/591892/tools-for-generating-mock-data

Back to top

Free SQL Server and R ebooks

SQL Server:

• Avesome Red Gate ebooks
• Microsoft huge collection
• Microsoft large collection
• Microsoft MVA Free ebooks
• OnlineVideoLectures ebooks
• Brent Ozar ebooks
• E-books SQL Server Directory
• TOAD SQL Server ebooks
• Syncfusion Techportal
• Modern Storage Strategies for SQL Server
• Migrating SQL Server Databases to Azure
• SQL Sentry Free eBooks
• Microsoft Cloud Security for Enterprise Architects (PDF)
• Brent Ozar SQL Server Setup Checklist eBook
• Introducing Microsoft SQL Server 2016

R:

• FreeComputerBooks R EBooks
• Effective Graphs with Microsoft R Open
• Little Book of R for Time Series (by Avril Coghlan)
• Little Book of R for Biomedical Statistics (by Avril Coghlan)
• Little Book of R for Multivariate Analysis (by Avril Coghlan)

Back to top

License

MIT

If some procedures or scripts are restricted due to ELUA (or we can not find original author), please email or add issue - we remove/update it immediately. Thanks for understanding and patience.