2 New Things in SSMS 17.2 – I Think You’ll Like.

QueryStoreUI reports now supports additional metrics (RowCount, DOP, CLR Time etc.) from sys.query_store_runtime_stats. Performance Dashboard server report now available as a default report: Can connect to SQL Server 2008 and newer versions. Missing indexes sub-report uses scoring to assist in identifying most useful indexes. Historical wait stats sub-report now aggregates waits by category. Idle and…

SQL Server 2016 AlwaysOn in Windows Server 2012R2 WFC – MultiSubnet Listener returns wrong IP address after a FlushDNS – Error Connection Timeout!

Issue: (Failover had not occurred, commands executed without pause).   Fixed. Key changes were RegisterAllProvidersIP=0, HostRecordTTL=300 (5 minutes instead of default 1200 (20 minutes)). If using .Net Framework 4.5+, recommend adding to client connection strings: MutiSubnet=True; Ref SqlClient support for HA and DR. Key steps were: Set RegisterAllProvidersIP=0 for the Cluster and LG. Set HostRecordTTL=300 (5 minutes instead of default…

SQL Server 2016 Always Encrypted – Parameterized Samples

With Always Encrypted in SQL Server 2016, if you want to Insert, Update or Filter by an encrypted column (ie. DML) the statement must be parameterized and/or use sp_executesql. Jakub shows how to do this on his post at the SQL Server Security Blog, but here's the condensed version of the article with all the sample scripts/instructions…

SQL Server 2016+ Query Store Blitz – This is AWESOME!

Check out: https://www.brentozar.com/archive/2017/06/introducing-sp_blitzquerystore/ Test it like this:   Here's the sp_BlitzQueryStore.sql - Thank you Erik Darling! If you have SQL Server 2017+ it gives you the top 3 wait stats per plan too. Download it here, via the link above or copy it and run it on Master db. Enjoy! Hiram

Loving the new UI on SSMS 17.0 – Get Ready for SQL Server 2017

If you haven't downloaded the new SSMS 17.0 I recommend you go get the free upgrade. Loving the new UI too. https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms It still looks similar as before, but the subtle changes are nice. SCREENSHOTS:  New Icon: Start Menu (Yes I my workstation still has Win 7) Splash Screen: New Query Window, Object Explorer Icons, etc: Here's…

TRY/CATCH Custom Message – SQL Error Handling

Crediting Basa for neat details, check out his blog is full of good things. The idea is to control the message returned to the app front-end, rollback the transaction if need be or retry with another command. More on this...with how to log the activity as well. Thanks, Hiram

SQL Server 2014 SP2 Update – Silent Install

Long story short, you should reboot the server twice, before and after running the command. Thanks, Hiram If you want to keep reading, keep going. The installation summary file can be found here, which you can double check the status of the update: C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt First time I tried this, the cmd…

SQL Sync Logins – Domain Accounts

Hypothetically, say you can't grant dbo permissions. Instead you just need read, write and execute for stored procedures. Common Database-Level Roles are: db_datareader, db_datawriter, and my personal favorite db_executor. Ideally, you would use a domain security group (making the accounts members of the group) and grant the group access to the SQL Server instead of…

SQL Server Error – Incorrect time syntax in time string used with WAITFOR.

This is a simple one but it doesn't stick until you write/read about it.  Note, I use WAITFOR DELAY to pause/sleep the session between batches or T-SQL commands. For logic reasons, sometimes, I just gotta buy myself a few seconds for one thing to finish, before I start the next one. Ref: Control-of-Flow (lots of goodies…

Batching SQL Scripts to Automate Deployments (with .bat/cmd)

With this technique, you don't have to buy anything or spend time installing fancy stuff. Just write some commands, copy/paste and save as .bat or cmd. [Not discrediting any other implementation, just sharing my experience with a simple method]. When you end up running the batch I recommend doing it on the target server, not your workstation…