PowerShell to Simulate Load

PowerShell
Gathering metrics is quite difficult if there are no queries. So, if you're working in non-production environments, but you still want to see some sort of load on the server, how can you do it? I use a simple PowerShell script to simulate load. Simulate Load I've posted a sample of the meat of the script before. It's a simple way to test a procedure. However, I've modified it to do a little more than just run the procedure forever. Here's the script: # connect to the database $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = 'Server=WIN-8A2LQANSO51;Database=AdventureWorks2017;trusted_connection=true' # gather values $RefCmd = New-Object System.Data.SqlClient.SqlCommand $RefCmd.CommandText = "SELECT th.ReferenceOrderID, COUNT(th.ReferenceOrderID) AS RefCount FROM Production.TransactionHistory AS th GROUP BY th.ReferenceOrderID;" $RefCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $RefCmd $RefData = New-Object System.Data.DataSet…
Read More

Automating Automatic Indexing in Azure SQL Database

Azure
I've been in love with the concept of a database as a service ever since I first laid eyes on Azure SQL Database. It just makes sense to me. Take away the mechanics of server management and database management. Focus on the guts of your database. Backups, consistency checks, these easily automated aspects can just be taken care of. Same thing goes with some, not all, but some, index management. Azure SQL Database can manage your indexes for you. I call it weaponizing Query Store. Anyway, I needed a way to automate this for the book I'm writing. I couldn't find any good examples online, so I built my own. Queries in Need of Automatic Indexing Because I want this to be as simple and repeatable as possible, I'm using…
Read More

Azure, PowerShell, Databases and the PASS Summit

Azure, PowerShell
I am quite honored to say that I am speaking at the single largest and most important Microsoft Data Platform conference in the world, PASS Summit 2017. Now, I'm excited to say that I get to speak on three topics that are near and dear to my heart, Azure, PowerShell and databases. The session is called Using PowerShell to Manage Your Azure Databases. You can read the abstract there at the link. I'm going to go through a whole bunch of the database types on Azure and show how you can manage each. I'm not sticking to SQL Server. We'll cover MySQL, Cosmos and others. Let's get together and talk about these topics. Azure adoption is growing like mad. You're going to need to automate it, so PowerShell is your…
Read More

PASS Summit 2017

Azure, PowerShell
Don't you want to go to the single largest collection of Microsoft Data Platform professionals and developers on the planet? Sure you do. PASS Summit 2017 is coming up. Now is the time to register for this unique event. This year I've been honored to be able to present a session at the PASS Summit. It's on three subjects that are very near and dear to my heart, automation, PowerShell, and the Azure data platform. The session is called Using PowerShell to Automate Your Azure Databases. It covers a lot of material from controlling your Azure SQL Databases to creating Azure SQL Data Warehouse databases to automating maintenance. We'll even get your Azure PowerShell installed & working locally. I'm very excited about this and I sure hope to see you…
Read More

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

Export All Plans From Cache to a .SQLPLAN File

PowerShell
I was asked if there was an easy way to export all the plans from cache to a .SQLPLAN file. My immediate answer was, "I'm sure there's a PowerShell script out there somewhere." However, rather than do a Gingle search, I figured I'd quickly knock up an example. The Script I've gone minimal on the script. I'm creating a connection to the local instance, defining a command, and returning the data into a data set. From there, since the data set consists of a single column, I'm walking through them all to export out to a file: $Query = 'SELECT deqp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp WHERE deqp.query_plan IS NOT NULL;' $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = 'Server=ServerX\DOJO;Database=master;trusted_connection=true' $PlanQuery = new-object System.Data.SqlClient.SqlCommand $PlanQuery.CommandText = $Query $PlanQuery.Connection…
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

Using PowerShell to move files to Azure Storage

Azure, PowerShell
I was searching all over the place to try to find out how to move files into Azure Storage. Most of the examples I could track down were for API calls through C#. There were very few examples using PowerShell, and most of those were for older versions of Azure. But, the one thing I've learned about Azure, it's a fast moving target. Sure enough, in March there were new PowerShell blob cmdlets released. This is great news. I was able to get this to work: [System.Reflection.Assembly]::LoadFrom("C:\Program Files\Microsoft SDKs\Windows Azure\.NET SDK\2012-10\bin\Microsoft.WindowsAzure.StorageClient.dll") $account = [Microsoft.WindowsAzure.CloudStorageAccount]::Parse("DefaultEndpointsProtocol=https;AccountName=mystorage;AccountKey=mykey") $client = [Microsoft.WindowsAzure.StorageClient.CloudStorageAccountStorageClientExtensions]::CreateCloudBlobClient($account) $blob = $client.GetBlockBlob('container\adw.bacpac') $blob.UploadFile("c:\bu\adw.bacpac") But let's face it, this code makes more sense and is a heck of lot easier to write: $context = New-AzureStorageContext -StorageAccountName mystorage -StorageAccountKey mykey Set-AzureStorageBlobContent -Blob "adw.bacpac" -Container…
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