Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

Azure does Powershell too

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

Nothing to it really. You create the PSCredential, your WASD SQL login, proving your user name and password (and no, that’s not my user name, and I have a much more secure password, going all the way to 8). Then you set the context, passing it your Server Name and the PSCredential you just created. That’s it. Now you’re connected to your Azure account and get up to all sorts of deviltry. Well, currently you can mainly manage the system, adding & dropping databases, servers and firewall settings. For example, this will create a new database:

$db = New-AzureSqlDatabase -Context $context -DatabaseName 'MyNewDB' -Edition Web

Nothing to it. And this will delete it:

Remove-AzureSqlDatabase -Context $context -Database $db -Force

Easy stuff. Just don’t forget the -Force property or you’ll have a window popping up asking “Are you sure Private Pyle? Which side was that Private Pyle?” You can also pull down information about the database:

$db.CreationDate
$db.Edition
$db.MaxSizeGB

Now, you want to run queries and other stuff, then you’re back to relying on Invoke-SqlCmd, but that works too. No reason you can’t start combing these commands together. Powershell on Azure quickly starts to open up possibilities for automation of your management. Further, like all things Azure, Microsoft is constantly working on making it better and better.

For more Powershell, WASD, database management, Azure VMs, tuning, high availability and the rest, be sure to register for the precon I’ll be doing with Dandy Weyn (b|t) and Thomas LaRock (b|t) at TechEd 2013 North America and TechEd 2013 Europe in just a very few weeks. It’s going to be fun stuff and we might teach you a little too.

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...