It’s 8 days until SQL Saturday South Island in Christchurch, and apart from this guy who still has to write his presentation – the event is going to be awesome.  SQL Saturday South Island always punches above it’s weight for a SQL Saturday in a small city, and this year is no exception with 4 streams chock full of quality speakers.  We’ve got a good mix including a bunch of MCM\MVP’s, subject matter experts and seasoned international speakers.  But with quality like that comes a dilemma or two…

Case in point…which session do you go to first?  In the Microsoft Room Craig Ryan is taking a must see session on “Tuning SQL Server Standard Edition”.  You wouldn’t want to miss this if you are in an  Enterprise that is cost conscious – that is….pretty much all of them.  With the release of SQL 2016 SP1 Standard Edition is no longer the poor cousin, and even in SQL 2014 with the doubling of RAM limits to 128GB it was capable of handling some pretty impressive workloads.  Not everyone needs Enterprise Edition features, and if you can get the performance levels you need out of an edition that is a third the price then why wouldn’t you?  Craig is one of the smartest SQL guys I know and this session is going to be full of take home advise that will help people tune up their environments and get immediate gains from their Saturday spent learning SQL.  You simply have to go to this session.

Unless…..unless you look in the SQL Services room next door, where you’ll see Reza Rad talking about Security in Power BI.  I’m excited about this session, because Security is usually the last thing that BI folk like to talk talk about.  If I had a dollar for every time I found a linked server some BI guy had set up on a test server that gave sysadmin access to everyone on the production box I’d be a rich man.  Or at least able to afford a couple of decent rounds of drinks.  Sure, some people will tell you that BI is just about pretty colors and pictures(I’m looking at you Victor), but those pretty colors and pictures are increasingly leading business decisions that effect everyone!  BI is so often a race to a deliverable that it’s going to be really interesting to hear Reza’s take on how to factor in security.  Then you take into account that this session is being presented by Reza Rad who is a speaker who’s presented on pretty much every SQL stage in the world and is in demand wherever you go and you’ve got a difficult choice of session starting to form.  You’ll definitely not want to miss Reza’s session…..

…unless you look behind door number 3.  We originally had Nabeel Derhem lined up for a session on “A Practical Introduction to Machine Learning”.  Nabeel is a former Microsoft MVP who now works for Microsoft and was going to take us through the skillsets needed to get going with interesting Machine Learning.  Unfortunately, due to some travel issues which are probably Trumps fault Nabeel wasn’t able to make it, but we’ve been lucky enough to have Nick Burns pick up exactly the same topic.  I work with Nick and can say without any doubt that this session is going to be brilliant.  Why?  Machine learning is just an awesome topic anyway, and I love Nick’s take on it’s practical applications…and also his healthy skepticism about it’s use.  It’s easy to get caught up in the glossy sales pitches around what Machine Learning can offer, but like everything it’s also important to step back and consider the implications of what happens if it’s implemented poorly.  Nick’s going to talk about how easy it is to get started with Machine learning, and what things you need to understand to apply it in a practical, useful manner.  He’s got a brain the size of a planet, and a grin that would put any Jahovas Witness to shame.  You simply cannot afford not to go to this session.

Unfortunately I’ll miss all 3 of those sessions because I’ll be sitting by myself in the Jade Software room wondering if nobody actually cares about “The things you absolutely must be doing in your SQL environment!”  I’ve been asked a few times why I usually submit beginner level sessions fr SQL Saturdays.  My answer is pretty simple.  Most of the people who attend a SQL Saturday are beginners.  It does pain me to hear SQL getting blamed for poor performance when 10 seconds of investigation pulls out half a dozen queries written by people who clearly don’t know how a Relational Database works.  It pains me when I do a healthcheck and discover a database running a critical application that has had a corrupt table for 3 years.  It pains me even more when it takes 3 months before I get approval to fix that.  It actually physically hurts when I see backup software that is sold and used around the world that not only takes a transaction log backup to the NUL device at midnight every night but also sells this as a good thing!  In short, with all the information that is out there, it makes me a little sad to see people managing SQL Servers so badly, and worse still not realizing that it is actually their oversights which are causing their applications to run badly, not any fault of the SQL product itself.

So I don’t expect a whole bunch of people to turn up to my session next Saturday.  I know it would be choice number 4 of the 4 that are available for me.  But a smaller group might just work for this session.  Bring your questions and we can have a discussion rather than just listen to me drone on up the front about stuff you should be doing in your SQL environments.  And when I’m finished I’ve got 4 more tough choices between 16 more great sessions.  It’s going to be a good weekend.

At SQL Saturday Melbourne, an otherwise excellent event, my demo failed on me because the DBAtools module failed to load with the following error:

Set-WSManQuickConfig : <f:WSManFault xmlns:f=”http://schemas.microsoft.com/wbem/wsman/1/wsmanfault”
Code=”2150859113″ Machine=”localhost”><f:Message><f:ProviderFault provider=”Config provider”
path=”%systemroot%\system32\WsmSvc.dll”><f:WSManFault
xmlns:f=”http://schemas.microsoft.com/wbem/wsman/1/wsmanfault” Code=”2150859113″
Machine=”<machinename>”><f:Message>WinRM firewall exception will not work since one of the
network connection types on this machine is set to Public. Change the network connection type to either Domain
or Private and try again. </f:Message></f:WSManFault></f:ProviderFault></f:Message></f:WSManFault>
At line:116 char:17
+ Set-WSManQuickConfig -force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Set-WSManQuickConfig], InvalidOperationException
+ FullyQualifiedErrorId : WsManError,Microsoft.WSMan.Management.SetWSManQuickConfigCommand

For a piece of code that had run successfully a dozen times this was of course a VERY annoying error, not least because I set it to run in another window and carried on with my talk, so didn’t really have time to either troubleshoot it or re-run it once I was back.  I had initially believed the problem was due to an earlier deviation I had made from the order I was going to do things and therefore related to execution policy.  But I hadn’t even read the error message properly during the demo because I just wanted to curl up in a corner and cry.

But running it again later I remembered the other change that I had made to my laptop from every other time I had run the code.  I had adjusted my network settings from a static IP address to allow me to connect to the university wireless.  That was a dumb rookie mistake, and I really had no excuse having not re-run my code in the new environment.  My session was right after lunch, so I had all lunchtime to step through my code, and had done so with everything but the particular script that failed.  Lesson learnt the hard way I guess.  Speakers be warned.

Anyway, the error is obviously related to the change in wireless settings.  I was quickly pointed in the correct direction by this post from Matt Wrock who provides the powershell commands needed to set all your network connections to private:

$networkListManager = [Activator]::CreateInstance([Type]::GetTypeFromCLSID([Guid]"{DCB00C01-570F-4A9B-8D69-199FDBA5723B}")) 
$connections = $networkListManager.GetNetworkConnections()
# Set network location to Private for all networks 
$connections | % {$_.GetNetwork().SetCategory(1)}

As the article referenced above explain the implications and manual process better than I could I won’t add additional junk to the internet, except to say that the failure of the import of the DBA tools module appears to be due to a failure of Set-WSManQuickConfig  in the PS-Remoting module.  Also not that making your networks private actually has less security associated with it than having them “Public” which is a bit counter intuitive.  A “Public” network setting means you are on a public network, don’t trust it so security is high, whereas a private network means you trust the network and are prepared to let traffic do a few extra things – like ps-remoting for instance….ahem.

This is the first session which has been so heavily reliant on demo code, normally I do a lot of speaking from a slide deck.  The turnout was really good, but I felt my presentation in general was pretty poor.  It’s much easier to deviate from what you planned when you are talking from a slide deck, because the slide deck brings you back to the intended presentation.  When you are just typing code into a powershell console and someone asks you a question that takes you away on a tangent it’s harder to come back on topic.  In general I think the presentation went a little bit all over the place, which is exactly what people don’t need when they are trying to grasp the fundamentals of using a programming language.  The structure I had in place was good, but my execution on the day was poor.  So apologies to the audience on the day, but hopefully the core message of the usefullness of powershell still got through.  You can run through my demo scripts by downloading them from the SQL Saturday site at:  http://www.sqlsaturday.com/582/Sessions/Schedule.aspx

I walked into the office this morning and got asked a strange question:  “Does a database you detach and re-attach have the same database id before and after the detach?”.  While it may appear that it does in a simple test, it actually doesn’t – it just takes the next available id.  If you detach database id 5 and then re-attach it, it will retake id 5, and this will mostly work – unless other databases have been removed from the server at some point in the past and there are ‘holes’ in the list of available id’s.  So…”How do you force a database to have a specific database id?”

First of all, you can get the current database_ID of all your databases by running:

select name, database_id from sys.databases

What you will likely notice is that the numbers go from 1(master) to roughly the number of databases on your instance. SQL Server assigns the next available database id. What that means is that if you have a clean install database id’s 1-4 will be taken by the system databases and the first database id you create will be 5 and the second 6 and so on. The exception is when you delete\detach a database – this creates a hole which is the next assigned number.  So if you have created databases with ID’s 5-10 and detach the database with id 7, the next database you attach or create will take database_id 7.

The upshot of all this is that if you ever want to assign a specific database id to a new database you need to do the following:

1. If all your database id’s are sequential and the next available number is the id you want – just create the database.
2.  If all your database_id’s are sequential and the database_id is already assigned, detach the database that is currently using it, create the database you want to take that ID and then re-attach the database you dropped.
3. If you want to assign a number that is greater than the currently available database_ids, simply create databases until the next available id is the number you are after, then create the database you want to take that id, then remove all the filler databases you created to get there.

I’m still unsure what the usecase for this is – but now you know how to do it.