07Jan 2019 by Grant Fritchey 4 Comments

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
19Sep 2017 by Grant Fritchey 3 Comments

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
14Aug 2017 by Grant Fritchey 1 Comment

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
13Feb 2017 by Grant Fritchey 2 Comments

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
05Dec 2016 by Grant Fritchey 3 Comments

Presentations for SQL Server Beginners

Azure, PASS, PowerShell, Professional Development, SQL Server, SQL Server 2016
[caption id="attachment_2548" align="alignleft" width="300"] Tired from all those blog posts[/caption] For my final blog post in the #gettingstarted, #iwanttohelp series, I decided to curate a set of presentations from the PASS Virtual Chapters. This content is available online. It's free. Most importantly for the person just getting started as a SQL Server data pro, it's good. I'm going to marry each of the presentations with my eleven blog posts in this series. The Importance of a Full Backup in SQL Server For this one I'm going to recommend Tim Radney's session Understanding SQL Server Backup and Restore. I know Tim personally and guarantee this is a good session. Why Is The Server Slow Jes Borland is a very close personal friend and an absolutely outstanding presenter (and person). She has…
Read More
23Nov 2016 by Grant Fritchey 5 Comments

PowerShell to Test a Query

DevOps, PowerShell, SQL Server, SQL Server 2016, T-SQL
So you want to do some tuning, but you're not sure how to test a query on it's performance. Not a problem. Here's a very rough script that I use to do some recent testing. This script to test a query is post #11 of the #enterylevel #iwanttohelp effort started by Tim Ford (b|t). Read about it here. The Script The goal here is to load a bunch of parameter values from one table and then use those values to run a query to test it. To do this I connect up to my SQL Server instance, naturally. Then I retrieve the values I'm interested in. I set up the query I want to test. Finally a loop through the data set, calling the query once for each value. [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #…
Read More
03Jun 2013 by Grant Fritchey 6 Comments

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
28May 2013 by Grant Fritchey 3 Comments

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
20May 2013 by Grant Fritchey 2 Comments

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
28Apr 2011 by Grant Fritchey No Comments

SQL University: SQL Azure & PowerShell

Azure, PowerShell
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