SQLskills SQL101: Validating SQL Server Backups

At the Spring SQLintersection conference, I presented my session Understanding SQL Backups and Restores. During this session I primarily talk about how to create a solid recovery strategy and how to restore SQL Server backups. I cover full, differential, transaction log, and piece-meal restores. During the session, I stress how important it is to practice restoring your backups. There are multiple reasons for doing this, one is so that you know how to properly restore. You don’t want the first time you have to perform a restore with a full, differential, and 30 log files to be in production, with a boss looking over your shoulder. Another benefit is restore validation. This is the process of performing regular restores to make sure that your backups are valid.

Even though you may be an expert at restoring backups, a regular process to restore your backups for validation is still needed. I always tell my audience that if you are in a heavily regulated industry, that examiners and auditors love to see that you have a restore validation process. You can easily prove that restores are good, and how long restores take.

A couple of weeks after the conference, I had an attendee email me that when they tried to restore their backups from an offsite location to their DR site that the restores failed. They received an error “The media family on device ‘backup_file.bak’ is incorrectly formed”. I wrote back that this message usually indicates a bad backup file, or that they are trying to restore a backup from a higher version of SQL to a lesser version. For example, a 2016 backup to a 2014 instance. I was assured that both servers were the same version and that all backup files were failing, but that if they tried restoring the same file to the original server, that it works. At this point, I asked for them to explain their entire process. How are they taking the backups, getting the files offsite, and then to the DR site. It turns out that they FTP from production to offsite, and then FTP from offsite to production. The FTP is where the process was failing. They were transmitting binary from production to offsite, but transmitting text from offsite to DR. Transmitting the file using text was slightly modifying the file and was causing an issue. They re-transmitted the file using binary and was able to successfully restore.

Had they not tested this and a true production issue had occurred, they would have lost valuable time which could have also resulted in significant financial loss. I cannot stress enough how important it is to practice testing and validating recovery strategies. When an issue arises, you should have 100% confidence in your plan and your ability to recover. If anything in your recovery plan changes, make sure a full regression of your recovery strategy is validated as well.

I hope you found this post helpful! And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101

SQLskills SQL101: Database Maintenance for Azure SQL Database

I’ve been presenting on Azure SQL Database to groups all over the world. A common question that I’m asked is around maintenance on Azure SQL Databases. Since backups are handled by the platform, some think that index and statistics maintenance and consistency checks are also handled by the platform. This just isn’t the case. The customer is still responsible for regular maintenance. With that said, certain safeguards are in place to help contend with physical corruption, however logical corruption is up to the consumer to check for, therefore I still recommend clients to run their own consistency checks.

Azure SQL Database doesn’t come with a SQL Server Agent like Enterprise and Standard Editions. This means you have to find another way to schedule maintenance rather than manually running scripts on a daily or weekly basis. There is some good news, we have a number of different ways to schedule jobs.

  • Linked servers
  • Database Maintenance Plans
  • PowerShell
  • Azure Services
  • Elastic Jobs

Using a linked server on an existing on-premises server or an Azure VM is one of the most common methods to schedule jobs. Most of my clients use this method, they just reference the linked server in the job step to call the stored procedures.

Database Maintenance Plans are also a common method, you just reference the connection string in the step, provide login credentials and let it run.

Working with many aspects of Azure is simplified with PowerShell. Running database maintenance through PowerShell scripts isn’t much different than using T-SQL. With using PowerShell, you still need to utilize some type of scheduler to automate running the script.

Azure Services include Azure Automation, which to me is a fantastic method to create and run processes to manage your Azure platform. This service allows you to create runbooks to do everything from provisioning, monitoring, and more. There are over 100 built in runbooks that you can select from. All of this is driven by PowerShell too.

Elastic Jobs is another Azure based service that can be used with elastic pools. This option can be used to run processes against an entire pool, or custom created groups.

Regardless of your method of scheduling your tasks, for the most part, you treat Azure SQL Databases like any other database. They need regular maintenance and tuning just like your on-premises databases.

My latest course: Understanding and Using Azure SQL Database

SQLskills just launched our new training class on Azure that I will be teaching in Chicago, and my course on Azure SQL Database was just published on Pluralsight too!

My latest Pluralsight course – SQL Server: Understanding and Using Azure SQL Database – is just over two hours long and is based on my popular user group and conference session. This was a fun course to develop and record.

The modules are:

  • Introduction
  • Understanding Azure SQL Database Features
  • Exploring Unsupported Features
  • Understanding Azure SQL Database Pricing
  • Migrating Data to Azure SQL Database
  • Monitoring and Tuning Azure SQL Database

You can check it out here and I hope that you enjoy it.

SQLskills now has 150 hours of online training available (see all our 51 courses here), all for as little as $29/month through Pluralsight (including more than 5,000 other developer and IT training courses). That’s an unbeatable value that you can’t afford to ignore.

SQLskills SQL101: File Extensions for SQL Server Database Files

I recently had a client reach out to me about database performance dropping drastically. I asked if anything had changed recently on the server and they told me that all they had done was increase the number of files for tempdb from 1 to 8 per best practice.

Knowing that this shouldn’t have a negative impact on the system, we agreed for me to spend an hour or so troubleshooting. What I immediately noticed was that McAfee consuming a considerable amount of CPU. IO on tempdb was also considerably high. McAfee was constantly scanning portions of tempdb, and the reason why is because the dba named the additional data files as tempdev.002, tempdev.003, and so on.

McAfee had exclusions for the standard naming conventions of .mdf, .ndf, and .ldf. There was no exclusion for .00X. Although .mdf, .ndf, and .ldf are the default naming convention, SQL Server doesn’t really care what extensions you use. However, if you plan to deviate from the standards, make sure that any antivirus or driver based filters account for the new naming standard.

What happened for this client, is they performed a denial of service against tempdb with their antivirus software. Lesson learned, if you are going to deviate from a standard, make sure to test in a development or QA environment first.

I hope you found this post helpful! And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101

SQLskills SQL101: Azure SQL Database Pricing Models

Microsoft has implemented a database-as-a-service/platform-as-a-service environment known as Azure SQL Database. In this environment, the hardware and instance configuration is taken care of for you. In this environment, you manage and support the databases. Finally we have a production ready cloud database. When looking at pricing models, you will find DTUs, eDTUs, and DSU, which of these is correct for you?

What are DTUs?

Database Transaction Units are the unit of measure comprised of cpu, memory, and disk I/O that are available to a single Azure SQL database. Azure SQL databases have three service tiers made up of basic, standard, and premium. The basic tier supports 5 DTU and a database up to 2GB. The standard tier supports between 10 and 100 DTUs and a database up to 250GB. The premium tier supports between 125 and 4000 DTUs and a database up to 500GB. The top two premium levels P11 and P15 support a database up to 1TB.

What are eDTUs?

Elastic Database Transaction Units are just like DTUs, except these are for elastic pools. An elastic pool is a set of resources that are shared between a set of databases on an Azure SQL server. These are very helpful when you have to support multiple Azure SQL databases or a set of databases with unpredictable workloads. Much like the service tiers with DTUs, eDTUs service tiers are made up of basic, standard, and premium. The basic tier has pools from 100 – 1200 eDTUs, however no one database can consume more than 5 eDTUs. The standard pool also supports 100 – 1200 eDTUs however the databases can consume up to 100 eDTUs. The premium pool supports 125 – 1500 eDTUs. A database can consume up to the pool eDTU limit except for the 1500 eDTU pool. A database can only consume up to 1000 eDTU in that pool.

What are DSUs?

Database Stretch Units are the unit of measure for compute when using SQL Server Stretch Database. When you migrate warm or cold transactional data using Stretch Database, it stores the data in an Azure SQL database. DSU levels range from 100 DSUs up to 6000 DSUs.

I hope you found this post helpful! And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101

Spring 2017 SQLintersection

The Spring SQLintersection conference will be May 21st – May 24th. I attended my first SQLintersection conference Spring of 2015 and was blown away at how awesome a conference can be. There was an incredible lineup of speakers and sessions. What I found to be one of the best features of this conference is the attendee to speaker ratio. I have been to conferences where you have hundreds of attendees in each session, while the content of that session can be good, if you have any additional follow up questions, you couldn’t gain access to the speaker. At SQLintersection, the speakers were readily available and eager to share follow up answers to questions after their session.

Another great thing about SQLintersection is the ability to attend workshops. For the Spring conference there are two days of pre-conference workshops and one day of post conference workshops. I am fortunate to be presenting a full day pre-conference workshop on Azure as well as three general sessions.

If you are planning to attend the Spring 2017 SQLintersection conference, you can use the discount code RADNEY to save $50 off of registration.

The Spring 2017 conference has a stellar line-up of speakers to include (in alphabetical order)
Aaron BertrandSQL_Spr17_300x250_Radney
Tim Chapman
Jonathan Kehayias
Ben Miller
Brent Ozar
David Pless
Tim Radney
Paul Randal
Joe Sack
Shep Sheppard
Erin Stellato
Kimberly L. Tripp
Bob Ward
Buck Woody

Using the Azure SQL Database DTU Calculator

Over the past year I’ve been teaching and helping clients with Azure SQL Database. One of the most common questions I’m asked is, “What size tier do I need for my database?” I instinctively refer them to the DTU Calculator. This is a free tool that you can use to capture local instance metrics and upload to a website for an estimation of the service tier needed. The DTU calculator captures data on processor, disk reads and writes, and log bytes flushed.

Something to take into consideration about the DTU Calculator is that it collects the metrics for the entire instance. If you are looking to migrate a single database from an instance, then those metrics will be skewed. Depending on your situation, this may be fine, you’d know that the database in question would fit within the tier recommended by the DTU Calculator. If this is not acceptable, another option would be to isolate the database to it’s own instance, and then run the DTU Calculator against that instance.

Justin Henriksen, the creator of the DTU Calculator suggests that you could use SQL Server DMVs to isolate a database, but I personally have not tried this.

The DTU Calculator is offered in an executable with a configuration file and a PowerShell script. The two items I typically change are the location and name of my output file and the duration the script runs. The duration defaults to 3600 seconds, but when I’m conducting demos, I usually reduce this to 60 seconds. In the real world, I run the process for 1 hour increments, and continuously throughout the day so I get a full picture of the workload. I was excited to see the addition of the executable command line tool rather than just the PowerShell option.

If you’re looking to migrate multiple databases to Azure SQL Database, then an Elastic Database Pool is most likely a better option for you instead of managing multiple individual Azure SQL Database tiers. With an Elastic Database Pool, you’ll have a pool of eDTUs that each database can pull from (individual database limitation differences between Standard and Premium pools).

If you are considering Azure SQL Database, check out the DTU Calculator and if you need assistance migrating from On Premises to Azure or Azure SQL Database, let us know.

How to run Windows Server 2012 Hyper-V on VMware Workstation

I recently needed to create a new virtual machine, however with this virtual machine I needed to demo Windows Server 2012 with Hyper-V enabled. Since I run VMware Workstation as my hypervisor on my laptop, I needed to run a hypervisor within a hypervisor.

Installing Windows Server 2012 was no problem, however when I tried to add the Hyper-V server role I received an error

Check Hyper-V and click next

I then click Add Feature and…..

This is where things failed. Hyper-V cannot be installed: A hypervisor is already running.

By default, you cannot install a hypervisor under another hypervisor. However there is a work around. First you to power down your VM and edit the .vmx file for that VM. You will need to add a two lines of code, save the file and then startup the VM.

hypervisor.cpuid.v0 = “FALSE”
mce.enable = “TRUE”


Once I edited the VM and restarted and tried to add the Hyper-V role, I received another error regarding my CPU type. Hyper-V cannot be installed: The processor does not have required virtualization capabilities.


To correct this I needed to again power down the VM and edit the virtual machine settings, specifically, the processor values.


Once I change the preferred mode to “Intel VT-x/EPT or AMD-V/RVI” and checked the box for “Virtualize Intel VT-x/EPT or AMD-V/RVI” I was able to successfully add the Hyper-V role to my VM and start provisioning additional VM’s under Hyper-V.


If you experience any of these issues then I hope this post solves your issues like it did mine.

Configuring SIOS DataKeeper for multiple volume snapshots

Recently I had the opportunity to work with a client that was utilizing SIOS DataKeeper in a Windows Server Failover Cluster to create a readable secondary in order to offload reporting. SIOS DataKeeper is an application that synchronizes data between two volumes, allows you to present local storage to a WSFC as shared storage, and gives you the ability to take snapshots of the mirrored volumes.

Taking a snapshot will make the mirrored volume available to the OS on the secondary. You can then attach those databases to an instance of SQL Server and have your users to connect for reporting. This is all doable with Standard Edition of SQL Server which can save thousands of dollars on SQL Server licenses.

Installing and configuring DataKeeper to mirror volumes and adding them to the WSFC was very simple and well documented. To keep the snapshot current with the previous day’s data, I needed to schedule a process that would drop any existing snapshots and take a new snapshot. I did this by using the built in command line tool EMCmd. To take a snapshot of the E and G drive on my secondary server named AGNODE6, I needed to run


To delete the snapshot I would run


I put together a batch file that would navigate to the proper folder and issue a drop snapshot to clear any existing snapshots and then take a new snapshot. This was scheduled daily on the secondary server.

The problem I encountered was dropping the snapshots during a failover event on the cluster. In order for the SQL Server clustered resource to failover to the secondary, all snapshots had to be dropped first.

Refreshing the snapshot was straightforward, but how do I drop the snapshots in a timely fashion when the SQL Server clustered resource needs to failover from the primary server, in my case AGNODE5 to the AGNODE6.

SIOS provides a sample VBScript named DKSnapshotCleanup that can be copied and modify to drop the snapshots on the secondary. For this to work I needed a folder on each server and a copy of the modified DKSnapshotCleanup script. Next, I had to add a resource to the cluster and point to the VB Script and make my DataKeeper volume dependent on the new resource. The instructions in the VB script are detailed and easy to follow, however I had an issue when it stated to create a “Generic Application” and point to the script, I had to choose “Generic Script” instead.


In my environment, I had a drive E for data files and drive G for log files. In my DKSnapshotCleanup script, I needed to drop the snapshot for both volumes. To do this, I added two lines, one for each volume toward the bottom of the script.


I assigned the DKSnapshotCleanup Script resource as a dependency on drive E.


When my cluster failed over to the secondary node and back, things didn’t go as planned. Occasionally drive G would fail to come online which failed the cluster.


I was able to quickly bring drive G: back online however that isn’t an acceptable course of action in production. This has to work consistently each time the cluster fails over.

I did some more testing and validated with SIOS support that the best course of action was to create a dependency for drive G on drive E.


This makes drive G have to wait for drive E to come online before it starts. Once I configured the second dependency, I was able to consistently fail the cluster over with both snapshots being dropped and both volumes coming online.

My overall experience with SIOS DataKeeper was positive. The support team was very quick to respond and the product was easy to install. Although I had a couple of configuration issues, I was able to resolve them quickly. Another cool thing about SIOS DataKeeper is that it works on both Windows and Linux, it is a great way to build in HA for storage without having to have a high dollar SAN replication solution.

Issue publishing to SSRS 2012 with SSDT 2015

I recently was helping a client who was trying to use SQL Server Data Tools 2015 to update and generate reports that would be deployed to SQL Server Reporting Services 2012. When trying to deploy the report, the client would get the following error:

“[rsInvalidReportDefinition] The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas.”

I knew that SSDT 2015 should be backwards compatible and able to work with multiple versions of SQL Server products, so I had to do a little research to figure out what exactly needed to be changed in order to publish a report to SSRS 2012.

In my lab, I created a new project named GetDatabases. This project was a basic report that would get query a system table. The first thing I tried to change in the project was the TargetServerVersion located under the project properties.



I updated the TargetServerVersion to “SQL Server 2008 R2, 2012, or 2014”, the default in was “SQL Server 2016”. I also updated the TargetServerURL to my development server running SSRS 2012. I made these changes to both the release and debug configuration options.

After making these changes, I was still unable to deploy the report. After a bit more research, I found a location to change the default deployment server version of the Business Intelligence Designer. This is located under ‘Tools and then Options’. The default was set to version 13.0, once I changed the version to 11.0 to match the server I was deploying to, I was able to successfully deploy my report to SSRS 2012.


It seems odd to me that I would have to change the overall behavior of SSDT instead of just the properties of my project, however I confirmed with my client that they couldn’t deploy until they modified the default deployment server version.

I hope this helps if you are having similar issues.