SQL Server with Mr. Denny


August 27, 2018  4:00 PM

SQL Karaoke 2018 at the PASS Summit 2018 is live

Denny Cherry Denny Cherry Profile: Denny Cherry
MSSQL

Another excellent PASS Summit is almost here. And with the PASS Summit comes the SQL Karaoke party. And once again DCAC is hosting this great event which will let us all ring in the PASS Summit in style.  We have two fabulous sponsors for the event this year, with our great friends at SentryOne and SIOS sponsoring the event.

Just like in years past, this is the PASS Summit party you don’t want to miss. We’ll have the live band playing all your favorite tunes for you to sing to, soda, beer, wine and well drinks for those with a wristband and a cash bar for those that don’t get a wristband.

The party starts at 9:30 pm and rocks until 1:30 am giving you plenty of time for a nap before the keynote begins on Wednesday.

Be sure to bring your Eventbrite ticket to the door to get your wristband, as well as Photo ID (drivers license or Passport) to get to get into the bar). And come and have a great time.

The PASS Summit Summit code of conduct will be in effect at this event.

You’ll find the link to the party here, so get registered.

Just like in prior years, you should register for EITHER the cash bar or the open bar. The open bar includes beer, wine, and well drinks. For the cash bar, you’ll be paying for your drinks.  So get signed up, and we’ll see you at the party.

Denny

August 20, 2018  4:00 PM

Database Maintenance and ColumnStore Indexes

Denny Cherry Denny Cherry Profile: Denny Cherry
Database, SQL Database

Recently on Twitter I saw a question about what index maintenance you should be doing on your ColumnStore Indexes when it comes to maintenance. As I looked through DCAC’s blog posts I realized that none of us had blogged about this topic before.

The reason for this is pretty basic, there’s really nothing to do when it comes to maintenance on a ColumnStore index.

Normally we do maintenance on indexes to reduce fragmentation, but there’s no fragmentation on ColumnStore indexes as the data isn’t stored on the table in a sort order. Data pages that aren’t needed anymore are removed automatically (the same with dictionary pages). The other big thing that index maintenance does, it update statistics on indexes. Well, there are no statistics on ColumnStore indexes, so there are no statistics to maintain. Why aren’t their statistics? Because every operation is a scan of a ColumnStore index. Since everything is a scan, there’s no need for statistics.

Denny


August 13, 2018  4:00 PM

Why Profiler For Life?

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Profiler

I admit it, I still use SQL Profiler. I always have, and I will for the foreseeable future. My reasons are pretty simple.

  1. When it comes to troubleshooting I can spin up a Profiler session must faster than an Extended Events session.
  2. Usually, I’m troubleshooting something as a one-off. So having my session isn’t really something I care about.
  3. I usually can’t bring scripts into my client’s servers to setup Extended Events, so I have to go create everything manually. (See #1)
  4. Done correctly with filtering, there’s no risk of a production outage using Profiler.
  5. If I could bring in scripts to set up an Extended Events session (see #3) I’d have to modify the session which I can do faster in profiler than in Extended Events.

Should you be using Extended Events? Probably. Odds are you got a full-time job somewhere, so extended events sessions are going to make more sense for you as you can run them against your servers and easily jump on and see what the server is doing.

What would it take get me to use Extended Event sessions instead of Profiler? Speed. Whatever GUI Microsoft creates for Extended Events needs to be just as responsive as the Profiler GUI, and the data that is returned needs to be returned by Extended Events just as quickly as data is returned from Profiler.

Denny


August 6, 2018  4:00 PM

Get some behind the scenes information on PASS Summit Speaker Idol

Denny Cherry Denny Cherry Profile: Denny Cherry

If you’ve been thinking about submitting for the PASS Summit 2018 Speaker Idol, but you aren’t sure that it’s for you, we can solve that problem.  On

https://www.flickr.com/photos/oskay/8668965783

August 30th, 2018 at 1600 UTC (4 pm) join myself, Karen Lopez, and Joey D’Antoni at the PASS Professional Development Virtual Chapter as we talk about Speaker Idol at the PASS summit.  You can register for the session on the PASS Professional Development VC website. We look forward to seeing everyone at the webcast.

 

Denny


July 31, 2018  11:46 PM

PASS Summit Speaker Idol is here, and the window to submit is closing

Denny Cherry Denny Cherry Profile: Denny Cherry

While yes, the PASS Summit is a few months away still, the window for submitting for the PASS Summit 2018 Speaker Idol competition is starting to close. As I PASS Logoannounced in June all entries to this years speaker idol need to be submitted by September 8th, 2018. That’s just a little over a month away. This give me time to review the submissions, notify the contestents, have a call with them, and give them time to make their presentation for the PASS Summit.

If you’re an aspiring presenter who’s looking to learn from some of the best presenters out there, then this is for you and you need to sign up.

Denny


July 29, 2018  12:52 AM

Why has my Azure Load Balancer stopped working?

Denny Cherry Denny Cherry Profile: Denny Cherry

Azure and the load balancers can be annoying little things. Especially when you are doing maintenance on your Azure environment, or setting it up for Cloudflare being the cloudthe first time. One of the quirks you may run into is that if you leave an Azure Load Balancer sitting for to long with no machines behind it, you may find that the load balancer itself just stops working.

Thankfully fixing this is actually pretty straight forward. Simply delete the load balancer from Azure and recreate it. Now you may be worried about dropping the IP address that it’s configured with, and don’t worry, you don’t have to. If it’s an Internal Load Balancer (for a failover cluster or a SQL Availability Group for example) then it’s just a private IP and you can just reassign the same IP when you recreate it. If it’s a public IP, you don’t need to drop the public IP address object from Azure, which means that the public IP address will stay right where it is. You can just reuse the same IP address object and you’re good to go.

It’s an easy fix to an annoying problem. For internal load banacers (ones with private IP addresses) they can be really hard to troubleshoot as there’s next to no logging done on an internal load balancer.

Denny


July 17, 2018  10:19 PM

General availability: SQL Database auto-failover groups – and it looks awesome

Denny Cherry Denny Cherry Profile: Denny Cherry

A really great feature in Azure SQL DB went GA today. That feature gives you and SQL DB the ability to automatically fail databases over to a Secondary replia, without having Cloudflare being the cloudto configure your application to handle that failover. You point your application at a VIP and that VIP will automatically handle failover of the resource.

Say for example you have a database in US West named db1-west.database.windows.net and the DR copy of it in US East named db1-east.database.windows.net.  This feature lets you create the VIP db1-vip.database.windows.net which automatically points to whichever database is currently active.  In the event of a failover of US West, the VIP is going to failover to the database in US East, the database in US East become writable and when the US West is back up, the data will sync back.

Another cool thing which this feature does is something that most features won’t do, it’ll trigger a failover that allows for data loss. Now, this normally would be a very dangerous thing, but the Azure team has come up with a safe way of doing it. When you figure the service to do the failover, you decide how long you want to wait for there to be no data loss. If you want the system back up as soon as it allows for, select the smallest number, otherwise select a larger number. This allows you, and the business unit that you support, to decide what level of protection you want to have built into the system.

If you are thinking about moving to PaaS, not being able to have a DR option may have been stopping you. This is no longer a blocking point, you now have an easy to configure DR, that you can manually failover is need be.  If you’re thinking of moving to Azure, DCAC can help to plan and execute that migration. Contact us today, to schedule a meeting to discuss if the cloud is right for you.

Denny


July 10, 2018  6:59 PM

Exchanging SQL Certificates with different service accounts can be tricky

Denny Cherry Denny Cherry Profile: Denny Cherry

Setting up Transparent Data Encryption, or Backup Encryption, or basically anything else where you need to restore a certificate to another SQL Server instance can get tricky as soon as you run the instances under different service accounts.  The problem that people will usually run into is that all the example code assumes that all the instances are running under the same service account, but this isn’t always the case in the real world.  So let’s review the code that Nic posted to Stack Exchange earlier today.

/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
GO

/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
WITH SUBJECT = ‘Backup Encryption Certificate’;
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
ENCRYPTION BY PASSWORD = ‘SomeRandomPwd’;

BACKUP CERTIFICATE BackupCert TO FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShareBackupCert.pvk’,
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);
GO

/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
GO

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShare\BackupCert.pvk’,
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);

–Msg 15208, Level 16, State 6, Line 32
–The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’;

–Msg 15317, Level 16, State 2, Line 39
–The master key file does not exist or has invalid format.

Now as we can see, the creation of the certificate failed, but only when running each server under its own service account. The question becomes why.

The answer is depressingly simple, NTFS permissions. By default SQL Server will make it so that the backup of the certificate is only available to the account that is running the instance that creates the certificate backup. So if you need to restore the certificate using the CREATE CERTIFICATE syntax shown in the example you’ll get an error. If you change the permissions so that the second SQL Account can read the certificate backup file you created then the restore will work.

Denny


July 2, 2018  4:00 PM

Monday November 5th, 2018 Brings My PASS PreCon on Security

Denny Cherry Denny Cherry Profile: Denny Cherry

On Monday November 5th, 2018 I’m thrilled to say I’ll be presenting a precon at the PASS summit.  This years precon will be on database security.

The abstract from the PASS website:

With increased focus on data breaches, security issues, and privacy laws, organizations are feeling greater pressure to provide sufficient data protection. GDPR brings substantial fines and potentially criminal prosecution. Are you ready? Are your database systems ready?

During this full day Precon, we will review a variety of ways to secure your SQL Server databases and data from attack. In this session, we will review proper network designs, recommended firewall configurations, and physical security options. We will also review data encryption options, password protection options, using contained databases, and Always On Availability Groups security.

There will also be discussions about additional measures which should be taken when working with Internet-facing applications. From there we will move to the most common attack vector: SQL Injection. We will include all the ways that attackers can use SQL Injection to get into your system and how to protect against it. The security options for database backups is the next topic on the list followed by proper SAN security designs. We will then finish up by reviewing the auditing options which are available against a database and how they can be used to monitor everything else which we’ve just talked about during the day.

There are no prerequisites to attend the precon, so anyone can attend, no matter their experience level with SQL Server (some experience with SQL Server would be helpful).

There are already 100 seats sold, but the room will hold plenty of more people, so there are still seats available. However don’t wait too long, as you could end up waiting for the pre-cons to be completely sold out. So book now, while there are still seats available.

I’ll see you at the PASS Summit,

Denny


June 25, 2018  4:00 PM

Welcome back, to the PASS Summit 2018 Speaker Idol competition

Denny Cherry Denny Cherry Profile: Denny Cherry

I’m thrilled to report that the folks at the PASS Summit have asked me to put together the speaker idol competition again.

Picture “borrowed” from Jamie Wick’s website. Click for original.

If you haven’t seen the speaker idol before it’s kind of like the TV show of the similar name. But instead of winning a contract that’ll never be fulfilled by some recording company, you win a speaking spot at the next PASS Summit.  One of the parts I love about Speaker Idol is that by the end of the PASS Summit we know who is the first speaker is for the next year.  It also gives 12  speakers the chance to speak at the PASS Summit lightning talk style with immediate feedback from our panel of judges, who are all top PASS Summit speakers.

If you’ve always wanted to speak at the PASS Summit but haven’t been selected, this is your chance to get in front of the community and make yourself known. Several past contestants of Speaker Idol have gone on to speak at that PASS Summit even without winning the competition.  One of the benefits of competing in the Speaker Idol competition is that it gives you visibility to the program committee, the judges and the community at large so that more people are aware of you and your speaking style.  This alone will increase your odds of being selected to speak at the PASS Summit in the future (if you don’t win the competition).

If you competed in a prior speaker idol (and you didn’t win a speaking slot at the PASS Summit), you’re welcome (and encouraged) to submit again.

Here are a few blog posts that some of our prior contestants have posted about the experience.

Ginger Grant

Shabnam Watson

Rob Volk

Now for the rules (or you can just sign up here):

Eligibility

You must have spoken at one of the following events (or a large international event, in a format other than a lighting talk, where your session had at least 30 minutes of content delivered by you):

  • SQL Saturday
  • SQL Bits
  • SQL Server Days
  • Microsoft Ignite
  • Dev Connections
  • Another large event with a national or international reach

You have not spoken at the PASS Summit of PASS Business Conference for a breakout, spotlight or pre-con session. (If you gave a lightning talk and no other session you are eligible.)

You are not scheduled to speak at the 2018 PASS Summit.

You will be in Seattle during the PASS Summit (attending PASS isn’t needed, and if you aren’t attending PASS we will get you to the session room for Speaker Idol).

You are available to attend whichever speaker idol session(s) you are assigned to (including the final session on Friday afternoon).

You have a 5-minute technical presentation which you can give. (Time management is your responsibility.)

You sign up on the registration form before September 8th, 2018 at 11: 59:59 pm (23:59:59) according to Google’s time function in their spreadsheet application.  Late applications can’t be considered, so just don’t be late. There’s plenty of time.

You are not a member of the PASS Board of Directors or an employee of PASS.

You are not employed by a sponsor of Speaker Idol.

Contingencies

If a contestant drops out of the contest before the PASS Summit starts the organizer (Denny Cherry) will reach out to the next person on his list that wasn’t accepted and offer them a position in the contest.  People will be offered spots in the competition until someone accepts.  The organizer will NOT announce how many people if any turned down a spot in the competition.

If a contestant drops out of the contest during the PASS Summit (for purposes of this clause “during the PASS Summit” is defined as from the time that Denny gets to Seattle, which will probably be Saturday or Sunday before the conference, until Friday afternoon), no changes to the lineup will be made.

If a contestant wins their round and cannot continue to the finals for any reason, the runner-up from their round will be declared the winner of their round and will be moved to the finals.

If a judge is not able to complete the competition, they will not be replaced.

Other Idol Rules

The wild card winner will be selected by the judges at the end of the Friday morning round from the runners-up from each round.  If a winner is not able to compete in the finals, and the runner-up is already speaking in the finals, the judges may consider all contestants from that round as eligible for the wildcard slot.

Judges do not have to recuse themselves because a friend is in the competition.  Judges are expected to be fair (We’ve proven that over the years we can act like adults.)

You are responsible for your travel to the PASS Summit to compete in this competition.

Presentation Format

You may present your session in any format that you’d like.  You can view the recordings from 2015 (the 2016 recordings current require that you purchased the recordings, I’m working on fixing that and the 2017 sessions I couldn’t find when I looked) via PASS TV.PASS Logo

While you can use any format when giving your presentation that you’d like, it is recommended that you use a format which will show the judges how your presentation skills will translate from a 5-minute session to a 75-minute session as that’s how long the PASS Summit sessions are.

Contestants are allowed to watch the other sessions during speaker idol and make changes to their presentation as they see fit.

Signing Up

In case you missed it above, the Sign Up Form is <- there.

Stay tuned for announcements on the judges and the contestants.  (Judges won’t be announced until a little later.)

What’s Next?

After you sign up, shortly after the sign-up period closes, the applicants will be sifted through, and the 12 contestants will be selected and emailed.  They’ll have a short period of a couple of days to accept the invitation to compete.  Once we have 12 “yeses”, we’ll all get on the phone to talk through what you should expect, talk about what kinds of sessions you want to give, and get any questions answered from this years 12.

That’ll give a few weeks for the 12 contestants to put together their slides for the speaker idol and prepare to give the best 5-minute sessions that they can.

See you at the Summit,

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: