Apr 16 2015

Azure SQL Database v12 and SQL Magazine

I spend many of my evenings researching and writing. Sometimes it’s writing new books. Sometimes it’s fixing and rewriting old books. Occasionally it’s a blog post like this one. Lately, it’s been a series of articles for SQL Magazine that are all about the new functionality available in Azure SQL Database v12 that was released in February for most data centers in Azure. It’s a whole new ball game. Check out my introductory article for v12 and the one on DMVs in Azure. I have more coming up on CLR in Azure, getting started, PowerShell, DBCC, T-SQL enhancements, Premium Tier and more. I’ll also get into “old” functionality like backup and restore. I’ll also explore new functionality, Azure is nothing if not dynamic, as becomes widely available.

I know a lot of you have been putting off exploring Azure, especially Azure SQL Database until it was “ready.” Guess what? It’s that time. Feel free to lean on me here, and over at SQL Mag, for questions, suggestions, thoughts, concerns, anything you have about Azure.

Apr 13 2015

Azure SQL Database Firewall Settings

The new portal for managing Azure is pretty. I’m not sure I’m in love with it, but it’s pretty.

However, one thing that I have to access regularly is the firewall settings for my Azure SQL Database. I do demos from all over the place. I never know what my IP address is going to be. Rather than expose everything, I just set up whatever IP address I’m on and then remove it later. The old portal made this easy. The new one… not so much.

So, let’s get this down real quick. Assuming you connect to the new portal and go straight to your database, you’ll see this image showing you the database and the server it’s on:


You won’t see anything else that suggests FIREWALL. But, you can click on the server. When you do, you’ll see another panel open up to the right of the one you’re on. It’ll have an image like this:


Still nothing that screams FIREWALL, but if you click on the little gear icon that says SETTINGS you’ll get yet another panel opening to the right that resembles this image:


There she blows, Firewall. Click on that and you’ll see the standard screen for editing your IP address access into the firewall:


Just remember that after adding a new IP address to your firewall you must hit the save icon at the top of the screen, or you still won’t be able to access your database.

Sep 05 2014

Speaker of the Month: September 2014

This month I’m very grateful because I was given the opportunity to present at DevLink in Chattanooga. I got to meet a lot of new people and see presentations by people that just don’t hang around SQL Server specific events. It was great. I’m going to apply next year (depending on scheduling of course) and I’m applying for a lot more development conferences. I still get to see friends present, Louis Davidson, Kevin Boles and Kevin Kline were all there. But I get to see new people. Speaking of which, speaker of the month for September 2014 is Josh Lane (b|t) and his presentation AWS vs. Azure, Which One Is Right for You.

The thing that I found the most amazing about this presentation was how even-handed it was. Josh Lane really went out of his way to find positive (and negative) things to say about both platforms. Because of this, I think anyone really trying to understand which platform to pick, and, more importantly, why they should pick it, walked away with good data. I’m working with Azure regularly and I picked up a few things that I didn’t know. I also liked how he identified lots of commonality between the two platforms. The slides were very simple and clean and well structured. Mr. Lane’s delivery was excellent. He was engaging, handled questions well, and kept things moving. I was really impressed.

The one issue I have with the presentation is that there was really way too much material. He didn’t just rush through the last of it. He skipped right by sections. And those sections looked interesting. I’m pretty sure this was put together for a venue that had more than an hour of time, but I don’t think he would have made it through on a 75 minute schedule either. So, as much as I hate to say it, I’d suggest trimming some material. Going quickly through some doesn’t usually make people too angry, but skipping stuff entirely makes them feel ripped off. But, what a great problem to have. Too much awesome, well delivered material.

Thanks for the session Mr. Lane. I’ll keep an eye out for other material from you in the future.

May 13 2014

Add an Instance to SQL Server Azure Virtual Machine

How do you add an instance to your local SQL Server installation? You run the executable that you probably downloaded from MSDN or maybe from a CD. Works the same on an Azure VM right? Sure… but wait. Do I have to go and download the software to my VM instance? Let’s assume that you’re running one of the VMs from the Gallery, then, the answer is “No.” Just navigate to C:\SQLServer_12.0_Full. There you’ll find the full installation setup for SQL Server. And you’re off and running… Until you realize that you don’t have the Product Key for this thing. What happens when you get to this screen:


You can look around all you want and you won’t see a product key anywhere. At least no where that I could find. So what do you do? Same question was asked and answered over on this forum at SQL Server Central. The trick is to get the product key from SQL Server. I tried several different methods, the ones you’ll find if you search for how to get the product key from an existing copy of SQL Server. But finally, as was posted on the forum, a method that worked was found. I tested it out and I was able to add an instance to a VM from the Gallery.

Which brings up the next question. Did I just violate some type of licensing with Microsoft? Lordy I hope not. But I did some research. This definition of the support policy at Microsoft says that anything that is not explicitly denied in that documentation, that is normally supported is still supported. There’s nothing in there about multiple instances. There’s nothing in the basic Azure Licensing FAQ. There’s nothing against this in the Pricing details either. And since the standard iron version of SQL Server allows you to have as many instances running on a given server that you want, from what I can tell, this still applies here.

Personally, I don’t think I’d want to run multiple instances on a single Azure VM. I wouldn’t really want to run multiple instances on a VM or, in some cases, even on iron. Multiple instances frequently have difficulty playing nice. I can’t see that getting any better inside Azure. However, there’s nothing to keep you from doing it except tracking down that Product Key. Get that, and you’re golden.

Apr 23 2014

Azure Automation

I introduced Azure Automation in a previous post. I’ve spent some more time exploring it.

There’s a set of documentation available as I noted before. Unfortunately, reading through the full set of documentation, I have some criticisms to offer. The layout of the documentation goes through “Common runbook tasks” actually more or less laying things out as I did, inadvertently, I assure you, in my previous blog post. The problem with that, as I found in that post is, the administration of the runbooks seems fairly straightforward from the screens. But, you can’t do a darned thing with any of it until you have a runbook . Further, you can’t have a runbook until that thing has some code in it. And, the documentation doesn’t include documentation about code. Instead, we just get a page with a list of samples, but no links to that code, nor an indication of where it might be. The scripts are located here. But man, that ought to be in the documentation. There’s also no clearly documented method for how to start doing the development. It’s not really necessary since the GUI leads you inevitably to the Draft screen we saw in my other post. But, documentation is generally supposed to let you know what to do, where to look, etc.

There is another set of documentation just on authoring runbooks. Lots and lots more meat there. I’ll go through it and follow up further.

Enough criticism, let’s play with some code.

I’m going to start with the “Hello World” code set. It’s supposed to be an introduction to how everything works. You can’t open it from the Azure Portal. Instead you have to download it to your machine and then either upload it into a new runbook or copy and paste it into the Draft editor window. Presumably this is so you can do the coding locally using the PowerShell ISE or other tools. Documentation for the script is clear. It’s description:

If you are brand new to Automation in Azure, you can use this runbook to explore testing and publishing capabilities.


Well, let’s just say that’s a little grandiose for what is, literally, a “Hello $Name” example. But, it’ll get your feet wet. I took the script, pasted it into my “RunningScare” runbook. From there, I have the  capacity to Save, Test, or Publish. Being a good paranoid type, I ran test first. It popped up a window to input the parameter and then showed the output in the Output Pane (which I hadn’t actually noticed):


I can’t tell you why it output multiple times, but it did from one test of the script. To see the rest of the functionality, scheduling, etc., I went ahead and hit Publish. That moved it from Draft to Published where all I can see is a faded outline of the actual script and a Start button at the bottom of the screen. I went ahead and ran it from there. It actually takes a surprisingly long time for such a silly small script to complete.  There’s event the ability to view the Job as it’s running:


So that works. Next up, scheduling. It’s pretty straight forward to walk through the GUI in the Portal (although, now I want to see if I can programmatically control the Automation interface, more to explore). I’m going to try to run this script once an hour. So, I’ll give the schedule the name, unique to my account, Hourly (imagination knows no bounds). And then things get weird. I can only schedule this for a “One Time” run or “Daily.” No other options available:


Nothing in the core documentation about the details of scheduling. Checking the authoring doc (which has tons of stuff in it) there is a PowerShell command for directly controlling this (oh yes, much more to explore), Set-SmaSchedule. But, it’s not clear if the command has more variables other than a day interval. I’ll have to test it out to see. The Portal recognized that parameters were necessary, so I put one in and scheduled my runbook. Worked great.

With that, I have my first run book set up, tested and scheduled. So far, this is looking really interesting.




Apr 16 2014

Microsoft Azure Automation

AutomationMicrosoft just announced a new mechanism for managing your Azure resources, Automation. You can check out the documentation on it here. It’s a mechanism to create runbooks using PowerShell that you can then combine with other runbooks inside a runbook, etc. Let’s check it out. I’m doing everything you see here without consulting the documentation. I want to see how easy it is to put this stuff together. First, because it’s still in preview, you have to sign up. Once you’re accepted in the program, you get a new icon in your Management Portal.

Next, you’ll have to create an automation account. That’s pretty straight forward. It’s just a name, your selected region and the subscription you’re putting it under. No immediate guidance on where, when or if the region matters that much:


When you get into Account, nothing is there. No default runbooks or anything. Now, I know you can go and get sample runbooks and I suspect there will even be a clearing house through GitHub or somewhere for runbooks. But right now, we’re flying by the seat of our pants, so let’s just create our own runbook. It’s Azure, so the New button is right there in the lower left. Clicking on it, we can do a quick create for runbooks, code to be complete later:


Nice and easy so far, although, so far, this thing doesn’t do anything. So now, I have a runbook. If I open it up, it shows a dashboard with several tabs, Jobs, Author, Schedule, Configure. Clicking on Jobs, I don’t see anything interesting displayed. Obviously I should click on Author next, but where is the fun in that? So I click on Schedule. That brings up a message that “You must publish this runbook before you can add a schedule. Click AUTHOR to author and publish this runbook.” So I go ahead and click on Configure (yeah, I’m that guy). It’s not that interesting. So, let’s go back to where we should have started, Author. Clicking there, I get this:


OK, fine. Let’s click on Draft. Which brings me to, well, I think it’s a fascinating screen:


It’s a script. And that’s PowerShell it expects me to type in there, but I really don’t know what kind of commands I should be using, so… Here endeth the beginning of my exploration of Azure Automation. I need to actually go and read the docs. More to come.


Mar 25 2014

Save Money On Your Training Server

Save MoneyYou can spend less money. Some of us are lucky. We work for very large corporations who can easily set aside a spare desktop or even space on a rack for a server on which we can train. Others of us are not as lucky. We work for smaller organizations that have to be more careful with their money. Not only do we not get the extra machine to train on, but our laptops could be weak things that can’t run two or more VMs. In this case, how can you go about learning stuff? Spend your own money? Sure, it’s an option.

There are some very cheap servers available out there that won’t cost you even $1000 dollars to set up. And for pretty cheap you can buy some network attached storage to have your own little SAN-style setup. That’s very doable. Let’s break it down a little:

HP Proliant MicroServer G8: $549
Added Memory to 16GB: $209
24oGB SSD: $129
Lenovo/Iomega 1TB of storage NAS: $878

We’ve just spent $1765 for a decent little set up. So now you could run 3-5 VMs on this machine and you’re good to go. Of course, now you’ve got to maintain that system, patching, upgrades. What happens when it gets old? You’ve got to replace it. What if you’re not using it? That was a lot of money spent then.

Ah, but wait. Software. We need to get Windows server licensed and SQL Server. Let’s see:

Windows Server 2012 R2 Fundamentals: $501
SQL Server Developer Edition: $44

We’re now up to $2310. But… oh, yeah, the licenses for the servers, that doesn’t include VM licensing, so let’s buy… 4. That’s enough for one server and 3 VMs. That’s an additional $1500, so now we’re up to $3810. Cool though, right. That’s not much money and we’re off and running.

Here’s a suggestion, even if you have to spend your own money, how about Azure? Currently, I’ve left three servers running on my account (not something I recommend, but I’ve been doing this as an experiment), plus the storage they use, plus the SQL Databases I have, I’m racking up a bill of about $80/month. That’s $960 in a year. Which means in about 3.9 years, I’ll have spent as much as you just did on that server that’s sitting under your desk.

Yeah, I know. It runs somewhat faster, except when I burn a little cash and bump my servers up to 8 core and 56gb of ram for a test, then turn it back down, or even, turn it off or deallocate it. Because, you’re only going to pay for what you use. So if you just throw the VMs away between tests, you’re saving tons of money, way above and beyond what that hunk of iron under your desk cost. You can even estimate exactly what things are going to cost using the engine Microsoft provides.

But did I say pay? Not quite. You see, I have an MSDN account. That includes Azure credit. Anywhere from $50 to $150 per month. So, for $1199/yr, I can get $50 a month of Azure credit. That means, just buying an MSDN account, it’ll take me three years to equal what I spent on that box under the desk.

Oh, and that’s before we get to the electricity you paid.

Look, there’s a reason to buy iron. I believe in it. But, there are also reasons not to buy iron. Testing, training, personal use… maybe iron. Or, maybe it’s time to step into the 21st Century.

Mar 21 2014

PASS DBA Virtual Chapter Talk

I almost forgot to tell you about the Database Administration Virtual Chapter meeting next week, March 26th, 2014. I’ll be doing a talk about query tuning in Windows Azure SQL Database. It’s a talk I’ve given before (it was in the top 10 at the PASS Summit last year). Come find out why you’ll need to tune queries in WASD, the tools you get, and the glorious fact that you’ll actually be actively saving your business money by tuning queries! Click here now to register.

Dec 19 2013

How to Set Up Managed Backups in SQL Server 2014

Earlier this week I introduced the concept of Managed Backups (and caused less of a turmoil than I thought I would). Now I want to show you how it works. It’s really simple and quite well documented. Before you get to the, insanely simple, task of actually enabling Managed Backup, you will need to go through the prerequisites. First, and this should be obvious, but I’ll state it, just in case, you need to set up an Azure storage account. That’s so insanely straight forward that I’m not going to say more. Then, you have to set up encryption on your system. I used these commands to prep it:


WITH SUBJECT = 'Backup Encryption Certificate';

Again, shouldn’t have to say this, but covering my bases, don’t lose that password. You might need it later. You’ll also need to create a credential (I had one created for Backup to URL already):

WITH IDENTITY = 'MyStorageAccountName',
SECRET = 'MyAccessKey';

The actual command to get the backups started is crazy simple:

EXEC smart_admin.sp_set_db_backup @database_name = 'MyDatabase',
    @retention_days = 10, @credential_name = 'CloudDojo',
    @encryption_algorithm = 'AES_128', @encryptor_type = 'Certificate',
    @Encryptor_name = 'CloudDojoCert', @enable_backup = 1;

That’s it. By setting @enable_backup to 1, I’m and running. I can repeat the script for multiple databases and I’m done. Backups are now automatic. There’s auditing built in. For example, I seem to get errors on RESTORE HEADER_ONLY occasionally:

DECLARE @eventresult TABLE
     event_type NVARCHAR(512),
     event NVARCHAR(512),
     timestamp DATETIME

INSERT  INTO @eventresult
        EXEC smart_admin.sp_get_backup_diagnostics @begin_time = '12/1/2013',
            @end_time = '12/19/2013';

FROM    @eventresult
WHERE   event_type LIKE '%admin%';


[FileRetentionAdminXevent] Type = SqlError, Code = 3292, Stage = Undefined, Message = A failure occurred while attempting to execute Backup or Restore with a URL device specified. Consult the Windows Event Log for details.
RESTORE HEADERONLY is terminating abnormally., Additional Info = N/A.

Browsing through the files (using Cerebrata because it makes my life so much easier), I can see that it’s an intermittent problem, not a consistent one. And, since it’s just the verification of the backup, I’m not sweating it that much:


It’s the kind of intermittent error you’re likely to get when working with cloud-based technology. Code accordingly.

But that’s it. It’s so easy to get set up. You can see that I’ve been running backups for days. Managed Backups are easy to set up. They’re easy to turn on. You can use RESTORE FROM URL to pull these back into your database just as easily as I put them out there. There’s really nothing to it.

Again, this is not for everyone. It’s just another tool in the toolbox. Yet another way of getting things done. Explore it. Let me know what you think.

Dec 17 2013

Introducing Managed Backups in SQL Server 2014

Some of the new functionality of 2014 is straight forward, non-controversial and easily welcomed by the community. Think, updateable column store indexes. Some of the new functionality is going to raise an eyebrow or three (most of the time, not on one person, but you know the #sqlfamily, we have some interesting mutations represented). Think… managed backups.

Now, why on earth would a process that takes backups for you, including log backups, does it automatically per a schedule and/or when data has changed sufficiently, stores it offsite for protection and is easy to set up and maintain going to cause so much controversy? Hey, I’m wrong, it won’t, move along… Or, maybe, I’m right, and this is going to raise more than eyebrows.

We’re talking about surrendering control over your backup processes and letting Microsoft decide if and when you need a full backup or a log backup. We’re also talking about surrendering control over your storage and letting Microsoft manage your files in Azure Blob Storage. OMG! I said Azure. Several people vomited, just a little, into their mouths. Yep, Microsoft is going to take away your backups and your ability to control them. It will then store them in a way you can’t access and Microsoft will not let you have any control over them ever again…

Oh good gosh, really? You believe that? I do have a bridge for sale that I can let you have pretty cheap.

Let’s talk about what managed backup really is, who it might be for, where it could work extremely well and where it might not work well at all.

Managed backup is a service running on your system that is enabled, if you so desire, through T-SQL commands. That’s right, it’s not even turned on if you don’t want it to be. The service is pretty simple. When you enable it, you define a few parameters, which database or databases and a retention period. That’s pretty much it. From there it runs your first full backup on each of the databases you’ve put under management (no system databases and the backup size has to be less than 1tb, a limitation of Azure Blob Storage). Then, it runs log backups at least every two hours, or, after 5mb or more of transactions. Heck, if your log backup chain is broken, it’ll reset and start over. It’s smart. It’ll also run a full backup after 1gb of data growth, or once a week. And again, if you break the log backup chain, it’ll take a full and keep going. All the files go to Azure Blob Storage where you can use backup/restore from a URL to retrieve them. The backup format is, wait for it, SQL Server backups. Because they’re SQL Server backups you can restore them to a SQL Server machine. That’s it. It’s very simple and simplistic.

Who this is for, well, if you’re one of those people with the funny taste in your mouth right now, probably not you (although, you know I think you should start learning it). If you’re managing one of the larger database systems, over 1tb in size, you’re out. In fact, if you have a sophisticated backup routine in place now combining differentials and file/filegroup backups, you’re out as well. This is meant for those people who don’t want to or can’t take all the time and trouble to set up a sophisticated backup routine. It’s for people who are already running their servers on Azure VMs. It’s for small shops that don’t have the money to set up multiple off-site storage locations for their backups. It’s for consultants who want a really simple and reliable method for getting clients to have protected backups with off-site storage with a minimal number of steps. In short, there are a lot of people who are going to find this pretty handy. Again, not everyone. But that’s OK, every feature isn’t for everyone either. That mom & pop database that’s about 8gb in size and running on a 32bit server just isn’t going to benefit from in-memory table storage either. Does that negate that functionality? Nope. Nor should the fact that some large enterprise clients will never use this.

If you’ve never set up log backups and aren’t sure how often your system should have them, this is going to be pretty handy since it does it for you. If you don’t have access to a DBA… well, you may still need one because setting up is T-SQL only currently, no GUI, so it might not work well for everyone, but I’m sure someone will write an app or a script for it soon. If you’re already running your databases on Azure VMs, this thing is fast. Really fast. In fact, I think it works quicker than my local disks (the spinning ones, not the SSDs). And, the storage is right there in the cloud, so you won’t have to pay for transfer down to your machine (although plucking a weekly backup out of the blob storage might be a good idea).

Now, there are a few things I don’t like about it. First, I’d like to have the option to backup locally first and then copy to the cloud (that’s how Red Gate implemented their cloud backup solution and it works great). And, I’d like to be able to tweak the knobs on when the backups occur. I’m also still not convinced I like how the monitoring is done with it, although since the schedule is maintained by SQL Agent, you can set up alerts, secondary monitors, all that fun stuff, all on your own. While some sophisticated DBA types will absolutely use this, I think the prime audience is non-DBAs. As such, it needs a clean & extremely simple GUI. You’ll get more implementation of it. Heck, I’d say it ought to be a check box on the SQL Server install process, and one you have to uncheck to prevent getting it set up when you install the instance. That way, Next-Next-Next deployments that probably never even consider recovery models or any of the rest of that fun stuff, will get backups as part of their installation.

Overall though, I’m excited about the added functionality. I’m especially excited because this functionality is added. It’s not replacing anything. I can still do all my backups my way, completely and utterly. Or, I have another automation tool that I can put to work. I’ve been running this on a test platform for over a month with no issues (while running other tests for other new functionality, I’m finally digging into 2014). I’ll write another post on how to configure this, although I don’t really need to because the Books Online are pretty clear. But, I wanted to get the “They’re taking away my control and putting it all on Azure” freak out done before I posted the technical part of the info. Proceed with the meltdown.