Alerts From Azure Automation

Azure
In a previous post, I showed how to set up statistics maintenance for your Azure databases using Azure Automation. However, what I didn't show was how to generate an alert when things go south. Let's do that now. An Error Needing an Alert First, I need to generate an error. I'm going to modify the code just slightly from the previous example so that it will fail: $Cmd=new-object system.Data.SqlClient.SqlCommand("UPDATE STATISTICS dbo.TableNotInDB", $Conn) If I modify my Runbook with the code above and then run it, I will get an error: This is from the test pane. I strongly recommend you use tests on your PowerShell scripts when writing your own automation. It'll save you a lot of pain trying to troubleshoot things later. Alert on Error Believe it or not,…
Read More

Scheduling Statistics Maintenance in Azure SQL Data Warehouse

Azure
The power and capabilities of Azure SQL Data Warehouse are pretty amazing. However, it's still basically SQL Server under the covers. SQL Server still needs maintenance and one type of maintenance is keeping statistics up to date. Statistics maintenance is doubly important within Azure SQL Data Warehouse because statistics are not created automatically, nor are they maintained automatically. It's up to you to ensure your statistics are up to date. Statistics in Azure SQL Data Warehouse It's worth re-emphasizing the point that there is no automatic creation of statistics in Azure SQL Data Warehouse. This means you need to plan to add statistics to your tables. Microsoft recommends an easy method is to add them to every column. While that does make things easy, it's not like statistics are free. If you…
Read More

Azure Automation

Azure
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,…
Read More

Microsoft Azure Automation

Azure
Microsoft 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…
Read More

Book Review: The Phoenix Project

DevOps, Professional Development
Let's get this straight right up front, the thought of reading a novel that's about IT is so repellent, so repugnant, just so horribly wrong, that it's kind of hard to fathom why I would even attempt it. What's even more difficult for me to fathom is how much I enjoyed this book. Which is a novel. About IT. I can't figure it out. Maybe I need to start reading more IT novels... no. Let's hope that's not actually a thing. On with the review... The Phoenix Project is a story about a mid-level manager in a large company who has been running part of the IT organization that is a bit of a backwater, maintaining old big-iron systems, VAX, that type of thing. He gets called into the CEOs…
Read More

SMO vs Invoke-Sqlcmd in Azure

Azure, PowerShell
I'm working on a series of Powershell scripts for the pre-conference seminars on Windows Azure SQL Database that I'm going to be giving at TechEd and the PASS Summit. One of the things I want to do is script out a full backup process (one that's consistent with transactions) which means I need to create a database copy. There's a nice neat T-SQL command that does this: CREATE DATABASE ADW_COPY AS COPY OF nmhdhskcew.AdventureWorks2012; So, being the lazy Powershell freak that I am, I figured I'd just pass it in with Invoke-sqlcmd as a single line like this: Invoke-Sqlcmd -ServerInstance $server -Database $db -Username $user -Password $password -Query $sql Which resulted in an unexpected error: Invoke-Sqlcmd : The CREATE DATABASE statement must be the only statement in the batch. Well, now I'm curious.…
Read More

Azure does Powershell too

Azure, PowerShell
Or, I guess it might be more appropriate to say that Powershell does Azure. Regardless, there are a set of commandlets for Azure and Azure SQL Database. Here's the link to get you started and the basic documentation. After that, it's a little tricky to know for sure what's required. Follow the instructions on the link to get the basic set up done, and then I'll show you just a little bit about how you can manage your Windows Azure SQL Database through PowerShell. First up, you need to set up a context, which is basically a connection to your Azure server. This requires very specific objects. The code below outlines what you need: $SQLLogin = new-object System.Management.Automation.PSCredential("UserName", ('12345' | ConvertTo-SecureString -asPlainText -Force)) $context = New-AzureSqlDatabaseServerContext –ServerName 'MyAzureServer' -Credential $SQLLogin…
Read More

Experimenting with Social Media

Professional Development
After multiple talks with Jason Strate (blog|twitter) and Denny Cherry (blog|twitter) about how they are working on automating more of their blogging and social media interactions, I'm going down that road... a little ways. The first thing that I'm trying out is Pocket. It's a way to capture web sites that you want to read later. You don't want to have to try to manage book marks in your browser. They all suck (and Google Chrome on the Mac sucks even worse) in this task. So this app lets you quickly mark a site and tag it for reading later. Further, you have an account that you can access from multiple devices. I'm already trying out two different readers that will work with Pocket on the Surface and I already…
Read More

SQL University: SQL Azure & PowerShell

Azure, PowerShell, SQLServerPedia Syndication
Welcome once more to the Miskatonic branch of SQL University. I see that most off you survived out last encounter… uh, class. Most of you even appear somewhat sane… somewhat. Today we’re going to talk about PowerShell and it’s use with SQL Azure. Which sounds a something like an eldritch horror breaking in from some outer dimension… hmmm… that could explain certain things… So, back to Powershell and SQL Azure. You can’t run Powershell from SQL Azure. Thank you for attending and enjoy your trip home. Still here? I did try. Let’s clarify. You can’t run PowerShell from SQL Azure, but I didn’t say that you couldn’t use SQL Azure as a target for PowerShell. It is possible to connect to your SQL Azure databases running PowerShell from a local…
Read More

Powershell to Automate SQL Compare

Tools
A question came up on the SQL Server Central Forums, how could you use Red Gate SQL Compare to automate creating scripts for all your databases at once. What’s the first thing that popped into my head? Yep, Powershell. SQL Compare Pro comes with a command line utility that allows you to automate just about everything you can do with the GUI. I like to use it when I’m setting up deployment mechanisms for systems because it makes one click deployments possible. I won’t go into all the methods of that type of automation here. For a lot more information on that topic, check out the Team-Based Development book. If you’re interested in exploring how you can use the command line, just type this: [sourcecode language="sql"]sqlcompare/?[/sourcecode] Now is a good…
Read More