Triple Check Your Restores

I used to work at a nuclear power plant. Not really in the plant, but as a network administrator in the building next door to the plant. Probably a good thing since I struggled to get to work on time and everyone going into the plant had to go through a metal detector like that at most airports. My tenure might have been shorter if I had been late every day to my desk.

However, there was one thing that got drilled into me by each person I knew who did work closely with the power generation side of the business. Everything was (at least) triple redundant. Not only did they want a backup for a component, or a system, but they wanted a backup for the backup. There was a limit to the paranoia, but in critical places, where radiation was concerned, we had multiple backups. One notable, and impressive, area was the power for the control rooms and pumps. In addition to batteries for short term power loss, there was a large diesel generator for each of our two reactors, plus a third that could take the load if either of the first two failed. Those were impressive engines, each about the size of a very large moving truck and jump started with a few dozen large canisters of compressed air that could spin the crankshaft in a split second.

This week there was a report that the database for the US Air Force Automated Case Tracking System had crashed. Apparently the database became corrupted, which happens. However, the surprising part of this story is that the company managing this system reported they didn’t have backups and had lost some data going back to 2004. They are looking to see if there are copies in other places, which I assume might mean exports, old backups or something else, but the reports make this seem like a completely unacceptable situation. I assume this is an RGE event for a few people, perhaps all of the staff working the system.

I was reminded of my time at the nuclear plant because we had a similar situation. We didn’t lose any data, but we found a backup system hadn’t been working for months. Those days we had a tape drive system that automatically rotated 7 tapes. I think this would last us about 4 or 5 days, so it was a once a week job for one administrator to pull the used tapes and replace them with new ones. We had a system where tapes were used 4 or 5 times before being discarded, and our rotation had a tape being used every 3-4 months. However, the person managing the system rarely restored anything.

One day we decided to run a test. I think this was just my boss giving us some busy work to keep us occupied but in a useful way. When we went to read a tape, it was blank. Assuming this was just a mix-up, we grabbed one of the tapes from the previous day and tried it.


At this point, my coworker turned a bit red and started to stress. He was in his 40s, with a family and mortgage. I was in my early 20s and had no responsibility here, but I could appreciate his concern. We frantically loaded tape after tape, even looking at the oldest tapes we’d just received from our off-site provider. None were readable, and most were blank. We nervously reported this to our boss, who had us request a sample of tapes from off-site storage going back over 6 months.

Eventually we realized that we hadn’t had any backups for about 4-5 months. The tape drive had stopped working properly, hadn’t reported errors, but dutifully kept retrieving files and rotating tapes each week, unable to properly write any data. No databases, no email, no system was being backed up.

A rush order to our computer supplier had been placed the first day to get us two working tape drives that we manually loaded tapes in each day, and checked them the next morning. Eventually we replaced the drive in our tape leader and instituted random weekly restores to be sure we had working backups. I’m not sure if the plant manager or upper IT management was ever told, but I’m glad we never had to deal with a hard drive crash during that period.

Backups are something we all need to perform. I note this as the #1 thing a new DBA or sysadmin should perform on systems. However, backups are only good if you can read them and actually restore data. I’ve made it a point to regularly practice restores as a DBA, randomly restoring backups with diffs, logs, or to a point in time. Not only do I test the backup, but I test my skills. I’ve also tried to keep an automated process around that restores all production systems to another server to test both the restore as well as run a DBCC CHECKDB. Corruption can live in databases for a long time. It flows through backups, at least in SQL Server, and this is something to keep in mind.

I’d suggest that you make sure you ensure that your backup plan is actually working by performing a few restores. Build an automated process, but also run some manual restores periodically. You want to be sure that you can really recover data in the event of an emergency.

Steve Jones


I first heard this little acronym from Grant Fritchey (b | t). He used it when talking about backups and restores, and I like it. However I realize that I’ve never actually noted what it is, so a short blog to do so today.

An RGE is a Resume Generating Event. This is usually when you make a mistake so egregious that you’ll be packing up your personal effects and exiting the building. If it’s really bad, such as releasing financial or other confidential information, you might be escorted out and someone else packs up your things. I’ve seen it happen, and it will shake you. Don’t do this.

We talk about forgetting about backups, or writing bad code or some important task we often perform as causing an RGE. In my experience, that doesn’t happen too often. Companies usually have a fairly high tolerance for mistakes.

However, that tolerance is usually extended only once. Don’t make the same mistake again. I’d also note that some managers can be very short tempered, and a single, large issue might be an RGE in their eyes.

I don’t usually worry about causing an RGE, but I keep the acronym in mind. Especially when I do something that could affect the core parts of my organization’s business.

SQL Server 2016 is Just Faster

I’ve enjoyed the series of posts from Microsoft on the ways in which SQL Server 2016 was improved and is faster. Some of these posts show improvement without any change in your code. Some showcase features that might require change, but will bring about improvements, and after all, wouldn’t you rewrite some code if you knew you could get performance improvements? Most of us would, if the changes are fairly simple. I know some changes are hard, but that’s our job as developers: make systems run better, even if it’s work to change our code.

Some posts are just fascinating. The one on log stamping, which highlights something I had no idea about. I wouldn’t think that stamping 0xC0 is better than 0x00, but I’m glad someone dives deep into hardware changes like this. I know many of us don’t have more than 8 CPUs running our databases, but we might at some point. Having a better way of ensuring soft NUMA works well automatically is good. Even if you don’t care about how this works from a practical standpoint, it’s an interesting look at how Microsoft is keeping up with hardware advances where a single CPU these days can encapsulate more than 8 cores.

SQL Server 2016 is not only the best version to date, but it’s also the most tested and evaluated. Apart from all the evaluations and writeups performed on the CTP 2.x’s , CTP 3.x’s, the RC’s, much of the code has also been running in Azure, where metrics have been gathered and evaluated for quite some time. There’s at least one feature I’ve been hearing about for over 3 years, since well before SQL Server 2014 was released, at which time it was running in Azure already. In all that time, there have been lots of tuning and tweaking to ensure that the code is running as efficiently and effectively as possible for this release.

Note that I’m not saying that the product is without bugs. While some people think there’s no need to wait for SP1 (I agree), I also think you should test thoroughly for your situation. It is entirely possible that there will be some bug that affects your code. And you need to test with actual systems and code. Also, be aware that there are documentation updates being made constantly, as we find that bugs also exist in BOL.

While SQL Server 2016 is faster than previous releases, there are quite a few new features that do require code changes. However, if you can take advantage of those features, I think it’s well worth the development effort and the upgrade cost. Let me know what you think.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and LibSyn.


What’s Your Area of Expertise?

I ran across a great quote that made me laugh, but also struck me as being quite true. The quote is from Scott Curie’s post on the PASS Summit Speaker’s Contract and goes like this: “You would laugh at a lawyer who tried to build their own data warehouse, so why would you try to write your own legal agreement?” Having used lawyers for a few contracts, I am well aware that I’m not remotely qualified to putting together a detailed contract. I know if I need a business agreement, I need to either use a lawyer, or make it really simple. “I’ll cut your grass and you’ll give me $25” is probably the last well written contract I wrote.

We all have strengths and useful skills, things we are really good at performing. We often improve those skills over time and may develop some level of expertise in our field. However, even if we were the best T-SQL coder in the world, or the hands-down, acknowledged world-renowned expert on replication, that wouldn’t mean that we necessarily had any skill in some other area of the SQL Server platform, such as Integration Services. We might know something, we could learn, and certainly be competent quickly, but would a company that had a mission critical, multi-TB import or export of data want us to patch their ETL process today? Probably not.

Usually when we have a problem with a specific technology, we want someone that knows that technology well to guide us or do the work. The same thing is true in business. If an inventory specialist questions the way some system works, or a financial guru wonders about the calculations in an application, we should defer to them and ensure that we can explain how the code works, double checking the accuracy. After all, specifications could contain bugs as easily as code, and an expert in the end user of software might have a better idea of whether a system is working properly than the developer.

We should remember that when we venture outside of our own area of specialization. Many technology professionals are quite intelligent, but they aren’t going to be experts in all problem domains, and they shouldn’t present themselves as such.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

When were statistics updated?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across the STATS_DATE function recently, and it’s one that I hadn’t used in production code. I’m not sure how this escaped me, as it was added in SQL Server 2008, but I rarely see it written about, so it’s not just me.

This function takes an object_id and a stats_id, and returns the date the statistics were last updated. The statistics id is the id from sys.stats and doesn’t necessarily correspond to the index ID.

As a quick example, if you look at the Sales.SalesOrderHeader table in AdventureWorks2012, you can run this:

SELECT STATS_DATE ( 1266103551 , 2) 

This should return a simple date. I don’t know if you’ll have the same date in your database, but I assume this is the default date for the sample database.

2016-06-06 14_07_56-Phone

Obviously these stats are out of date.

Or are they? I don’t use this database a lot and haven’t changed the data in this table that I’m aware of. In that case, they may be up to date.

This can be a handy function, but remember, the age of stats only matters if you’ve had data changes. However with having an understanding of both pieces of information, you might use this to accelerate statistics rebuilds ahead of what AUTO STATISTICS might do.


This was a good chance to dig into and look at how a function works in SQL and how I might use it. You could write this easily.

Stretch Pricing

I have to admit that I was really excited about the Stretch Database feature in SQL Server 2016. This will automatically archive older data away from your database, but let the query optimizer go get the data if needed. That’s outstanding. That’s the type of archive solution I’ve struggled to implement in the past, finding the effort complex and requiring application change or active DBA management. Often I’ve found that only 10-20% of the data in my database was accessed often, and the rest relatively rarely. Maybe old data was queried every day, but still somewhat rarely compared to a small percentage of the data.

Then the pricing for Stretch was released, and I think it’s definitely aimed at the Enterprise. If you stretch a sales database, say a 100GB database and want to move 60GB of that data away, you’re going to pay at least US$930/month for the compute at the lowest performance level. Regardless of whether anyone queries the data. If you want better performance, you can run up in roughly multiples of that amount ($1860, $2790, $3720, etc), however, that’s just for compute. If you add in storage, and you must, it’s a minor cost even 1TB, but still, having a $1k bill for access to archive data, especially when you might find people make the mistakes and do query cold(er) data might seem like a lot for a small or medium sized business (SMB). If you have to get better performance, you’ll pay more per month.

I’m not the only one that doesn’t love the pricing of stretch. It seems to me that the pricing very much favors the Microsoft share price more than the value of my own business. Perhaps this makes more sense at an enterprise level where storage costs can be high, and separating out older data could result in savings. However for smaller companies, if you’re running a SQL Server, even a 1TB (or 10TB) database, is the addition of another 1TB of storage going to cost much? I’m not sure it does.

Of course, there are other factors. Less data should mean much better performance from your local system. With some tuning of the feature, I would bet that plenty of people might be able to get 90% of their queries satisfied by on premise resources, when they have a substantial amount of older data stored in Azure. That’s not something I can easily do in my own archival system, or with the addition or more storage.

What I’d really like to see is a stretch to another SQL Server feature added. I’m guessing we’ll see that, likely in SQL Server 20,18 as I don’t think this would be hard to implement. However since this looks like a cash cow for MS, I bet when we get stretch to an on premise SQL Server, this will be an Enterprise only feature, once again, ignoring SMB needs and desires. Maybe in 2020 or 2022 we’ll be able to stretch on premise at a reasonable cost.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and LibSyn.

A Better (Free) Search in SSMS

A few years back, Redgate Software released SQL Search. This was an internal project that became a free product we released. Free as in beer, so go get it.

Why use SQL Search?

There are a number of good reasons, which Ike Ellis of Crafting Bytes does a good job of highlighting in a short video. In short, SQL Search is much more robust in helping you track down objects.

I’m sure plenty of you are like me. You think you know where most of the objects live in a database. I’ll use the SQLServerCentral database as an example. If I want to look in the forums for how to modify profiles, I know there’s an InstantASP_Users table. I can quickly go to the Object Explorer, hit Databases, go to SQLServerCentral_Forums, hit tables, and pick the table. I can get the designer, a list of columns, etc. This is a familiar way of working with databases.

2016-06-13 11_49_33-Start

However, I’d be lying if I said I knew all of the objects that referenced that table. Especially stored procedures. Even if I work with a database every day, and I have a great memory, I don’t always keep every reference in mind.

I still follow that process (too often), but I’ve learned there’s a better way of working with a database.

SQL Search.

If I type “asp_user” in the search dialog, I get this:

2016-06-13 12_01_32-Photos

Now I can see lots of objects, including those in other databases. If I know I want tables, I can easily filter.

2016-06-13 12_01_47-Photos

However I don’t just get the names, I can get some details:

2016-06-13 12_01_55-Movies & TV

The more I move to search, the more I start to realize that the Object Explorer is quite slow, and inefficient. I’d be better served by just going to search right away. At least, a search that gives me plenty of useful information,

So download SQL Search today and see how it can make development easier in large databases, and check out Ike’s video.

It’s 2016 RLS for T-SQL Tuesday #79

tsqltuesdayIt’s T-SQL Tuesday time again. I missed last month, being busy with travel, though I should go ahead and write that post. Maybe that will be next week’s task.

In this case, Michael J Swart is hosting this month’s blog party and he asks us to write about something to do with SQL Server 2016. Read the rules at his invitation.

Row Level Security

I’ve wanted this feature to be easy for a long time. In fact, I’ve implemented a similar system a few times in different applications, but it’s been a cumbersome feature to meet, plus each developer needs to understand how the system works for it to work well. Even in the case where we once used views to hide our RLS, it was a performance issue.

Microsoft has made things easier with their Row Level Security feature. This was actually released in Azure in 2015, but it’s now available in SQL Server 2016 for every on premise installation as well.

Essentially for each row, there is some data value that is checked to determine if a user has access. This doesn’t mean a join. This doesn’t mean you write a lot of code. The implementation is simple, and straightforward, and I like it.

Security Predicate Functions

The one piece of code you need is an inline table valued function (iTVF) that returns a 1 for the rows that a user should see. You need to have some way to match up a row with a user, and that can be tricky, but if you identify a row, even in another table, you can use it.

For example, I have this table.

    OrderID INT IDENTITY(1, 1)
                PRIMARY KEY
  , Orderdate DATETIME2(3)
  , CustomerID INT
  , OrderTotal NUMERIC(12, 4)
  , OrderComplete TINYINT
  , SalesPersonID INT

There’s nothing in this table that really helps me identify a user that is logged into the database. However, I do have a mapping in my SalesPeople table.

    SalesPersonID INT IDENTITY(1, 1)
                      PRIMARY KEY
  , SalesFirstName VARCHAR(200)
  , SalesLastName VARCHAR(200)
  , username VARCHAR(100)
  , IsManager BIT

Granted, this could mean some change of code, but perhaps you can somehow use a user name in tables to query AD or other directory and map this to a user name.

Once I have that mapping, I’m going to create a function. My function will actually look at the SalesPeople table, and map the parameter passed into the function to the value in the table.

CREATE FUNCTION dbo.RLS_SalesPerson_OrderCheck ( @salespersonid INT )
            1 AS [RLS_SalesPerson_OrderCheck_Result]
            dbo.SalesPeople sp
              @salespersonid = sp.SalesPersonID
              OR sp.IsManager = 1
            AND USER_NAME() = sp.username;

In the function, I look at the USER_NAME() function and compare that to a value in the table. This is in addition to checking the SalespersonID column.

I can use a Security Policy to bind this function to my OrderHeader table as shown here:

CREATE SECURITY POLICY dbo.RLS_SalesPeople_Orders_Policy
  ADD FILTER PREDICATE dbo.RLS_SalesPerson_OrderCheck(salespersonid)
  ON dbo.OrderHeader;

This sets the function, passing in a column from the OrderHeader table, which is the column I want evaluated in the function.When I now query the OrderHeader table, I get this:

2016-06-13 11_42_16-Photos

There is data in the table. However, I don’t get rights by default, even as dbo. My USER_NAME() doesn’t match anything in the table, therefore no SalesPersonID matches. However, for other users, it works.

2016-06-13 11_42_32-Photos

There is a lot more to the RLS feature, but I think it’s pretty cool and it’s something that will be highly used in many applications moving forward, especially those multi-tenant systems.

Go ahead, get the free Developer Edition and play around with RLS.

We Manage Algorithms

“Every business is an algorithmic business.”

That was a phrase that Microsoft’s Joseph Sirosh used in a keynote at SQL Nexus, talking about the future of software and data. Rather than managing data, many of us will move to manage algorithms, which will determine how data is interpreted, used, processed, and potentially returned to users as information. There are starting to be too many sources of data, too much data itself, being generated at too quick a rate, to the point where algorithms become more important than the actual data in examining, grading, interpreting, filtering, and more.

This is exciting on one hand, with new opportunities for those that can develop, choose, write, tune, or enhance algorithms. I can easily see greater influence from both developers and DBAs as we work to better manage the floods of data. Especially with 50 billion sensors, IoT devices, and more that are predicted to be online in the 5 years. That’s potentially a tremendous amount of data being generated.

On the other hand, this is a bit scary as separating good data from bad in the ocean of bits, and choosing helpful rather than hurtful algorithms might create lots of stress, and perhaps even fewer opportunities if few algortihms are reused. This also means we will need algorithms that can help us determine if data is actually good enough to use. After all, in the deluge, there will be bad data, that potentially needs to be excluded from queries. Will software developers become more important than DBAs as we end up with more unstructured data stores, data lakes, or other constructs that might require less administration?

I’m not sure how things will change, but it will be an interesting world the next few years as we work with larger and larger, more diverse sets of data in our organizations.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Penalty for a Data Breach

Many of us that work with data are somewhat insulated from the effects of a data breach. Each of us is responsible for writing software, managing the database platform, perhaps even in charge of configuring security. However, in the event of an attack or loss of data, many of us would likely be responsible for the technical aspects of analyzing the weakness or cause of the incident, determining what data might be lost, or perhaps helping to change systems to prevent future incidents.

Would many of us deal with the penalties or financial liabilities of data loss? Perhaps indirectly, but not much. However our employers (or more likely, their insurance companies) will deal with penalties. Right now there isn’t a lot of liability for companies that lose data. Minor penalties and credit monitoring, but many people are getting upset, which might result in changes.

What does a company owe you if your information is lost? It’s debatable, and this is likely to become a bigger issue over time as more and more people are affected by this issue. I expect that penalties will increase over time, this will also mean that the requirements and work each of us as data professionals need to do will grow.

While encryption and other measures can help, they don’t completely protect data. Good security comes about from a number of layers, each of which is designed to thwart, or limit the success of, certain attacks. Those of us that work with data, whether in development or administrative roles, need to educate ourselves and continue to improve our ability to implement secure applications.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.