DBCC Clonedatabase and Query Store

Published On: 2018-05-25By:

Cloning things is all the rage these days…..even Storm Troopers.

Anyway, by now, you probably have seen documentation on Query Store and DBCC Clonedatabase.  Query Store, introduced in SQL Server 2016, is considered the “black box” or “flight recorder” of queries.  It retains a history of executed queries, including run-time statistics as well as execution plans from query store enabled databases.   This flight recorder helps to diagnosis and pinpoint query performance issues.

Another useful tool is DBCC Clonedatabase.  This command started was released in older versions via service packs before being fully released in 2016.  This tool allows you to make a “schema” only copy of a database which will reside on the same server as the source database.  The schema includes both user and system schema, as well as any corresponding statistical data.

Why is this useful?

Let’s say you have a 1 terabyte database and wanted to perform query tuning on it. Storage is usually a premium commodity in most organizations and there isn’t a spare terabyte of storage just laying around.  DBCC Clonedatabase would allow you to make a schema only copy, including statistics.  Once the new database has been created, you could move the new database onto another server without having the requirement of large amounts of storage.  Since the database is really a schema-only copy, the footprint of the database is pretty small.  After moving the database, queries ran against it would utilize the statistics contained within to execute the query.  True, there isn’t any data in the database, but you can account for that when perform query performance analysis.

DBCC CLONEDATABASE (source_database_name, target_database_name) WITH [NO_STATISTICS],[NO_QUERYSTORE],[VERIFY_CLONEDB],[BACKUP_CLONEDB]

NO_STATISTICS – This option specifies that table/index statistics are excluded. Available with SQL Server 2014 SP2 CU3 & SQL Server 2016 SP1
NO_QUERYSTORE – This option specifies that query store data is excluded.  Available with SQL Server 2016 SP1
VERIFY_CLONEDB – Verifies the consistency of the new cloned database.  Available starting with SQL Server 2016 SP2
BACKUP_CLONEDB – Creates and verifies a backup of the newly cloned database.  Available starting with SQL Server 2016 SP2

The command works in this order:

  1. Creates a new destination database.  Same file layout as the source database however with the default file sizes from Model.
  2. Generates a snapshot of the source database.
  3. Copies the system metadata from the source to the target database created in step 1.
  4. All object schema is copied from the source to the target.
  5. Index statistics are copied from the source to the target.

Fairly easy and straight forward.  But wait! There’s more!

You will noticed the “WITH NO_QUERYSTORE” option in the command.  If you are running SQL Server 2016 SP1 or higher and utilizing Query Store for the source database, DBCC Clonedatabase will also bring over the query store data! This is enabled by default so if you do not want the query store data, you have to explicitly define using NO_QUERYSTORE.  This means, by default, if you clone a database you will get the flight recorder data from query store as well.

These two tools, query store and DBCC Clonedatabase are just another means to help troubleshoot performance issues.

 

 

 

© 2018, John Morehouse. All rights reserved.

Query Predicates

Published On: 2018-05-24By:

Sometimes people speak “SQL” and expect you know all the terminology. In recent conversation about query plans I was ranting on and on about how the predicates were written when the person stopped me and asked a very simple question. Like a lightbulb going off, it reminded me that not everyone knows what the other person is talking about when terminology is mentioned without clarification.

So, this post will answer that persons very valid simple question.

What is a SQL Predicate?

Predicates are expressions that evaluate to TRUE, FALSE, UNKNOWN. There are two types of predicates Filtered Predicates and Join Predicates.

Filtered Predicates cover your WHERE or HAVING clauses in your query. They are essentially search criteria.

JOIN Predicates cover your FROM clauses in your query. They are essentially merge criteria so to speak. They bring two or more tables together based on the join criteria.

So, there it is. Just a simple blog to answer a great question. Don’t ever be afraid to stop someone and ask a question, no matter how simple the question may be. Knowing the terminology is important. I stop and ask people to clarify terminology all the time during their presentations or a conversation between my co-workers.

 

 

Microsoft Kinda, Sorta Broke RDP: That CredSSP RDP Error

Published On: 2018-05-16By:

If you’re running into this, it probably started on May Patch Tuesday, which was last week (the 8th). You are getting this error when you try to RDP to a/some servers:

An authentication error has occurred.

The function requested is not supported.

This could be due to CredSSP encryption oracle remediation.

Here’s what happened

An authentication error has occurred. The function requested is not supported. … This could be due to CredSSP encryption oracle remediation.

Errors make me sad

In March, a vulnerability in CredSSP (Credential Security Support Provider) was patched, which would affect authentication via RDP (this is outlined in advisory CVE-2018-0886).  However, it was implemented in such a way that the behavior change didn’t have to be “honored” by either the server or the client involved in an RDP session.

 

The intent was that this would be controlled by GPO in enterprise environments, and a new GPO setting to activate or deactivate this behavior was released at the same time.

GPO settings have a default value, which they will use when nothing has been explicitly set for a particular setting. In this case, the GPO has three possible values: Force Updated Clients (for servers to only take connections from patched clients), Mitigated (for both, and on a workstation means that it won’t fall back to old/insecure behavior when attaching to unpatched servers), and Vulnerable (for both, and means what it sounds like–anything goes!).

In March, the default behavior was set to “Vulnerable”, which means everything kept working for everyone. But in the May security rollup, the default setting for that GPO was flipped to “Mitigated” if there was not an explicit setting for it… end result being the core problem some are running into: Clients that have received the May update are no longer able to connect to RPD servers that have not received the March vulnerability fix.

Welp.

(For a bit more background on all of this, see this Microsoft blog post: https://blogs.technet.microsoft.com/askpfeplat/2018/05/07/credssp-rdp-and-raven/)

Good News: Easy Workaround

Fortunately, there’s an easy workaround that can be applied to any Windows workstation facing this behavior, with a couple caveats.

If you are getting the above error trying to RDP to a server, all you have to do is set the corresponding GPO on your local workstation to Vulnerable.

To set this, run “gpedit.msc” on your machine. When the Local Group Policy Editor launches, navigate to Computer Configuration\Administrative Templates\System\Credentials Delegation on the left side, and then find Encryption Oracle Remediation on the right. Open that up, flip it to Enabled, and then choose “Vulnerable” for Protection Level. Hit OK, close GPEdit & you’re done; the change will take effect immediately.

There are a couple caveats: First is, this means you’re choosing to operate in an unpatched situation, which I don’t recommend. The second is that you can only apply this GPO setting on your local workstation if you’re not in an AD environment where it’s been set at the domain level and it’s getting applied to your machine. If that’s the case, the AD-level GPO will stomp on your local setting if it’s different.

Again: This should only be a temporary measure. The real fix is to get the March updates on your servers so you can set your workstation back to at least Mitigated (really should be Force Updated Clients). It’s not going to be my fault if you leave things unpatched and in Vulnerable and then something bad happens!

Commentary

Some have been referring to this as a “bug” and…This isn’t a bug; I mean, the “breaking RDP” part isn’t a bug (the original vuln obviously is). This is 100% “system functions as designed.” There’s a vulnerability in a widely-used feature of Windows, and MS pulled the “better to be on the ground wishing you were in the air, than being in the air wishing you were on the ground” card here. Being a patch hard-liner (I saw too much shit in the early 2000s), I think this is fine. If you don’t like it, there’s a workaround. But, my attitude on this is tempered by the fact that it’s only listed as an “Important” update, and the exploitability seems a little bit out there. Maybe give us all a few more months to notice?

Regardless, I DO think there was a communication failure here, though. Since few people read patch notes on a regular basis (I don’t even, anymore), relying on those to get the message to people isn’t going to work. Even that PFE blog post–which is great–is still a little bit of shouting into the void until someone runs into the problem and goes looking for a solution.

I don’t know what to do about this part, because there’s really just not a mechanism to deal with it. And really, do we need another thing to watch for alerts and stuff? Plus, breaking changes happen on a regular basis… where do you draw the line? And what, should they have made the RDP client throw a pop-up message about this? That seems like an awful big hammer.

I guess I’m going to have to go back to reviewing KB articles for patches again :-/

How should I pass exams?

Published On: 2018-05-14By:

There have been some questions recently about how people should pass certification exams.

https://www.flickr.com/photos/ilamont/4150684641/

 

Taking the exams

The answer is pretty straightforward, use the product that the exam is on. Certification exams (I’ve only taken the Microsoft ones, but I’m sure the other vendors are similar) are build

on actual product use. Now you may not use every feature of the product in your day to day life, but the exams aren’t going to be based on what you do. They’ll be based on what the product can do.

Let’s look at one of the SQL Server exams for example, specifically “Administering a SQL Database Infrastructure.” The notes on the exam say what it will cover, and in what percentages.  For example, 35-40% of the exam will be on managing and monitoring SQL Server instances.  And if you aren’t sure what’s considered “Manage and monitor SQL Server instances” the website spells this out as “Monitor database activity, queries, indexes, statistics, and instances.”

Now you may be thinking that you use RedGate’s SQL Monitor or SolarWind’s Data Platform Advisor (or another tool) to monitor your instances. These exams won’t be testing on these. Assume that only native tools exist because that’s what Microsoft will be testing you on. After all, Microsoft can’t test on another companies product.

There may be times when you don’t agree with the percentages on the exams. I know a big argument exists over how much XML should be on the exam.  I referrer you to the notes on the exam on the Microsoft site. If it says the exam may be 40% of something, then it probably will be.

What’s the best way to PASS the exams?

First, let’s talk about brain dumps. They’re not always right, and there’s no way to know when they are wrong. And when they are wrong, who are you going to complain to, the person/company that violated their NDA with Microsoft to make the brain dumps?

When you take Microsoft Exams (or any exam), you sign an NDA that you won’t talk about the content of the exam, and you won’t cheat on the exam. That means that if you use a brain dump, or tell other people to use a brain dump, or help build a brain dump, you’re cheating and potentially in violation of your NDA with Microsoft.  What that NDA says, in short, is that Microsoft can revoke your certifications and prevent you from passing any future certification exams.  All for talking about their exams.  Now you may feel like that’s a bit strong, but Microsoft takes their anti-piracy actions against the certification exams very sceresouly.

The good news is that there’s an easy way to take the exams more than once for free.  That’s using the Microsoft Exam Replay option as it lets you take the exam again if you don’t pass.  What you can do with this, is get the Replay and take the exam. If you don’t PASS that’s OK, you’ll now know what you didn’t know on the exam so you can study up in those areas and take the exam again for free.

Also, keep in mind, the exam documents online will say how much usage of the product is expected to PASS the exam.  Most of the exams assume 3-5 years experience. If you’re just getting into technology, then you probably aren’t ready for the exams.

Practice Tests

Practice Tests are written by different people then the exams, so they’ll be different questions. This is on purpose. The questions can’t be the same, that would be cheating.  While practice tests are good, they aren’t the actual exam.

Now I know this hasn’t all been good news, but it wasn’t supposed to be. It was supposed to be an honest breakdown of what the certification exams look like and how to study for them.  The first step is to have a couple of years experience using the product.  The second step is to use the Retry feature that Microsoft offers, or whatever they move towards in the future.

Denny

The post How should I pass exams? appeared first on SQL Server with Mr. Denny.

1 2 3 377

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.