Skip to content

Category: Presentations

Managing Ola Hallengrens scripts with Powershell

I presented for the PASS Powershell Virtual Chapter on June 15th, 2016
It was an online session and it was recorded, the youtube video of it is here

I figured out what was wrong with the two little demo fails.
I was pressing F8 to only execute my selection but because of a CLS I didn’t see the error message.
That function that I use to load a script from a relative path doesn’t work when you press F8, only when you run the whole script with F5
Apparently F8 gives you a different execution context.

And the reason why the Excel demo didn’t produce 2 paths is because I specified the -SHOW option in the first one, the second export option failed because the excel file was in use by Excel itself

The slides and scripts can be downloaded here

Leave a Comment

Building a Monitoring System with Powershell Remoting

For people who have seen my presentation and are interested to play with the demo code.
Please find the PDF of the presentation and the demo scripts here

Pre-Requisites:

  • Create a database called BlitzOutput and run sp_blitz, make sure it safes the result in a table called spBlitz like this:

exec sp_blitz
@OutputDatabaseName =’BlitzOutput’,
@OutputSchemaName =’dbo’,
@OutputTableName =’spBlitz’

You can get sp_blitz and lots of other cool stuff from http://brentozar.com/blitz/

  • Unpack the 7z file to c:\remoting
  • Create the DBA database with tables, demo entries and sp’s with the db_database.sql script
  • Run Enable-PSRemoting in a Powershell window under admin rights to be able to remote to the localhost

Now you should be able to go through the 4 Demo scripts that I showed which are in the c:\remoting directory.

Let me know if you have any question, I welcome any feedback…
My contact details are in the PDF of the presentation which is in the zip file.

Leave a Comment

SQL Server 2014 PreCon at SQLSaturday Exeter 2014

With just a couple of weeks to go I wanted to emphasise something that is not completely clear from the title and abstract.
It says that the session is for DBAs but that title is not complicated enough, it should read something like :

“What’s new in SQL Server 2014 for (Involuntary) DBAs, (DevOps type) Developers, Large Site DBAs and ‘Every Other Size Site, for instance Standard Edition only with just a few servers’ DBAs, basically everyone except for BI people who want to see lots of BI content”

But I assume that this title would have been deemed too long…
So, why casting such a wide net?

Well, that’s because I love discussions between the various types of people in roles I just mentioned and I respectfully think that my upcoming pre-con is perfect for that. I think DBAs should be interested in Dev features and vice versa and while it’s good to know what the power of Enterprise Edition brings, with the price difference these days I see more and more sites trying to get the most out of Standard Edition. During the day I will show what’s advertised, namely what is new in SQL Server 2014 but I will make sure that the material will teach developers about the use of certain DBA features and so on.

What will we be talking about ?

Always On – Failover Clustering & Availability Groups
What’s the difference? Can you combine them? Are AG’s the ultimate scale out mechanism for Developers?
I will show you how it works and we’ll talk about the good stuff and the interesting challenges. I’m also going to explain a bit about what a Quorum is and what has changed in this area because I see it causing a lot of confusion sometimes.

In-Memory OLTP, aka Hekaton.
If you’re thinking that SQL Server has had a caching mechanism forever and your hot pages should be In-Memory already then you are right. This feature is a lot (a whole lot) more than that and not to be mistaken with a re-incarnation of DBCC PINTABLE. 

Updatable Clustered Column store Index
If you have studied how the compression mechanism behind Column Store works you are probably wondering how it can suddenly be updatable?

Buffer Pool Extensions
Have a part of your buffer pool on fast disks. 
We will take a look at how this works and we will talk about why you would use this
(You’re probably thinking “Why would you not just buy more memory?”)

Resource Governor for IO.
With RG you can control CPU, Memory and now also IO. I’ll demo how this works, and we’ll talk about the various scenarios where this would be useful. And also discuss where you could be shooting yourself in the foot.

Improved Cardinality Estimator.
This this will make better query plans for you. I have a few samples and will explain why poor cardinality estimations could result in a query plan. Don’t expect me to explain how the engine was improved under the covers.

Separation of Duties enhancements. 
I’ve heard this so many times, “can we prevent the DBAs from seeing the data”. BOL seems to suggest that that should be finally possible. We will talk about wether this is actually the case. (the short answer is no!) It is a step in the right direction though and if you combine this with some other settings could give you what you are looking for. 

Sysprep Enhancements.
I have yet to see a site where Sysprep was actively used to roll out servers. These enhancements might just do the trick though. 
Lazy developers should also pay attention to this one 🙂

Windows Azure related features.
We’re going to have a lot of laughs with these ones. 
Would you consider running your instance on your own in-house server while placing the data and log files in Azure? Me neither, but why does this feature exist? We’ll look into it and talk about use cases where it does make sense.
There’s more: Backup to Azure and even “fully automagically backing up whenever needed to Azure” are also interesting features to say the least

I want to get to my personal favourites without dragging this blog post on too long so here’s what else I’ve got something to say about:

  • sys.dm_exec_query_profiles
  • parallel select into
  • Delayed Durability
  • Backup Encryption
  • Rebuilding a single partition online
  • Managing the lock priority of online operations
  • Incremental Statistics

Last but not least: Windows Server 2012R2!
Huh, I thought this was a day about SQL Server?
Well, as it turns out, some of my potentially favourite features of SQL Server are actually Windows Server 2012 R2 features. If you don’t have Enterprise Edition and/or an expensive SAN you may be somewhat jealous at all the cool stuff you can do regarding HA & DR for instance. Let me show you how Data Spaces in combination with Clustered Shared Volumes and Storage Tiering let’s you store any SQL Server version on highly available network shares (!) and how it automatically will place the most used portions of your datafiles on the fastest storage (!!!) 
Needless to say that I leave it up to you if you really want to be the first one to use this in production but that’s not what this day is about…
If we have time we will look at some other R2 features that can be useful icm with SQL Server.

And of course, we’ll end the day just as last year, if the hotel will let me, with a Q&A where a round of pints is on me! 😉
Oh, and did I mention that you can win an exam voucher and other cool stuff ? Sign up here, do it now.

Leave a Comment