What can I say except “You’re Welcome for the AG voting script”

We recently had an issue where the network between our GEO-Cluster would go down and both Availability Group Instances thought they were supposed to take charge.  When the network came back up, both of them still though they were in charge.  You can imagine with an AG, you can’t have two instances that think they are in charge without problems.  This brought up the question of how voting was configured between the two of them.  This script helped a bunch:

SELECT member_name, member_state_desc, number_of_quorum_votes
 FROM sys.dm_hadr_cluster_members;

We found that the File Share Witness wasn’t working properly by checking the member state. In a simple AG, a good practice is to have each instance and then a File Share Witness,that keeps each side from accidentally taking over.  You’re Welcome.

The song for this post:  You’re Welcome From Moana

For one good, naughty little girl found a diamond…Object Explorer Details

It’s Christmas time again and time to listen to my FAVORITE Christmas song called Joel The Lump of Coal.
Just before Thanksgiving we had our SQLSaturday\Big Mountain Data event and I spoke! This is my third time speaking at this event and every year I regret speaking and feel like everyone would have been better in another session, every year that is until this one! I loved my session and I will actually be submitting it to PASS Summit this year. Keep your fingers crossed with me. It was on SQL Server Management Studio Tips and Tricks.

One of the tips that I was super surprised that many people didn’t know is the Object Explorer Details. It allows you to delete multiple objects at once, script out multiple objects at once and just do some really cool stuff. How do I access this magic you are asking? When in management studio, click on View>>Object Explorer Details.

 

ObjectExplorerDetails

Now you can have a diamond that will help you too!

In the music the moment you own it and can decode your database!

Yesterday I had the awesome opportunity to present at Big Mountain Data and SQL Saturday Salt Lake City.  I was super nervous, but I think it went well over all.  Huge thank you to the kind friends that sat in the audience to help build my confidence and for everyone that attended.  Here are the scripts that I promised to post.  If you would like the slide deck, it is posted on the Utah Geek Events website here: http://www.utahgeekevents.com/Downloads

The first script is the one that gets the row counts on each table so you can see what tables you want to look at and what tables you want to skip.

 
-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id = 2
AND o.is_ms_shipped = 0
ORDER BY ddps.row_count DESC

This next part is the second demo I did about digging through the database.

 
--What columns are in the Sales Tables?
SELECT A.name, B.name
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE A.name LIKE '%Sales%'

--Column called "Order" something with amount?
SELECT A.name, B.name
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE B.name LIKE '%Order%'

--OrderQty is the column I am looking for...
SELECT A.name, B.name
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE B.name LIKE '%OrderQty%'

--How do I know for sure it is the table I want?
SELECT 
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
--c.scale ,
--c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID('Sales.SalesOrderDetail')

This is the code from the third demo where I was looking for the foreign keys. I got this off stack overflow and it has been very helpful.

 
SELECT obj.name AS FK_NAME, sch.name AS [schema_name], tab1.name AS [table], col1.name AS [column], tab2.name AS [referenced_table], col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE tab1.name = 'SalesOrderDetail'

The other demos that I did were opening Views and stored procedures and a walk through of how to use the Database Diagram feature.

Hope you all had a wonderful time at the event like I did!

Free tools are priced right…

Isn’t it great when you work for a company that buys you all the nifty, expensive tools and gives you a handsome Christmas bonus to boot?  I remember the days of having a dozen instances of Quest Spotlight on SQL Server monitors, LiteSpeed, Redgate, you name it.  But these days, times seem to be a bit tighter.  I’m always looking for a good deal on SQL tools and wouldn’t you know… free tools are prices right!

What is your favorite free SQL tool?  Here are a couple of mine:

ClearTrace      Many Thanks to Bill Graziano for a fantastic way to quickly analyze SQL Profiler Trace files.  Slick as a whistle, find your expensive batches or statements by Application Name, Login Name, Normalized SQL Text or Host Name. I am worthless as a DBA without it… I am a hero with it!  He even has an online version now.

SMSS Tools Pack     This is new to me, but my colleague just passed it forward.  Thank you Mladen Prajdic for the add-in to Management Studio.  One of the features I already use is Windows Connection Coloring, a handy tool to know what type of server you are connected to (for me red=prod, orange =staging and test, green=dev and local).  Just a little indication to keep you from getting yelled at by mistakenly connecting to the wrong environment!  Query Execution History is a lifesaver when you’ve spent a bunch of time writing code and close your window without saving the script.  SQL Code Snippets are fantastic.

Hope you have a chance to give these tools a shot.  Let me know what your favorite free SQL tool is, I’d like to get some in my Christmas Stocking!

~Todd Carrier