Press "Enter" to skip to content

Curated SQL Posts

Check Those Backups

Published 2015-12-04 by Kevin Feasel

Andy Galbraith walks us through a backup issue he experienced recently:

These messages showed that a process of some kind ran just after 9 pm that switched the databases from FULL recovery to SIMPLE and then back again.  This broke the LOG recovery chain and required new FULL backups before any LOG backups could succeed, which is why the LOG backup job was failing.

This sort of interesting user behavior is why it’s so important to have automated systems in place to check for issues and, whenever possible, fix them.

Comments closed

Relational Lives On

Published 2015-12-04 by Kevin Feasel

Tony Davis on NoSQL:

There have been some spectacular examples where the lack of transactional integrity of NOSQL databases led to financial disaster. Even ardent NoSQL enthusiasts did U-turns on the value of ACID-compliance. And therefore, slowly, inexorably many NoSQL database begin to acquire the essential characteristics of a relational database. MongoDB now offers joins; N1QL and U-SQL bring good old SQL-style querying to “NoSQL” data. Many of the NoSQL databases are now laboring towards some form of proper transactional support.

I enjoyed Robert Young’s first comment:

the notion that NoSql “databases” are more flexible isn’t even true: chaotic, yes. but flexible means being able to move without breaking, and NoSql, due to the lack of schema, means that all manner of inconsistencies and redundancies are allowed. that’s not flexible, that’s nuts.

Comments closed

Windows Server Licensing Changes

Published 2015-12-04 by Kevin Feasel

Allan Hirt prepares us for a licensing letdown:

Say Hello to Core-based Licensing for Windows Server

This is the one that may annoy most folks. Like SQL Server, Windows Server 2016 licensing will be core-based, including the Core Infrastructure Suite SKU. Historically, Windows pricing has been MUCH lower than SQL Server, and no prices have been announced. So before anyone has a conniption, let’s see what the core pricing will be based on the chart shown on page 2, there are cases where the cost may be the same as it is today.

I’m now curious about how many people will hit a wall with Windows Server editions like we’ve seen with SQL Server 2008 R2.

Comments closed

Azure SQL Database Threat Detection

Published 2015-12-04 by Kevin Feasel

Troy Hunt introduces us to a fantastic new feature with Azure SQL Databases:

Azure has just introduced another tool to help in the fight against SQL injection known as SQL Database Threat Detection. You can go and read all the Microsofty bits there or watch it work in a real live app here.

Firstly, this is threat detection, not prevention. In a nutshell, this feature will tell you when an attack is mounted against your database and in order to do that, the upstream app has to have a vulnerability in it that’s allowing the attack to get that far. Now before you give it a bit of “well that’s pretty useless then”, the main reason this makes sense is that you can go and enable it with a single checkbox tick and it won’t break your things. Plus, even if the code is solid and you have a device or a service like a WAF, this is just one more layer that’s good to have in place. Let’s just jump into it.

This is a useful tool. If you’re using Azure SQL Databases, go forth and activate this.

Comments closed

Ending A Powershell Loop

Published 2015-12-04 by Kevin Feasel

Steve Jones shows how to end a loop early:

That’s fine, but it’s not a great loop. It runs 9999 times, which isn’t what I want. It works, but it’s an unnecessary use of resources. However I don’t want to break the loop when the file file isn’t found. There have been issues generating a file, like #350, when #351 exists and is there.

I decided to use a shortcut technique I had learned as a kid. I set a variable and then incremented it when I missed a file. When the increment reaches some value, I break the loop.

I’d just as soon use a break statement, but there are many ways to skin a cat.

Comments closed

Watch TMP Space

Published 2015-12-03 by Kevin Feasel

Erin Stellato shows us that Extended Events and Profiler both use local temp storage:

Depending on what events you have configured for Profiler, your filter(s), the workload, and how long you run Profiler, you could generate more events than the UI can handle. Therefore, they’ll start buffering to the User TMP location. If you’re not paying attention, you can fill up the C: drive. This can cause applications (including SQL Server) to generate errors or stop working entirely. Not good.

Reference: https://msdn.microsoft.com/en-us/library/ms174203.aspx

Now, back to the original question. Does the same problem exist for Extended Events? Only if you’re using the Live Data Viewer.  After you have an event session created (you can just use system_health for this example), within Management Studio, go to Management | Extended Events | Sessions, select the session and right-click and select Watch Live Data

This is one of those things you hardly think about, but it makes sense:  that data’s got to be stored somewhere if things are moving too fast for the app.

Comments closed

Naming Transactions

Published 2015-12-03 by Kevin Feasel

Gail Shaw asks, why name transactions?

So what conclusion can we come to here? Pretty much that naming of transactions has one real use, as a form of documentation. Instead of putting a comment above an BEGIN TRANSACTION we can give the transaction a name that indicates what the transaction does, That’s about the only real use.

With two exceptions.

The one reason I have to name transactions:  name and shame.

Comments closed

Getting Started With Azure

Published 2015-12-03 by Kevin Feasel

Mickey Stuewe introduces us to Azure:

At the bottom of the portal, there is a New link and a Delete link. These are for creating and deleting databases.

After clicking the New link, I went through a series of screens to create my database.

The first screen asked me for the name of my database and what size database I wanted to create. This is an important step, since it will affect my monthly charges. Remember, I only have $150 in free credits each month. You can go here to see the pricing for the various service tiers and the performance levels. I chose to create the smallest database I could (2 GB, and 5 DTUs). I also created this database on a new SQL Database Server (I kind of have to, since it is the first database).

Louis Davidson has begun to embrace(?) the cloud as well:

Both of the products, the On Premises versions and the Azure SQL Database versions are part of the Relational Database family of products. They share a common base, and a common purpose: to work with relational data. They look basically the same, and operate mostly the same, and serve (at their core) very same purposes.

As such I will make sure that all of the scripts that end up in the final book have been validated on the different editions of SQL Server (as I have always done), and have been executed at least once on Azure SQL Database as well. What I won’t do is go into many details of how to connect to an Azure SQL Database, mostly because it takes quite a few pages to do so (I just tech edited a book that covers such details, and I will direct readers to that book for details on how to connect… Peter Carter “Pro SQL Server Admin” http://www.springer.com/gp/book/9781484207116).

We’re already seeing Microsoft move to a cloud-first philosophy, so get in on Azure if you’ve avoided it thus far.

Comments closed

Feature Spelunking

Published 2015-12-03 by Kevin Feasel

Aaron Bertrand shows us how to find hidden features in CTPs:

In honesty, I’m just meticulous about installing each new build and immediately digging into the metadata. It would be hard to take a look at sys.all_objects and identify what’s new by sight; even columns like create_date and modify_date are not as accurate as you might expect. (For example, in CTP 3.1, sp_helpindex has a create_date of 2015-11-21 18:03:15.267.)

So instead of relying on photographic memory or hoping that something new will jump out at me while scanning the new catalog, I always install the new CTP side-by-side with the previous CTP (or, in the case of the very first CTP, side-by-side with the previous version). Then I can just perform various types of anti-semi-joins across a linked server to see objects and columns that have been added, removed, or changed.

Very interesting.

Comments closed

When To Use Temporal Tables

Published 2015-12-03 by Kevin Feasel

Randolph West wraps up his temporal tables series by asking when you should use them:

Tracking changes to your data is a big deal, and in databases with heavy churn, you can end up needing a massive amount of space to handle your history, especially if you need to retain seven years of data, like some of our customers.

If data storage is a concern, I’m going to recommend SQL Server Enterprise Edition. The Books Online documentation specifically states that the history tables for Temporal Tables are implemented with Page Compression by default, which is an Enterprise Edition feature.

My quick thought is, use them when you want a type 2 dimension without putting in the effort to create a type 2 dimension—in other words, non-warehouse systems in which you need long-term data changes.

Comments closed