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

Lay your weary head to rest and run your query more…

Just coming from a wonderful week at PASS Summit. I sat in two separate sessions where this trick came up and watched as people furiously wrote it down.  Did you know that you can set a query to run multiple times in one command?  All you have to do is add “Go” and the number of times you want it to run.  For example:

SELECT GETDATE()
GO 5

Will return something like this:

go5results

Super cool, right?  It was mentioned in both Brent Ozar’s and Argenis Fernandez’s presentations and I didn’t want to forget. Now you can easily run your queries more and create load on your test system without having to click run over and over again.

Today’s post features Lyrics from Panic! At the Disco’s Cover of Carry On My Wayward Son

Mayday this is an emergency, my linked server is using ODBC to connect to an AG…

Availability Groups (AG) and Linked servers can get really tricky. Especially if you are dealing with multi-subnet failover.  The only way we have figured out how to do this is with ODBC. Here is how.

First, we have create an ODBC connection on our SQL Server.  The single server in the picture below is the server we are going create the ODBC connection and the linked server on.  It will go over to the AG Listener. ag-pic

First we are creating an ODBC Connection on our server that is going to link to the AG.

odbc1

Make sure to be in the System DSN section. Click Add

odbc2

We can pick either ODBC Driver 11 or 13.  This is a separate driver install that we can get here.  We want to install the driver and then we will see it as an option in the screen above.  Click Finish.

odbc3

Give it whatever name and description wanted, but save the name for later. The server should be the name of the AG Listener. Click Next.

odbc4

Technically we don’t have to put in a login and password, but I like to test the login and password that I am going to use for the linked server. It won’t be saved here. Click Next.

odbc5

Here is the magic part, make sure to check multi-subnet failover.  That is what is going to make the connection automatically fail between the two nodes. Click next, test the connection and then Finish. The ODBC connection is ready to be used by the linked server connection.  Let’s build that part now.

Go into SQL Server Management Studio and under Server Objects, right click on Linked Servers and select “New Linked Server”.

odbc6

The provider needs to be OLE DB Provider for ODBC Drivers. Remember the name we gave the ODBC connection?  We are going to use that here. Then go to the Security Tab.

odbc7

This is where we put in the login and it will be stored here.  I also make sure this user name and password is on both Nodes of the AG with the permissions that I need. Click Server Options

odbc8

The above is what I need, but I check that I am only giving access to what is needed and not more.  When we click ok, it will test our connection.  If everything works with no errors, we are ready to go.

Some of the problems that we have noticed are querying tables that have big datatypes like time(3-7), timestamp, and a few others.  Casting or converting the datatypes doesn’t help. If we pull the table into a view without the big datatype columns, we are able to query the view from another server, but never the base table. It has been a bit frustrating, but we are still hopeful that we can find a solution or that Microsoft with fix ODBC connections. If there is a better way to do this, please reach out to me.  We have things we need to solve and could use some help.

The song for this post is Mayday by Cam.

Coming soon: Count down to PASS Summit 2016 with more pictures from PASS Summit 2015. Watch twitter and the Magic Mirrors page for more.

Listen to the AG that’s tricking you…

 

Another AG (Availability Group) Post? Yes, I learned something new and it must be cataloged. When you are failing AG’s back and forth really fast and a major indexing job kicks off in the middle, it can cause a transaction to have to rollback.  This rollback may take a REALLY long time, even if you were only on the node for 10 minutes and a large transaction had only been running for about 5 minutes. When I failed back to my preferred primary node and the AG Dashboard didn’t go completely green, I got worried.  Why in the world would it not go green? I just failed to the preferred secondary and applied a patch (see? I learned.) and then was failing back. It had been green when I started, green when I failed over to the secondary and now one of my biggest databases was not synchronizing on the primary….*sigh*

I panicked. In this situation I would normally pull the database out of the AG and then re-add it.  I didn’t have that option because it is a HUGE database and didn’t have that much time and space to move it around. I knew a large transaction had kicked off (thank you alert email that I created to warn me about such things) but thought that surely the rollback would have cleared quickly.  That lead me to looking for rolling back transactions.

I ran this on the alarming secondary node:

SELECT R.session_id, R.command ,R.status, R.percent_complete
FROM sys.dm_exec_requests R
WHERE R.command IN ('killed/rollback','rollback')

To my surprise, there were no results.  Nothing was killed or rolling back; or was it? I ran the query again, but this time without the where clause.

SELECT R.session_id, R.command ,R.status, R.percent_complete
FROM sys.dm_exec_requests R

I saw one command listed as “UNKNOWN TOKEN” that had a percent complete at about 5%. That percent was rising. I theorized that this was my rolling back process and when it finished, my AG would be healthy again.  The system isn’t used overnight. We had started the maintenance in the late afternoon and it was the secondary node in trouble, so I had time to test my theory.  It was an agonizing 8 hours as I kept checking on the percent_complete all evening.  It finally completed and the AG went green.

My lesson learned: When my AG isn’t healthy and I have already resumed data movement, before I pull the database out of the AG,I need to check for processes that have a percent complete on the secondary node. Being patient is really hard but necessary with AG’s.

The song that goes with this post Listen to the Man.

Hello Darkness my old friend, I can talk to you again because my Availability Group is quiet…

We have a lovely Availability Group that holds A LOT of data that is broken into partitions.  We have 42 partitions and they are usually moving information around daily between them.   The index rebuilds on them were making our logs HUGE because the the Availability Group was taking too long to catch up, we tried both Synchronous and Asynchronous mode.  We would see all kinds of errors.  We were doing horrible things like auto shrinking our transaction log after the indexing finished and  ignoring alarms during the time the database was rebuilding.  We had requested more and more space from our storage team and sometimes the job wouldn’t even finish because it ran out of space.  Our first idea was to split out the index rebuilds so that we could do one partition at a time.

It looks like this:


ALTER INDEX IndexName
ON [dbo].Table
REBUILD PARTITION = 42

By splitting this out, we were able to get the job to finish, but with tons of alarms, and log growth.

Then we had a thought, maybe the server is just spinning too fast and we need to give the Availability Group time to catch up.  So we added some simple waits in between each step.


WAITFOR DELAY '00:10';

As an example this is in minutes and will wait for 10 minutes before running the next step.

 

It has now been a quiet week and we are looking forward to the Sound of Silence.

It was the coldest night of the year a query with a lot of columns had me in tears…

Yesterday I was working with Jason so super big thank you to him for this script.  We were writing an insert statement and it had a lot of columns.  I was getting ready to script out the table when he showed me this little bit of code.  If you enter your database name and table name it will give you all your columns with commas.  You can even toss your alias in the query so you don’t have to spend a bunch of time adding it.

Declare @DBName as varchar(100)
Declare @tablename as varchar (100)

Set @DBName = 'MyDatabaseName'
Set @tablename = 'MyTableName'

Select T.TABLE_SCHEMA, T.TABLE_NAME
, Stuff(
(
Select ', ' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 2, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
Where T.TABLE_CATALOG=@DBName
and T.TABLE_NAME=@tablename

I hope you enjoy it too!

Welcome to the new Deja Vu, you can populate a column from another database….

Greetings and other Salutations!
Yesterday I was working with someone new to T-SQL who needed to get data from one table in a database to another table in a different database. He was stumped and spent hours trying to find some way to do it. I was able to help him and realized this might be a common problem for people new to T-SQL.

We have two databases on our Instance SuperHero and Marvel and each database has a table Superhero.dbo.Character and Marvel.dbo.Bio.

First, prep the table you want to put data in. You won’t be using an insert statement for this task because in our scenario we already have data in our table. To prep my table I am adding a column at the end of my table and allowing it to have nulls.

Character
CharacterID, Name, Universe, RealName(This is our new column that is null)
1, Spider-man, Marvel,
2, Batman, DC,
3, Iron Man, Marvel,

Bio
BioID, Name, CharacterID, RealName(This is where we will update our new column from)
1, Spider-man, 1, Peter Parker
2, Iron Man, 3, Tony Stark

Next, we join across databases. As you can see these two tables would join on CharacterID so:

 
SELECT C.CharacterID, C.Name, C.Universe, C.RealName, B.BioID, B.Name, B.CharacterID, B.RealName
FROM Superhero.dbo.Character C
INNER JOIN Marvel.dbo.Bio B ON C.CharacterID = B.CharacterID

The “INNER JOIN” also acts as my where clause so that it will only update records that match in both. Please always build and test your SELECT statement first to make sure it is going to update what you expect.
Now we build our update:

 
UPDATE dbo.Character
SET RealName = B.RealName
FROM Superhero.dbo.Character C
INNER JOIN Marvel.dbo.Bio B ON C.CharacterID = B.CharacterID

Be sure you are running on the Superhero database for your update.
Who knew bringing super heroes together would only take a query?

They’ll never take my body from your side, Love don’t die from partitioning…

New dress for Christmas

New dress for Christmas

So this week there was this small fire around running out of partitions.  Because I had to learn how all of it works really fast, I need to write it down before I forget.  I was getting a range doesn’t exist error so here is what I did:

I already had my file groups created.  If you don’t do that before you move on.

Find what partition schemes and functions you have in your database:

SELECT * FROM sys.partition_schemes

SELECT * FROM sys.partition_functions

(Hint: the fanout is the last range so that can help you identify which one is failing.)

Next, see what your current ranges look like:

SELECT sprv.value AS [Value], sprv.boundary_id AS [ID]
FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
WHERE (spf.name = N'PartitionFunctionName')
ORDER BY [ID] ASC

Now we are going to split out our partitions one at a time.  Even if you need to add 5 or more ranges, this is an easy step to do one at a time.  Also, you can only split a range into to pieces at a time, so just run the whole query for each range you want to add.

ALTER PARTITION SCHEME [PartitionScheme] NEXT USED [YourFileGroup]

ALTER PARTITION FUNCTION [PartitionFunction]() SPLIT RANGE (N'NewRange')

This should get you back on track.

Happy Trails!

They said “If you don’t let it out, You’re going to let it eat you away” – CHARINDEX with SUBSTRING

Greetings and other Awesome Salutations,
It has been a while because I am learning oodles of new stuff. There are a few things that I don’t want to forget so here goes.

CHARINDEX is a super easy way to get the starting location of something within a string. For example, I was looking for the last half of Superhero names. My column is SuperheroName in the Comics Table and pretend there are dashes in their names, please just for this example – I know there aren’t really dashes please continue to love me with my flaws.
So…
SELECT TOP 4 SuperheroName
FROM Comics

Results:
Bat-man
Bat-girl
Super-man
Super-girl

Now the magic, CHARINDEX will give you the location of the character you specify.

SELECT TOP 4 CHARINDEX('-', SuperheroName)
FROM Comics

Results:
4
4
6
6

How cool is that?!! But I want to pull back everything after the “-” character, which means I have to get tricky because CHARINDEX will only give me the location number of where it is within the string. SUBSTRING will save the day (Tada!). SUBSTRING is really cool. I am going to pass it ColumnName, Starting Position (using CHARINDEX), Ending Position (using LEN and the column name that will get me the length of that column on that row). So it looks like this:

SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName), LEN(SuperheroName))
FROM Comics

Results:
-man
-girl
-man
-girl

But that still isn’t quite what I want…so I am going to add 1 to the Starting point position number (this will allow me to skip over the character I am using):

SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics

Results:
man
girl
man
girl

Oh but wait, there is more! I think it is really silly to have the dashes in the first place so check this out:
SELECT TOP 4
SUBSTRING(SuperheroName,1,CHARINDEX('-',SuperheroName)-1) + SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics

Results:
Batman
Batgirl
Superman
Supergirl

Holy Rusted Metal Batman! What did we do? This time in my substring I used the first position and then the CHARINDEX -1 to tell me what was right before the dash. Then I combined them! With these super powers combined…well awesome things happen.

Enjoy!

You must not know about me, I can kill connections in a minute

It is still snowy and cold outside so short post today about killing connections.  I love this quick little script and can’t remember where I got it from, so huge Thank You to the person that created it.  I use it when I need to restore a database that still has active connections.  This will first print a list of the connections it wants to kill for the database in the where clause, if you uncomment the “EXEC” part, you will get the fastest connection killer you have ever used. 

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('DatabaseName')

PRINT @SQL
--EXEC(@SQL) /*Replace the print statement with exec to execute*/

Enjoy and Have a Magical Day!