SQL Server with Mr. Denny


October 7, 2015  4:00 PM

Basic Database Mirroring Monitoring

Denny Cherry Denny Cherry Profile: Denny Cherry
Database mirroring, Disaster Recovery, DR, High Availability, Monitoring, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

If you have database mirroring setup and running, you’ll notice that it has a lack of built in monitoring. And if you have a lot of databases having monitoring for each potential state that each database can be in can flood you with email if the network between sites goes down and comes back up. Especially if it happens a few times in a minute (the record that I’ve gotten using this sort of configuration is about 700 emails in one night of network maintenance.

So I’ve put together a slightly more simplified database mirroring monitor. It’ll still tell you if the network goes down and comes back up, but it’ll do so if a slightly nicer way. You’ll want to change the @operator_name value to a valid operator on your system before you run this, otherwise you’ll get an error as the operator doesn’t exist. I’ve set the delay between retries at 5 minutes, so even if the network is going nuts you’ll get at most 12 emails from each alert per hour. Depending on how stable your network is you may want to adjust this as needed.

Never fear, this works on SQL Server 2005 – SQL Server 2016 (CTP 2 at least).

USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirror Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 6',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM Perf: Oldest Unsent Transaction Threshold'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=32040,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirroring Suspended'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 9',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Principal Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 5',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

Happy monitoring,
Denny

October 1, 2015  1:08 PM

SQL PASS Summit 2015 Discount Code

Denny Cherry Denny Cherry Profile: Denny Cherry
discount coupons, IT conferences, IT conferences and events, SQL Server

Are you looking to register for the SQL PASS Summit and you haven’t already done so?  Would you like to save $150 off the current registration price?  Would you like to save that $150 with no strings attached?

Have I got a deal for you.  Simply use the discount code EXSP150 when you register for the PASS Summit and you’ll save $150.

That’s it.

Denny


September 30, 2015  6:00 PM

Upgrading a SQL FCI to Premium Storage in Azure Without Downtime

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure, Cluster computing, SQL Server, Windows Azure, Windows Cluster

Recently I was asked by a client to upgrade their SQL Server Failover Cluster from standard storage to Azure premium storage with as little downtime as possible. Due to the fact that the SQL Server instance was clustered already this was actually a pretty straight forward process.

The first step was to figure out which node of the cluster was running as the active node, so we could start with the passive node. The next step was the tell cluster to not allow failovers of the SQL Cluster. Next we opened the SIOS Data Keeper Cluster Edition GUI and break the mirror for the disks that we are going to upgrade. Then I logged into the Azure portal and converted the VM from a G2 into a GS2 so that premium disks could be attached. After the VM restarted (don’t forget, this is the passive node so there’s no outage for the restart) the disks are removed from the VM and the new disks are added. The new disks were added via PowerShell like this:

get-AzureVM -name ServerName | add-AzureDataDisk -CreateNew -DiskSizeInGb 1023 -DiskLabel ServerName-T http://Something.blob.core.windows.net/vhds/ServerName-t.vhd” | update-AzureVM

After the disks are all added, they are formatted and given the correct drive letters.

Next SIOS Data Keeper Cluster Edition is told to restart the mirror. This forces it to do a full sync as there’s no data on the old drives. This takes forever as we are limited to reading data from the disk at the speed of the old standard disks (500 IOPs). Once it’s done (in this case there was about 200 Gigs of data to replicate across three disks) the cluster can be failed over (this is the only outage in the process).

We can now upgrade the second VM to support premium storage, then change out the disks and restart the replication again. It’s a long process but it works, and there’s just a single outage to the process.

Denny


September 25, 2015  5:00 PM

Recommended reading from mrdenny for September 25, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Using Microsoft DiskSpd to Test Your Storage Subsystem

DBTA – StretchDB, a Cool New Feature in vNext SQL Server

The Case Of The Auto-Truncating Table

Updating Your Data Strategy Requires a Shift in Thinking

Study Finds that CIOs Vastly Underestimate Shadow IT

 

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


September 17, 2015  10:59 PM

PASS Summit 2015 1st Timers Webcast Recording

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences and events, SQL Server

This year’s PASS Summit 1st Timers webcast recording didn’t happen as planned. And sadly my voice is currently gone so I’m not able to record a new one in the few days that I’m at home. So because of that I’m reposting a link to last years video (which is mostly the same as this years) and you’ll find the updated information below. So without further delay here is your PASS Summit 2015 2014 1st Timers video. Last years video was kindly sponsored by SIOS, so they are sponsoring this years video as well. 🙂

The differences between this year and past year are:

  • This is 2015 not 2014
  • My SQL Karaoke party is on Tuesday October 27th, 2015
  • The URL to register for the party is http://www.dcac.co/go/2015-party, and this years party is being sponsored by SIOS just like last year
  • The URL to register for Argenis Without Borders is here
  • The URL to register for PASS Summit 2015 Speaker Idol can be found here
  • The convention center is a little bigger in 2015.
    • PASS is expanding into the convention space called the TCC (The Conference Center).  You can access the TCC by talking across Pike Street or by walking through the lunch hall (4E & 4F).
    • The registration area will be back where is used to be in the atrium under the escalator to the 6th floor.  I’m pretty sure I pointed it out in the 2014 recording as where it was in prior years.

Sorry for having to use the 2014 recording, but it is what it is.  Thank you to SIOS for coming through so quickly with the recording for last year’s webcast.

Denny


September 16, 2015  4:00 PM

Want to speak at the PASS Summit, but haven’t been selected?

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

The Speaker Idol may be your answer to how to get to the PASS Summit as a speaker. The Speaker Idol competition, in it’s second year now with PASS, will be held throughout the conference with four different rounds. During the competition people who haven’t spoken at the PASS Summit will be giving 5 minute presentations to a panel of judges and the audience. The competitors are competing for the grand prize, a speaking slot at the PASS Summit 2016.

What do you need to do to get into speaker idol?  Step 1 is to fill out the application form.  It’s got a few basic questions about where you’ve presented before and how to get in contact with you.  That’s pretty much it.  From the people that submit for the Speaker Idol we’ll select 12 people to be in the competition.

The full rules for being eligible can be found here.  With the rules as they are written probably 95% of PASS attendees are eligible as are most people who have spoken at an event (other than the PASS Summit) before.

The Speaker Idol is a great way for those speakers who aren’t as well known in the community to get in front of a national audience, which will include members of the content committee (the folks who select the sessions for next year).

So if you’ve ever wanted to speak at the PASS Summit, this is your chance.  Get signed up, and get ready to present.  If you don’t register you can’t earn yourself a speaking spot at the PASS Summit 2016.

Denny


September 9, 2015  5:17 PM

SQL PASS Summit 2015 Karaoke Party

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences, SQL Server

It’s that time of year again, it’s almost time for the PASS Summit. Like the last few years I’m pleased to announce that I’ll be hosting my annual SQL Karaoke party during the PASS Summit again. SIOS Technology Group is kindly sponsoring this years party, just like last year so a hugeSIOS Logo thank you to SIOS for making this great party possible. Last year we had a sell out crowd at the party (even after increasing the number of people by getting a much larger venue) and hopefully we’ll have the same great turn out.

20141105_004201This years event will be at the same venue, Cow Girls Inc. Like last year we’ll have the live band instead of the KJ. And yes the mechanical bull will be running (we didn’t hurt anyone last year, so we’ll try again this year) so you can drink, sign and ride the bull; the perfect combination.

 

The party will be October 27th at 9:30pm and will run until about 1:30am Wednesday morning. Conveniently the welcome reception for the PASS Summit ends at 9pm Tuesday night so you can go straight 20141104_232201from there to the Karaoke party. The only requirements to attending is the ability to have a good time. Singing isn’t required (but someone has to do it, and it won’t be me, which is for your benefit not mine, trust me), but if after you’ve had a little courage in a glass you want to, we’ll have a great band to back you up.

Like in prior years anyone is welcome to attend, but you’ll need a wristband for the open bar if you want the free drinks. You can register for a wristband at http://www.dcac.co/go/2015-party. The wristbands are first come first served, and you’ll need to be at the bar by 11pm to pick up your wristband.

I look forward to seeing everyone at the PASS Summit and at the SQL Karaoke party on Tuesday night.  Below you’ll find some pictures from last years party.20141105_004131

Denny


September 7, 2015  11:18 AM

Pre-SQL PASS Webcast is Today!

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences, SQL Server

Just a reminder that today is my Pre-SQL PASS Webcast (also known as the 1st timers webcast). The webcast is at 1pm Pacific Time today (Sept 7th, 2015). Get signed up, to get the conference call details. If you missed it because of the US Holiday don’t worry, it’ll be recorded and posted shortly.

Denny


September 4, 2015  2:00 PM

Recommended reading from mrdenny for September 04, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Building High Performance, Highly Available SQL Servers on Azure

Fear has Replaced Apathy as the Number One Enemy of Data: Implications for Lovers of Data

10 Emerging Big Data Vendors to Watch

Modeling Slides: $#@! Your Database Says About Me…and How to Fix It in Your Data Model

Nuance Says Your Car Will Understand You Much Better Within a Few Years

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


September 2, 2015  6:39 PM

PASS Summit 2015 1st Timers Webcast is Next Week

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

Just a reminder, that if you are planning on attending the PASS Summit this year you’ll want to make sure to get registered for my 1st timers webcast which is scheduled for September 7th. 2015 at 1pm Pacific Time. I know it’s a holiday here in the US, but apparently I forgot that when I scheduled it, and it is one of the few days that I’m actually in the US during the month, so you take what you can get.

In order to register for the session please visit this url. Once you have registered you’ll be able to get the conference call details as well as download a calendar reminder.

If you haven’t ever attended the PASS Summit before we’ll cover all the important things you need to know about the event, such as how to get from the airport to the convention center (hint, you don’t want to rent a car). Where the good food in town is, how to find things inside the convention center (it’s a big place if you’ve never been there before), and much more.

So get signed up for the webcast, get the calendar entry put into your calendar, and I’ll see you on Monday September 7th.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: