Blog

SQL Server with Mr. Denny

Releasing a Page Blob Lease in Azure

2478229521_f40dbba2b4_bSometimes when firing up VMs or moving VMs from the page or blob store you’ll get an error that there is still a lease on the file.  To solve this you need to release the lease. But waiting won’t do the trick, as the leases don’t have an expiration date.

I found some VB.NET code online that with some tweaking (with the help of Eli Weinstock-Herman and Christiaan Baes) I was able to get to release the lease.

The first thing you’ll need is Visual Studio 2015.  You’ll also need the Azure Storage Client.

Once those are both installed you need to create a new VB.NET project.  I used a command line app.

Then put this code in the app. Replace the placeholders that I show in {} with the actual values from your Azure account. Then compile and run the code. The lease will be released.

Imports Microsoft.WindowsAzure.Storage.Auth
Imports Microsoft.WindowsAzure.Storage.Blob
Imports Microsoft.WindowsAzure.Storage
Module Module1

Sub Main()
Dim Cred As New StorageCredentials(“{StorageAccount}”, “{StorageAccountKey}”)
Dim sourceBlobClient As New CloudBlobClient(New Uri(“http://{StorageAccount}.blob.core.windows.net/”), Cred)

Dim sourceContainer As CloudBlobContainer = sourceBlobClient.GetContainerReference(“{ContainerName}”)

Dim sourceBlob As CloudPageBlob = sourceContainer.GetBlobReferenceFromServer(“{FileName}”)

Dim breakTime As TimeSpan = New TimeSpan(0, 0, 1)

sourceBlob.BreakLease(breakTime)

End Sub

End Module

Sadly, short of doing this I haven’t been able to find an easier way of doing this.

Denny

The post Releasing a Page Blob Lease in Azure appeared first on SQL Server with Mr. Denny.

But What about Postgres?

What About Postgres? Since I wrote my post yesterday about Oracle and SQL Server, I’ve gotten a lot of positive feedback (except for one grouchy Oracle DBA) on my post. That said, I should probably stay clear of Redwood Shores anytime soon. However there was one interesting comment from Brent Ozar (b|t) Screen Shot 2016-08-20 at 12.05.36 PM While Postgres is a very robust database that is great for custom developed applications, this customer has built a pretty big solution on top of SQL Server, so that’s not really an option. multiple-cords-in-one-outlet However, let’s look at the features they are using in SQL Server and compare them to Postgres. Since this a real customer case, it’s easy to compare. 1. Columnstore indexes—Microsoft has done an excellent job on this feature, and in SQL Server 2016 new features like batch mode push-down drive really solid performance on large analytic queries. Postgres has a project for columnstore but it is not developed. There’s also this add-on feature https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/ which does not offer batch execution mode performance enhancements and frankly offers extremely mediocre performance. You can compare this benchmark: https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout to the SQL Server one:
SQL Server 2016 posts world record TPC-H 10 TB benchmark
2. Always On Availability Groups—In this system design we are using readable secondaries as a method to deliver more data to customers. It doesn’t work for all systems, but in this case it works really well. Postgres has a readable secondary option, but it is far less mature than the SQL Server feature. For example, you can’t create a temp table in a readable secondary. 3. Analysis Service Tabular—There is no comparison here. Postgres has some OLAP functions that are comparable to windowing functions in T-SQL. Not an in-memory calculation engine. 4. R Services—You can connect R to Postgres. However, SQL Server’s R Services leverages the SQL Server engine to process data, unlike Postgres which uses R’s traditional approach of needing the entire dataset in memory. Once again, this would require a 3rd party plug-in in Postgres 5. While Postgres has partitioning, it is not as seamless as in SQL Server, and requires some level of application changes to support. https://www.postgresql.org/docs/9.1/static/ddl-partitioning.html While I feel that SQL Server’s implementation of partitioning could be better, I don’t have to change any code to implement. 6. Postgres has nothing like the Query Store. There are data dictionary views that offer some level of insight, but the Query Store is a fantastic addition to SQL Server that helps developers and DBAs alike 7. Postgres has no native spatial feature. There is a plug-in that does it, but once again we are making an even bigger footprint of 3rd party add-ins to manage. Postgres is a really good database engine, with a rich ecosystem of developers writing code for it. SQL Server on the other hand, is a mature product that has had a large push to support analytic performance and scale. Additionally, this customer is leveraging the Azure ecosystem as part of their process, and that is only possible via SQL Server’s tight integration with the platform.

Please, Please Stop Complaining about SQL Server Licensing Costs and Complexity

Recently, I’ve seen a number of folks on twitter and in the blogosphere complaining about the complexity of SQL Server licensing. While it is a slightly complicated topic and nowhere near as simple as Azure SQL Database (need more perf? Spend more $€£), there are other products in our space like Oracle and SAP that make licensing SQL Server look like a piece of brioche.   german_sailing_grand_prix_2006_oracle-2   And while talking about vendors who spend your hard earned licensing dollars on racing sailboats and MIG fighter planes, through a recent project, I’ve had the opportunity to make a direct comparison between the licensing cost for SQL Server and Oracle. These numbers are not from quotes (list price), however this is a real customer of mine, and the features they use. This customer was an early adopter of SQL Server 2016, and uses MANY of the features in the product. Most of which are cost options in Oracle.  
SQL Server Oracle
Core Engine (16 cores)  $109,980.00 Database Engine  $380,000.00
Compression Advanced Compression  $92,000.00
Columnstore Database In-Memory  $184,000.00
Analysis Tabular OLAP  $184,000.00
R Services Advanced Analytics  $184,000.00
Partitioning Partitioning  $92,000.00
Query Store Tuning Pack  $40,000.00
Spatial Spatial  $140,000.00
Availability Groups Active Data Guard  $92,000.00
Total  $109,980.00  $1,388,000.00
When I see those numbers in Microsoft marketing slides, I sometimes wonder if they can be real, but then I put these numbers together myself. Granted you would get some discounts, but the fact that all of these features are built into SQL Server, should convince you of the value SQL Server offers. Pricing discounts are generally similar between vendors, so that is not really a point of argument. If you are doing a really big Oracle deal you may see a larger upfront discount, but you will still be paying your 23% support fees on that very large list price. (Software Assurance from Microsoft will be around 20%, but from a much lower base) Additionally, several of these features ae available in SQL Server Standard Edition. None of these features are in Oracle’s Standard Edition.

Making Azure PowerShell Scripts Work in PowerShell and As RunBooks

Runbooks are very powerful tools which allow you to automate PowerShell commands which need to be run at different times.  One of the problems that I’ve run across when dealing with Azure Runbooks is that there is no way to use the same script on prem during testing and the same script when deploying. This is because of the way that authentication has to be handled when setting up a runbook.

The best way to handle authentication within a runbook is to store the authentication within the Azure Automation configuration as a stored credential.  The problem here is that you can’t use this credential while developing your runbook in the normal Powershell ISE.

One option which I’ve come up with is a little bit of TRY/CATCH logic that you can put into the PowerShell Script, which you’ll find below.

In this sample code we use a variable named $cred to pass authentication to the add-AzureRmAccount (and the add-AzureAccount) cmdlet. If that variable has no value in it then we try get call get-AutomationPSCredential. If the script is being run within the Azure Runbook environment then this will succeed and we’ll get a credential into the $cred variable. If not the call will fail, and the runner will be prompted for their Azure credentials through an PowerShell dialog box box. Whatever credentials are entered are saved into the $cred variable.

When we get to the add-AzureRmAccount and/or the add-AzureAccount cmdlets we pass in the value from $cred into the -Credential input parameter.

The reason that I’ve wrapped the get-AutomationPSCredential cmdlet in the IF block that I have, is so that it can be run over and over again in PowerShell without having to ask you to authenticate over and over again. I left the calls for the add-AzureRmAccount and add-AzureAccount inside the IF block so that it would only be called on the first run as there’s no point is calling add-AzureRmAccount every time unless we are authenticating for the first time.

if (!$cred) {
try {
[PSCredential] $cred = Get-AutomationPSCredential -Name $AzureAccount
}
catch {
write-warning ("Unable to get runbook account. Authenticate Manaually")
[PSCredential] $cred = Get-Credential -Message "Enter Azure Portal Creds"

if (!$cred) {
write-warning "Credentials were not provided. Exiting." -ForegroundColor Yellow
return
}
}

try {
add-AzureRmAccount -Credential $cred -InformationVariable InfoVar -ErrorVariable ErrorVar
}
catch {
Clear-Variable cred
write-warning ("Unable to authenticate to AzureRM using the provided credentials")
write-warning($ErrorVar)
return
}

try {
add-AzureAccount -Credential $cred -InformationVariable InfoVar -ErrorVariable ErrorVar
}
catch {
Clear-Variable cred
write-warning ("Unable to authenticate to AzureSM using the provided credentials")
write-warning( $ErrorVar)
return
}
}

You’ll be seeing this coming up shortly as part of a large PowerShell script that I’ll be releasing on Git-Hub to make live easier for some of us in Azure.

Denny

The post Making Azure PowerShell Scripts Work in PowerShell and As RunBooks appeared first on SQL Server with Mr. Denny.

1 2 3 308

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.