SQL Server with Mr. 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


August 26, 2015  6:00 PM

Connecting To SQL Server Running On An Azure VM

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure, Cloud Virtual Machine Security, SQL Server, Virtual Machine Security, Virtual Machines, Windows Azure

If you have setup a Windows Azure Virtual Machine running SQL Server you may have noticed that you might not be able to connect to the SQL Server running on the machine by using SQL Server Management Studio on your workstation (desktop, labtop, etc) from your home or office.

This is normal and setup this way by default.

First I’ll explain the why, then how to change this.

The Why


The reason that this is disabled is that Microsoft doesn’t want to leave the SQL Server exposed to the public Internet where people can and will try to break into the SQL Server instance, pretty much 24 hours a day, 7 days a week until they get into the server. Microsoft is doing you a favor by not letting this happen by default.

Fixing It


Now, assuming that you want this access there’s a couple of ways to set this up.

Site to Site VPN


The first (and best) is to setup a site to site VPN between your office and the Windows Azure network. Now this requires a bit of configuration and a little understanding of networking (and access to make changes to your office’s router). If you aren’t the IT guy/gal, you’ll need to talk to the IT guy/gal in order to get this done. If you are the IT guy/gal and you have access to the router, Microsoft has provided some scripts to make this much easier to complete.

The first thing you’ll want to do it connect to your Azure account and browse to the virtual networks. Select the network that you want to modify and you’ll get the properties of the network to pop out on the next blade. It’ll look something like this.

Network_pre_vpn1

You’ll want to click on the VPN Connections section on the right, select site-to-site, check the “Create gateway immediately” check box, then click on Local Site and enter a name and the public IP for your office network as well as tell Azure what all the IP Subnets for your internal office network are. It’ll look something like this, then click OK to get back to the “New VPN Connection” blade.

VPN_in_process

Click the “Optional Gateway Configuration” button and change the routing from Dynamic to Static. If you want to change the IP subnet for the private side of the VPN within Azure you can do here as well. You can also select the gateway size between Normal and High performance, but the high performance one will probably cost more money. If you are a large enterprise you probably need the high performance option, if you are a small/medium business the normal gateway will be just fine. Click OK all the way back down to the virtual network blade and wait for the network changes to be made. This can take 5-10 minutes for the VPN endpoint to be created and spun up.

After Azure is done doing it’s thing the configuration section of the portal will look something like this.

vpn_almost_ready

Click on the “VPN Connections” panel and a new blade will open. At the top you can download the script to configure the office router to use the VPN connection.

Point to Site VPN


If you don’t want to setup a site to site VPN so that all office users can access Azure, or if you don’t have access to do so, you can setup a VPN from your specific machine to the Azure cloud. Just keep in mind that this option doesn’t support any user level authentication so if you give someone else access to your Azure network this way, you won’t be able to remove their access without revoking access for all users and setting everyone up from scratch.

The first thing you’ll want to do it connect to your Azure account and browse to the virtual networks. Select the network that you want to modify and you’ll get the properties of the network to pop out on the next blade. It’ll look something like this.

Network_pre_vpn1

You’ll want to click on the VPN Connections section on the right, select Point-to-site, and tell Azure what all the IP Subnet to use for people who VPN in should be. Any private IP subnet will work as long as you haven’t used it in one of your Azure networks already. The portal will verify that the IP subnet you enter will work. Something like “192.168.5.0/24” should work fine.

Click OK all the way back down to the virtual network blade and wait for the network changes to be made. This can take 5-10 minutes for the VPN endpoint to be created and spun up.

After Azure is done doing it’s thing the configuration section of the portal will look something like this (but with the Point to site icon in color).

vpn_almost_ready

Click on the “VPN Connections” panel and a new blade will open. At the top you can download the VPN software that you will need to connect your computer to the Azure network securely.

Opening Access to the Virtual Machine from the Public Internet


If for some reason you don’t want to VPN in, first you need to figure out why you don’t want to do this. If there’s a really good reason to not VPN in, then continue with setting up direct Internet access to the SQL Server. But keep in mind, when I talk to clients about this sort of thing, there is almost never a good reason to setup access to the VM directly from the Internet.

To open an endpoint browse to the VM in the Azure portal. Open the properties of the VM in the Azure Portal, then click the “All Settings” option. Then select “Endpoints”. It’ll look something like this.

VM_Settings

If you see a “SQL Server” endpoint with 0 ACL Rules then the work is half done (shown above). If there are ACL rules then you should be finished unless you need to add more ACL Rules.

If there is no SQL Server endpoint click the “Add” button at the top of the Endpoints blade. Name the endpoint “SQL Server”, select the protocol TCP, then set the ports to 1433 (or whatever TCP ports you want to use, but 1433 is the default). Select to setup access rules for whoever needs access and block any subnets that don’t need access and then OK back to the VM’s properties.

Endpoint

Now What


At this point you can connect to the SQL Server instance through whichever method you’ve setup. If you are using either VPN option you can just connect to the Virtual Machine’s network name. If you are going through the public endpoint (again this is REALLY NOT recommended) you’ll need to connect to the machines full DNS name.

Denny


August 19, 2015  6:00 PM

pssdiag has some useful parameters

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

Anyone who has ever called Microsoft’s CSS team for support for a SQL Server has probably been told to run pssdiag against the server while reproducing the issue, or for some period of time. To run pssdiag we just run pssdiag.cmd and let it do it’s thing, then press +C to stop it. But there’s actually a few command line options which are available which can make using pssdiag a bit easier.

/Q – Delete the data from the output folder before running.
/E – Stop pssdiag after n minutes or at n time. If you want to run it for 10 minutes for example use “/E +00:10:00”. If you want to stop it at 10pm use “/E 22:00:00”.
/B – Start pssdiag capture after n minutes or at n time. If you want to start it in 10 minutes for example use “/B +00:10:00”. If you want to start it at 10pm use “/B 22:00:00”.
/O – write the output to a different folder. Such as “/O T:\pssdiag\output”.

/N – Weather you should use the same folder everytime pssdiag runs, or keep all the captures. Using “/N 1” always using the same folder. Using “/N 2” appends a number to the end of the folder so that you can run the capture over and over and keep your results each time.
/M – Specifies the machine to run pssdiag against if you want to run it remotely using “/M ServerName Server2 Server3”
/C – To use compression or not on the files. “/C 1” uses no compression (this is the default). “/C 2” uses NTFS compression. “/C 3” uses CAB file compression.

If you need to make pssdiag a little more automated so that you can capture overnight issues this should present you with some really good options.

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: