Should I back up system databases on an Azure VM?

Graphic with a back up button. Because we can't restore with out backups.

Well that really depends. When your database VM fails for some reason (either someone does something bad like delete the VM by accident, or a Windows patch doesn’t install correctly and the VM won’t start correctly, etc.) do you care about having your logins on the server, your jobs, any SSIS packages deployed to MSDB, any operators, etc. restored to the system. Or do you want to have to redeploy all those things after you rebuild the server and get SQL set back up? If you aren’t OK with redeploying those (or the risk of those deployments is to high) and all the risks that go with that, then doing a restore of those system databases is going to be the way to go.  (Hint, a restore is always going to be more reliable than a redeployment.)

Now I know with AlwaysOn Availability Groups you can just fail over to another node, but at some point you need to rebuild that failed node.

What if you don’t have Availability Groups in place. Maybe it’s a tier 2 server that doesn’t have HA beyond what Azure offers, or maybe it’s a failover cluster so you don’t have multiple copies of the system databases.

Now I get that restoring master isn’t as easy as a user database, but it’s actually pretty easy to restore. It just requires restarting the SQL software in single user mode with a couple of switches (SQLServer.exe -c -f -m) from the command line, then you have to login using another command line window to actually restore the database using sqlcmd and the RESTORE DATABASE command to actually restore master. Restoring msdb is easier as all you have to do is stop the SQL Server Agent (any anything else using msdb) then restore the msdb database like normal.

If I can easily describe how to restore the two databases in a single paragraph then it shouldn’t be all that hard for someone to do.

Now the cost. Your system databases should be small. If you’ve got pruning setup correctly in your msdb database then the backups (which you should be writing to RA-GRS, Readable Geo-Redundant Storage using the Backup to URL feature of SQL Server) should be a few megs per day. Assuming you keep them for 10 days (which is probably more than most people do) and we’ll assume 1 Gig of space needed for backups to make the math easier you’re talking about $1.20 per month to store the backups. If you kept the backups for 31 days then the cost is $3.72 per month for the system backups. (Assuming you don’t have an EA, and you are paying the full retail price, which basically no one should be.)

I’ve shown that system databases are easy to restore.

I’ve shown that they don’t cost much to backup (if you aren’t paying attention, the cost to store these backups is a rounding error in the cost of the SQL VM).

So what’s your excuse now for not backing them the system databases?


The post Should I back up system databases on an Azure VM? appeared first on SQL Server with Mr. Denny.

An “Ask” for Microsoft—A Global Price List

And yes, I just used ask as a noun (I feel dirty), I wouldn’t do that in any other context, but this one. In reviewing my end of year blog metrics, my number one post from last year was a post that listed the list price of SQL Server. I wrote this post because a) I wanted clicks and b) I knew what a pain it was to find the pricing in Microsoft documents. However, the bigger issue is that to really figure out what a SQL Server cost, you need to go to another site to get Windows pricing, and probably another site to find out what adding System Center to your server might cost.

This post came up because Denny and I were talking the other night, as someone had posted to the Data Platform MVP list asking how much the standalone R Server product cost. We found a table on some Microsoft site:


I’m not sure what math is required to translate “Commercial Software” into a numeric value, but it is definitely a type conversion and those perform terribly. Eventually I found this on an Azure page:

This image is charged exactly like SQL Server 2016 Enterprise image, but it contains no Database elements and has the core ScaleR and DeployR functionality optimized for Windows environments. For production workloads we recommend that you use a virtual machine size of DS4 or higher.

This leads me to believe that R Server has the same pricing as SQL Server, but with the documents I have I am not certain of that fact.

What Do I Want?

What I want, is, a one-stop shop where I can pricing for all things Microsoft, whether they be Azure, On-Premises, or Software as a Service. At worse it should be one click from the product name to it’s pricing page. Ideally, I’d like it all in a single table, but let’s face it, software pricing can be complex and each product probably needs it’s own page with pricing details.

The other thing that would be really cool, and this is more of an Azure thing, is to have pricing data built-in to the API for deploying solutions. That way I can build pricing based intelligence into my automation code, to rollout cost optimized solutions for Azure.

Anyone else have feature suggestions?

SQL Server for the New or Non-dba – Nashville Edition Reminder

I wanted to throw out a reminder that I’ll be giving a pre-con at Nashville SQL Saturday 2017. I know that the announcement for it was right before the holidays and things can get lost around the holidays pretty easily. So with that…

I’ll be presenting a pre-con named SQL Server for the New or Non-dba on January 13th, 2017 at 8am CST.sqlsat581_header

In this all day session on Microsoft SQL Server we will be learning about how Microsoft SQL Server works and what needs to be done to keep it up and running smoothly when you don’t have a full time database administrator on staff to help you keep it running.

In this session we will cover a variety of topics including backups, upgrade paths, indexing, database maintenance, database corruption, patching, virtualization, disk configurations, high availability, database security, database mail, anti-viruses, scheduled jobs, and much, much more.

After taking this full day session on SQL Server you’ll be prepared to take the information that we go over and get back to the office, get the SQL Server’s patched and properly configured so that they run without giving you problems for years to come.

Be sure to go register for the pre-con, as registration is required.

See you at SQL Saturday Nashville.


The post SQL Server for the New or Non-dba – Nashville Edition Reminder appeared first on SQL Server with Mr. Denny.

SQL Server on Linux Clustering—A Few Other Notes

So I was chatting with fellow MVP Allan Hirt (b|t) about the cluster build that I wrote about yesterday, and I had a few more realizations about the Linux HA process as it stands right now. I haven’t talked to the the Linux product team at Microsoft about this, but I hope to in the near future to get a better idea of where things are headed. So these are my notes as of now, strictly relating to failover cluster instances (FCI), AlwaysOn Availablity Groups are coming, but are not in the latest CTPs of SQL Server on Linux.

It was faster than building a Windows cluster

It took me a while, I laughed, I cried, I cursed a lot, but if I look at the time it took for me to actually build the cluster and install SQL Server, it was a much faster process. Much of this comes down to the efficiency of the SQL Server installation process on Linux, which is as simple as running yum install mssql-server (mostly). Which leads me to my next point..

Installation options would be nice

The cluster building process is a little kludgy. Basically, you install two standalone instances of SQL Server, and then remove the data files from one them, and copy them into your NFS share. Having the option to do the equivalent of an “Add Node” install, would mean you wouldn’t need to worry about cleaning up your second node.

There’s no cluster validation, explicitly

This is a bit scarier, or easier depending on your view point. There are tests at various parts of the process to make sure things are working. For example, the first step of building your Linux cluster is to authorize the nodes to take part in the cluster, which validates certain security and network settings. However, the storage validation consists of starting and stopping SQL Server on each node to make sure it can talk to the storage and startup. Given that Microsoft doesn’t own the clusterware for this solution, I’m not sure how much they can enhance that, or if they will. This is a good open question.

There’s no dns

(Happy Late Birthday Kris!) One interesting thing I realized after talking to Allan was that I did all of my networking setup through the /etc/hosts file on each individual node. I remember doing this for RAC, and I think it may be a requirement of Pacemaker, but you will still want to make a DNS entry for your cluster identifier. When you do this on Windows, if you are using Active Directory for DNS, the installation does this for you. Not in Linux, you will need to do this yourself.

Screen Shot 2017-01-04 at 11.40.26 AM

Get comfortable with command line and scripting

There’s no cluster wizard to get you through the process. I think this isn’t a huge deal—Denny and I were talking yesterday about how relatively easy it would be to script the whole process in bash (I’m holding off until I find out if Microsoft is doing this), and most Linux sysadmins are really comfortable with writing bash scripts. But if you aren’t comfortable with Linux and the command line, now is the time to brush up, before things go prod.


We are in the very early days of this process, there is much that will likely change. From a functional and conceptual perspective, this is very similar to the way a SQL Server Failover Cluster works in Windows, but the implementation is quite different. I’d like to see things resemble Windows a bit more, at least from a SQL Server perspective, but we’ll see where the product heads.

1 2 3 320


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.